July 18, 2008 at 4:21 am
Hi,
I am inserting bulk data into DB2 9.5 from SQL SERVER 2005 using SSIS(lot of components involved, so direct export and import is not possible).
It is taking 6 hours to insert 400000 records. How can I reduce the time?
July 18, 2008 at 8:40 am
Information you provided in your post is too little for anybody to come up with any meaningful suggestion.
Having said that, look at the db configuration on DB2 side, check logging, logfile size, bufferpool etc. Check to see if the target tablespace in DB2 is System managed or Database managed, that also can make a huge difference in performance. Lastly, drop all indexes on target table(s) and recreate them once you're finished running your data load.
July 24, 2008 at 1:28 am
Thanks Kaushal..
Actually I tried all what you said and still no progress. Right now, I am using SMS. Should I use DMS?
January 2, 2009 at 1:19 pm
We found the some versions of the DB2 oledb provider don't support the kind of cursor that SSIS wants to use, so a simple insert in an SSIS Dataflow may query all the results from the table first, which can slow it down if the table is large.
Try the Microsoft OLEDB provider for DB2 for comparison
May 6, 2009 at 2:41 pm
I was having a very similar issue pulling data from DB2 and then loading back to db2. Inserting 100K rows would take up to 1hr to insert. I do not use the MS OLE DB2 provider as it has a bunch of issues with server side cursers and SSRS store procedure issues. I use IBM version 8 provider and found the issue to be with the IBM client. I found that if you monitor the SSIS package with Perfmon we saw the pull was very quick but the insert took forever. We also saw that if 10 or more connection were connected to the destination DB2 database then the time increase was exponential.
After working on this for 2 months we found that the db2 mincommit setting had to be set to 1.
"Warning about MINCOMMIT
Do not set the MINCOMMIT DB2 tuning to anything other than 1. The latest version of the db2_tunings.sh script correctly sets the MINCOMMIT parameter to 1. Previous versions of this script set the MINCOMMIT parameter to 25. A setting other than 1 might cause timeouts on update operations and might slow down the performance of these updates."
try looking to see if this might help you.
July 10, 2009 at 2:25 pm
Does anyone have any solution for this? Please help.
I am doing between two SQL Server Databases only. When the package starts it starts fine and in nice speed. As time passes it slows down and chokes.
January 19, 2010 at 8:45 pm
There was a recent fix posted to resolve an incompatbility between SSIS and IBM DB2 provider. The fix is explained here http://support.microsoft.com/kb/975950
The gist is that due to incompatible cursor types, SSIS during validation phase would fetch all the rows from the source table, and besides taking a long time, could exhaust available memory.
It won't fix all problems, but could be helpful for perf issues.
Thx, Jason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply