System Monitor Guide and Reference

Database Manager Configuration

The following elements provide database manager configuration information.

Agents and Connections

An agent is a process or thread that carries out the requests made by a client application. Each connected application is served by exactly 1 coordinator agent and possibly, a set of subordinator agents or subagents. Subagents are used for parallel SQL processing in partitioned databases and on SMP machines. Agents are classified as follows:

• Coordinator agent

  This is the initial agent to which a local or remote application connects. There is one coordinator agent dedicated to each database connection or instance attachment. The maximum number of coordinating agents per partition is controlled by the max_coordagents configuration parameter.

• Subagent

  In partitioned databases, additional agents can be enlisted by the coordinator agent to speed up SQL processing. Subagents are selected from the agent pool and are returned there when their work is done. The size of the agent pool is controlled by the num_poolagents configuration parameter.

• Associated agent

  A coordinator or subagent that is doing work for an application is associated with that application. After it is finished an application's work, it goes into the agent pool as an associated agent. If the application attempts to do more work, DB2 will search the agent pool for an agent already associated with the application and assign the work to it. If none is found, DB2 will attempt to get an agent to satisfy the request by:

  1. Choosing an idle agent that is not associated with an application.
  2. Creating an agent, if an idle agent is not available.
  3. Finding an agent that is associated with another application. For example, if an agent cannot be created because maxagents has been reached, DB2 will try to take an idle agent associated with another application. This is referred to as a stolen agent.
• Primed agent

  A gateway agent in the DRDA connections pool that is connected to a DRDA database in anticipation of work on the remote database.

The maxagents configuration parameter defines the maximum number of agents, regardless of type, that can exist for an instance. The maxagents value does not create any agents. The initial number of agents that are created in the agent pool at DB2START is determined by the num_initagents configuration parameter.

Assuming no idle agents, each connection creates a new agent, unless max_coordagents has been reached. If subagents are not used, max_coordagents equals maxagents. If subagents are used, some combination of coordinator agents and subagents could reach maxagents.

When an agent is assigned work, it attempts to obtain a token or permission to process the transaction. The database manager controls the number of tokens available using the maxcagents configuration parameter. If a token is not available, the agent will sleep until one becomes available, at which time the requested work will be processed. This allows you to use maxcagents to control the load, or number of concurrently executing transactions, the server handles.

The following elements provide agent and connection information:

• Remote Connections To Database Manager
• Remote Connections Executing in the Database Manager
• Local Connections
• Local Connections Executing in the Database Manager
• Local Databases with Current Connects
• Connects Since Database Activation
• Applications Connected Currently
• Applications Executing in the Database Currently
• Agents Registered
• Agents Waiting for a Token
• Maximum Number of Agents Registered
• Maximum Number of Agents Waiting
• Number of Idle Agents
• Agents Assigned From Pool
• Agents Created Due to Empty Agent Pool
• Maximum Number of Coordinating Agents
• Stolen Agents
• Maximum Number of Associated Agents
• Committed Private Memory
• Secondary Connections
• Number of Associated Agents
• Maximum Agent Overflows
• Total Inactive DRDA Agents
• Connection Switches

)"

Remote Connections To Database Manager

Description:  The current number of connections initiated from remote clients to the instance of the database manager that is being monitored.

Usage:  Shows the number of connections from remote clients to databases in this instance. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage. This number does not include applications that were initiated from the same instance as the database manager.

When used in conjunction with the "Local Connections" monitor element, these elements can help you adjust the setting of the max_coordagents configuration parameter, described in the Administration Guide.

Remote Connections Executing in the Database Manager

Description:  The number of remote applications that are currently connected to a database and are currently processing a unit of work within the database manager instance being monitored.

Usage:  This number can help you determine the level of concurrent processing occurring on the database manager. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage. This number does not include applications that were initiated from the same instance as the database manager.

When used in conjunction with the "Local Connections Executing in the Database Manager" monitor element, this element can help you adjust the setting of the maxcagents configuration parameter, described in the Administration Guide.

Local Connections

Description:  The number of local applications that are currently connected to a database within the database manager instance being monitored.

Usage:  This number can help you determine the level of concurrent processing occurring in the database manager. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage.

