Today's question comes from Scott W.:
I have an AS/400 application that does all the processing and holds the data in DB2 tables and data is changing by the second. There is a need to access this data on a frequent basis via a web page by our customers. The AS/400 does have web-serving capabilities but does not do well with interactive processes.
My thought is pull this data into a SQL table and let IIS handle the web requests thus freeing up the AS/400 from web processing.
Is there a way to automatically update a SQL table from an external ODBC compliant database? If so what would be the best way to automatically do this (every X minutes) so that users don't get errors while trying to view a page on a SQL table that is being updated? Is Access a better choice for this instead of SQL? The Number of records will be roughly 2,000.
Thanks for any input.
Let me just begin by saying that this question has my three least favorite topics in it:
2) cross-platform replication
3) Microsoft Access
I'm not sure what I did to deserve this, but here goes! ;)
|My thought is pull this data into a SQL table and let IIS handle the web requests thus freeing up the AS/400 from web processing.|
Yep, this is a pretty good way to do it.
Just out of curiosity, have you tried the web serving on the /400? I'm curous as to what kind of performance you're getting. How much web traffic are you taking (simultaneous connections)?
|Is there a way to automatically update a SQL table from an external ODBC compliant database? If so what would be the best way to automatically do this (every X minutes) so that users don't get errors while trying to view a page on a SQL table that is being updated?|
You have two different problems here:
1) sucking the data off of the AS/400 without causing concurrency problems,
2) processing the data changes without causing concurrency problems.
Let's look at problem #1 first...
You didn't mention which version of SQL Server you're using, so I'm going to assume SQL 7.0. If you have your heart set on the ODBC drivers, then you can use a DTS (data transformation services) package to pull data off of the /400 into a table on SQL Server. You're going to want to set up a "data pump task" between the /400 data source and the SQL data source.
If you can find an OLEDB driver for the /400 (not sure if it also works with DB/2, but SNA Server now ships with an OLEDB driver that will read VSAM files), then you may be able to set up a linked server (again, SQL 7.0) and pull the data out that way. I prefer this method, because I've found DTS to be a bit flaky.
You'll want to check closely to make sure that whatever you do to pull the data doesn't cause locking problems for your AS/400 application.
Okay, on to #2...
assuming that you have a tablefull of data from the /400, you can process it a couple of ways:
1) delete and re-insert into the production table (the one hit from the web
2) incrementally update the records in the production table based on the imported data
Given the fact that you have 2000 records that you're refreshing, I'd try option #1 first. SQL server shouldn't even blink with that amount of data, so your impact on concurrency should be pretty low.
You can then use
SQLExecutive (pardon, SQL *Agent* in 7.0) to schedule the
data download and refresh.
|Is Access a better choice for this instead of SQL? The Number of records will be roughly 2,000.|
Ordinarily, I would say "run away screaming from Microsoft Access". However, if you're not expecting much web traffic, and the number of records will stay pretty low, then you can probably get away with using Access. The tricky part, though, would be scheduling the data refresh.
|Read Other SQL Guru Questions|