Data transfer - from AS/400 to SQL server

[mikes2nd]

so..

I tried using a ADO to ADO transfer from a AS/400 server to a SQL server. 2.5 million records.

Using transoft ODBC as/400 driver to sql server 2000.

currently doing single record at a time.

Then I tried RDO(as/400) to ADO(sql server) picked up some speed.

I know of the getrows function, and am willing to try it.

Just wondering if anyone know what the fastest way to do this is?

RDO to RDO? Im guessing the bottle neck is the AS/400 side.

no data manipulation going on.

Any else able to bulk load data from As400 to sql server with good speed?

 

[fredericofonseca]

Use SQL Server built in DTS.

You can use this in conjunction with any ODBC/OLEDB provider but I would advise you to you the one supplied with Client Access Express which you should have as part of your AS400 licenses.

On Enterprise Manager create a DTS task, with two connections, one to the 400, and the other to your SQL Server.
Then add a Transform Data Task, select your 400 as source, SQL table as destination, perform whatever transformations you need (I advise you to remove the default link, and create a new one by selecting ALL the fields you need in both source and destination.

On the options tab of the Transform Data Task I would also set up the batch size to 10000. (This value not be the best for your system. TRY IT).


And no the bottleneck is not the 400. It is your VB program and the fact that it is dealing with one record at the time.

Performance on my system is as follows.

AS400 - V5R1- fast machine
10MBits network.
SQL Server 7, on a PIII 500 (4 procs).

800 000 Records, 600 bytes each record.
Batch size set to 9000.

Takes 35 Minutes to transfer data from the 400 to the SQL Server.

If your NT server is faster, speed will be faster also. Network connection speed is not the most important here, but will help if you have a fast machine.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com