This number only includes applications that were initiated from the same instance as the database manager. The applications are connected, but may or may not be executing a unit of work in the database.

When used in conjunction with the "Remote Connections To Database Manager" monitor element, this element can help you adjust the setting of the maxagents configuration parameter, described in the Administration Guide.

Local Connections Executing in the Database Manager

Description:  The number of local applications that are currently connected to a database within the database manager instance being monitored and are currently processing a unit of work.

Usage:  This number can help you determine the level of concurrent processing occurring in the database manager. This value will change frequently, so you may need to sample it at specific intervals over an extended period of time to get a realistic view of system usage. This number only includes applications that were initiated from the same instance as the database manager.

When used in conjunction with the "Remote Connections Executing in the Database Manager" monitor element, this element can help you adjust the setting of the maxcagents configuration parameter, described in the Administration Guide.

Local Databases with Current Connects

Description:  The number of local databases that have applications connected.

Usage:  This value gives an indication of how many database information records you can expect when gathering data at the database level.

The applications can be running locally or remotely, and may or may not be executing a unit of work within the database manager

Connects Since Database Activation

Description:  Indicates the number of connections to the database since the first connect, activate, or last reset (coordinator agents).

Usage:  You can use this element in conjunction with the "Database Activation Timestamp" and the "Start Database Manager Timestamp" monitor elements to calculate the frequency at which applications have connected to the database.

If the frequency of connects is low, you may want to explicitly activate the database using the ACTIVATE DATABASE command before connecting any other application, because of the extra overhead that is associated with the first connect to a database (for example, initial buffer pool allocation). This will result in subsequent connects being processed at a higher rate.

Note:

When you reset this element, its value is set to the number of applications that are currently connected, not to zero.

Applications Connected Currently

Description:  Indicates the number of applications that are currently connected to the database.

Usage:  You may use this element to help you understand the level of activity within a database and the amount of system resource being used.

It can help you adjust the setting of the maxappls and max_coordagents configuration parameters, which are described in the Administration Guide. For example, its value is always the same as maxappls, you may want to increase the value of maxappls. See the "Remote Connections To Database Manager" and the "Local Connections" monitor elements for more information.

Applications Executing in the Database Currently

Description:  Indicates the number of applications that are currently connected to the database, and for which the database manager is currently processing a request.

Usage:  You can use this element to understand how many of the database manager agent tokens are being used by applications connected to this database. If the sum of "Remote Connections Executing in the Database Manager" and "Local Connections Executing in the Database Manager" is equal to the value of the maxcagents configuration parameter, you may want to increase the value of that parameter, as described in the Administration Guide.

Agents Registered

Description:  The number of agents registered in the database manager instance that is being monitored (coordinator agents and subagents).

Usage:  You can use this element to help evaluate your setting for the maxagents configuration parameter.

Agents Waiting for a Token

Description:  The number of agents waiting for a token so they can execute a transaction in the database manager.

Usage:  You can use this element to help evaluate your setting for the maxcagents configuration parameter.

Each application has a dedicated coordinator agent to process database requests within the database manager. Each agent has to get a token before it can execute a transaction. The maximum number of agents that can execute database manager transactions is limited by the configuration parameter maxcagents. For more information about this parameter, see the Administration Guide.

Maximum Number of Agents Registered

Description:  The maximum number of agents that the database manager has ever registered, at the same time, since it was started (coordinator agents and subagents).

Usage:  You may use this element to help you evaluate your setting of the maxagents configuration parameter, described in the Administration Guide.

The number of agents registered at the time the snapshot was taken is recorded by "Agents Registered".

Maximum Number of Agents Waiting

Description:  The maximum number of agents that have ever been waiting for a token, at the same time, since the database manager was started.

Usage:  You may use this element to help you evaluate your setting of the maxcagents configuration parameter, described in the Administration Guide.

The number of agents waiting for a token at the time the snapshot was taken is recorded by "Agents Waiting for a Token".

If the maxcagents parameter is set to its default value (-1), no agents should wait for a token and the value of this monitor element should be zero.

Number of Idle Agents

Description:  The number of agents in the agent pool that are currently unassigned to an application and are, therefore, "idle".

