Re: sql cli handle leaks followup -- JAVA400-L

Jacob,

Looks like you are having a similar problem. The only difference we see is that in our stack trace we get a nullPointerException right after the DB2Statement.<init>, and your trace goes on a bit further.

The problem for us boils down to creating Statement objects and not always closing them. (I am out of town this week, so I am doing the following code snippet from memory.) We had instructed our developers to do something like:

// assuming connection is an instance of Connection instantiated elsewhere and

// sql is a string with the sql text to be executed

System.out.println("Executing sql stmt=" + sql);

Statement statement = null;
try

        {
            statement = connection.createStatement();
            statement.executeQuery(sql);

try

{

ResultSet rs = statement.getResultSet();
// !!! do something with the result set here

}

catch(Exception rs)

{

System.out.println("Problem with result set = " + rs);

rs.printStackTrace();

}

finally

{

if(rs != null)

{

try{rs.close();}

Catch(Exception rs1){}

}

                }
        }
    catch ( Exception e)

{

System.out.println("Problem creating statement or executing sql = " + e);
e.printStackTrace();

{
finally

{

if(statement != null)

{

try{statement.close()}

catch(Exception e1){}

}

System.out.println("Done executing sql");

You can verify whether your problem is running out of handles by running an sql cli trace on your application. You turn on cli traces by:
    1. ADDENVVAR QIBM_USRTRC_LEVEL 'INFO'
    2. CHGUSRTRC JOB(XXXXXX/YYYYYYYYYY/ZZZZZZZZZZ) MAXSTG(16382) CLEAR(*YES)
        where xxxxxx = jobnumber of application
              yyyyyyyyyy = user name of application

zzzzzzzzzz = jobname of application

3. let the application run till the error happens.

4. use DMPUSRTRC (specifying the same info as step 2 above) to create a physical file

QTEMP/QAP0ZDMP of the trace. You can use DSPPFM QTEMP/QAP0ZDMP to look at the trace.

5. somewhere in the trace you will see a message like "handle limit exceeded". Sorry I dont

remember the exact syntax of the error message -- again, I am away from my desk this week.

Maybe someone can jump in here with the exact syntax?

In our case, most of the code followed our standard, but some did not. Net result is we slowly leaked handles until the application would fail. It also seemed random to us since the offending bits of code were rarely executed. It would usually take 24 to 48 hours for our handle leaks to reach the limit. Our developers swore they were always following our standard, so I swore to IBM that we always closed our statements. I ended up wasting some IBMers time, but the cli trace had the smoking gun message about reaching our handle limit. Consequently we had to go back and make all developers do a line by line search through their classes to find where statements were not being closed.

On the as/400 you can get away with not closing your result sets. Closing the statement will close the result sets. However, if you want to be able to port to other platforms, make sure you also close any result sets.

Hope this helps.

----- Original Message -----
From: Jacob Madsen
To: JAVA400-L@midrange.com
Sent: Tuesday, April 11, 2000 9:14 AM
Subject: Re: sql cli handle leaks followup

Hi Alex (or anybody, who has had a similar experience)

Thank you for sending the previous mail. You might be able to help me out of, what I see as a similar problem. I have a multithreaded Java server application running, which always after some random number of queries to the database crashes with this exception:

com.ibm.db2.jdbc.app.DB2SQLException2: SQL CLI (Call Level Interface) error
      com/ibm/db2/jdbc/app/DB2StatementRuntimeImpl.allocStmt(IIII)I+17(DB2StatementRuntimeImpl.java:74)
      com/ibm/db2/jdbc/app/DB2Statement.<init>(Lcom/ibm/db2/jdbc/app/DB2Connection;IIZ)V+315 (DB2Statement.java:248)
      com/ibm/db2/jdbc/app/DB2Connection.createStatement(II)Ljava/sql/Statement;+64 (DB2Connection.java:494)
      com/ibm/db2/jdbc/app/DB2Connection.createStatement()Ljava/sql/Statement;+19(DB2Connection.java:455)
      SQLInterface.createStatement()Ljava/sql/Statement;+4(SQLInterface.java:94)
      Iterator.<init>(Ljava/lang/String;)V+8 (Iterator.java:22)
      AccountR.<init>(Ljava/lang/String;)V+1 (AccountR.java:10)
      AccountR.findAccountID(Ljava/lang/String;)LKSProjekt/AccountR;+25(AccountR.java:45)
      Worker.getAccountInfo(Ljava/lang/String;)[Ljava/lang/Object;+5(Worker.java:460)
      Worker.inCash(LKSProjekt/InCasher;)V+93 (Worker.java:340)
      Worker.run()V+40 (Worker.java:41)
      java/lang/Thread.run()V+11 (Thread.java:466)
getSQLState() returned HY014

Does this sound familiar to you? I would appreciate, if you could re-send the information you mentioned in your previous mail, or if you could give me some hints on how to fix this crash.

Thanks in advance!

Jacob

----- Original Message -----
From: Alex Garrison
To: JAVA400-L@midrange.com
Sent: Tuesday, April 11, 2000 9:38 AM
Subject: sql cli handle leaks followup

Several weeks ago I posted some information indicating we were seeing continued handle leaks that eventually caused the native jdbc (sql cli) driver to stop working. Our only solution at that time was to restart the websphere instance.

As a followup, several helpful folks at IBM (Richard D. and crew) helped us interpret the cli trace results. Bottom line is that we still had some servlets that were not closing the statement objects in all cases - hence the slow leak of handles. Although we had gone over this with all our developers months ago, a few hadnt made the necessary changes. I just wanted to let everyone know that there does not seem to be any problem with the as/400 native jdbc driver/sql-cli handle management.

Thanks again to the IBM'ers who took our problem seriously and helped out work it out.

Alex Garrison