Database connections for around 200 Jobs

verify · Post by **verify** » Thu Apr 10, 2008 12:11 am

Hi,

I have a sequencer which calls around 200 jobs in parallel. When I run the sequencer around 150 jobs are aborting due to Connect(eg. ORA-12518: TNS:listener could not hand off client connection) errors. Its not able to open more number of connections parallely. Do we need to change the hardware configurations or do we need to change the aptconfig file parameters to get rid of this error. Please advice.

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Apr 10, 2008 12:17 am

This is an Oracle configuration. You need to sweet-talk your DBA into increasing the number of connections, and you'd better have some pretty convincing reasons.

verify · Post by **verify** » Thu Apr 10, 2008 12:54 am

Thank you for the quick reply.

I am new to datastage. We have nearly 350 jobs and each job opens a oracle connection. Is it a standard practice to use 1 connection per job? Or is there any connection pooling concept in datastage?[/quote]

chulett · Post by **chulett** » Thu Apr 10, 2008 1:07 am

Jobs will need as many connections as you have DB stages. Yes, that is 'common' - what is uncommon is attempting to run 200 jobs at the same time. Unless you've ensured both your hardware and database resouces have been configured accordingly.

verify · Post by **verify** » Thu Apr 10, 2008 10:38 am

chulett wrote:Jobs will need as many connections as you have DB stages. Yes, that is 'common' - what is uncommon is attempting to run 200 jobs at the same time. Unless you've ensured both your hardware and database resouces have been configured accordingly.

Dear Chulett/Ray,
Thanks for your response. What could be possible hardware and database resources we need to configure to achieve the run of around 200 jobs in parallel.

Thanks.

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

More, obviously.

However, maximum number of permitted connections is configured in Oracle, so you still need to convince your DBA that you need this many.

Can you not schedule your jobs differently, so that fewer connections are required at any one time? For example do some of your extraction earlier and into staging areas (Data Sets).

pilli · Post by **pilli** » Fri Apr 11, 2008 12:24 am

ray.wurlod wrote:More, obviously.

However, maximum number of permitted connections is configured in Oracle, so you still need to convince your DBA that you need this many.

Can you not schedule your jobs differen ...

We have observed that these connect errors are coming after replacing DRS stage with Oracle Enterprise stage in all the jobs(200). Will DRS stage be a better option in this case over Oracle Enterprise stage or Is our observation wrong.Please clarify.

pilli · Post by **pilli** » Fri Apr 11, 2008 12:25 am

ray.wurlod wrote:More, obviously.

However, maximum number of permitted connections is configured in Oracle, so you still need to convince your DBA that you need this many.

Can you not schedule your jobs differen ...

We have observed that these connect errors are coming after replacing DRS stage with Oracle Enterprise stage in all the jobs(200). Will DRS stage be a better option in this case over Oracle Enterprise stage or Is our observation wrong.Please clarify.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 11, 2008 1:39 am

So long as you don't mind your jobs taking rather longer because the loading of the tables is no longer occurring in parallel.