Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MencitPutih
Starting Member
4 Posts |
 Posted - 2007-07-31 : 02:27:59
|
| 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] |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-31 : 23:22:49
|
| Why delete archived data from sql server? will you move them back to sql server later? The issue is when you need transfer large amount of data to as400, your app may timeout. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-01 : 00:39:59
|
| I don't like the idea of deleting data all over the place either.What about completely warehousing the data in SQL. Keep whatever window of data that you need for the 400 apps to work properly, but also put that data over in SQL Server. You could run a daily job to load your SQL based warehouse so that reporting data is only a day behind (you get the point) You could then point all reporting to the SQL server instead of the 400 and then you don't have to worry about where the data lives and copying stuff back and forth. All reporting would be done off of the SQL Server. simple.-ec |
|
|
|
MencitPutih
Starting Member
4 Posts |
Posted - 2007-08-02 : 05:43:54
|
| Thanks a lot rmiao and eyechart for your kindly replies.rmiao: Yes, i think it's needed to delete data from sql server, since after we use the downloaded data (from SQL to AS400) we make some manipulation on it in AS400. If we don't delete data in SQL in the first place, when we want to transfer back the data from AS400 to SQL (archived it back), there will be data duplication? (since i only want to kept data from now to 4 years back from now)eyechart: it seems changing server from AS400 to SQL it's not an option, since our main goal is only "freeing" some AS400 capacity so it will work faster and have more memory capacity, so we don’t have to upgrade the AS400 memory. |
|
|
|
|
|
|
|
|
Transferring data from AS400 to SQL Server