8.2 Persistent session

---

8.1 WebSphere data sources

In previous chapters, we have discussed WebSphere data sources. In this section, we provide more detailed information on two important WebSphere components in data source, connection pooling and statement cache, which have an critical impact on performance of a WebSphere Application Server and DB2 UDB system.

8.1.1 Connection pooling

Each time an application attempts to access a database, it must first connect to that database, before it can issue queries against it. A database connection incurs overhead, as it requires resources to create the connection, maintain it, and then release it when it is no longer required.

Database connection overhead can be particularly high for Web-based applications for the following reasons:

• Web users connect and disconnect more frequently.

• User interactions are typically shorter, with more effort often spent connecting to the database, and disconnecting from it, than performing the actual user requests.

• Web requests tend to be unpredictable both in terms of volume and frequency, which can place severe demands on database connections.

To address this problem, WebSphere has provided a connection pooling feature based on the JDBC 2.0 Optional Package API specification. For a basic understanding of the JDBC 2.0 Core API and the JDBC 2.0 Optional Package API, refer to:

http://java.sun.com/products/jdbc/download.html

Connection pooling is a mechanism whereby a system administrator can define a pool of connections for a single data source that may be reused by multiple users, without each one of them incurring the overhead of connecting and disconnecting from the database. Connection pooling can improve the response time of any application that requires connections, especially Web-based applications.

When a user makes a request over the Web to a WebSphere application, it is very common that the application needs to access DB2 server before sending a response back. These user requests do not incur the overhead of creating a new connection, because the data source might locate and use an existing connection from the pool of connections. When the request is satisfied and the response is returned to the user, the connection is returned to the connection pool for reuse.

Here again, the overhead of a disconnect is avoided. Each user request incurs a fraction of the cost of connection or disconnection. After the initial resources are used to produce the connections in the pool, additional overhead is insignificant because the existing connections are reused. Such reuse can have significant performance benefits since the cost of database connect and disconnect is amortized over multiple users.

WebSphere exceptions

These refer to WebSphere monitors' specific errors thrown by the database. A set of these errors is mapped to WebSphere Application Server specific exceptions.

All enterprise bean container-managed persistence (CMP) beans under the EJB 2.0 Specification receive a standard F when an operation fails.

JDBC applications receive a standard SQLexception if any JDBC operation fails.

The product provides special exceptions for its relational resource adapter (RRA), to indicate that the connection currently held is no longer valid. The ConnectionWaitTimeoutException indicates that the application timed out trying to get a connection. The StaleConnectionException indicates that the connection is no longer valid.

ConnectionWaitTimeout

The ConnectionWaitTimeout exception indicates that the application has waited for the number of seconds specified by the connection timeout setting and has not received a connection. This situation can occur when the pool is at maximum size and all of the connections are in use by other applications for the duration of the wait. In addition, there are no connections currently in use that the application can share because either the connection properties do not match, or the connection is in a different transaction.

When using a Version 4.0 data source, the ConnectionWaitTimeout throws an exception whose class is com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException.

For connection factories, the ConnectionWaitTimeout throws a ResourceException whose class is com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException.

Finally, Version 5.0 data sources throw an SQLException subclass called com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException.

Stale connections

WebSphere V5 provides a special subclass of java.sql.SQLException when using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a WebSphere 4.0 data source and in the new data source using the relational resource adapter. It is used to indicate that the connection currently held is no longer valid. This situation can occur for many reasons, including the following:

• The application tries to get a connection and fails, as when the database is not started.

• A connection is no longer usable because of a database failure. When an application tries to use a previously obtained connection, the connection is no longer valid. In this case, all connections currently in use by the application can get this error when they try to use the connection.

• The connection is orphaned (because the application had not used it in at most two times the value of the unused timeout setting) and the application tries to use the orphaned connection. This case applies only to Version 4.0 data sources.

• The application tries to use a JDBC resource, such as a statement, obtained on a stale connection.

• A connection is closed by the Version 4.0 data source auto connection cleanup and is no longer usable. Auto connection cleanup is the standard mode in which connection management operates. This mode indicates that at the end of a transaction, the transaction manager closes all connections enlisted in that transaction. This enables the transaction manager to ensure that connections are not held for excessive periods of time and that the pool does not reach its maximum number of connections prematurely.

  One ramification of having the transaction manager close the connections and return the connection to the free pool after a transaction ends, is that an application cannot obtain a connection in one transaction and try to use it in another transaction. If the application tries this, a StaleConnectionException is thrown because the connection is already closed.

