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!
how to decrease the retrieval time from database
Moderators: chulett, rschirm, roy
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.
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.
Om Prakash
"There are things that are known, and there are things that are unknown, and in between there are doors"
"There are things that are known, and there are things that are unknown, and in between there are doors"
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: how to decrease the retrieval time from database
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]
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]
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.