Usage:  You can use this element to help set the num_poolagents configuration parameter. Having idle agents available to service requests for agents can improve performance. See the Administration Guide for more information.

Agents Assigned From Pool

Description:  The number of agents assigned from the agent pool.

Usage:  This element can be used with "Agents Created Due to Empty Agent Pool" to determine how often an agent must be created because the pool is empty.

If the ratio of

     Agents Created Due to Empty Agent Pool / Agents Assigned From Pool

is high, it may indicate that the num_poolagents configuration parameter should be increased. A low ratio suggests that num_poolagents is set too high, and that some of the agents in the pool are rarely used and are wasting system resources.

A high ratio can indicate that the overall workload for this node is too high. You can adjust the workload by lowering the maximum number of coordinating agents specified by the maxcagents configuration parameter, or by redistributing data among the nodes.

See the Administration Guide for more information on the Agent Pool Size (num_poolagents) and Maximum Number of Concurrent Coordinating Agents (maxcagents) configuration parameters.

Agents Created Due to Empty Agent Pool

Description:  The number of agents created because the agent pool was empty. It includes the number of agents started at DB2 start up (num_initagents).

Usage:  In conjunction with "Agents Assigned From Pool", you can calculate the ratio of

     Agents Created Due to Empty Agent Pool / Agents Assigned From Pool

See Agents Assigned From Pool for information on using this element.

Maximum Number of Coordinating Agents

Description:  The maximum number of coordinating agents working at one time.

Usage:  If the peak number of coordinating agents represents too high a workload for this node, you can reduce the number that can be concurrently executing a transaction by changing the maxcagents configuration parameter.

See the Administration Guide for more information on the Maximum Number of Concurrent Coordinating Agents (maxcagents) configuration parameter.

Stolen Agents

Description:  The number of times that agents are stolen from an application. Agents are stolen when an idle agent associated with an application is reassigned to work on a different application.

Usage:  This element can be used in conjunction with Maximum Number of Associated Agents to evaluate the load that this application places on the system.

Maximum Number of Associated Agents

Description:  The maximum number of subagents associated with this application.

Usage:  If the peak number of subagents is close to the num_poolagents configuration parameter, this might indicate too high a workload for this node.

See the Administration Guide for more information on the Agent Pool Size (num_poolagents) configuration parameter.

Committed Private Memory

Description:  The amount of private memory that the instance of the database manager has currently committed at the time of the snapshot.

Usage:  You can use this element to help set the min_priv_mem configuration parameter (see the Administration Guide) to ensure you have enough private memory available. This element is returned for all platforms, but tuning can only be accomplished on platforms where DB2 uses threads (such as OS/2 and Windows NT).

Secondary Connections

Description:  The number of connections made by a subagent to the database at the node.

Usage:  You can use this element in conjunction with the "Connects Since Database Activation", "Database Activation Timestamp", and the "Start Database Manager Timestamp" monitor elements to calculate the frequency at which applications have connected to the database.

Number of Associated Agents

Description:  At the application level, this is the number of subagents associated with an application. At the database level, it is the number of subagents for all applications.

Usage:  You can use this element to help evaluate your settings for your agent configuration parameters.

Maximum Agent Overflows

Description:  The number of times a request to create a new agent was received when the maxagents configuration parameter had already been reached.

Usage:  If agent creation requests are still being received when the maxagents configuration parameter has been reached, this might indicate too high a workload for this node.

See the Administration Guide for more information on the Maximum Number of Agents (maxagents) configuration parameter.

Total Inactive DRDA Agents

Description:  The number of DRDA agents in the DRDA connections pool that are primed with a connection to a DRDA database, but are inactive.

Usage:  Using this element over time will help determine if the number of agents allocated to the connections pool is adequate.

Connection Switches

Description:  The number of times that an agent from the agents pool was primed with a connection and was stolen for use with a different DRDA database.

Usage:  Use this element in conjunction with Total Inactive DRDA Agents to determine if the size of the agent pool should be increased.

Sort

The following elements provide information about the database manager sort work performed:

• Total Sort Heap Allocated
• Post Threshold Sorts
• Piped Sorts Requested
• Piped Sorts Accepted
• Total Sorts
• Total Sort Time
• Sort Overflows
• Active Sorts

Total Sort Heap Allocated

Description:  The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

Usage:  The amount of memory allocated for each sort may be some or all of the available sort heap size. Sort heap size is the amount of memory available for each sort as defined in the database configuration parameter sortheap.

It is possible for a single application to have concurrent sorts active. For example, in some cases a SELECT statement with a subquery can cause concurrent sorts.

Information may be collected at two levels:

• At the database manager level, it represents the sum of sort heap space allocated for all sorts in all active databases in the database manager
• At the database level, it represents the sum of the sort heap space allocated for all sorts in a database.

Normal memory estimates do not include sort heap space. If excessive sorting is occurring, the extra memory used for the sort heap should be added to the base memory requirements for running the database manager. Generally, the larger the sort heap, the more efficient the sort. Appropriate use of indexes can reduce the amount of sorting required.

You may use the information returned at the database manager level to help you tune the sheapthres configuration parameter. If the element value is greater than or equal to sheapthres, it means that the sorts are not getting the full sort heap as defined by the sortheap parameter.

Post Threshold Sorts

Description:  The number of sorts that have requested heaps after the sort heap threshold has been reached.

Usage:  Under normal conditions, the database manager will allocate sort heap using the value specified by the sortheap configuration parameter. If the amount of memory allocated to sort heaps exceeds the sort heap threshold (sheapthres configuration parameter), the database manager will allocate sort heap using a value less than that specified by the sortheap configuration parameter.

Each active sort on the system allocates memory, which may result in sorting taking up too much of the system memory available. Sorts that start after the sort heap threshold has been reached may not receive an optimum amount of memory to execute, but, as a result, the entire system may benefit. By modifying the sort heap threshold and sort heap size configuration parameters, the performance of sort operations and/or the overall system can be improved. If this element's value is high, you can:

• Increase the sort heap threshold (sheapthres) or,
• Adjust applications to use fewer or smaller sorts via SQL query changes.

Piped Sorts Requested

Description:  The number of piped sorts that have been requested.

Usage:  Each active sort on the system allocates memory, which may result in sorting taking up too much of the available system memory.

The sort list heap (sortheap) and sort heap threshold (sheapthres) configuration parameters help to control the amount of memory used for sort operations. These parameters are also used to determine whether a sort will be piped.

Since piped sorts may reduce disk I/O, allowing more piped sorts can improve the performance of sort operations and possibly the performance of the overall system. A piped sort is not be accepted if the sort heap threshold will be exceeded when the sort heap is allocated for the sort. See "Piped Sorts Accepted" for more information if you are experiencing piped sort rejections.

The SQL EXPLAIN output will show whether the optimizer requests a piped sort. For more information on piped and non-piped sorts see the Administration Guide.

Piped Sorts Accepted

Description:  The number of piped sorts that have been accepted.

Usage:  Each active sort on the system allocates memory, which may result in sorting taking up too much of the available system memory.

When the number of accepted piped sorts is low compared to the number requested, you can improve sort performance by adjusting one or both of the following configuration parameters:

• sortheap
• sheapthres

If piped sorts are being rejected, you might consider decreasing your sort heap or increasing your sort heap threshold. You should be aware of the possible implications of either of these options. If you increase the sort heap threshold, then there is the possibility that more memory will remain allocated for sorting. This could cause the paging of memory to disk. If you decrease the sort heap, you might require an extra merge phase that could slow down the sort.

See the Administration Guide for more information on sorts.

Total Sorts

Description:  The total number of sorts that have been executed.

Usage:  At a database or application level, use this value with "Sort Overflows" to calculate the percentage of sorts that need more heap space. You can also use it with "Total Sort Time" to calculate the average sort time.

If the number of sort overflows is small with respect to the total sorts, then increasing the sort heap size may have little impact on performance, unless this buffer size is increased substantially.

At a statement level, use this element to identify statements which are performing large numbers of sorts. These statements may benefit from additional tuning to reduce the number of sorts. You can also use the SQL EXPLAIN statement to identify the number of sorts a statement performs. See the Administration Guide for more information.