In the case of trying to use an orphaned connection or a connection cleaned up by auto connection cleanup, a StaleConnectionException indicates that the application has attempted to use a connection already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a StaleConnectionException indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, you cannot recover it, and you must completely close the connection rather than returning it to the pool.

Detecting stale connections

When a connection to the database becomes stale, operations on that connection result in an SQLException from the JDBC driver. Because an SQLException is a rather generic exception, it contains state and error code values that you can use to determine the meaning of the exception. However, the meanings of these states and error codes vary depending on the database vendor. The connection pooling runtime module maintains a mapping of which SQL state and error codes indicate a StaleConnectionException for each database vendor supported. When the connection pooling runtime module catches any SQLException, it checks to see if this SQLException is considered a StaleConnectionException for the database server in use.

Recovering from stale connections

Recovering from stale connections is a joint effort between the application server run time and the application developer. From an application server perspective, the connection pool is purged based on its PurgePolicy setting. For instructions about setting PurgePolicy, please refer to the topic "Connection Pool Settings" in WebSphere Application Server V5 InfoCenter.

Explicitly catching a StaleConnectionException is not required in an application. Because applications are already required to catch java.sql. SQLException, and StaleConnectionException extends SQLException. StaleConnectionException can be thrown from any method that is declared to throw SQLException, and is caught automatically in the general catch-block. However, explicitly catching StaleConnectionException makes it possible for an application to recover from bad connections. When application code catches StaleConnectionException, it should take explicit steps to handle the exception.

Error mapping in DataStoreHelper

Error mapping is necessary because various database vendors can provide different SQL errors and codes that might mean the same things. For example, the StaleConnectionException has different codes in different databases. The DB2 SQLCODEs of 1015, 1034, 1036 and so on, indicate that the connection is no longer available because of a temporary database problem.

To provide portability for applications, WebSphere Application Server provides a DataStoreHelper interface to enable mapping of these codes to the WebSphere Application Server exceptions. The following code segment illustrates how to add two error codes into the error map.

Example 8-1: DataStoreHelper

 public class NewDSHelper extends GenericDataStoreHelper {   public NewDSHelper()   {     super(null);     java.util.Hashtable myErrorMap = null;     myErrorMap = new java.util.Hashtable(2);     myErrorMap.put(new Integer(-803), myDuplicateKeyException.class);     myErrorMap.put(new Integer(-1015), myStaleConnectionException.class);     myErrorMap.put("S1000", MyTableNotFoundException.class);     setUserDefinedMap(myErrorMap);     ...   } } 

8.1.2 Prepared statement cache

Applications may access a database using JDBC via any of the three options shown in Figure 8-1 on page 293.

Figure 8-1: JDBC statements

Below we explain statements.

• A statement is a class that can execute a SQL string passed into it. When the SQL statement is executed, two high-level phases occur: The statement must be "prepared", and then it can be "executed". During the prepare phase, DB2 will parse the SQL text and perform the steps necessary to put the query into a form DB2 can understand. Then during the execution phase the query can actually be performed. The prepare phase can consume a noticeable amount of time and CPU resources.

