Page 1 of 1
How to read data parallel from DRS and ODBC stage
Posted: Sun Jan 18, 2009 10:42 pm
by pradkumar
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
Posted: Mon Jan 19, 2009 1:00 pm
by sryarraguntla
Hi,
Use native db2 stage to read data in parallel..
Posted: Mon Jan 19, 2009 3:13 pm
by pradkumar
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..
Posted: Mon Jan 19, 2009 3:27 pm
by samsuf2002
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.
Posted: Mon Jan 19, 2009 4:24 pm
by ray.wurlod
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.
Posted: Mon Jan 19, 2009 10:13 pm
by chulett
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.
Posted: Mon Jan 26, 2009 1:40 pm
by pradkumar
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