Total Sort Time

Description:  The total elapsed time (in milliseconds) for all sorts that have been executed.

Usage:  At a database or application level, use this element with "Total Sorts" to calculate the average sort time, which can indicate whether or not sorting is an issue as far as performance is concerned.

At a statement level, use this element to identify statements that spend a lot of time sorting. These statements may benefit from additional tuning to reduce the sort time.

This count also includes sort time of temporary tables created during related operations. It provides information for one statement, one application, or all applications accessing one database.

When using data elements providing elapsed times, you should consider:

1. Elapsed times are affected by system load, so the more processes you have running, the higher this elapsed time value.
2. To calculate this data element at a database level, the database system monitor sums the application-level times. This can result in double counting elapsed times at a database level, since more than one application process can be running at the same time.

   To provide meaningful data from the database level, you should normalize the data to a lower level. For example:

    
    total sort time / total sorts
   

   provides information about the average elapsed time for each sort.

Sort Overflows

Description:  The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.

Usage:  At a database or application level, use this element in conjunction with "Total Sorts" to calculate the percentage of sorts that had to overflow to disk. If this percentage is high, you may want adjust the database configuration by increasing the value of sortheap.

At a statement level, use this element to identify statements that require large sorts. These statements may benefit from additional tuning to reduce the amount of sorting required.

When a sort overflows, additional overhead will be incurred because the sort will require a merge phase and can potentially require more I/O, if data needs to be written to disk.

This element provides information for one statement, one application, or all applications accessing one database.

Active Sorts

Description:  The number of sorts in the database that currently have a sort heap allocated.

Usage:  Use this value in conjunction with "Total Sort Heap Allocated" to determine the average sort heap space used by each sort. If the sortheap configuration parameter is substantially larger than the average sort heap used, you may be able to lower the value of this parameter. (See the Administration Guide for more details.)

This value includes heaps for sorts of temporary tables that were created during relational operations.

Hash Join

Hash join is an additional option for the optimizer. A hash join will first compare hash codes before comparing predicates for tables involved in a join. In a hash join, one table (selected by the optimizer) is scanned and rows are copied into memory buffers drawn from the sort heap allocation. The memory buffers are divided into partitions based on a hash code computed from the columns of the join predicates. Rows of the other table involved in the join are matched to rows from the first table by comparing the hash code. If the hash codes match, the actual join predicate columns are compared.

• Total Hash Joins
• Hash Join Threshold
• Total Hash Loops
• Hash Join Overflows
• Hash Join Small Overflows

Total Hash Joins

Description:  The total number of hash joins executed.

Usage:  At the database or application level, use this value in conjunction with "Hash Join Overflows" and "Hash Join Small Overflows" to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.

Hash Join Threshold

Description:  The total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space.

Usage:  If this value is large (greater than 5% of Hash Join Overflows), the sort heap threshold should be increased.

Total Hash Loops

Description:  The total number of times that a single partition of a hash join was larger than the available sort heap space.

Usage:  Values for this data element indicate inefficient execution of hash joins. This might indicate that the sort heap size is too small or the sort heap threshold is too small. Use this value in conjunction with the other hash join variables to tune the sort heap size (sortheap) and sort heap threshold (sheapthres) configuration parameters.

Hash Join Overflows

Description:  The number of times that hash join data exceeded the available sort heap space.

Usage:  At the database level, if the percentage of "Hash Join Small Overflows" is greater than 10% of this value, then you should consider increasing the sort heap size. Values at the application level can be used to evaluate hash join performance for individual applications.

Hash Join Small Overflows

Description:  The number of times that hash join data exceeded the available sort heap space by less than 10%.

Usage:  If this value and "Hash Join Overflows" are high, then you should consider increasing the sort heap threshold. If this value is greater than 10% of "Hash Join Overflows", then you should consider increasing the sort heap size.

Fast Communication Manager

The following database system monitor elements provide information about the Fast Communication Manager (FCM):

• FCM Buffers Currently Free
• Minimum FCM Buffers Free
• Message Anchors Currently Free
• Minimum Message Anchors
• Connection Entries Currently Free
• Minimum Connection Entries
• Request Blocks Currently Free
• Minimum Request Blocks
• Number of Nodes
• Connection Status
• Total FCM Buffers Sent
• Total FCM Buffers Received

FCM Buffers Currently Free

Description:  This element indicates the number of FCM buffers currently free.

Usage:  Use the number of FCM buffers currently free in conjunction with the fcm_num_buffers configuration parameter to determine the current FCM buffer pool utilization. You can use this information to tune fcm_num_buffers.

Minimum FCM Buffers Free

Description:  The lowest number of free FCM buffers reached during processing.

Usage:  Use this element in conjunction with the fcm_num_buffers configuration parameter to determine the maximum FCM buffer pool utilization. If buff_free_bottom is low, you should increase fcm_num_buffers to ensure that operations do not run out of FCM buffers. If buff_free_bottom is high, you can decrease fcm_num_buffers to conserve system resources.

Message Anchors Currently Free

Description:  This element indicates the number of message anchors currently free.

Usage:  Use the number of message anchors currently free in conjunction with the fcm_num_anchors configuration parameter to determine the current message anchor utilization. You can use this information to tune fcm_num_anchors.

Minimum Message Anchors

Description:  The lowest number of free message anchors reached during processing.

Usage:  Use this element in conjunction with the fcm_num_anchors configuration parameter to determine the maximum message anchors utilization. If MA_free_bottom is low, you should increase fcm_num_anchors to ensure that operations do not run out of message anchors. If MA_free_bottom is high, you can decrease fcm_num_anchors to conserve system resources.

Connection Entries Currently Free

Description:  This element indicates the number of connection entries currently free.

Usage:  Use the number of connection entries currently free in conjunction with the fcm_num_connect configuration parameter to determine the current connection entry utilization. You can use this information to tune fcm_num_connect.

Minimum Connection Entries

Description:  The lowest number of free connection entries reached during processing.

Usage:  Use this element in conjunction with the fcm_num_connect configuration parameter to determine the maximum connection entry utilization. If CE_free_bottom is low, you should increase fcm_num_connect to ensure that operations do not run out of connection entries. If CE_free_bottom is high, you can decrease fcm_num_connect to conserve system resources.

Request Blocks Currently Free

Description:  This element indicates the number of request blocks currently free.

Usage:  Use the number of request blocks currently free in conjunction with the fcm_num_rqb configuration parameter to determine the current request block utilization. You can use this information to tune fcm_num_rqb.

Minimum Request Blocks

Description:  The lowest number of free request blocks reached during processing.

Usage:  Use this element in conjunction with the fcm_num_rqb configuration parameter to determine the maximum request block utilization. If RB_free_bottom is low, you should increase fcm_num_rqb to ensure that operations do not run out of request blocks. If RB_free_bottom is high, you can decrease fcm_num_rqb to conserve system resources.

Number of Nodes

Description:  The number of nodes in the current configuration.

Usage:  Use this element to determine the number of fcm_node structures that will be returned.

Connection Status

Description:  This element indicates the status of the communication connection status between the node issuing the GET SNAPSHOT command and other nodes listed in the db2nodes.cfg file.

Usage:  The connection values are :

SQLM_FCM_CONNECT_INACTIVE

No current connection

SQLM_FCM_CONNECT_ACTIVE

Connection is active

SQLM_FCM_CONNECT_CONGESTED

Connection is congested

Two nodes can be active, but the communication connection between them will remain inactive, unless there is some communication between those nodes.

Total FCM Buffers Sent

Description:  The total number of FCM buffers that have been sent from the node issuing the GET SNAPSHOT command to the node identified by the node_number (see the db2nodes.cfg file).

Usage:  You can use this element to measure the level of traffic between the current node and the remote node. If the total number of FCM buffers sent to this node is high, you may want to redistribute the database, or move tables to reduce the inter-node traffic.

Total FCM Buffers Received

Description:  The total number of FCM buffers received by the node issuing the GET SNAPSHOT command from the node identified by the node_number (see the db2nodes.cfg file).

Usage:  You can use this element to measure the level of traffic between the current node and the remote node. If the total number of FCM buffers received from this node is high, you may want to redistribute the database, or move tables to reduce the inter-node traffic.