Hi,
I am dealing with tuning a DS job which mainly involves oracle stages.
job contains two oracle stages whose output is clubbed using a funnel stage,written into a sequential file.
the query in oracle stage-1 will resemble
select c1,c2,c3 from t1,t2,t3
the query in oracle stag-2 will resemble
select count(t4.c1),sum(t4.c2) from
(select c1,c2,c3 from t1,t2,t3
)t4
from the datastage logs each of these satges are taking around 1.2hrs.because of parallel execution the job is getting completed in 1.2hrs.
what I am thinking of is as the part of logic (select c1,c2,c3 from t1,t2,t3) is same in both the satges,I am planning of doing both these stages in a single stage by creating a temp table.
what I want to know is.
1)Is my aproach correct
2)can I create a temp table in datastage(if yes,please tell me,how?).
please help me find a solution.
Tuning a Ds job involving Oracle stages
Moderators: chulett, rschirm, roy
While the approach might be correct, I am not certain that your measurements are. Did you separate these stages into their own jobs with no other links and check the timing? If not, then your results are likely skewed since timings in jobs usually depend on other stages and often you just end up measuring the slowest stage in the chain but don't really know which one it was.
no in ora1 stage we get a set of records from a select statement(detail records).DSDexter wrote:Please give us more information about the queries. Are you trying to create a Cross-Product looking at the queries that you posted that will surely fetch a lot of data, Since it will be a cartesian product. Give us clear requirements than we can help you.
in ora2 stage we will get an aggregated result(trailer record).
so in ora2 stage,we rewrite the same query written in ora1 stage as a subquery and take an aggregated sum.
we will get some 2 million records from ora1 stg and 1 record from ora2 stg.
my job looks similar to this
ora1---
|----funnel---sequentai_file
ora2---[/img]
sun
no in ora1 stage we get a set of records from a select statement(detail records).DSDexter wrote:Please give us more information about the queries. Are you trying to create a Cross-Product looking at the queries that you posted that will surely fetch a lot of data, Since it will be a cartesian product. Give us clear requirements than we can help you.
in ora2 stage we will get an aggregated result(trailer record).
so in ora2 stage,we rewrite the same query written in ora1 stage as a subquery and take an aggregated sum.
we will get some 2 million records from ora1 stg and 1 record from ora2 stg.
my job looks similar to this
ora1---
|----funnel---sequentai_file
ora2---
sun
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: