DB2 Agent and optimization

DB2 agents are some of the processes or threads that serve application requests in the DB2 server. When an external application connects to an DB2 instance to make an access request, the DB2 agent is activated to answer those requests. The agent of General DB2 is called work agent, there are about three kinds of work agent: idle agent, coordination agent of activity, sub-agent.

• Idle agent: Refers to an agent that does not have any tasks. This agent does not serve any remote connections and does not serve local connections, and is in a standby or standby state.
• Active Coordinator: Refers to the agent that is in the working state, and each external application generates a database activity connection that has an activity coordination agent to serve it.
• Sub-agent: Refers to the next-level agent that receives the work distributed by the coordinating agent. Before DB2 V95, only sub-agents existed in the multi-partition environment (MPP) or intra-node parallel environment (SMP), and sub-agents could exist in all environments in DB2 V95.

There is a proxy pool in the DB2 server, and there are some proxies here (depending on the instance parameter num_initagents) when the instance has just started. When there are no database connections, they are on standby and are idle agents. When an external program connects to the database, the agents start getting commands to serve the new connections, and they become the coordinating agents for the activity. These coordination agents then gradually subdivide the request, assigned to the next level of agent is the sub-agent to deal with. If the current agent is already working and a new request is made, the database Manager will generate a new proxy to answer. When the transaction is complete and the database connection is broken, the coordinator agent either returns the agent pool back to the idle agent or disappears automatically (depending on the instance parameter num_poolagents). This is the life cycle of an agent.

Back to top of page

Related configuration parameters

By executing the DB2 get dbm cfg you can see several of the following agent-related instance parameters: Maxagents,num_poolagents,num_initagents,max_coordagents,max_connections, Maxcagents. Here is a brief introduction to them:

• MAXAGENTS: This parameter is the total number of agents in the current instance, including the coordination agent, the sum of the idle agent and the sub-agent. However, this parameter is no longer used in DB2 V95.
• num_poolagents: This parameter is used to control the number of idle agents in the agent pool. When the active agent completes the work Return agent pool becomes an idle agent, if the number exceeds this parameter, then the agent will automatically disappear. Note: In the case of connection concentrator activation, the number of idle agents in the agent pool may exceed the size of num_poolagents at some point in order to handle bursts of high-density connections.
• num_initagents: This parameter is the number of idle proxies that were previously mentioned when the instance was just started. This is to improve performance, because these agents can turn into a coordination agent at any time to answer external application requests instead of temporarily regenerating into new proxies.
• max_coordagents: This parameter determines the maximum number of coordination agents at the same time in the instance (in a multi-partition environment, which is the maximum number of coordination agents on a node).
• max_connections: This parameter determines the maximum number of connections allowed to an instance (in a multi-partition environment, which is the maximum number of connections on a node).
• maxcagent: This parameter determines the number of tokens in the instance, and a coordinating agent only gets tokens to serve the application. When the token is not available, the coordination agent waits only. However, this parameter has also been canceled in DB2 V95.

There is also a connection parameter MAXAPPLS can be obtained by DB2 get DB CFG for database_name, which is a database-level parameter that determines the maximum number of connections to a database at the same time. The sum of the MAXAPPLS values of all databases under one instance cannot exceed the instance parameter max_connections.

Back to top of page

Connection Concentrator 1. Basic principle

Starting with DB2 V8, there is an attribute in the DB2 instance called the connection concentrator that can be used to optimize the connection to the database. By default, when an instance is created, the values of max_connections and max_coordagents are consistent. This time each coordinating agent uniquely serves a connection. For example, there are 1000 connections that will have 1000 coordination agents to serve. This is a huge burden to the server, because each agent consumes a certain amount of resources. And when we set the value of the max_connections larger than the max_coordagents, then the DB2 connection concentrator is activated. It allows multiple connections to correspond to one agent.

