Maximum Oracle connections in parallel job

psk_270185 · Post by **psk_270185** » Mon Apr 13, 2009 3:47 am

We are facing the following error when we run the job containing more than 9 simultaneous oracle connections [ Oracle Enterprise stage ]

ERROR:
Oracle_Enterprise_9,2: ORA-12537: TNS:connection closed
Oracle_Enterprise_9,2: The call to sqlldr failed; the return code = 256;

eg., Oracle Source -----> Oracle_Target1, Oracle_Target2, Oracle_Target3, Oracle_Target4, Oracle_Target5 (Each target has two statements, one for insert and other as update)

What do we do here? We dont want to split the job. ANy options to increase teh max oracle connections?

Regards,
Shrinivas

ray.wurlod · Post by **ray.wurlod** » Mon Apr 13, 2009 4:50 am

The maximum number of connections, of listeners, is set on the Oracle side, not on the DataStage side. Consult your friendly DBA.

Bear in mind also that the "nine connections" of which you speak are on each node. So, if you are executing with a four-node configuration file, you are requesting as many as 36 connections.

psk_270185 · Post by **psk_270185** » Mon Apr 13, 2009 5:33 am

Hi Ray,

Thanks for the solution (though it falls under premium category and we arent able to see it). A si see from the first visible statement of yours, teh setting is to be made on the oracle side and not on DS side.

But on the contrary we could find teh following outcome as a hit and trial experiment:

Run the same job on AIX with database on Windows - same error
Run the same job on Windows with database as the one used for running on AIX - no error.

Hence we could infer that the problem is with Datastage and not with Database. This job runs fine when run on version 8.0.1 and database on windows. Alsoc an you post the premium content. I understand that we need a membership, but its kinda very urgent.

Thanks,
PSK

chulett · Post by **chulett** » Mon Apr 13, 2009 7:33 am

Regardless of your experimental conclusions, any settings of this nature are strictly controlled by your DBAs on the Oracle side. What did they say when you showed them your error? You did involve them, yes?

And before Ray comes back and goes off, anything "kinda very urgent" needs to go through your official support channel - your company pays them big bucks for "urgent", kinda or not. We don't do urgent here, being a volunteer organization and all, with people posting what they can when they can. And then there's that whole doing other things part, like working our own jobs, eating, sleeping, etc etc. Which pretty much throws your "urgent" right out the window.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 13, 2009 2:15 pm

Please read this post first.

If a company as large as yours doesn't have a corporate premium membership I would be very surprised.

After being surprised, I would be of the opinion that it deserves every obstacle that occurs. TANSTAAFL (there ain't no such thing as a free lunch).

Premium membership is one of the ways that the hosting and bandwidth costs - and therefore the continued existence - of DSXchange are funded. Expecting the full benefits without bearing some of the cost is just greed, and we don't believe that greed is good any more.