×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DTS FROM SQL Server 2000 to IBM DB2

DTS FROM SQL Server 2000 to IBM DB2

DTS FROM SQL Server 2000 to IBM DB2

(OP)
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

RE: DTS FROM SQL Server 2000 to IBM DB2

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.

RE: DTS FROM SQL Server 2000 to IBM DB2

(OP)
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.

RE: DTS FROM SQL Server 2000 to IBM DB2

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.  

RE: DTS FROM SQL Server 2000 to IBM DB2

(OP)
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

RE: DTS FROM SQL Server 2000 to IBM DB2

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).

RE: DTS FROM SQL Server 2000 to IBM DB2

(OP)
Thanks again. I will check their posts.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close