Read DB2 table performance

mukejee · Post by **mukejee** » Tue Nov 25, 2014 6:45 am

Hi Experts,

I am trying to extract a DB2 table which holds 60 million records and i am trying to pull the entire data from the table.

What are the possible options that I should take care to get the best performance.

I am using DB2 connector stage to pull the data ..
Current array size = 1000
Record count = 2000

Its taking more than 20 - 25 minutes to extract the whole 60 million and load a dataset. There is no other stages in between.

Regards
Mukejee

ray.wurlod · Post by **ray.wurlod** » Tue Nov 25, 2014 3:34 pm

First do some diagnosis - create a job that pulls the DB2 table into a Copy stage (which acts as a sink). That will allow you to determine whether the slowness is in the DB2 stage or the Data Set write operation.

Let us know, then we can address the correct problem instead of wasting time.

ArndW · Post by **ArndW** » Wed Nov 26, 2014 1:48 am

You can use the partitioned read method which often speeds up DB2 access, assuming that is your bottleneck and not network performance.

If your source table is partitioned, then use that or partition your read using a non-nullable integer column with sufficient cardinality and the connector's "modulo" method, or create your own virtual column using a DB2 OLAP function.

This will ensure that there are multiple processes reading from DB2 rather than the default single thread and might speed up the reading process .