Database Connection limit exceeded error

[snatarajan]

Hi there,

I'm facing database server connection limit error while running the parallel job. I've around 40 lookups, 1 source and 1 target. I'm using Sybase OC & ODBC stage as the relational operators and my database is Sybase IQ.

I've set the Database connection limit to 100. But the problem is coming whenever, I'm adding more lookups in to the job.

As Datastage are creating 2 connection per Sybase OC & ODBC relational operator. So in the case of 40 lookups, 1 source and 1 target ; data stage has created 84 connection in the database and it's work fine as my database connection limit is 100. But when I'm adding the lookup count to 50; it just started giving "database server connection limit error" as it's creating more than 100 connections in the database. As per the limitation from DBA; I can't request to extend the database connection limit to 100+.

Can somebody help me in resolving this issue. I've no clue first of all that why Data Stage are creating 2 connection per Sybase OC or ODBC relational connections into the database.

Thanks
javed

[chulett]

I thought this was your fourth post but it's actually your first as you've posted this in four different forums. Please don't do that, it doesn't help get you an answer faster and just causes problems maintaining a coherent conversation.

I can't help other than to strongly suggest you delete the other three posts before someone replies to them and that becomes impossible. Thank you.

[snatarajan]

I deleted the other three posts. Hope it'll be fine now.

I thought this was your fourth post but it's actually your first as you've posted this in four different forums. Please don't do that, it doesn't help get you an answer faster and just causes problems maintaining a coherent conversation.

I can't help other than to strongly suggest you delete the other three posts before someone replies to them and that becomes impossible. Thank you.

[chulett]

Yes, thanks. I'm sure someone will be along shortly with some help for your problem.

[ray.wurlod]

Can somebody help me in resolving this issue. I've no clue first of all that why Data Stage are creating 2 connection per Sybase OC or ODBC relational connections into the database.

Let me guess: your configuration file specifies two processing nodes.

Each processing node does all the work you've designed, on approximately half of the rows to be processed. Therefore each node will require the full complement of connections to the database.

You need a better design (multiple consecutive jobs would be one way) or a higher limit.

[chulett]

Ah... that makes sense.

[snatarajan]

Thanks Ray. Let me test the job with one configuration node.

Can somebody help me in resolving this issue. I've no clue first of all that why Data Stage are creating 2 connection per Sybase OC or ODBC relational connections into the database.

Let me guess: your configuration file specifies two processing nodes.

Each processing node does all the work you've designed, on approximately half of the rows to be processed. Therefore each node will require the full complement of connections to the database.

You need a better design (multiple consecutive jobs would be one way) or a higher limit.

[snatarajan]

I've just checked the job with single node.. but it's still creating somewhat double connections.

Any clue that why it's behaving this way. Logically in one node it should create the same no of connections as the total of source+Target+Reference = 50 but it's creating around 97.

Thanks Ray. Let me test the job with one configuration node.

Can somebody help me in resolving this issue. I've no clue first of all that why Data Stage are creating 2 connection per Sybase OC or ODBC relational connections into the database.

Let me guess: your configuration file specifies two processing nodes.

Each processing node does all the work you've designed, on approximately half of the rows to be processed. Therefore each node will require the full complement of connections to the database.

You need a better design (multiple consecutive jobs would be one way) or a higher limit.

[ray.wurlod]

Are you working with partitioned tables?

[snatarajan]

Hi Ray,

If you are talking about database partition. So none of my tables are partitioned.

But data stage job is using Auto option for partiotining the records at runtime.

Thanks
Javed

Are you working with partitioned tables?

[snatarajan]

Hi there,

Will appreciate if somebody help me in resolving this issue.

Thanks
Javed

Hi Ray,

If you are talking about database partition. So none of my tables are partitioned.

But data stage job is using Auto option for partiotining the records at runtime.

Thanks
Javed

Are you working with partitioned tables?

[ray.wurlod]

Folks here post as and when they can (in between earning a living). We're either thinking about it or have nothing further to offer. You are paying for support - get them to earn some of that money.