how to design with optimal performance...........

[ajay.prakash03]

I have to design a job with optimal performance..scenario is..

create tabel NAME1 as slect from sal,eanme...from dept,emp whaere dept.deptno= emp.deptno.

create table NAME2 as select * from NAME1where.

again Create tabel NAME3 as select from tabel2...


like that i have to ran 5 times create table.statement . ane finally select query from last one and put into db2...


anyone let mw know..how i will designfrom performance point of view in server job. OS in unix....target is DB2 and source is ORACLE.

[ray.wurlod]

First, define "performance" in an ETL context.

This may help you to get a better handle on what is meant by "optimal" in this context.

Note particularly that rows/second is an almost entirely meaningless metric, as I have posted on a number of occasions.

[chulett]

I'm curious why you 'need' to create 5 temp tables? I'm sure you could do all these in one SQL statement without necessarily creating scads of temp tables.

[ajay.prakash03]

hi
thanks for quick reply.
it,s client requriment that i have create 5 tem table. finally i have to select the data from last one. the script i got they ghave used so many jons in each query. As i ahve to get data from oracle..finally target is DB2.

let me know how to proceed in server job..

ithink in this way==
oracle stage----->trans---->oracle--------->trans----------------->oracle..last table DB2..

please helpme

[ray.wurlod]

Push back (intelligently and constructive) against illogical client requirements.

[chulett]

ithink in this way==
oracle stage----->trans---->oracle--------->trans----------------->oracle..last table DB2.

Yes, that's what the job would need to look like. Have you tried to create it yet? What part do you help with?

[shamshad]

(1) Please post all your 5 statements clearly, so that we can what exactly
is being done.
(2) How much data is being processed .i.el loaded to DB2 table?
(3) Any other transformation happening in the ETL flow, or just select
and dump data in DB2?

Replies to above will help to answer how to performance tune.

in General:-

If the columns from all your 5 "CREATE STATEMENT" are same, then why not use UNION / UNION ALL at source stage? This way, you will have just 1 SQL source and then no need to have 5 steps.