E-fix: PQ48877

Date:  4/11/2001

Release:  WebSphere Advanced 3.5.4, 3.5.3, and 3.5.2

Related CMVC internal defects: 92071, 93387, 93585, 94646, 93387, 95474, 
                               94207, 95478, 95076, 96772, 98027, 100103                             

Related APAR: PQ45992, PQ45004, PQ46883, PQ47339, PQ47404, PQ43427, 
              PQ48194, PQ48307, PQ48412, PQ48833, PQ48877

------------------------------------------------------------------

Directions to apply fix:

1) Create a directory to store the jar file 
     (e.g. c:\WebSphere\AppServer\efix)
2) Copy the jar file into the directory.
3) Add the directory/jar file to the begining of the admin server's
   classpath in admin.config:
     com.ibm.ejs.sm.adminserver.classpath=C:/WebSphere/AppServer/efix/PQ46883.jar; ...
4) Stop and restart the Admin Server.  

------------------------------------------------------------------

This Connection Pooling e-fix is cumulative for 3.5.3 and 3.5.2  
All previous non-custom Connection Pooling e-fixes are included in
this e-fix.  

The following defects were fixed in this e-fix:

1) StaleConnectionException handling was improved.  (Defect 92071) 

    An additional Sybase error code (JZ0C0) was mapped to the 
   StaleConnectionException class (PQ45004).
   In addition, appropriate try/catch blocks were added in all methods
   on a Connection object that could drive a call to the database. 
   This ensures that a StaleConnectionException is thrown back to the 
   caller when an error code is caught that could result in a 
   StaleConnectionException.  


2) Ensure the setLoginTimeout method on a DataSource drives back to
   the JDBC driver.   (Defect 93387)

    The setLoginTimeout method on the Datasource only had set an
   internal WebSphere timer, and did not drive the call all the way
   down to the database.
   Due to an issue with Sybase handing threads, we have changed 
   the setLoginTimeout method to call all the way to the driver.  
   This change will allow the client code to set the Connection login
   timeout so that if a thread gets hung at the database on a 
   getConnection, it will be timed out by the driver.


3) The ObjectAlreadyClosed exception was replaced by 
   StaleConnectionException.   (Defect 93585)

    ObjectAlreadyClosed was thrown when a Connection object was used
   but the Connection had already been closed, either by the user or
   by preemption/orphaning. 
   Now, with this efix, using a Connection that has already been
   closed will result in an StaleConnectionException instead.  
   The reason for the change is because the client's method for 
   recovery from an ObjectAlreadyClosed and a StaleConnectionException
   should be the same.   Now only one exception needs to be caught.


4) Null pointer exceptions. (Defect 94646)

    This fix eliminates an internal cause of null pointer exceptions.
   The exceptions had no impact on function since they were being handled 
   appropriatly.  This does improve performance for some users.


5) Update setLoginTimeout to work as stated in JDBC spec.  (Defect 93387)
   
    In the past, the timeout parameter passed in was only used for setting the time 
   one would wait in getting a connection from the free connection pool when the 
   pool was empy and the maximum number of connections had been allocated.  
   Now, in addition, the setLoginTimeout will also be passed to the underlying 
   JDBC Driver so that getConnection calls will similarly be timed out.


6) Check for null before wrapping result set in proxy. (defect 95474)    

    Prior to this fix, if you made a JDBC call on a connection which did not return a
   ResultSet, but then did a getResultSet.  Instead of getting back a null resultSet 
   you would get a WebSphere resultSet which when used could get a null pointer exception.


7) Throw StaleConnectionException when getting a new connection. (defect 95478)  

    For our recovery programming model to be consistent, we will now 
   throw a StaleConnectionException whenever we are trying to get a new 
   connection from the database and something goes wrong.  In the event 
   that this is the first connection to the database, it is still considered
   a stale connection, even though the situation causing the failure may be
   temporary.  This means it may be reasonable to do a limited number of 
   retries when the application receives a StaleConnectionException on 
   getConnection.  This change will require the application to have the 
   getConnection call in a try catch block that catches either 
   StaleConnectionException or SQLException.
  

8) Prepare statement cacheing fix. (defect 94207)    

    The original problem which we ran into was that a customer was getting 
   out of handle errors from db2. The reason was because they were using 
   a large number of different prepared statements within a single transaction
   and the prepared statement cache was not purging "discarded" statement until
   commit time.  

    In the original e-fix, which doesn't get as much testing as a ptf, we made a 
   change to immediately remove "discarded" preparedStatements.  This fixed the 
   original customer problem.

    Later, during ptf testing, we found that this solution could deadlock the 
   server so we removed the original fix and engineered a new one to take care 
   of the problem.  
   
    Note:  if you are getting db2 out of handles errors you should first try 
   bumping up your applheapsz. If this db2 configuration parameter is too small 
   you could also get the out of handles error.  Another configuration parmater 
   which can have the same affect is our preparedStatementCacheSize.  Making this 
   value too large will also increase the number of prepared statements which the 
   Connection Pool will keep around.  More preparedStatements means you need a 
   larger applheapsz.