The function of the connection concentrator is similar to the connection pool in DB2 connect. However, the advantage of a connection concentrator over a connection pool is that it can reuse an external connection, that is, multiple queued applications can reuse an existing connection, and the connection pool needs to be removed before rebuilding a connection to serve a new application. Each coordination agent in the connection concentrator does not uniquely serve one connection, and when an external connection is broken, the coordination agent is assigned to another connection. Such It also allows more connections to the database and reduces the memory consumption of each connection, avoiding the overhead of frequent deletion and creation of proxies. Here's how the connection concentrator works:

First set the value of Max_connections to greater than max_coordagents to activate the connection concentrator. In the connection concentrator, the agent is divided into logical agents and work agents. The logical proxy corresponds to an external application and does not correspond to a particular engine allocation unit (EDU). The work agent, as defined earlier, is the specific engine allocation unit. The connection concentrator is activated when the logical proxy is more than the work agent. When multiple connections are connected to the server at the same time, the connection is assigned to each logical proxy by one by one. The logical agent then goes to the service that requests the work agent.

For example, a proxy pool is a restaurant, usually more than a waiter in a restaurant. At first, there was no customer (equivalent to an external application). There are some waiters on duty waiting in the restaurant (equivalent to the idle agent Num_initagents created in the agent pool when the instance starts). Once the application request (customer), the scheduler (equivalent to the foreman) to arrange for the waiter to start work, the waiter began to busy to greet customers. At this point the role of the waiter is equivalent to coordination agent. After receiving the customer, they communicated the menu to the chef and the handyman (the equivalent of a sub-agent). And when more and more customers, more than the initial number of on-duty attendants. The server generates new proxies to serve these applications, as if calling more waiters from the staff quarters to work. When the number of attendants reached a number (max_coordagents), the restaurant all the waiters are working, there is no other staff. At this time the new customer (external application) can only sit in the seat waiting. Max_connections here is equivalent to the total number of dining seats in the hotel, when the number of customers (external application) reached this value, later customers can only leave (the equivalent of not connected to the database).

It is important to note that Max_connections does not refer to active connections that are connected to the instance at the same time, because some connections even on the instance have to wait for the coordination Agent service, and the number of connections currently active is equal to the number of coordinated agents for the activity. When a coordinating agent finishes processing an application, it is assigned to other waiting applications, which is equivalent to serving other waiting customers. There are also some seats in the hotel that are specially prepared for the waiter's rest (the number of seats is equivalent to num_poolagents). As customers gradually dispersed, and fewer, some of the waiters (coordination agents) have nothing to do, return to these seats (become an idle agent). When these seats are also occupied, there is no seat available for rest (assuming the waiter cannot sit in the dining seat) when the attendant (co-ordinator) returns to rest. These attendants will only be returned to the staff quarters (equivalent to the removal of agents). This process is reflected in Figure 1. The solid arrows in the graph indicate the current state, and the dashed arrows indicate the events that will occur.

Figure 1. Workflow Flowchart for Agents

2. DB2 V9.5 new Features

A new feature in DB2 V9.5 is that both max_connections and max_coordagents can be set to AUTOMATIC. If you think that the system can withstand all the connections and you want to limit the resources that are consumed by the coordinating agent, you can only set Max_connections to AUTOMATIC and max_coordagents to a value. At this point the system thinks that the number of connections to the instance can be unlimited. If you don't want to limit the maximum number of connections and the number of coordination agents, you can set them to AUTOMATIC. If at this point the value of Max_connections is set to AUTOMATIC is greater than the value max_coordagents set to AUTOMATIC, the connection concentrator is activated. The server then adjusts the coordination agent proportionally to the number of connections based on the ratio of the two values just as a reference (here called the concentration rate). Examples are as follows:

DB2 update dbm CFG using max_connections AUTOMATIC;

DB2 update dbm CFG using max_coordagents-AUTOMATIC;

At this point the concentration rate is 300/100=3, and when the connection is 1 to 100, a coordination agent is created, and a new coordination agent is not created when the greater than 100 is less than 301. Increased from 301 to 400, but also increased by 100 coordination agents, greater than 400 is less than 601 and stopped increasing ... That is, each additional 300 connections will add 100 coordination agents. The current specific values can be obtained by DB2 attach to instance_name, DB2 get dbm CFG show detail. Here are two things to allow to set AUTOMATIC:

