How to read data parallel from DRS and ODBC stage

pradkumar · Post by **pradkumar** » Sun Jan 18, 2009 10:42 pm

Hi Everyone,

I have a simple that extracts data from DB2 database and loads in to sql server.Iam using DRS stage for both the source and target databases. The job is taking 12 minutes of time to process 2 million of records. I would like to bring down the processing time under 8 minutes . I wonde why but we dont have DB2 EE stage so we have to use either DRS stage or ODBC EE stage .. Even i tried to read the data through the ODBC EE stage but no luck. Could anyone suggest how to read the data parallel from db2 database by using DRS or ODBC EE stage . And my db2 database is not partitioned.

Thanks in Advance

sryarraguntla · Post by **sryarraguntla** » Mon Jan 19, 2009 1:00 pm

Hi,

Use native db2 stage to read data in parallel..

pradkumar · Post by **pradkumar** » Mon Jan 19, 2009 3:13 pm

Hi Thanks for your reply .. But we dont have DB2 native stage to use.. So by using the DRS or ODBC EE stage how to read the data in parallel and improve the performance..

samsuf2002 · Post by **samsuf2002** » Mon Jan 19, 2009 3:27 pm

I think 12 minutes for 2 million rows with i dont knw how many columns but look reasonable. Check the indexing on your source and target tables.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 19, 2009 4:24 pm

pradkumar wrote:But we dont have DB2 native stage to use.

Very doubtful. All stage types are supplied with DataStage. Did "someone" elect not to install them all?

Why not use DB2 Enterprise stage? It can automatically pick up the table's partitioning information and effect parallel SELECT on that basis. Parallel SELECT is not available for non-partitioned tables.

ODBC (including DRS selecting ODBC protocol) does not inherently support parallelism. If you are selecting from a partitioned table, then it may establish multiple connections to the database to read from each partition. But otherwise there are no parallel-read options using ODBC.

chulett · Post by **chulett** » Mon Jan 19, 2009 10:13 pm

pradkumar wrote:So from your suggestion only thing to improve the performance is to use native DB2 stage to read the data.

No, no-one has suggested that. What they have suggested is that the DB2 Enterprise stage inherently supports parallelism, whether that will improve anything or not is a whole 'nuther kettle of fish.

pradkumar · Post by **pradkumar** » Mon Jan 26, 2009 1:40 pm

Hi,

Ray -- My apology for the wrong information i gave .. We do have DB2 stages in the palette but DB2 EE stage is not configured. Perhaps our DB2 tables are not partitioned. My admin is working on configuring the DB2 EE stage . So if i use the DB2 EE stage how will that effect the parallel read (since db2 tables are not partitioned). The job is simply extract and load, i dont understand exactly why its taking 20 mins to load 2 million records ( iam doing truncate and insert) . how to find the bottleneck in the job and any suggestions to improve ..

Thanks