DTS FROM SQL Server 2000 to IBM DB2

[jsql12]

Hi all,

I've set up a DTS to export a table from SQL Server 2000(SP3) to an IBM AS400 box that is running a DB2 database. The DTS uses an ODBC connection (ibm iseries for windows) to connect to DB2. The problem I'm having is the DTS runs very slowly. It takes about 20 sec to process 1000 records which is way unacceptable because we're looking to transfer data (1 million + records)back and forth between the two servers on a regular basis. If anybody has any suggestions on how I can improve this process, please let me know.

Thanks a lot!

jsql12

 

[RiverGuy]

The first thing to check would be if you get good response time on your network from the SQL Server to the AS400. Ping the AS400 from the SQL box and see what you get. Second thing would be to have the AS400 admin check the job which handles the ODBC connection to make sure it's set to an adequate priority. Also, have you tried any other ODBC/OLEDB drivers? If you are using SQL Server Enterprise Edition, there is a Microsoft written driver for DB2 which may perform better. I'm not sure if it works with SQL2000 or only 2005, but it's worth a look.

 

[jsql12]

Thanks RiverGuy for taking the time to answer my question. We are getting actually good response time from the IBM server. We have standard edition installed in the SQL 2000 box and it didn't have the drivers for IBM that's why I installed the IBM iseries for Windows to use the odbc connection to IBM. I herad the OLE Db provider for BM is actually slower than ODBC. Do you have any provider in mind that might help with the performance?

Thank you.

 

[RiverGuy]

There is a Microsoft DB2 Provider, but as I recall, it's only available to customers who are using it with an Enterprise Edition license. I haven't heard the OLEDB driver would be slower--you would think it would be faster. It's worth a try.

I had similar problems in the past, but it was pulling records from AS400 to SQL Server. It would take way too long to transmit the records, and the AS400 would need to be rebooted to fix it temporarily. We never found the root problem as the AS400 was eventually replaced and the new one didn't have any issues.

By the way, how wide are the rows you are transmitting? If this is a lot of data, then maybe the performance is not out of line.

 

[jsql12]

I checked our SQL Server 2005 instance (standard Edition SP2) but I didn't see the the microsoft Ole db provider for IBM UDB DB2. Like you said, it might be available on the SQL server 2005 Enterprise Edition. I have installed the OLE DB provider for IBM but it doesn't seem to work for some reason. I'm getting the following error:
CPF4328: Member TableName not journaled to journal *N.
I have absolutely no clue what it means as it seems to be an IBM UDB error.
Please let me know if you have an idea about it.

Thanks.

jsql12

 

[RiverGuy]

I'm not sure. You might be able to get some more tips in the DB2 or RPG forums (RPG tends to get a lot of iSeries posts).

 

[jsql12]

Thanks again. I will check their posts.