• Max_connections is AUTOMATIC and max_coordagents is a certain value.
• Max_connections and max_coordagents are also AUTOMATIC.

Of course, the connection concentrator also has some limitations:

• Federated database does not support connection concentrator
• Connection concentrator is not valid for applications that use withhold feature
• Global temporary tables must be explicitly closed when the transaction completes, or the connection concentrator will be shut down
• Connections that connect a two-phase commit transaction can only be used to connect to a two-phase commit transaction, and the connection to a one-phase commit transaction can only be used to connect to a phase commit transaction.
• The connection concentrator cannot be activated online, that is, the instance needs to be restarted to take effect.

If you do not want to use the connection concentrator and you do not want to limit the number of database connections, you can run the following command:

DB2 update dbm CFG using max_coordagents AUTOMATIC;

DB2 update dbm CFG using max_connections AUTOMATIC;

Back to top of page

Agent and Connectivity FAQ analysis and Optimization 1. Connection overrun problem

The max_connections or MAXAGENTS value set in DB2 v8,v9.1 is compared to an hour, and an error occurs if there are too many external connections. The error is shown in Listing 1.

Listing 1. Db2diag.log Diagnostic logs

2008-01-15-14.30.13.090289-360 i12983210a1195     level:infopid     : 762076               TID  : 772         PROC: Db2acdinstance:db2inst1             node:000appid   : *local.db2inst1.080115203015eduid   : 772                  eduname: DB2ACDFUNCTION:DB2 UDB, DRDA communication Manager, sqljcreceive, PROBE:30MESSAGE:ZRC=0X8136001C=-2127167460=SQLZ_RC _no_connection, SQLT_SQLJC          "NO CONNECTION" DATA #1: String, Bytescci error:data #2: unsigned integer, 8 bytes ...

You can then view the current number of connections by using the following command:

Listing 2. View current number of connections

$ DB2 List Applicationsauth Id application Appl. Application Id DB # of name Handle name Agents-------------- ---------------------------------------------------------------------------------------------db2inst1 DB2TASKD 5 *local.db2inst1.080112150958 svt_db 1db2inst1 db2stmm 582 *local.db2inst1.0801121   50957 svt_db 1db2inst1 java 592 *local.db2inst1.080115201505 svt_db        1db2inst1 Java 572 *local.db2inst1.080115201445 svt_db 1db2inst1 java 585 *local.db2inst1.080115201458 svt_db 1db2inst1 java 565 *local.db2inst1.0801152014 PNS svt_db 1db2inst1 java 584 *local.db2inst1.080115201457 svt_db 1D  B2inst1 Java 590 *local.db2inst1.080115201503svt_db 1db2inst1 db2bp       591 *local.db2inst1.080115201502 ... 

You can make adjustments by looking at the comparison of the number of connections and max_connections values at this point. It should be noted that in the v9.1 or v9.5 environment, there are two special applications within the server DB2STMM and DB2TASKD should not be counted as external connections. DB2STMM is a proxy for managing memory auto-tuning features, and DB2TASKD is a proxy for assigning database background tasks. Java in the example represents an external connection from a Java application. DB2BP represents a connection from the CLP (DB2 Command Window). You can see that these connections are attached to the database svt_db.

You can then view the current number of agents by using the DB2PD command:

Listing 3. To view the current number of agents by using the DB2PD command

$ db2pd–agents–db svt_dbdatabase Partition 0--Active--up 1 days 01:24:44agents:current agents:36idle agents:            0Active coord agents:28active Agents total:28pooled coord agents:8pooled agents total:8address Apphandl [Nod-index] agenteduid priority Type State clientpid Userid clientnm rowsread rowswrtn Lktmot DBN      AME0X0780000000DABD60 522 [000-00522] 2315 0 Coord inst-active 655614 db2inst1 db2bp 375793     9620 NotSet svt_db0x07800000027a4160 523 [000-00523] 6170 0 Coord inst-active 655614 Db2inst1 DB2STMM 0 0 NotSet svt_db0x07800000027a5700 524 [000-00524] 6427 0 Coor  D inst-active 655614 db2inst1 db2taskd 0 0 NotSet svt_db0x0780000000dad840 525 [000-00525] 5158 0 Coord inst-active 655614 db2inst1 db2wlmd 0 0 NotSet svt_db0x078000000 27a0080 526 [000-00526] 5415 0 Coord inst-active 655614 db2inst1 db2evml_ 0 0 3 svt_db0x0780        0000028c0080 566 [000-00566] 10810 0 Coord inst-active 905284 db2inst1 java 160282 102 NotSet svt_db0x07800000027ab2c0 567 [000-00567] 7469 0 Coord inst-act ...

Here you see that the idle agents value of 0 indicates that there are no free proxies in the agent pool (state is all inst-active). The value of the current agents can then be compared to the value of MAXAGENTS, or the value of the Active agents total is compared to the value of max_coordagents, thereby making the corresponding adjustment.

For this issue, you can also make adjustments by analyzing a snapshot of the database manager:

Listing 4. Analyzing a snapshot of the database manager

DB2 get snapshot for dbm: ... Remote Connection executing in the database Manager = 58Local Connection executing in the database manager  = 1...Agent S assigned from pool                      = 38Agents created from empty pool                 = 158Agents stolen from another application         = 1High wa TER mark for coordinating agents        = 60MAX agents Overflow                            = 3  Hash joins after heap threshold exceeded       = 0 ......

You can see that the value of Max agents overflow equals 3, indicating that there are 3 generation agents exceeding the limit. You will see the previous error message in the DB2diag.log. You must adjust the value of MAXAGENTS to fix the current error. The max_coordagents can be set to the same value as the high water mark for coordinating agents, which can be set to MAXAGENTS in a single-partition environment in a multi-partition environment ( MPP) or intra-node parallel environment (SMP), the results are calculated based on the number of nodes MAXAGENTS = (n+1) * Max_coordagents (N is the number of nodes). On the other hand, if max_coordagents is not AUTOMATIC, if the Remote Connection executing in the value of the Database Manager is the same as the Local Connection execut The sum of the values of the ing in the Database Manager is close to max_coordagents, when the value of max_coordagents is increased appropriately.

In general, this principle, when the connection to the database is a memory error, adjust the following parameters:

• Increases the value of MAXAGENTS in a single partition and without intra-node parallelism (SMP).
• Increase the value of MAXAGENTS or max_coordagents in the case of multi-partition (MPP) or intra-node parallel environment (SMP).
• Increase the value of the max_connections in case the connection concentrator is active.

2. Connection Hang Problem

There is also a connection-related issue: The connection time is always longer when the database is first connected. This is because the database allocates memory for the first connection, primarily the buffer pool. The duration of the connection depends on the memory invocation of the operating system and the size of the buffer pool. Sometimes users tend to blindly expand the buffer pool in order to improve the application performance, causing the buffer pool to be set too large, or even more than the database shared memory, so that the instance can not allocate enough memory for the database, there will be a hang when connecting the database. And then want to set the buffer pool small also no way, because the database is not connected, unable to set the buffer pool. This is also a common problem. When confronted with this problem, some users are even forced to rebuild the database. In fact, this problem can be set by setting the DB2 registration parameter DB2_OVERRIDE_BPF to the size of the buffer pool, so that the database can be connected again. By default (v9.1,v9.5) the size of the buffer pool is set to 2 (via select Npages from Syscat. Bufferpools), which indicates that the buffer pool is automatically growing, in this case it is best not to modify the buffer pool size, you can let DB2 automatically adjust.

3. Common communication Errors

There are also some network communication-related errors that are commonly encountered when connecting to the database, such as sql30080,sql30081, and so on. There are several ways to try to solve the problem:

• Execute the command db2set–all to check if there is a db2comm=tcpip, and if not, add it.
• Execute command DB2 get dbm CFG | grep SVCENAME to check whether the services set by SVCENAME are defined in/etc/services (UNIX) (WINDOWS is in%windir%\system32\drivers\etc\ services). Of course, if SVCENAME is a port number, it is not defined in services. (port number should be less than 65536)
• Execute the command netstat–a check the output for any ports or services defined in the service that are listening. If not, you may need to restart your network or machine.
• This problem can also be caused by a firewall, which allows you to bypass the firewall by editing the/etc/sysconfig/iptables file (requires root privileges) on Linux.
• In WINDOWS sometimes you will also encounter "No buffer space available (maximum connections reached?)" Error message, this error is unrelated to DB2, and you need to increase the registry parameter value for WINDOWS:
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\SystemPages

If you encounter other special problems can be DB2 through the command? Sqlxxxxx to analyze the tool according to the tips obtained.

The body problem.

4. Performance optimization

Adjustment num_poolagents:

For decision support systems, due to the small number of connections, num_poolagents can be set to a smaller value, thereby avoiding excessive idle proxies and wasting resources. For online transaction processing systems, because of the number of connections, it can be set to a larger value, thereby reducing the system consumption resulting from frequent creation and deletion of agents. The specific values can be adjusted by analyzing the database Manager snapshot:

Listing 5. Tuning Num_poolagents by analyzing database Manager snapshots

DB2 get snapshot for dbm ... Agents assigned from Pool                      = 38Agents created from empty pool                  = 158Agents stolen from another application             = 1. .

When the ratio of Agents created from empty pool/agents Assigned from pool is small, the reuse rate of agent is higher. When the ratio is relatively large, the agent is created and deleted more frequently, at this time need to increase the num_poolagents to reduce the system frequently created, delete the agent when the resource consumption. The value of num_poolagents should also be increased when the value of Agents stolen from another application is large. Of course, if the num_poolagents is set too large, there may be a lot of unnecessary idle agents stuck in the agent pool for a long time, resulting in a waste of resources. The default value for Num_poolagents in v8,v9.1 is half of the value of MAXAGENTS, while the num_poolagents default in V9.5 is set to AUTOMATIC (initial value is 100) so that the database manager can automatically manage the agent Pool Hollow The number of idle agents.

Adjustment num_initagents:

The value of num_initagents is best consistent with the num_poolagents value. This reduces the time that the agent is generated when the transaction is processed, which is ideal for the user when it is transferred to the boot instance.

Adjust max_connections and max_coordagents:

Activate the connection concentrator, that is, set max_connections greater than max_coordagents, which can save the number of DB2 agents, reduce resource consumption, expand the number of connections. In V9.5 it is best to set max_connections and max_coordagents to AUTOMATIC, which allows the DB2 to automatically adjust the number of proxies based on the number of connections.

Back to top of page

The difference of DB2 v8,v9.1,v9.5 agent

DB2 has a lot of changes in agent features from V8 to V95, and table 1 lists some of the typical differences in features for readers to refer to.

Table 1:DB2 differences between different versions of agents

Characteristics	V8	V9.1	V9.5
New Agent	-	Db2taskd Db2stmm	Db2taskd Db2stmm
Proxy Presence Form	UNIX: Process Windows: Threads	UNIX: Process Windows: Threads	UNIX: Threads Windows: Threads
Subagent Presence Environment	Mpp Smp	Mpp Smp	Mpp Smp SERIAL
Whether the agent supports AUTOMATIC	Whether	Whether	Is
Whether parameters are supported MAXAGENTS Maxcagents Agentpri	Is	Is	Whether
Whether to support monitoring elements Max_agent_overfolows	Is	Is	Whether
Max_connections Default Value	Max_coordagents	Max_coordagents	AUTOMATIC
Num_poolagents Default Value	Maxagents/2	Maxagents/2	AUTOMATIC
Max_coordagents Maximum Value	MAXAGENTS	MAXAGENTS	64000
Num_initagents Maximum Value	Num_poolagents	Num_poolagents	64000

DB2 Agent and optimization