• A PreparedStatement ^[1] refines a statement by adding substitution parameters, and by separating the SQL compilation process from the execution of the statement.

  This allows applications to prepare the statement once, and then reuse it multiple times with different distinct values in the parameter markers.

  Applications that repeatedly execute the same SQL statement across multiple transactions can save a significant amount of processing time and network traffic by:

  1. Associating each such statement with its own statement handle.

  2. Preparing these statements once at the beginning of the application.

  3. Then executing the statements as many times as is needed throughout the application.

  By holding onto the statement handle, application programmers can reuse prepared statements across units-of-work (UOW).

  The global dynamic statement cache is a memory area on the server that is used to store the most popular access plans for prepared SQL statements. Before each statement is prepared, the server automatically searches this cache to see if the access plan has already been created for the exact SQL statement (by this application or any other application or client). If so, the server does not need to generate a new access plan, but will use the one in the cache instead.

  Note 

  Note the following:

  • For DB2 Universal JDBC Driver Provider, a custom property of a data source named deferPrepares is provided as a performance directive that affects the semantics of the input data type conversion capability of the driver. By default the Universal driver defers prepare requests. For example, a logical call to java.sql.Connection.prepareStatement() will not result in a physical prepare on the DB2 server until a logical request to execute the statement is issued, that is, java.sql.PreparedStatement.execute().

    If deferPrepares is enabled, the internal server prepare requests are deferred until execute time. This allows the prepare and execute to be piggybacked as a single request to the server, thereby reducing network delays for Type 4 connectivity. The deferral of prepare means that the driver works without the benefit of a described parameter or result set meta data. So undescribed input data is sent "as is" to the server without any data type cross-conversion of the inputs.

    Support for cross conversion of input types is an extension to JDBC, which does not require such support, but is supported by the Universal driver. Therefore, if a statement execution fails when deferPrepares is enabled, the execution is retried by the driver with described input. This statement retry logic is internal to the driver, and is seamless to the application. However, if an application enables deferPrepares, it is strongly recommended that input data types match the database column types as required by the JDBC specification.

    This means that if deferPrepares is enabled, then setter calls such as PreparedStatement.setShort() should match the type of the underlying column type (for example, SMALLINT) for optimum performance. If deferPrepares is disabled, then the driver requests describe information when the statement is logically prepared and therefore has described input parameter meta data available, and cross conversion of input types is supported for all PreparedStatement setter methods without requiring internal driver statement retry logic.

  • For DB2 Legacy CLI-based Type 2 JDBC Driver, DB2 CLI provides an option, DEFERREDPREPARE, which defers the sending of the PREPARE request until the corresponding execute request is issued. The two requests are then combined into one command/reply flow (instead of two) to minimize network flow and to improve performance.

  • Deferred prepare is the default and must be explicitly turned off, if required.

• A CallableStatement takes away the SQL compilation process entirely by executing a SQL stored procedure ^[2].

WebSphere Application Server provides the administrator with an option of specifying a prepared statement cache. Similar to a prepared statement in the database, this cache stores the PreparedStatement object of previously prepared statements on a connection basis.

The statement cache contains PreparedStatement objects of the most recently executed statements on a per connection basis, as shown in Figure 8-2. It describes a data source configured with a statement cache size of 10 statements, and a maximum of three concurrent connections.

Figure 8-2: PreparedStatement cache— An example

In Figure 8-2 the application runs five SQL statements (two selects, one delete, one insert, and one update). The connections have already been created, and many SQL statements have been executed. There are three prepared statements cached for Connection 1 and 2. Connection 3 has four statements cached. Because statements are compiled into prepared statements as they are used, the prepared statement cache reflects the database usage patterns of the application.

A PreparedStatement object, representing a given SQL statement, can appear multiple times in the prepared statement cache. In particular, it can appear once for every connection in the connection pool. In Figure 8-2 on page 296, statements 1 and 2 appear three times—once for each connection. Statement 3 does not appear for connection 3, and Statements 4 and 5 only appear for connection 3. Hence, it might take a little longer to execute Statements 4 and 5 if they occur on Connections 1 and 2 because of the need to recompile them for those connections. A better alternative for this example would be to set the prepared statement cache size to 15 statements, to allow for each of the 3 connections to cache their 5 prepared statements.

The value of the statement cache can be set in a data source configuration panel in the WebSphere V5 Administrative Console. To disable the statement cache, set the Statement Cache Size value to 0.

The actions performed by WebSphere Application Server when the statement cache is defined with non-zero and zero values is described here.

Non-zero statement cache size

The following actions are performed by WebSphere Application Server when an application issues a prepare statement against a connection:

• It does a look aside in the statement cache for this connection to see if a PreparedStatement object already exists for this statement.

  • If such an object exists, it returns this object to the application

  • If such an object does not exist, WebSphere Application Server requests a prepare against the database for this statement, and returns the PreparedStatement object to the application ^[3]

  Note 

  A WebSphere Application Server request to prepare a statement does not necessarily cause the database to execute the prepare statement. DB2 UDB will first perform a look aside in its own global dynamic statement cache for this statement, before deciding to execute the prepare statement and return the PreparedStatement object to WebSphere Application Server.

  Note 

  DB2 UDB will prepare a statement if it is not found in its global dynamic statement cache, even if the PreparedStatement object for this statement is still available in WebSphere Application Server's statement cache.

  This operation is repeated for each prepare statement.

