DSXchange

hi,

I'm using db2 udb api stage for selecting columns from a table.

The time taken is too high.the job is running for more than half an hour and it is getting delayed because of one table.

select ..... from ...with ur is my query.

all other tables are retrieving quickly but only two tables are creating problems.

what is the error .or how to decrease the retrieval time.

in one stage i'm using left outre join on two tables.
in another one simple fetch from one table.

can anyone suggest some solution for this.

thanks!

use DB2 EE stage for extraction, which would definitely be very fast as it retrives data on all the different nodes from DB2.
Also check the DB2 join you are doing ask your DBA to check how costly is the join. Joins can be expensive on if not executed properly.

If the expected output is 10 rows, then your problem is now the time it takes to fetch and move the data out of the database, but the time to construct the result set. Your solution is to tune the SQL.


If the expected output is 100000000 rows, then your problem is probably the fact you're moving a lot of rows out of the database and across the network. Your solution is to adjust the SQL to insure that maximum data output can occur using the DB2 EE stage.

I agree with kcbland. Best solution tune your SQL. Or else dump the each table data into different datasets. Then perform joins in datastage.

Thats how helped us a lot

[quote="sravanthi"]hi,

I'm using db2 udb api stage for selecting columns from a table.

The time taken is too high.the job is running for more than half an hour and it is getting delayed because of one table.

select ..... from ...with ur is my query.

all other tables are retrieving quickly but only two tables are creating problems.

what is the error .or how to decrease the retrieval time.

in one stage i'm using left outre join on two tables.
in another one simple fetch from one table.

can anyone suggest some solution for this.

thanks![/quote]

Proper indexing of the tables may also be a factor.

True. Make sure you have indices set up on the joining keys. I have successfully joined four tables (left outer join queries) along with data validations (lookups) in the user defined sql that retrievs millions of records in less than 10 seconds with 20+ columns.