URGENT HELP and/or experience needed - IBM i V7R1, Java 6 & 7, & maximum number of system-wide JDBC connections

[Mr. K.V.B.L.]

For the first time ever, we had 3-4 jobs fail to run successfully. These jobs all kick off at midnight via their respective WRKJOBSCDE entry. Presently there are 62 of these jobs, which doesn't sound to my ears like it's that many. These jobs are doing some calculations, then updating and inserting rows into a single table, although there is a separate library for this table for each job. Some details:

Each job runs the same program. The version of the program differs slightly because one runs from a production environment, another in a user acceptance. The program is a Java program. It makes one *LOCAL JDBC connection per run instance.

What I observed from our log is that the connection string is built, then about a 2 minute span of time occurs attempting the connection then the code bolts out with an SQL exception. The exception text is very generic. I don't know where in the system to look for the exact cause.

INFO 2014-06-11 00:02:15,881 [main] (Balancer.java:511) - SQL driver name com.ibm.db2.jdbc.app.DB2Driver
INFO 2014-06-11 00:02:15,882 [main] (Balancer.java:524) - SQL connection string jdbc:db2:*local;naming=system;libraries=TF99328,TE99328,DOROOT,QTEMP,IESSYN,ILPGMR,QGPL,ALLTAXS,CDNLU62N,MILEMAKER,ALKMVS,OMNIFIXES,OMNISITE,OMNIDATA,OMNITRACS,OMNINATL,EXECPTF200,EXECDBF200,EXECOBJ200,EXECGPL200,ICCTEST01P,IESTEST82
FATAL 2014-06-11 00:04:29,438 [main] (Balancer.java:533) - SQL driver or connection string problem - com.ibm.db2.jdbc.app.DB2DBException: SQL system error.
FATAL 2014-06-11 00:04:29,606 [main] (Balancer.java:2584) - Constructor problem: com.ibm.db2.jdbc.app.DB2DBException: SQL system error.

Maybe I should be trapping SQLException instead of the generic Java Exception, but I don't know if that would even matter with the message text given.

try {
Class.forName(sqlDriverName);
dbConnection = DriverManager.getConnection(m_connect_string);
}
catch (Exception e) {
log.fatal("SQL driver or connection string problem - " + e);
throw e;
}

My gut says this isn't a connection resource problem - but it doesn't matter. I need to find the answer so that I can sleep peacefully at night. :-)

[CRPence]

On 11-Jun-2014 10:47 -0500, Mr. K.V.B.L. wrote:
> <<SNIP>> SQL system error. <<SNIP>>

Very likely the job servicing the SQL request(s) has logged a msg
SQL0901 [sqlcode -901; �and sqlstate 58004?] that can be investigated.
There is a feature provided with IBM i 7.1 and prior release(s) to help
investigate any -901 error that occurred on the system. Part of the
support is a file in the library QRECOVERY [or the equivalent iASP
library]; see:

<http://archive.midrange.com/midrange-l/201403/msg00422.html>
"... The term "SQL0901 Logger" is used to describe the feature alluded
above. The qualified file name QRECOVERY/QSQ901S or instead QSQ901S in
QRECOVERY is included here as reference; the library name may be
QRCY##### for the iASP per "QRCYnnnnn/QSQ901S *FILE, where nnnnn is the
iASP number". Some column names of the file: SERVERNAME FAILTIME FAILRSN
CURUSER JOBNAME STMTTEXT VRM DBGROUP MSGS. Some documentation can be
found in the "IBM i 7.1 Technical Overview with Technology Refresh
Updates", IBM Redbook Form Number: SG24-7858-02 Publication Date:
20-Mar-2013, from a download link here: <http://ip.com/redbook/SG24785802>"

<http://archive.midrange.com/web400/201002/msg00093.html � midrange.com>
"Refer to the message in the following link to the <ed: midrange.com>
RPG archive, for mention of "SQL0901 logger" which records a -901 error
into a TABLE QSQ901S in QRECOVERY; as error logging by the OS database
SQL feature, because the -901 is generally indicative of a defect
associated with that part of the OS. Also information about PTFs on v5r4
& v6r1 for [enabling or enhancing] this feature, and directions for
getting more information on the logger.
<http://archive.midrange.com/rpg400-l/201002/msg00342.html>"

--
Regards, Chuck