July 31, 2007 at 12:45 am
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]
July 31, 2007 at 8:12 am
Install the Client Access drivers on the SQL machine and it should work fine. We had linked servers from SQL2K to AS/400 and people could query through SQL Server and get data back from the AS400.
Only issue was that sometimes the AS/400 driver would leak memory and then stop returning full result sets. We would be limited to xx bytes, which made for difficult troubleshooting. Watch for issues with the links, like the DTS failing sometime.
July 31, 2007 at 7:22 pm
I used DTS package to get data from AS400 to SQL Server 2000. It seems to work fine.
August 2, 2007 at 8:27 am
I'll second Steve here and reccpment Client Access and avoid Client Access Express. The full version is worth the extra money in a production environment.
Remeber that there are some T-SQL differences between SQL Server and DB-400. Google OPENROWSET 400.
ATBCharles Kincaid
August 2, 2007 at 8:39 am
I transfer datas from AS400 to SQL Server 2005 via SSIS, it works well but there is a bug in V5R3 that disable the possibility to make SQL querys against AS400.
It is only possible to transfer whole tables.
This bug is known by IBM and the V5R4 fixed this problem. But in my case, it is not planned to install V5R4 on our AS400s, so, I'm in an impass...
http://www-1.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9
August 9, 2007 at 8:28 am
Im doing the same thing. I need to migrate all data from AS400 to SQL, I get to connect to AS400 via Client Access, but at this point im just getting data from QGPL Library, i would like to know how to configure the client access to get data from all libraries.
Thanks!
August 9, 2007 at 9:03 am
It's been a lot of years since I delt with 400's. What I remember is that you should get a library list from your logon. If your libraries are not in the logon list you can specifiy a new library list in Client Access. I remember having to do it but don't remember how. Check the Client Access documentation. I hate saying RTM but sometimes the manual helps.
ATBCharles Kincaid
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy