No Sql Server Are Available.

[Jack@dba]

HI team,

Today we faced below issue during that time database and application are up and running fine.

Data stage team are connecting to our database they found this error on their jobs log.

Error: J_aps_rey_ps_mstr..TR: |J_aps_rey_ps_mstr..PS_MSTR: DSD.BCIConnect connecting to REY, call to function SQLConnect failed.?SQLSTATE=60, DBMS.CODE=-20225?[DataStage][SQL Client][ODBC][DataDirect][ODBC 20101 driver][ODBC Socket][IBM(DataDirect OEM)][ODBC 20101 driver][20101]Exceeding permissible number of connections|

Please help how to resolve this error.
I think we need to increase -Mpb parameter for SQl connection.
W e dont have primary broker assigned to this database.
How much we need to setup the -Mpb value for both primary and secondary brokers.

Progress Version : 9.1e
OS version : IBM AIX 5.3

test.pf file
#-------------------------------------------------------
# OVERRIDE database broker parms for test
#-------------------------------------------------------
# MAX USERS = Ma*Mn = 180
#-------------------------------------------------------

-B 46250 # Number of Blocks in database buffer
#-Mi 2 # Min processes on a client server
-Ma 15 # Max number of REMOTE clients per db server
-Mn 35 # Max number of REMOTE client servers
-Mxs 32768 # Shared memory overflow size (override)
-spin 4000 # Number of spin lock retries
-L 128000 # Lock Table entries

SQl broker :
more reyoi.sh
proserve /progdata/prd/db1/tes -m3 -Mi 3 -Ma 15 -Mn 20 -Mpb 4 -Ststreyoi -ServerType SQL


21:36:02 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:04 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:05 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:06 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:07 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:08 BROKER 3: No SQL servers are available. Try again later. (8839)
21:36:09 BROKER 3: No SQL servers are available. Try again later. (8839)

 

[TomBascom]

If you pop into PROMON it should tell you how many connections of what sorts you have.

Without studying the data posted too hard my guess is that you have 60 SQL connections. -Mpb 4 * -Ma 15

I also vaguely recall that the ancient, obsolete and unsupported v9 SQL had some issues if you tried to use -Ma greater than 5. But I might be misremembering that.

You really ought to upgrade. Running v9 is bad for your resume and bad for your employer's data.

 

[Rob Fitzpatrick]

W e dont have primary broker assigned to this database.

Yes you do. Any database that is online in multi-user mode has a primary broker. It is the process that opens the database and instantiates the shared memory segment(s). It is invoked by a proserve command without a -m3 parameter.

Since the proserve command shown above has the -m3 parameter, this SQL connection broker is a secondary broker. That means there is at least one other broker running. If there is only one other broker then it is the primary broker. If its proserve command line includes a "-pf test.pf" parameter then the displayed contents of "test.pf" *may* be relevant. They could also be overridden by similar parameters that come later in the proserve command line, or in another later .pf file.

Also:

• there appears to be a typo in "-Ststreyoi"; I assume it is meant to be "-S tstreyoi" (note the space);
• the "-Mn 20" parameter on that command line should not be there; it should only be specified on the primary broker. It defines the size of the _Servers table: the maximum number of allowed servers, across all brokers (i.e. sum of all -Mpb parameters), plus the number of secondary brokers.

So for your secondary SQL connection broker you have -Mpb 4 (max servers) and -Ma 15 (max clients per server). In different circumstances this might allow 60 SQL client connections. But we don't know your primary broker's parameters for sure so we don't know if your user connection table is large enough for that. If we assume that "test.pf" contains all of the primary broker's startup parameters, you won't get to 60 SQL connections. Since you haven't specified -n (number of connections) in test.pf, you get the default of 20 (you'll see 21 in the db log). So that will certainly limit you, if nothing else does. Note that that is a limit on database *connections*, not just clients. So other database connections like helper and utility processes (BIW, AIW, APW, WDOG, promon, proutil, probkup, etc.) also count against that total.

We also don't know for sure what -Mn is. If it is 4 or less then the SQL broker won't be able to spawn all 4 SQL servers, because the SQL broker uses up one of the records in the _Servers table.

In short, I think we need more info to determine definitely what is going on here.