Administration Guide

Database Agents

DB2 servers must facilitate communication between the database manager and client and local applications. UNIX-based environments use an architecture based on processes. For example, the DB2 communications listeners are created as processes. Intel operating systems such as OS/2 and Windows NT use an architecture based on threads to maximize performance. For example, the DB2 communications listeners are created as threads within the DB2 server's system controller process. For each database being accessed, various processes/threads are started to deal with the various database tasks (for example, prefetching, communication, and logging).

One of the most crucial processes/threads are those of database agents, which facilitate the operations of applications with databases. Each process/thread of a client application has a single coordinator agent that operates on a database. Once the coordinator agent is created, it performs all database requests on behalf of its application, and communicates to other agents using inter-process communications (IPC) or remote communication protocols. Each agent operates with its own private memory and shares Database Manager and database global resources such as the buffer pool with other agents.

In partitioned database environments and environments with intra-partition parallelism enabled, the coordinator agent distributes database requests to subagents, and these agents perform the requests for the application. Once the coordinator agent is created, it handles all database requests on behalf of its application by coordinating the subagents that perform requests on the database.

When a client disconnects from a database or detaches from an instance the coordinating agent will be:

• Freed and marked as idle, if the maximum number of pool agents has not been reached
• Terminated and its storage freed, if the maximum number of pool agents has been reached.

When idle, agents are not performing work on behalf of any applications, are waiting to be assigned, and reside in an agent pool. These agents are available for requests from coordinator agents operating on behalf of client programs, or for subagents operating on behalf of existing coordinator agents. The number of available agents is dependent on the database manager configuration parameters maxagents and num_poolagents.

If no idle agents exist when an agent is required, a new agent must be dynamically created. Creating a new agent involves a certain amount of overhead and as a result, improved CONNECT and ATTACH performance can be noticed if there is an idle agent that can be activated for a client.

When a subagent is working on behalf of an application, it is considered to be associated with that application. After completing the assigned work, it may be placed in the agent pool, but it remains associated with the original application. When the application requests additional work, the database manager first checks the idle pool for associated agents when finding an agent to work for the application.

When working in an environment requiring the use of DB2 Connect to connect to remote systems there is an outbound connect pool. This connection pool reduces the connect time (following the first connection) to a host. When a disconnection from a host is requested, DB2 Connect drops the inbound connection but keeps the outbound connection to the host in a pool. When a new request is made to connect to the host, DB2 Connect reuses an existing outbound connection (if available) from the pool.

Note:

When using connection pooling, DB2 Connect is restricted to inbound TCP/IP and to outbound TCP/IP and SNA connections. When working with SNA, the security type must be NONE for the connection to be placed in the pool.

With connection pooling, the active agent does not close its outbound connection following disconnection, but goes into the agent pool with an active connection to the remote host. This type of agent is called inactive DRDA agent. The pool of inactive DRDA agents is a synonym for the outbound connection pool.

Consider the following examples based on four different usage and workload requirements:

1. In the first example, an average of 40 concurrent users connect to remote host databases through DB2 Connect. At times the number of concurrent connections peaks at about 50, but never exceeds 55. The transactions are of short duration, and user connect and disconnect frequently.

   With these conditions, the system administrator should configure MAX_COORDAGENTS to 55 since he knows that the maximum number of users what will ever try to connect through DB2 Connect at the same time is 55. NUM_POOLAGENTS, the size of the agent pool, should be set to 40 since, at any one time, that is the average number of users connected or trying to connect. This pool size guarantees enough existing remote database connections to satisfy all inbound clients without having to establish any new ones except when the workload peaks.

2. In this second example the workload is much higher with about 1 000 inbound clients. User connections are also of short duration. The system administrator does not want to allow any more concurrent connections than that. Therefore, the system administrator sets both MAX_COORDAGENTS and NUM_POOLAGENTS to 1 000. This means that the maximum number of inbound clients that may be concurrently connected to the remote database(s) is 1 000. When all clients disconnect, the pool will contain exactly 1 000 connected agents all waiting to service new inbound clients.

3. The third example involves a single application connecting through DB2 Connect to just one remote database. The application remains connected for long periods of time. In this scenario, the best agent and connection pool configuration is to set MAX_COORDAGENTS to 1 since we know that at most only one client will connect. NUM_POOLAGENTS may be set to zero in this case since there is no frequent connection and disconnection from the remote host. Setting NUM_POOLAGENTS to zero effectively disables connection pooling since no agents with active connections to the remote database are kept in the pool. For every new inbound client that connects, a new agent is created and a new remote connection established to service it.

4. The fourth example is a variation based on all three previous workload scenarios. In this example, the system administrator wants to restrict concurrent access to remote databases to just 100. Therefore, MAX_COORDAGENTS is set to 100 and, in order to maximize connect performance, NUM_POOLAGENTS is set to 100. However, later, there may also be a need to connect locally to monitor the workload on the system where DB2 Connect is installed. The expectation is that no more than 5 concurrent monitor snapshots would occur at any one time so MAX_COORDAGENTS is set to 105. This new configuration value allows the maximum number of concurrent applications to grow beyond the earlier upper limit of 100 to accommodate the occasional monitor snapshot and/or instance attachment.

For partitioned database environments and environments with intra-partition parallelism enabled, each partition (that is, each database server or node) has its own pool of agents from which subagents are drawn. Because of this pool, subagents do not have to be created and destroyed each time one is needed or is finished its work. The subagents can remain as associated agents in the pool and be used by the database manager for new requests from the application they are associated with.

The following database manager configuration parameters affect the number of database agents:

• Maximum Number of Agents (maxagents). Once the number of agents reaches this value, all subsequent requests that require a new agent are denied until the number of agents falls below the value. This value applies to the total number of agents, whether coordinating agents or subagents, that are working on all applications.

• Agent Pool Size (num_poolagents). The number of agents in the agent pool cannot exceed this value.

• Initial Number of Agents in Pool (num_initagents). When the database manager is started, a pool of idle agents is created based on this value. This speeds up performance for initial queries.

• Maximum Number of Coordinating Agents (max_coordagents). For partitioned database environments and environments with intra-partition parallelism enabled, this value limits the number of coordinating agents.

• Maximum Number of Concurrent Agents (maxcagents). This value controls the number of tokens permitted by the Database Manager. For each database transaction (unit of work) that occurs when a client is connected to a database, a coordinating agent must obtain permission to process the transaction (known as a processing token) from the Database Manager. Only agents with a processing token are permitted by the Database Manager to execute a unit of work against a database. If a token is not available, the agent will wait until one is available, at which time the requested unit of work will be processed.

  This parameter can be useful in an environment in which peak usage requirements exceed system resources (memory, CPU, and disk). In such an environment, the peak load may cause excessive performance degradation because of, for example, paging. You can use this parameter to control the load and avoid the performance degradation.

For partitioned database environments and environments with intra-partition parallelism enabled, the impact to performance and memory costs within the system is strongly related to how your agent pool is tuned:

• The database manager configuration parameter for agent pool size (num_poolagents) affects the total number of subagents that can be kept associated with applications on a partition (that is, node). If the pool size is too small (and the pool is full), a subagent will disassociate itself from the application it worked on and terminate. This situation leads to poor performance, because subagents must be constantly created and reassociated to applications.

  In addition, if the value of num_poolagents is too small, one application may fill the pool with associated subagents. Thus, when another application requires a new subagent and has no subagents in its associated agent pool, it will "steal" subagents from the agent pools of other applications. This situation is rather costly, and causes poor performance.

• The above situations must be weighed against the resource costs of allowing too many agents to be active at any given time.

  For example, if the value of num_poolagents is too large, associated subagents may sit unused in the pool for long periods of time. These subagents use database manager resources that will not be available for other tasks.

In addition to the database agents, there are other asynchronous activities performed by the Database Manager which run as their own process (or thread), including:

• Database I/O servers (or I/O prefetchers) (see Prefetching Data into the Buffer Pool)
• Database asynchronous page cleaners (see Managing the Database Buffer Pool)
• Database loggers
• Database deadlock detectors
• Event monitors
• Communication and IPC listeners
• Table space container rebalancers.

For more information on identifying the various DB2 processes, refer to the Troubleshooting Guide.

[ DB2 List of Books | Search the DB2 Books ]