db2 connection issue in sequence

rafik2k · Post by **rafik2k** » Wed Apr 25, 2007 8:16 am

Hi All,
I have created one sequence, where independent jobs are running parallely(no dependency link).
In the sequence I will have max 100 db2 connection at a time.

Total no. of connection is set to 200 on db2 server.

But sometime I am getting following error.

POD_Elc_Load_Mst_Hist_Audit..DB2_Read_Source_Row_Count: [IBM][CLI Driver] SQL1040N The maximum number of applications is already connected to the database. SQLSTATE=57030

Unable to connect to DB2 server 'SOURCE'.

Any help will be appreciated.

Regards,
Rafiq

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 8:24 am

Just your connections might be 100, there might be other applications connected to the database at the same time. Take this error to your DBA so that he can analyze the number of open connections during the time this error occured and increase the maxappls.

rafik2k · Post by **rafik2k** » Wed Apr 25, 2007 8:30 am

The database is in development environment.
No other application is running at that moment other that datastage job.

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 8:48 am

How many nodes are you using. There might be parallel connections open by you within the same job for a single Enterprise stage.

rafik2k · Post by **rafik2k** » Wed Apr 25, 2007 9:01 am

DSguru2B wrote:How many nodes are you using. There might be parallel connections open by you within the same job for a single Enterprise stage.

In Server job, what do you mean by node?
Is that no. of client connection or no. of cpu?

For us, we are using 5 clients, and the sequence is running from one client only.

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 9:10 am

Been switching back and forth between server and px questions so much that this time the switch couldnt happen. For some reason I thought you were on px. Anywho, my bad.
Get together with your dba, and ask him to monitor the number of connections that hit the database at a particular time. If your getting this error, obviously the limit is being reached.

rafik2k · Post by **rafik2k** » Thu Apr 26, 2007 8:14 am

Thanks DSguru2B ,

DBA showed me the statistics and no. of connection at a time never crossed even 100.
I am guessing there might be other issue

One quick question,
Does DS open more connection internally to the database other normal connection?

chulett · Post by **chulett** » Thu Apr 26, 2007 8:21 am

It will open a connection per database stage in a job.

DSguru2B · Post by **DSguru2B** » Thu Apr 26, 2007 8:23 am

More line one connection per link going to a database stage. So if you have two links going to a database stage, two connections will be request. Right

chulett · Post by **chulett** » Thu Apr 26, 2007 8:35 am

Um... No. One stage = one connection.

chulett · Post by **chulett** » Thu Apr 26, 2007 8:37 am

Let's put it this way - it only needs to open 1 connection per stage to do its work, and that's the behaviour I've seen with the stages I use. Now, if the DB2 stage does something stoopie... [shrug]

DSguru2B · Post by **DSguru2B** » Thu Apr 26, 2007 8:40 am

You might be right. Just a thought that occured in my mind. I needed clarification on that too. Thanks. So a connection per database stage it is

rafik2k · Post by **rafik2k** » Thu Apr 26, 2007 8:47 am

Then there might be other issue, right?

Is it due to datastage/database setup issue or application issue?