DB2 for z/OS and WebSphere: The Perfect Couple

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

environment.

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