Option 1 : Use single oracle enterprise stage & use the single UNION query to get all the data from 52 tables
Option 2: Use 52 oracle entegerprise stages & use a funnel to union the data .
If the you are having a restriction of number of stages , Use union of 5 tables & it will end up in 10 enterpise stages & funnel stage .
Which option gives better performance depends on the load on the datastage & database servers.
ORACLE performance
Moderators: chulett, rschirm, roy
Option 2 sounded like using a single multi-instance job rather than 52 database stages in one job. Is that correct?
Note: Oracle Connector stages are recommended over Oracle Enterprise stages.
I would not know which option performs better without testing it both ways.
Note: Oracle Connector stages are recommended over Oracle Enterprise stages.
I would not know which option performs better without testing it both ways.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
We are not planning to use 52 stages rather , we are using 52 instances of the same job running parallely using datastage sequence. Each job will have a single query. The queries are not complex -just simple select * statements where Dateattribute between <start_date> and <end date>.
However the data volume is large - upto 15 million records per SQL. And requirement is to process as fast as possible.
None of these are materialised view. Its a Oracle Federated database on 52 different databases.
Problem with running a single SQL and union is Oracle union runs each SQL sequentially.
Does any of you see any issues in using 52 instances of the same job running parallelly?
However the data volume is large - upto 15 million records per SQL. And requirement is to process as fast as possible.
None of these are materialised view. Its a Oracle Federated database on 52 different databases.
Problem with running a single SQL and union is Oracle union runs each SQL sequentially.
Does any of you see any issues in using 52 instances of the same job running parallelly?
I think Running 52 instances of same job parallely is a too much load on the server ...I think it's not a bad idea to have 10 connector stages & union 5 tables in each connector stage ..Also there used to be a limitation of number of instances of job running in parallel in 8.0.1 ..Not so sure if it fixed in the later versions ..
Nag
Has your Oracle DBA already talked with Oracle support about optimizing the database to run those queries in parallel? It would seem if you can run the queries in parallel using DataStage that you could do the same directly within Oracle. (I wonder if the DB2 Federation Server has any similar limitation.)
I'm running over 50 multi-instance jobs in parallel now on 8.5, but they are not selecting large volumes of data. It should depend on your DataStage server's hardware resources and software and OS configuration.
If you go the DataStage route and hit a resource limit, then you could use a sequence job to run the 52 instances in 2 to 4 groups at a time back to back (15 to 25 jobs at a time).
I'm running over 50 multi-instance jobs in parallel now on 8.5, but they are not selecting large volumes of data. It should depend on your DataStage server's hardware resources and software and OS configuration.
If you go the DataStage route and hit a resource limit, then you could use a sequence job to run the 52 instances in 2 to 4 groups at a time back to back (15 to 25 jobs at a time).
Choose a job you love, and you will never have to work a day in your life. - Confucius