Transferring data from AS400 to SQL Server

Last Post 02 Aug 2007 12:45 AM by MencitPutih. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
MencitPutih
New Member
New Member

--
30 Jul 2007 10:02 PM
Hi all,
I am working at insurance company that using AS400 as it main server. All transaction data is already kept for about 10 years (or even more..). This data is growing larger from time to time, and after several upgrades (that cost a lot!), my supervisor has an idea to partly move the data from AS400 to SQL Server. (since the cost for upgrading sql server is cheaper than AS400).

So.. let say, we only want to kept data in AS400 from 4 years before until now (2004 – 2007), and the rest of data is kept in SQL Server.

So first, all transaction data from 1997 – 2003 is transferred to SQL and deleted in AS400.
If user queried data and didn’t found the data in AS400, it will search the SQL, if data is found in SQL, then data is transferred back to AS400 and deleted in SQL.

I’m using SQL Server 2000 DTS (use HiT OLEDB) to transfer the data from AS400 to SQL and vice versa.

I wanna ask if anyone has done this before? What’s the difficulties by using this approach? (btw, I will implement the DTS using user control in vb.net)

Or anyone has a better solution to overcome this problem?

Thanks,
[RU]
MencitPutih
New Member
New Member

--
02 Aug 2007 12:45 AM
Thanks a lot russell for your reply! The application itself is still on design process, there will be plenty room for changes and improvement.

I am trying to delete the archive data in SQL because, after we transfer data(for example, data from year 2000, 7 years before now) from SQL to AS400 (and process or manipulate the data is AS400) the data will be transferred back to SQL (since we only want to keep data in AS400 in range 2004 - 2007). If we do not delete the data in SQL at the first place, after we transfer (archived) data from AS400 to SQL, i'm afraid there will be data duplication. Is there's any better approach?

I don't know yet how big is my data, but there will be approximately 300 millions records.

[RU]
MencitPutih
New Member
New Member

--
05 Aug 2007 05:20 PM
Again, thanks for the kind replies.
The idea of run read only process against SQL db is possible, like what you’ve said, if we need the historical data only for reporting purpose, but I am still discussing this matter with AS/400 system analyst.

My next question is, if we need to modified this historical data is AS/400 (this modification process is not possible in SQL, for example). So, my approach for deleting the data in SQL, transfer to AS/400, modified in AS/400, and transfer back to SQL is right? Or there’s another better or effective way?
(another problem just pop up from my head just now, what happen if different user is requesting same historical data at same time, since he wont find the data, I’ve already deleted it in SQL for the first user that request the data.. I will try disccuss this more with the SA.)

Thanks,
[RU]
MencitPutih
New Member
New Member

--
13 Aug 2007 12:15 AM
.


Acceptable Use Policy
---