Solved

Synchronizing tables between AS/400 and MS SQL 2000

Posted on 2004-10-06
6
462 Views
Last Modified: 2012-06-27
Here's my dilemma.  I have a live production database running on an AS/400.  We use an MS SQL 2000 database as a warehouse for that same data so that we can run our reports off of the SQL2000 instead of taxing our aging AS/400.  We probably tax the AS/400 more the way we do it than if we could somehow just run our reports straight from the horse's mouth.

Our vendor has a solution that uses triggers to automatically sync each transaction from the AS/400 to the MSSQL server so that corresponding tables on both servers are always in sync.  It doesn't work anymore for reasons I won't get into.  Their secondary solution (the overtaxing one) is to use DTS packages to copy the tables from AS/400 to SQL.

The tables currently work as such:  Drop table from SQL database, create new table, copy all data from corresponding AS/400 table.

I would love a way to synchronize rather than drop and recreate.  We're talking about 1.5 million rows twice a day here.  I have it all scheduled so it's pretty hands off...but the data is never realtime in this method.  Our vendor's solution requires lots of triggers to be placed on the AS/400 and a client application to run on the SQL server.  We no longer have the AS/400 expertise to do this kind of AS/400 database stuff.

Appending tables won't work, because rows are not just added, the are also changed, and not always in sequence...so if you just added the new rows you might miss changes made to previous stuff.  Is there a way to compare the two tables and only copy data from changed entries?  I can't think of one and I don't think it's possible without the AS/400 triggers...but you people are the experts so I thought I'd pose my question to you.

Sorry this got so wordy...I just don't like having to go back and clarify things a lot.
0
Comment
Question by:mslunecka
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12243261
Ya, avoid the triggers--talk about overhead.

Do you have a natural key on the AS400 tables you can use to identify a row?  Are there any change dates on the AS400 data?
0
 
LVL 6

Author Comment

by:mslunecka
ID: 12243680
Sadly the data on the AS400 is some of the most horrible I've ever worked with.  No primary keys, no natural keys...hell half the tables aren't even in first normal form.  You can see why I don't want to deal with the vendor on this.  They're the ones that designed this rather digusting database.  I inherited this sytem and I'm trying to make the most of it.  Automated DTS packages get the job done...but they just aren't elegant.
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 12243890
Well, if you don't have a natural key, I don't see a way to not truncate and reload every time.  Are there fields that you can form a key out of once it reaches the SQL Server side so you can determine whether to do an update/insert?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 6

Author Comment

by:mslunecka
ID: 12249046
I've worked on that before...but it seems like it would just be a huge waste of comparisons since I can only have the natural key on the SQL Server side.  And if I'm not comparing the entire database (or at least anything dating back a month or so to be safe) I could miss something.

Our AS/400 runs slow during peak usage...so I just can't justify doubling the number of database transactions on it with those triggers, plus network traffic on an already overtaxed network.  I think it's better to just inconvenience people for 5 minutes while I sync tables than to degrade performance all day long.

Maybe someone has a better solution, but I can't see one just now.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12251276
I would ditch the triggers, download the entire set of data at night into a staging table.  Use the "derived natural key" to perform Updates/Inserts.

It doesn't sound like, with the current application, it's going to get much better than that...
0
 
LVL 6

Author Comment

by:mslunecka
ID: 12251517
I'm just going to accept this as a "you can't do that" answer and call it correct.  I can't change the tables on the AS/400...so I can't add a natural key.  I think causing a few minutes of slow down once or twice a day is ultimately a better solution than constantly increasing system overhead.  Especially since we're about to do an OS upgrade that will hurt system performance by a significant amount.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question