The SQL Guru Answers your Questions...
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.
--Scott W.
|
Scott,
Let me just begin by saying that this question has my three least favorite
topics in it:
1) AS/400's
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,
and
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
table)
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.
Sean