• When the application performs a statement close() or commits, it puts this PreparedStatement object in the statement cache. If this action would cause the cache size limit to be exceeded, then it discards an existing PreparedStatement object in the cache using a first-in-first-out (FIFO) basis, to make room for this one. The PrepStmt Cache Discards field in Figure 8-3 on page 299, which counts the number of discards from the statement cache, increments after a discard.

  
  Figure 8-3: TPV statement cache discard field counter

  Note 

  The statement cache holds a pool of PreparedStatement objects, each of which is associated with a statement handle that is tied to a particular connection.

  WebSphere Application Server allocates a separate statement handle for each open cursor in the application, and moves the information related to this statement handle to the statement cache at commit time. Think of the statement cache as a pool of open statement handles for reuse by subsequent application units-of-work (UOWs) that use this same connection.

Zero statement cache size

When there is no statement cache, WebSphere Application Server will execute a request for a prepare against the database for every application prepare statement. Here again, a separate statement handle is allocated by WebSphere Application Server for each open cursor. However, at commit, the statement handles are destroyed, since there is no statement cache. Subsequent applications that reuse this connection will require WebSphere Application Server to set up the appropriate structures to issue the prepare request against the database.

Note 

The elimination of the creation of a prepare request, and the creation and destruction of statement handle structures on the WebSphere Application Server and DB2 side, may contribute to measurable performance degradation depending upon the proportion of this activity to the overall processing of the application.

Given our understanding of the default deferred prepare statement option, and DB2's algorithm for the global dynamic statement cache, performance benefits of the statement cache is unlikely to stem from a reduction in network flows, or an elimination of an SQL prepare by DB2.

The following is an overview of how DB2 handles prepared statements.

In DB2, each SQL statement is cached at a database level, and can be shared among different applications, unlike WebSphere Application Server's prepared statement cache. Static SQL statements are shared among applications using the same package, while dynamic SQL statements are shared among applications using the same compilation environment, and the exact same statement text.

Once a dynamic SQL statement has been created and cached, it can be reused over multiple units of work without the need to prepare the statement again.

Note 

DB2 will automatically recompile the statement as required if environment or data object changes occur, such as an object being created or dropped, running runstats, etc.

The size of the DB2 global statement cache is defined via the PCKCACHESZ database configuration parameter. This cache is allocated in DB2 out of the Database Global Memory when the database is initialized, and freed when the database is shut down. It is used for caching both static and dynamic SQL statements.

Caching of packages allows the database manager to reduce its internal overhead by eliminating the need to access the system catalogs when reloading a package; or, in the case of dynamic SQL, eliminating the need for compilation. Sections are kept in the package cache until one of the following occurs:

• The database is shut down.

• The package or dynamic SQL statement is invalidated.

• The cache runs out of space.

This caching of the section for a static or dynamic SQL statement can improve performance, when the same statement is used multiple times by applications connected to a database, including WebSphere Application Server applications. This is particularly beneficial in a transaction processing application.

There must be sufficient memory allocated in the cache to hold all sections of the SQL statements currently being executed. If there is more space allocated than currently needed, then sections are cached. These sections can simply be executed the next time they are needed without having to load or compile them.

Note 

The limit specified by the PCKCACHESZ parameter is a soft limit. This limit may be exceeded, if required, provided memory is still available in the database shared set.

Use the pkg_cache_size_top monitor element to determine the largest that the package cache has grown, and the pkg_cache_num_overflows monitor element to determine how many times the limit specified by the pckcachesz parameter has been exceeded. Refer to DB2 product documentation for more details.

8.1.3 Monitoring WebSphere application on DB2 UDB server

In a typical scenario, applications running on WebSphere Application Server that access data on DB2 UDB servers through a data source are indistinguishable to the DB2 server.

For example, Figure 8-4 shows the output from the DISPLAY THREAD(*) command on DB2 UDB for z/OS and OS/390 before we change the environment to set additional information about the incoming client. The application name is labeled as java.exe and the user ID and workstation defaults to the user ID and workstation that was used to get the database connection. This might not be accurate for all scenarios where further modular information about the application or the module currently running needs to be set.

Figure 8-4: Monitor WebSphere application from DB2 for zOS

Client connections on a DB2 UDB for UNIX, Windows and Linux can be seen through a DB2 LIST APPLICATIONS command, as shown in Example 8-2 on page 302.

Example 8-2: Monitor WebSphere application from DB2 UDB

 > db2 list applications Auth Id  Application Appl.    Application Id                DB       # of          Name        Handle                                 Name    Agents ------- ----------- ------- ------------------------------ -------- ------ DB2V81   java.exe     195     *LOCAL.db2v81.07B0C0003412   TRADE3DB   1 DB2V81   java.exe     169     G91A59AF.O17A.0BA7C0210130   TRADE3DB   1 

To solve this problem, DB2 provides a native API (SQLESETI) that can be used to set additional information about the clients from the application. This API can be accessed from its C interface, or the JDBC programming interface. WebSphere provides an integrated feature in data source using this API to allow users to pass more client information to DB2 UDB server from WebSphere. This information could be the application name, user ID, workstation name, or an accounting string.

To use the integrated feature, you can set the client information in the data source configuration panel from the WebSphere V5 administrative console. In the custom properties panel, set any of the following properties (Figure 8-5):

• ClientApplName

• ClientWrkstnName

• ClientUserid

• ClientAcctStr

Figure 8-5: Set client application properties

After setting the properties, restart WebSphere Application Server. Then we can see the effort of the configuration in DB2 UDB monitoring.

On the server, turn on DB2 monitor switches and issue the db2 snapshot command (see Example 8-3).

Example 8-3: DB2 UDB Sanpshot command

 db2 update monitor switches using table on bufferpool on uow on sort on lock on statement on db2 get snapshot for applications on <dbname> 

In the snapshot, you should see content similar to the following:

    ...    ...    TP Monitor Client User ID:        WebSphere-Userid    TP Monitor Client Workstation Name:    WebSphere-Wrkstn    TP Monitor Client Application Name:    WebSphere-AppName    TP Monitor Client Accounting String:    WebSphere-AccStr    ...    ... 

This information can be used to identify WebSphere Application Server connections.

Figure 8-6 shows the output of a DISPLAY THREAD(*) command running on DB2 UDB for OS/390 and z/OS server after the client information has been set. Notice that the application name, user ID and workstation name have been set to the values we specified earlier.

Figure 8-6: Output of DISPLAY THREAD using the feature

8.1.4 Tuning WebSphere DataSources

Tuning WebSphere DataSources is a very critical part of tuning the WebSphere Queue network mentioned in Chapter 6, "WebSphere Application Server V5 performance tuning" on page 169.

There are two settings to be concerned with for determining data source queues:

• Connection pool size

• Prepared statement cache size

Connection pool size

When accessing any database, the initial database connection is an expensive operation. WebSphere Application Server supports JDBC 2.0 Standard Extension APIs to provide support for connection pooling and connection reuse. The connection pool is used for direct JDBC calls within the application, as well as for enterprise beans using the database.

Tivoli Performance Viewer can help find the optimal size for the connection pool. Use a standard workload that represents a typical number of incoming client requests, use a fixed number of iterations, and use a standard set of configuration settings. Watch the Pool Size, Percent Used and Concurrent Waiters counters of the data source entry under the JDBC Connection Pools module. The optimal value for the pool size is that which reduces the values for these monitored counters. If Percent Used is consistently low, consider decreasing the number of connections in the pool.

Figure 8-7: TVP monitors connection pooling

Better performance is generally achieved if the value for the connection pool size is set lower than the value for the Max Connections in the Web container. Lower settings for the connection pool size (10–30 connections) typically perform better than higher (more than 100) settings. On UNIX platforms, a separate DB2 process is created for each connection. These processes quickly affect performance on systems with low memory, causing errors.

Each entity bean transaction requires a connection to the database specifically to handle the transaction. Be sure to take this into account when calculating the number of data source connections.

Instructions on how to set the connection pool size are provided in "Creating and configuring DB2 Data Source" on page 144.

Deadlock can occur if the application requires more than one concurrent connection per thread, and the database connection pool is not large enough for the number of threads. Suppose each of the application threads requires two concurrent database connections and the number of threads is equal to the maximum connection pool size. Deadlock can occur when both of the following are true:

• Each thread has its first database connection, and all are in use.

• Each thread is waiting for a second database connection, and none would become available since all threads are blocked.

To prevent the deadlock in this case, the value set for the database connection pool must be at least one higher, one of the waiting threads to complete its second database connection and free up to allow database connections.

To avoid deadlock, code the application to use, at most, one connection per thread. If the application is coded to require C concurrent database connections per thread, the connection pool must support at least the following number of connections, where T is the maximum number of threads.

 T * (C - 1) + 1 

The connection pool settings are directly related to the number of connections that the DB2 UDB server is configured to support. If the maximum number of connections in the pool is raised, and the corresponding settings in the DB2 UDB server are not raised, the application fails and SQL exception errors are displayed in the stderr.log file.

DB2 MAX_COORDAGENTS and MAXAGENTS settings

In DB2 UDB V8, some parameters need to be adjusted to make DB2 UDB perform well with WebSphere. MAX_COORDAGENTS defines the maximum number of physical DB2 server agents. When configuring the connection pool size of a data source for DB2, confirm the MAX_COORDAGENTS setting in DB2 server is high enough to handle the maximum number of connections for all the data sources. If you are planning to use multiple cluster members, set the MAX_COORDAGENTS value as the maximum number of connections multiplied by the number of cluster members.

Prepared statement cache size

The data source optimizes the processing of prepared statements to help make SQL statements process faster. It is important to configure the cache size of the data source to gain optimal statement execution efficiency. A prepared statement is a precompiled SQL statement that is stored in a prepared statement object.

This object is used to efficiently execute the given SQL statement multiple times. If the JDBC driver specified in the data source supports precompilation, the creation of the prepared statement will send the statement to the database for precompilation. Some drivers might not support precompilation and the prepared statement might not be sent until the prepared statement is executed.

If the cache is not large enough, useful entries will be discarded to make room for new entries. In general, the more prepared statements your application has, the larger the cache should be. For example, if the application has five SQL statements, set the prepared statement cache size to 5, so that each connection has five statements.

Tivoli Performance Viewer (Figure 8-8 on page 307) can help tune this setting to minimize cache discards. Use a standard workload that represents a typical number of incoming client requests, use a fixed number of iterations, and use a standard set of configuration settings. Watch the PrepStmt Cache Discard counter of the JDBC Connection Pools module. The optimal value for the statement cache size is the setting used to get either a value of zero or the lowest value for PrepStmt Cache Discards.

Figure 8-8: Using TPV tuning statement cache

As with the connection pool size, the statement cache size setting requires resources at the database server. Specifying too large of a cache could have an impact on database server performance. It is highly recommended that you consult your database administrator for determining the best setting for the prepared statement cache size.

8.1.5 Best practices

Best practices for using WebSphere data sources are categorized as being application related, and system related.

Application-related best practices

The following application programming techniques are considered to be best practices for achieving optimal performance.

• Use connection sharing.

  Connections can be shared by the same user (user name and password), but only within the same transaction. This is known as connection sharing, and should be used only on a single thread. For example, when a servlet starts a user transaction and gets a connection, it does some database operations and then calls a CMP EJB, which also needs a connection. The connection is then shared between the servlet and the EJB on a single thread.

  Connections should never be shared across threads. It is possible to see the same connection on multiple threads at the same time, but this is an error state, and is caused by poor programming practices; for example, when a servlet init() ^[4] method performs the JNDI lookup for a connection, and also gets its own connection. Each time the service() method is called, the same connection gets used, but these calls come in on different threads within the JVM. This can cause problems like time-outs and StaleConnectionException errors.

• Do not declare connections as static objects.

  If connection objects are declared as static, then it is possible for the same connection to be shared by different threads at the same time, resulting in problems with connection pooling and database access. A connection should always be obtained and released within the method that requires it.

• Do not declare connection objects as instance variables.

  In a servlet, all variables declared as instance variables act as if they are class variables. For example, suppose a servlet is defined with an instance variable:

      Connection conn = null; 

  This variable acts as if it is static. This implies that all instances of the servlet would use the same connection object. This is because a single servlet instance can be used to serve multiple Web requests in different threads.

