August 12, 2010 at 2:23 pm
We are having an issue with data inserting into DB2 using SSIS. We are using the Microsoft OLE DB Provider for DB2 and we are reading from a DB2 staging table to a DB2 destination table that is the exact copy of the staging table. The following are some of the specifics as to what our problem is:
Data Volume: 48 Million
Fields: 757
Problem: Inserting
Through Put: 5-6 rows/sec
When
Fields: 20
Through put was 120 rows/sec
This is way too slow and we are wondering if there are any suggestions to remedy this issue.
August 16, 2010 at 8:07 am
How is the OLE DB Destination component configured? Did you use the fast load option and if yes, how did you configure the options?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 19, 2010 at 11:59 am
With the Microsoft OLE DB Provider for DB2 driver we are finding that the OLE DB Destination Data Access mode does not have a choice for a Fast Load.
August 19, 2010 at 11:56 pm
jessekraut (8/19/2010)
With the Microsoft OLE DB Provider for DB2 driver we are finding that the OLE DB Destination Data Access mode does not have a choice for a Fast Load.
That explains a lot. The OLE DB Destination without fast load is notoriously slow, as it treats the inserts row by row.
(see this article for a comparison: http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/4344.aspx%5B/url%5D)
You should try to find another way to insert your data into db2, in a more set based manner.
Maybe dump the data in a flat file, transfer the file to db2 and read the flat file there?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply