We help IT Professionals succeed at work.

Importing DB2 BLOBs into SQL 2016 database very slow

169 Views
Last Modified: 2018-09-26
We have a DB2 10.5 system (on windows) that we are exporting to a SQL Server 2016 data warehouse on a daily basis using SSIS 2013.  The issue I am running into is that some of the tables in DB2 have BLOB columns (picture data) and when I use SSIS to import that to SQL Server it is very slow.  The main DB2 table has about 25,000 new records per day and it takes about 3 hours to move that from DB2 to SQL Server.

The main bottle neck is that SSIS has to write the BLOB to disk, then read it and store it in SQL Server.  I am averaging about 130 rows per second with both systems on solid state drives.  
 
My SSIS data flow is set up like so...

OLE DB Data Souce connected to DB2 10.5
Data Converison task to change DB2 BLOB datatype to Unicode Text Stream (DT_NTEXT)
Derived Column to fix DB2 date to SQL date issue
OLE DB Data Dest to SQL Server 2016

I had the SSIS BLOB storage on C:\temp at first and that was even slower (about 75 rows per second) and I read a blog about why not to do that.  So I moved the BLOB to a different drive and that about doubled the speed but at 130 rows per second, it takes about 3 hours to process 25,000.

Is there any way to speed this up?  Is there any way to use a memory location to read and write the BLOBs too?

Any suggestions would be much appreciated!

Thanks!!!
Comment
Watch Question

Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT

Commented:
You might want to try using a RAM disk for your temp storage location for this process.  You allocate some memory to emulate a disk drive.  We have used this technique from time to time to improve the performance of certain types of processes like this.
Expert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim YoumansSr Database Administrator

Author

Commented:
Thank you for your suggestions.  I was excited about the RAM disk until I spoke to our server team and they told me that setting up a RAMDISK can be possible on a member cluster, this configuration is not recommended or supported while the cluster service is up.  So that will not work.

As for real time replication from DB2 to staging tables on SQL server, that is something I will look into but I still need to do the initial load in less than a week.

I was hoping to use the DefaultBufferSize and make it large enough (I think 100 MB is the limit) that there would not be a need to spool to disk but from what I can tell, SSIS will always write BLOB data to disk because it cannot tell the size before hand.  Is that correct?

Any other suggestions?
Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT

Commented:
Hi Jim.  Lots of possible solutions, but nothing I can think of that doesn't require some development work.  In a cluster environment, ramdisk is definitely a problem, since it is instance specific.
Jim YoumansSr Database Administrator

Author

Commented:
I have pretty much given up on using SSIS for large tables with BLOB fields.  It runs OK for a bit then starts throwing warnings about locked buffers and slows down to like 300 rows per second.  When you have 55 million rows that is like 48 hours or more.  I had set the buffer size to 100 MB (limit I believe for SSIS) but still hits same issues.  It ends up writing 1,000s of small files to disk no matter what I do.  

Jim
Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT

Commented:
Ultimately, the performance trick is to keep the blob in memory, without an intermediate trip.to disk.

I'm not an SSIS expert, so I'm going to go an alternate route.

I would write an application that retrieves the blob from db2 into a variable, and inserts or updates it directly into SQL server, without intermediate storage to disk.  Assuming there is enough memory available to your app, performance should improve significantly.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.