Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Extract Speed from DB2 Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 25, Visits: 475
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!
Post #1447485
Posted Monday, April 29, 2013 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
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?
Post #1447560
Posted Monday, April 29, 2013 9:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 25, Visits: 475
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.
Post #1447621
Posted Monday, April 29, 2013 12:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1447706
Posted Monday, April 29, 2013 12:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1447712
Posted Monday, April 29, 2013 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 25, Visits: 475
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.
Post #1447713
Posted Monday, April 29, 2013 2:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
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.
Post #1447781
Posted Monday, April 29, 2013 4:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1447814
Posted Tuesday, April 30, 2013 6:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 25, Visits: 475
Thank you Daniel, that was a driver I was looking into as well.
Post #1447977
Posted Tuesday, April 30, 2013 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 25, Visits: 475
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.
Post #1447979
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse