Chapter 10. DB2 - WebSphere performance aspects 229
holding resources that are not being used, and leaves more available connections in the
pool for other applications. Additionally, it removes the temptation to use the same
connection for multiple transactions. Of course, there may be times in which this is not
feasible, such as when using BMP.
Do not reuse the statement handle without closing it first.
To prevent resource leakage, close prepared statements before reusing the statement
handle to prepare a different SQL statement with the same connection.
Do not manage data access in Container Managed Persistence (CMP) Beans.
The reason for using a CMP Bean is to hide the details of the data access. By default,
users of CMPs cannot manage their own data access. If a CMP Bean is written to manage
its own data access, this data access may become part of a global transaction. It is
certainly necessary to pay attention to the transaction requirements of each method to
achieve data integrity and business objectives. If a user needs more fine-grained control
over the details of their data persistence, then they should use Bean-Managed
Persistence Beans (BMPs) instead of CMPs. Finally, it is also possible for the user to
perform data access functions in session Beans and servlets.
10.3 DB2 and JDBC
In this section we cover some topics related to DB2-specific parameters and settings that can
affect the performance of your applications running in a WebSphere Application Server
10.3.1 Adjusting the number of DB2 threads and connections
The WebSphere connection pool settings (see 10.2, “WebSphere Application Server
connection pooling” on page 222) are directly related to the number of connections that have
to be configured in DB2 to support this. Knowing the number of data pools is also important
when configuring the database’s maximum connections.
You set the limit of the number of allied and database access threads that can be allocated
concurrently using the MAX USERS and MAX REMOTE ACTIVE fields on installation panel
DSNTIPE. You should adjust the following DSNZPARM parameters according to your
machine size, your virtual and real storage, your workload, and the number of WebSphere
connection that are needed.
MAX USERS (CTHEAD) specifies the maximum number of allied threads, threads started
at the local subsystem, that can be allocated concurrently. These threads are for local
requests such as type 2 JDBC users.
MAX REMOTE ACTIVE (MAXDBAT) specifies the maximum number of database access
threads (DBATs) that can be active concurrently. These threads are for connections
coming into DB2 through DDF, such as DRDA, Private protocol connections, as well as
remote requests via the DB2 Universal Driver using type 4 connectivity.
MAX REMOTE CONNECTED (CONDBAT) specifies the maximum number of inbound
connections for database access threads.
10.3.2 Enabling DB2 dynamic statement cache
The WebSphere statement cache function works together with DB2 dynamic statement
caching. When the prepared statements are cached in the EDM pool (DSNZPARM
CACHEDYN=YES), re-calculation of the access path can be avoided if the statement in the
DB2 dynamic statement cache can be reused by a subsequent execution. This saving is in