DataStage Connections Issue

ray.wurlod · Post by **ray.wurlod** » Thu Apr 10, 2008 3:38 pm

The maximum number of connections to Oracle is something set in Oracle, not in DataStage.

Sweet talk your DBA with a request to increase the maximum but, if I were the DBA, you would need to have a very good case. Can you not run fewer jobs at the same time? Maybe do some of your extractions ahead of time into staging areas (Data Sets)?

Raftsman · Post by **Raftsman** » Thu Apr 10, 2008 6:58 pm

You will see an increase in performance if you decrease the node configuration. The startup time for 8 node will be high and with 1 or two node alot less. I would experiment with the node configuration. You will be very surprised.

John Smith · Post by **John Smith** » Thu Apr 10, 2008 8:30 pm

It may be even more than 320 * 8 connections opened because if your jobs have more than one Oracle EE stage e.g an input stage and and output stage then that's 2 connections per job. Best to scale back number of jobs that you run at the same time.

John Smith · Post by **John Smith** » Thu Apr 10, 2008 8:33 pm

one other option is to split your jobs into a "prepare" jobs and generic load job. the prepare jobs land data into datasets. then apply the loads in batches.