• Open one connection at a time.

  In general, an application should open only one connection to the database at a time. If two getConnection() calls with the same parameters are issued in the same global transaction, only a single connection is allocated. You are allowed to open only one single-phase-commit connection within a global transaction.

  However, if the application is not running in a global transaction (as is the case with most servlets), two getConnection() calls result in two separate connections. This utilizes more resources than necessary, and causes your connection pool to fill twice as fast, often resulting in ConnectionWaitTimeout exceptions.

  If the application requires multiple simultaneous connections, close each connection as soon as it is no longer required, to free that connection up for another user.

• Always close objects.

  It is very important that ResultSet, Statement, PreparedStatement, and Connection objects get closed properly in the application.

  If connections are not closed properly, users may experience long waits for connections to time out, and delay return of the connection to the free pool. Unclosed ResultSet, Statement, or PreparedStatement objects unnecessarily hold resources at the database as well.

  To ensure that these objects are closed in both correct execution and exception or error states, always close ResultSet, Statement, PreparedStatement, and Connection objects in the finally section of a try/catch block.

  WebSphere Application Server will try to clean up JDBC resources on a connection after it has been closed. However, this behavior should not be relied upon, especially if the application might be migrated to another platform in the future, thus requiring code rewrite.

• Do not close connections in a finalize method.

  If an application relies on the method finalize() to close a connection or other JDBC resource, the connection is not closed until the object that obtained it is garbage collected, because that is when the finalize() method is called.

  Databases can quickly run out of the memory required to store the information about all of the JDBC resources currently open. In addition, the pool can quickly run out of connections to service other requests.

• Set fetch size using JDBC.

  Use setFetchsize() to set the fetch size that match DB2's recording blocking parameter (RQRIOBLK) to optimize SELECT query's performance.

System-related best practices

Routine monitoring of the connection pool is critical to adjusting the various parameters for achieving optimal performance. The Tivoli Performance Viewer is the primary tool for monitoring connection pool usage. Appropriate monitoring levels have to be set in order to view desired information.

While the default monitoring level is none, we recommend that you choose an appropriate monitoring level based on your organization's needs, and the overheads associated with it for your particular environment. The administrator should also occasionally perform exception monitoring with a monitoring level of Maximum during bursts of peak activity to obtain adequate information about the system under stress, in order to effectively tune connection pool parameters.

The following considerations apply to tuning the various connection pool parameters.

• Minimum pool size: A correct minimum value for the pool can be determined by examining the applications that are using the pool. If it is determined, for example, that at least four connections are needed at any point in time, the minimum number of connections should be set to 4 to ensure that all requests can be fulfilled without connection wait timeout exceptions.

  At off-peak times, the pool shrinks back to this minimum number of connections. A good rule of thumb is to keep this number as small as possible to avoid holding connections unnecessarily open.

• Maximum pool size: The maximum number of connections that the connection pool can hold open to the database. The pool holds this maximum number of connections open to the database at peak times. At off-peak times, the pool shrinks back to the minimum number of connections.

  The best practice is to ensure that only one connection is required on a thread at any time. This avoids possible deadlocks when the pool is at maximum capacity and no connections are left to fulfill a connection request. Therefore, with one connection per thread, the maximum pool size can be set to the maximum number of threads. When using servlets, this can be determined by looking at the MaxConnections property in the Servlet Engine.

  The Avg Wait Times (ms), Faults, Percent Used and Percent Maxed fields in Figure 8-7 on page 304 can help you decide whether the maximum pool size ought to be increased or decreased.

• Connection timeout: If applications are often catching ConnectionWaitTimeoutException, this usually means one of two things: Either the connection timeout property is set too low, or the connection pool is always at maximum capacity, and cannot find a free connection for the application to use:

  • If the exception is being caused by the connection timeout value being set too low, the solution is to set it to a higher value.

  • If the exception is caused by too few connections in the pool, the maximum pool size setting needs to be investigated.

  The Faults field in Figure 8-7 on page 304, when analyzed in conjunction with the Percent Used and Percent Maxed fields, can provide guidance on the setting of this parameter.

^[1]A prepared statement is a pre-compiled SQL statement that is stored in a prepared statement object.

^[2]A stored procedure is a prepared sequence of statements that resides on the DBMS.

^[3]With Deferred Prepare (which is the default), this prepare request is only sent to the database with the next execute request for this PreparedStatement object.

^[4]This is a static method, which is what enables multiple threads to use the same connection.

---