Page 1 of 1
Database connections for around 200 Jobs
Posted: Thu Apr 10, 2008 12:11 am
by verify
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
Posted: Thu Apr 10, 2008 12:17 am
by ray.wurlod
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.
Posted: Thu Apr 10, 2008 12:54 am
by verify
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]
Posted: Thu Apr 10, 2008 1:07 am
by chulett
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.
Posted: Thu Apr 10, 2008 10:38 am
by verify
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.
Posted: Thu Apr 10, 2008 3:40 pm
by ray.wurlod
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).
Posted: Fri Apr 11, 2008 12:24 am
by pilli
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.
Posted: Fri Apr 11, 2008 12:25 am
by pilli
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.
Posted: Fri Apr 11, 2008 1:39 am
by ray.wurlod
So long as you don't mind your jobs taking rather longer because the loading of the tables is no longer occurring in parallel.