9) MS SQL Server does not support FOR UPDATE on select statement. (defect 95076)
  
    Microsoft sql server does not support FOR UPDATE clause on select statements.
   Using an EJB with generated deployed code from VAJ, the FOR UPDATE clause is 
   added to the select statement. This is not supported for SQL Server.  
   Logic has been added to pick the right select statement when going to 
   SQL server.
   
   
10)Close Connection when releasing in use stale connections. (defect 96772)

    When a StaleConnectionException is translated, all of the connections in 
   the free pool are dropped from the pool and all in use connections are marked
   for cleanup when they are closed.  However, in some cases the connections were
   marked for cleanup, but not destroy()ed.  As it is always possible
   that the connections being purged from the free pool may not be bad, it might
   be that we dropped the reference but that the connection remained open to the
   database.


11)NullPointerException using Statements/ResultSet after OrphanTimeout  (defect 98027)

    Fixes a problem where attempting to use JDBC resources (i.e. Statement, ResultSet) 
    previously obtained from a now stale connection resulted in a NullPointerException.  
    Attempting to use such resources now results in a StaleConnectionException, as expected.
    It was reproduced on Informix and DB2 but would likely have occurred on any database.


12)SQLException: Communication Link Failure using Merant SequeLink  (defect PQ47339)

    Map of 08S01 SQL code to StaleConnectionException (Merant).

13)DB2Exception CLI0614E Not thrown as StaleConnectionException  (defect PQ47404)

    Map of 08S01 SQL code to StaleConnectionException (DB/2).
    

14) Oracle "Broken pipe" and "no more date to read from socket" error (defect PQ43427)


15) Map DB/2 SQLState 58004 to StaleConnectionException (defect PQ48194)


16) CM part of E-Fix for EJB CMP to DB2 390  (defect PQ48833)
    
    Customer was running EJB CMP with DB2/390 as backstore and EJB had
    TX_REQUIRED as transactional attribute and TRANSACTION_SERIALIZABLE has   
    isolation level. When more clients interacted with the EJB, the following 
    exception was raised: DB:COM.ibm.db2.DB2exception:IBM CLI Driver DB2 
    SQL0913N Unsuccessful excution caused by deadlock or timeout.Reason 
    code 00C90088.SQLSTAE=57033. The DB/2 390 6.1 prereq for this E-Fix is
    PQ43910 or UQ50361.                     


17) Map Oracle TNS/ORA-12541 to StaleConnectionException (defect PQ48877)


18) Null byte array with setBytes causes NullPointerException with Oracle (defect 100103)

    Oracle was giving us a Null byte arrary on SetBytes(), so we added a check before
    getting the length of the array.
    

19) dbInitialized and LOCK_TABLE handling (defect 97952)
    
    When coupled with co-requisite E-Fix: PQ48625, this will allow user to specify 
    com.ibm.ejs.sm.adminServer.dbInitialized=true  in the admin.config file and have 
    it work properly, i.e.: not create admin. repository tables. 
    

20) Close bad XA Connections (defect PQ48307) 

    When an XA connection is marked bad, we now destroy() that connection and give the 
    user another one. the connection does not get returned to the pool.

     


Recommended changes to client code:
-----------------------------------

The only change the client code would need to make would be to trap
for StaleConnectionExceptions as documented in the sample code below.
In particular, all the methods on the Connection object could throw 
a StaleConnectionException.  Note that StaleConnectionException is a
type of SQLException, so it must be caught before SQLException in a
try/catch block.  

In most cases, if a StaleConnectionException is caught, the
programming model would suggest releasing the reference to the 
Connection and trying to obtain a new Connection from the datasource.
The only time you might not want to get a new Connection is if this 
happens on the close() of the Connection.  


An example of client code changes recommended:
----------------------------------------------

.....
try {
       boolean retry = true;                                                  
       while( retry) {
        try {
         Connection conn1 = ds1.getConnection();
         Statement stmt1 = conn1.createStatement();
         ResultSet res = stmt1.executeQuery(stmtString);
         .......
         // If all the database work was successful.
         retry = false;
	 .......
        }  catch (StaleConnectionException ex) {
          retry = true;
        }  catch (SQLException sqle) {
	  .....
        } // end try-catch
      } // end while
    } catch( Exception ex) {
      ...
    } // end try-catch
....