Setup for the DB2 Connect performance tests

DB2 Connect – connection pooling and concentrator function

The following is a short description of the DB2 Connect parameters that are configurable and relate to connection pooling and concentration.

db2 update dbm cfg using <parameter> <value>

Connection Pooling

Connection Pooling is a simple technique that allows reuse of an established connection infrastructure for subsequent connections and handles connection volume and helps to reduce the overhead of database connections. It avoids the overhead of opening and closing connections by holding the connections active in a pool. You can configure the pool size using the NUM_POOLAGENTS configuration parameter.

Connection Concentrator

The Connection Concentrator enables a few number of threads in z/OS DB2® and thus lower associated hardware usage by DB2 for z/OS to service the client requests created from that DB2 Connect server (see also https://www.ibm.com/developerworks/db2/library/techarticle/dm-0503katsnelson/index.html). A higher number of clients are connected to the DB2 Connect server, which is really connected to the database, rather than the clients connected directly to z/OS DB2. It introduces the concept of agents as follows:

• Logical Agent (LA) - handles user context
• Coordinating Agent (CA) - owns DB2 connections and processes

Any time an application user connects, DB2 Connect assigns a logical agent. A coordinating agent is needed to pass SQL to DB2, so one is assigned as soon as a transaction is initiated. The coordinating agent is disassociated from the logical agent and is returned to the pool when the transaction is finished. The term logical agent was created to try to explain the decoupling of the application state from agents that service the work when connection concentration is enabled. A logical agent is just a concept. There is no agent involved. A logical agent represents the state information associated with the application connection, which is then tied to the agent processing the transaction.

DB2 Connect parameters

MAXAGENTS - The maximum number of DB2 Connect clients that can concurrently execute work against DB2 for z/OS is defined by the MAXAGENTS value. This value should be less than or equal to the MAXDBATS value on z/OS.

Coordinating Agents - The configuration parameter MAX_COORDAGENTS limits the number of coordinator agents. A co-coordinator agent (COORD agent) is the agent that handles the connections to the database. The setting of MAXAGENTS controls the total number of COORD + sub-agents (and a few other types of agents) that can actually be spawned by the DB2 Connect instance. However, because a pure DB2 Connect instance will not have any sub-agents, MAX_COORDAGENTS and MAXAGENTS are the same thing. In other words, even if MAXAGENTS is set higher than MAX_COORDAGENTS, in a pure DB2 Connect server environment, the number of agents used will never exceed the MAX_COORDAGENTS.

For each run we set MAX_COORDAGENTS to be the same as MAXAGENTS.

MAX_CONNECTIONS - The configuration parameter, MAX_CONNECTIONS, controls the number of connections allowed to the DB2 Connect server from the client side. When MAX_CONNECTIONS is larger than MAXAGENTS, the system runs in connection concentrator mode.

NUM_POOLAGENTS - The configuration parameter, NUM_POOLAGENTS, indicates the number of agents that, when not assigned work, will be kept active (pooled).

For each run we set NUM_POOLAGENTS to be the same as MAXAGENTS.

NUM_INITAGENTS - The configuration parameter, NUM_INITAGENTS, indicates the number of idle agents spawned at db2start. These agents are like any agent and can become COORD, sub-agents, etc. as required.

Because NUM_INITAGENTS just primes the agent pool with idle agents, its value should not exceed NUM_POOLAGENTS.

The DB2 command db2 get dbm cfg shows the default settings for DB2 after installation of DB2 Connect for the above parameters. An example is shown below.

 Max number of existing agents               (MAXAGENTS) = 200
 Agent pool size                        (NUM_POOLAGENTS) = 100(calculated)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = MAXAGENTS
 Max no. of concurrent coordinating agents  (MAXCAGENTS) = MAX_COORDAGENTS
 Max number of client connections      (MAX_CONNECTIONS) = MAX_COORDAGENTS

DB2 for z/OS (DSNZPARM parameter)

On our DB2 for z/OS system we used the DSNZPARM parameter MAXDBATS.

MAXDBATS - The maximum number of database access agents from the DB2 connection pool. You can do a display command on DB2 for z/OS to see how many connections are concurrently active.

-dis ddf detail

From the results of this command, you can decide if MAXDBATS and/or MAXAGENTS needs to be increased. MAXDBATS should be equal to or greater than MAXAGENTS. For instance, if MAXAGENTS is 100 and MAXDBATS is 200 and you see 100 active connections from the display command, you will know the DB2 Connect connection concentration pool has limited the number of connections, while the DB2 connection pool can service 100 more connections from other sources.

Enable the DB2 Connect connection concentrator function

This section describes the requirements to enable the DB2 Connect connection concentrator on the DB2 Connect system.

On the Application level, the following must be done:

Workload management policy for z/OS

It is important to set the proper workload management policy for DB2's Distributed Data Facility (DDF). The default service classification for DDF is discretionary. This service class is the second lowest available and means that once the system becomes busy, all distributed DDF work will run at a very low priority.

For information about Workload Management and defining goals through the service definition see z/OS MVS™ Planning: Workload Management.

For general information about DB2 for z/OS see DB2 for z/OS Performance Monitoring and Tuning Guide.

WebSphere Application Server setup