SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extract Speed from DB2


Extract Speed from DB2

Author
Message
David.Lester
David.Lester
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 492
Hello again all, I am back again.
This time with a new company, new city, and new role!
I am now in a focused SSIS role, on a BI group, with a path toward advanced SSAS work. (yay!)

So here is my first question, anyone have any tricks to improve the speed of data pulls from DB2? These are data dumps from a source system to a staging table set on 2008 R2.
Presently it is using the MS OLE DB2 driver, and pulling very slow, 5-10 minutes for 1 million rows, and over all we are talking close to 100 million rows needed. This is just one of three source systems, the others being either Oracle or SQL Servers, and these other connections extract at far greater speeds, making me less suspect the hardware on the destination server.

Any thoughts?

Thanks tons!
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 2629
This is a hard question to answer because there are so many variables.
Do you have the latest drivers?
Is the pipe, and all the components in between you and the DB2 database big enough to handle your traffic?
Is your query being throttled at the source?
Did you remember to use the fast load setting on your destination?
What packet size setting are you using in your data connection?
David.Lester
David.Lester
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 492
Yes, it is a hard question to answer, that I am aware of.

The drivers are those which come with a SQL 2008 R2 installation, so I would guess not the latest drivers.

I believe the pipe & components between are good. I don't have much info on the DB2 machine itself, but other source systems to this particular server function at much higher rates of through put.

I will have to find out on the throttling at the source, that is one of the things I was wondering about myself.

It is using the fast load settings.

Packet size is set to: I am sure it is set to the default.

Would anyone know if there is a high speed DB2 driver anywhere? I know for example, that for an Oracle system Attunity is a good high speed driver which has helped transfer speed for me in the past.

Maybe a simpler question would be what is a reasonable through put for a data pull from DB2? I have no experience with DB2 elsewhere, my expectations might be too high.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205439 Visits: 41952
We have the same problem. A SELECT directly from a linked server is absolutely horrible. For some reason, an OPENROWSET through the same linked server worked a lot better but is still disappointing.

One thing to make sure of, and this isn't a joke... I'm dead serious, is to make sure that the battery that powers the cache system on the DB2 box (typically an AS400) hasn't run out of juice. It didn't help a huge amount with data transfers but the DB2 system nearly quadrupled in speed because... it finally could use cache.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205439 Visits: 41952
I guess I'll ask a similar question on these thread. Is there any way to bulk export data from DB2 to a Tab delimited file? One of the "fixes" we made for previous large data/long haul problems (between like servers, though) was to do such an export and Fedex the table backup of the export. Compared to an 'over the line' transfer for the same amout of data, it was a lot faster.

I'd like to do the same thing locally but no one here (not even the AS400 guys) know how to export DB2 files (tables) to delimited files.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David.Lester
David.Lester
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 492
Jeff, you always manage to give me such surprising answers.
Battery, hmm, now to find out in the IT hierarchy of this place, whom could check and tell me that.
Oh and yes it is an AS400.
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7646 Visits: 2629
The driver we use is
IBM DB2 for i IBMDASQL OLE DB Provider.
If I am not mistaken there is a download, install, and configuration process to get this driver working. I did not install or configure it so I am not sure. But it seems fairly performant. We have not had substantial issues with it.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205439 Visits: 41952
David.Lester (4/29/2013)
Jeff, you always manage to give me such surprising answers.
Battery, hmm, now to find out in the IT hierarchy of this place, whom could check and tell me that.
Oh and yes it is an AS400.


Heh... it's because I'm 3 days older than dirt and if I haven't seen it yet, it's usually because it hasn't happened, yet. :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David.Lester
David.Lester
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 492
Thank you Daniel, that was a driver I was looking into as well.
David.Lester
David.Lester
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 492
heh, well I am thinking my experiencing oddness levels are going to increase radically. This place is 20 times larger than the last place I was at. I think it is safe to say that increases the odds of weirdness.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search