Section 14.8. Case Study

14.8. Case Study

Diagnosing a Problem with the Help of DB2 Processes

The following real-life example shows how you can solve problems by reviewing the running DB2 processes of a system.

One afternoon, an AIX database server encountered a general slow-down in query response time. A DB2 list applications command did not show anything out of the ordinary running at the time. Before taking DB2 snapshots, we looked at the DB2 processes running on this AIX machine and found that the db2rebal process was running. This process performs a rebalancing of the data across containers when a container is added to a DMS table space. The DBA realized that earlier that day he had added one container to a table space containing a 40GB table. No action was required; when the rebalancing finished, the queries went back to its original good response time.

How to Control the Number of Connections by Setting the Number of DB2 Agents

Example 1

Consider an ESE environment with a single database partition in which 1,000 users on average are connected to the database concurrently. However, the number of connections that are actually active is 250 at the maximum. The transactions are short.

For this workload, you can enable the connection concentrator such that the database manager can allow up to 1,000 concurrent connections. You can set a maximum of 250 coordinator agents to handle the active connections at any one time. Without the connection concentrator, 1,000 coordinator agents are required to be created, one for each connection.

The following Database Manager Configuration parameters need to be set.

• Set MAX_CONNECTIONS to 1,000 to ensure support for the average number of 1,000 concurrent connections.

• Set MAX_COORDAGENTS to 250 to support the maximum number of 250 active concurrent transactions (assume MAXCAGENTS = MAX_COORDAGENTS).

• Set MAXAGENTS high enough to support all of the coordinator agents and subagents (where applicable) that are required to execute transactions on the partition.

• If INTRA_PARALLEL is OFF, set MAXAGENTS to 250, because there are no subagents in such an environment. If INTRA_PARALLEL is ON, set MAXAGENTS large enough to accommodate the coordinator agent and the subagents required for each transaction that accesses data on the partition. For example, if each transaction requires 4 subagents, MAXAGENTS should be set as follows: (1 coordinator agent + 4 subagents) x 250, which is 1,250.

• Set NUM_POOLAGENTS equal to MAXAGENTS to avoid the overhead of creating new agents.

• Set NUM_INIT_AGENTS to be the same as NUM_POOLAGENTS. This causes the database to create the appropriate number of agents when it starts instead of creating them before a given request can be handled.

Example 2

In a system for which you do not want to enable the connection concentrator but want to allow for 250 connected users at one time, set the Database Manager Configuration parameters as follows:

MAX_CONNECTIONS to 250

MAX_COORDAGENTS to 250