E-fix: PQ57624
Date: 02/15/2002
Release: WebSphere Advanced 3.5.5, may work on 3.5.4, and previous PTFs,
but it's not tested. This should also work fine on 3.5.6.
A significant number of these E-Fixes are incorporated into
the base 3.5.5 and 3.5.6
Related CMVC internal defects: 92071, 93387, 93585, 94646, 93387, 95474,
94207, 95478, 95076, 96772, 98027, 100103,
115405, 113141, 116978, 113141
Related APAR: PQ45992, PQ45004, PQ46883, PQ47339, PQ47404, PQ43427, PQ48194,
PQ48307, PQ48412, PQ48833, PQ48877, PQ49464, PQ49895, PQ50076,
PQ50372, PQ51496, PQ52713, PQ52731, PQ52986, PQ53331, PQ53415,
PQ54471, PQ55196, PQ55402, PQ55413, PQ56607,
**New this E-Fix -> PQ56665, PQ56737, PQ56858, PQ57201,
PQ57315, PQ57523, PQ57624
------------------------------------------------------------------
** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE **
This E-Fix contains a change for WebSphere Connection
Management which affects the way PreparedStatements are allocated
Please see the description under PQ53331 below.
** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE ** NOTE **
------------------------------------------------------------------
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 beginning of the admin server's
classpath in admin.config:
com.ibm.ejs.sm.adminserver.classpath=C:/WebSphere/AppServer/efix/PQxxxxx.jar; ...
4) Stop and restart the Admin Server.
------------------------------------------------------------------
This Connection Pooling E-fix is cumulative for 3.5.5 and may work for 3.5.4 or 3.5.3
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
time-out 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 E-fix, 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) 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 result set
you would get a WebSphere result set which when used could get a null pointer exception.
6) 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.
7) Prepare statement caching 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 parameter
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.
8) 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.
9) 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 destroyed. 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.
10) 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.
11) SQLException: Communication Link Failure using Merant SequeLink (defect PQ47339)
Map of 08S01 SQL code to StaleConnectionException (Merant).
12) DB2Exception CLI0614E Not thrown as StaleConnectionException (defect PQ47404)
Map of 08S01 SQL code to StaleConnectionException (DB2).
13) Oracle "Broken pipe" and "no more date to read from socket" error (defect PQ43427)
14) Map DB2 SQL State 58004 to StaleConnectionException (defect PQ48194)
15) 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.
16) Map Oracle TNS/ORA-12541 to StaleConnectionException (defect PQ48877)
17) Null byte array with setBytes causes NullPointerException with Oracle (defect 100103)
Oracle was giving us a Null byte array on SetBytes(), so we added a check before
getting the length of the array.
18) 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.
19) 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.
20) Merant OEMID is not being set during recovery in 3.5 (PQ49464)
Application server fails to restart with the following message:
Error to recover XAResource javax.transaction.xa.XAException
In Merant logs:
License verification failed. Diagnostics (Error) added: message: License verification failed.;
code:3070;component:4;function:28;row:0;column:0
If admin server is restarted, the app server will restart properly.
21) Oracle exception ORA01012 mapped to StaleConnectionException (PQ49895)
This particular test case established a session, then they killed the Oracle session.
The query was tried again, received the error:
ORA-00028: Your Session has been killed.
The browser was closed and a new one opened, then they get:
ORA-01012:not logged on.
22) Merant SQL State mapped to StaleConnectionException (PQ50076)
in this case the customer unplugged the network cable from WAS machine
and the following exception was thrown:
PortabilityLa D Trying vendor code 2311
PortabilityLa D Trying SQL state "HY000"
PortabilityLa D Trying detail message
" MERANT SequeLink JDBC Driver Internal
network error, connection closed."
From this, four new Merant codes have been added, 2310 and 2311,
plus two other StaleConnectionException already in 4.0: 2251 and 2306.
23) Added more Meta-Data for Sybase 12 (PQ50372)
Error was: Unrecognized database or driver "Adaptive Server Enterprise".
In the case of EBF 9422 and beyond, if the file SQL_server12.SQL is run,
the meta data is changed to "AdaptiveServerEnterprise" from
"SybaseSQLServer". The new meta data has been added.
24) Error SLOracle51 errorcode 2310 using Merant SQL Server (PQ51496)
This has been added to the map for 3.5 Merant Oracle layer.
25) Connection Pool not purged on prepareStatement exception (PQ52713)
Error was that if a StaleConnectionException was caught during a
prepareStatement, the pool would not get purged properly.
26) Fix for excessive waiters, and connection idling (PQ52731)
There was a condition where if waiter threads were encountered,
i.e.: More connections requested than in the pool, where the
waiters could increase at a rate faster than can be accounted for.
Not a fatal problem. Also fixed is a problem
where connections may not idle during periods of low activity.
27) Locks not freeing after Transaction Timeout occurs
and problems using prepared statements (PQ52986)
1. Locks not freeing after Transaction Timeout occurs
Rollbacks are delayed with the transaction timeout occurs and there
is still work in progress for the transaction. Normally what should
happen is that the rollback will be performed when the work in
progress completes. However, there are times when the rollback will
not occur due to the connection having been marked closed before
the work has completed. This fix ensures that the rollback will be performed
and so the locks will be freed.
2. When using prepared statements, an error can occur and work involving the
prepared statement is not completed and the connection is not rolled back nor
returned to the connection pool
Closes on prepared statements were not being properly synchronized, this has
been fixed.
28) Cached Prepared Statements performance (PQ53331 and 115405)
This E-Fix features a performance increase to the WebSphere Connection Pooling
prepared statement cache. The most benefit will be seen in the throughput of
applications which use multiple connections simultaneously, although the
performance for using a single connection is also improved.
The side effect of this improvement is that the meaning of "Prepared Statement
cache size" has changed. In previous versions this value signified the total
maximum number of Prepared Statements (PS) that would be cached in the
datasource or Pool. In this E-Fix, the value still represents the number of
PSs per datasource, but the number is now divided by the number of
connections. If the number of PS is less than the numner of connections, a
minimum of 1 per connection is allocated. For example given 100 PS, and 50
connections, you will have 2 per connection, and given 10 PS and 50 connections,
you will have 1 per connection. Customers may wish to adjust the statement cache
size. The default statement cache remains 100, and depending upon your
connection pool size, this may be reasonable. For larger pool sizes, the
statement cache size may need to be increased.
Statement cache size can be set by using (or creating) the file named
datasources.xml and place it in the $WAS_HOME\properties folder. This file can
be used to set other properties, but here is an example of what it would contain
for only setting the Cache size:
29) Oracle error ora-17401 added to StaleConnectionException Map (PQ53415)
Oracle issues ORA-17401 Protocol Invalid SQLException for various reasons
some of which are non-recoverable. This has been added to the map to
StaleConnectionException.
30) Illegal ConnectO State exception issued (PQ54471)
If a StaleConnectionException was handled during connection allocation,
the result would be an illegal State on the ConnectO, causing an internal
exception to be thrown.
31) Third insert results in CLI0112E (PQ55196)
The problem was when inserting UDB blob type data via WebSphere
datasource. a CLI0112E with SQLSTATE=22005 Data Assignment Error
was received on the 3rd insertion with same primary key.
We expected to continue to get DuplicateKey Exceptions.
32) StaleConnectionException hangs (PQ55402)
Possible hangs when processing StaleConnectionExceptions. Connections
become unusable and do not recover.
33) Setting default Transaction Isolation Level with servlets (PQ55413)
In 3.5, with certain DB's you will get an exception if you set the
isolation level after getting a connection in a servlet. We added the
ability for a customer to set default the Isolation level fora datasource.
In the past the default level was always set and there was no easy way to
override it. This is now set via the datasources.xml and
WAS35IsolationLevelOverride value. **** Attention! This will override the
Isolation level for the Entire DATASOURCE, which may cause EJB's to fail.
This work-around is intended for servlets only.
Ex:
34) Use of IBMConnMgr interfaces can cause null exceptions (PQ56607)
Applications that use the WebSphere 2.0 IBMConnMgr interfaces to get and
release connections from the same connection pool and the gets and release may occur
in overlapping requests, i.e. a get is occurring while a release is occurring,
may receive NullPointer exceptions.
35) DB2 (SQL) Exceptions added to StaleConnectionException Map (113141)
Cached preparedStatement becomes invalid when DB2 database CLI packages were rebound.
The problem was with the cached prepared statement. The scenario is, a user accidentally
rebinds or autobinds the CLI package to the database, this invalidates all CLI packages
on the server, and therefore all execution of cached prepared statements received
"SQL0514N The cursor "" is not in a prepared state." error. SQL0514N and SQL0518N
are now added to the StaleConnectionException map, allowing recovery from this scenario.
36) An internal improvement for Oracle. (116978)
37) New mapping for Merant (PQ56665)
We now map error code 227 and SQLSTATE 23000 to DuplicateKeyException for
Merant portability layer.
38) Connection pool waiter grows abnormally (PQ56737)
It was noticed that the Connection pool waiters would grow abnormally
large under load. This can be seen in Connection Manager (cm) traces.
The functional effects of this error are unknown.
39) Oracle 8.1.7 JTA Exception (PQ56858)
When using WAS 3.5.4 with JTA-enabled Oracle 8.1.7 JDBC driver,
customer sees TransactionAbortedException when a transaction is
made to the database. CM/JTS trace shows that the driver is
being read as an Oracle 8.1.6 driver.
40) StaleConnectionException causes hangs using JTA (PQ57201)
This is essentially the same as PQ55402, but the JTA case was is now
fixed as well.
41) (PQ57315)
Setting connection pool parameters for the pool created using IBMConnMgr
does not work when using the IBMConnPoolSpec. While the
IBMConnPoolSpec allows the parameters to be set, the object does not
pass these on to the connection pool. A workaround is now provided
that allows the application to change the default parameters for the
WAS20ConnMgr.
42) MSSQLPORTABILITYLAYER class converts TIMESTAMP to DATETIMESTAMP (PQ57523)
MSSQLServer does not support the DATE, TIME, or DOUBLE data types so the
createTableForPersister method converts occurrences of these Strings as follows,
DATE --> DATETIME
TIME --> DATETIME
DOUBLE --> FLOAT
The problem here is that ALL occurrences of these Strings (in this case " TIME")
within the CREATE TABLE statement are being replaced. Sometimes it is valid to
do so; sometimes it is not. This has been corrected.
43) Java.Lang.StingIndexOutOfBoundsException using SEQUELINK (PQ57624)
When using SequeLink driver, the customer was using a heretofore unsupported
property. We added ability to accept serverDataSource property. Also, we will
now print warnings if unsupported properties are used, rather than throwing
exceptions.
NOTES:
Recommended changes to servlet code for StaleConnectionException handling:
-------------------------------------------------------------------------
Some of the E-Fixes above deal with wrapping SQL Exceptions in
StaleConnectionException, which is itself a SQL Exception. The following
is a sample of how to take advantage of this model.
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:
----------------------------------------------
This example assumes that you are not running in a transaction.
If you are running in a transaction, you cannot do the retry until the
transaction has rolled back, for more info, please see the Connection
pooling whitepaper section on handling StaleConnectionExceptions.
.....
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)
{
// counter to retry a limited number of times?
retry = true;
// wait?
}
catch (SQLException sqle)
{
.....
} // end try-catch
finally
{
// close result sets
// close statements
// close connection
}
} // end while
}
catch( Exception ex)
{
....
} // end try-catch
....