Section 16.4. Configuring the DB2 Instance

16.4. Configuring the DB2 Instance

The Database Manager (instance-level) Configuration parameters affect the performance of all databases in the instance and all applications that access databases in the instance. This section examines the parameters that have the biggest impact on the system performance and gives suggestions on how to choose the right value for the parameter.

Table 16.2 lists the parameters with the biggest impact on system performance.

Table 16.2. Parameters with the Most Impact on System Performance for Configuring a DB2 Instance

Parameter Name	Description
RQRIOBLK	Maximum requester I/O block size.
INTRA_PARALLEL	Enables intra-partition parallelism.
SHEAPTHRES	Sort heap threshold.
NUM_INITAGENTS	Initial number of agents in pool.
NUM_POOLAGENTS	Agent pool size.

16.4.1. Maximum Requester I/O Block Size

The requester I/O block is a piece of memory that is used to send data back and forth between the DB2 server and the clients where the applications are running. This memory block passes application requests from the client to the server and returns the result set from the server back to the client. DB2 allocates as much memory as it needs, up to the maximum requester I/O block size. If the application request or the result set is larger than the block size, the data must be split into multiple blocks. These blocks are then sent using multiple underlying communication packets.

By default, the maximum size of requester I/O block size is 32KB; its maximum size is 64KB. Since DB2 only allocates and uses what is required, it is good practice to increase this to the maximum of 64KB, especially if your application requests and/or the result sets generated by your applications are greater than 32KB. This results in less network traffic and normally allows result sets to be returned to the application more quickly.

To increase the maximum requester I/O block size to 64KB, use the command:

 update dbm cfg using rqrioblk 64 

16.4.2. Intra-Partition Parallelism

Intra-partition parallelism refers to the ability to break up a query into multiple parts within a single database partition and execute these parts at the same time. This type of parallelism subdivides what is usually considered a single database operation, such as index creation, database load, or SQL queries, into multiple parts, many or all of which can be executed in parallel within a single database partition. You can use intra-partition parallelism to take advantage of multiple processors of a symmetric multiprocessor (SMP) server.

It is important to note that intra-partition parallelism is not recommended for all workloads, even if you are running DB2 on an SMP server. In general, if your workload is mostly OLTP and is composed of a large number of simple SQL statements, you will get better overall performance by disabling intra-partition parallelism. If you are running mostly large, complex queries on an SMP server, you likely will gain from enabling intra-partition parallelism.

You can enable or disable intra-partition parallelism using the instance-level configuration parameter INTRA_PARALLEL as follows:

 update dbm cfg using intra_parallel yes 

or

 update dbm cfg using intra_parallel no 

16.4.3. Sort Heap Threshold

If you issue an SQL statement that includes an ORDER BY, GROUP BY, or DISTINCT clause, DB2 must either use an index to sort the data or perform a sort operation. DB2 will need to sort the data if you have not defined an appropriate index on one or more of the tables you are querying.

DB2 can perform either a private sort or a shared sort. If you have not enabled intra-partition parallelism, then DB2 can perform private sorts only. If you have enabled intra-partition parallelism, DB2 then can choose private or shared sorts, depending on which is more optimal.

DB2 creates and assigns each individual sort its own sort heap space. The sort heap threshold specifies the maximum amount of memory that can be simultaneously used for private sorts across all databases in the DB2 instance. When the total amount of private sort memory in use reaches the sort heap threshold, DB2 reduces the amount of memory that is assigned to subsequent sort operations. So in effect, the sort heap threshold is a soft limit, since DB2 can allocate more total sort space than the sort heap threshold specifies.

Set the sort heap threshold based on the size of your database sort heaps and the average number of concurrently executing applications. For example, if:

• The sort heap for your database is set to 256 pages

and

• Database monitor snapshots show an average of 20 concurrently executing applications

you should set the sort heap threshold to at least 5,120 pages (20 x 256 = 5120). Since some access plans do more than one sort operation, you may want to set the sort heap threshold to 10,240 pages. To set the sort heap threshold to this value, use the command:

 update dbm cfg using sheapthres 10240 

16.4.4. The DB2 Agent Pool

Since all database requests are handled by the DB2 agents, the agent configuration is very important to overall system performance. The two main configuration parameters that influence the agent operation are the agent pool size and the number of initialized agents.

Let's step through the interaction of applications, DB2 agents, and the agent pool using as an example a database that has not been activated and that has had no other connections (i.e., immediately after a db2start was issued). When an application requests a connection to the database, DB2 creates a db2 agent process (or thread on Windows) to handle the application's requests. There is some overhead involved in creating this agent process and with freeing it when the application disconnects from the database.

The goal of the agent pool is to eliminate the overhead of creating and deleting the agent processes for all applications. If you configure DB2 to use the agent pool, when your applications disconnect from the database, instead of deleting the agent process, it is placed in the agent pool for reuse by other applications. An agent in the agent pool is considered idle, since it is not working on behalf of an application.

When a subsequent application requests a database connection, DB2 will examine the agent pool to see if there is an available agent that can be reused. If there is, it is then associated with the application that needs it, and DB2 avoids the overhead of creating the agent process. The agent pool size (NUM_POOLAGENTS) specifies the number of idle agents that DB2 will maintain before ending the agent processes as they become idle.

In the above example, the number of initialized agents was zero (by default), so that the first connection to the database always needs to create the DB2 agent process. DB2 also allows you to prime the idle agent pool when DB2 is started so that you do not have the overhead of creating the first few agent processes. The number of initialized agents (NUM_INITAGENTS) specifies the number of idle agents to create in the agent pool when DB2 is started to optimize the initial database connection requests.

If your workload contains relatively few concurrent applications, you do not need to have a large idle agent pool, as this may waste resources if you create more agents than needed. If your workload contains a number of concurrent applications, you should have an idle agent pool that will avoid the cost of constantly creating and ending the agent processes.

To set the size of the agent pool to 64, use the command:

 update dbm cfg using num_poolagents 64 

To initialize 32 agents when DB2 is started to minimize the overhead of the initial application connect requests, use the command:

 update dbm cfg using num_initagents 32 

In this case, when the DB2 instance is started there will be 32 agent processes created in the idle agent pool. The first 32 applications (assuming that intra-partition parallelism is disabled for this example) will then use these agent processes. The 33^rd application will find that there are no agent processes in the pool, so DB2 will create one for the application. This will continue for all subsequent applications unless one of the previous applications disconnects. If no applications disconnect, the idle agent pool will not be used. However, as applications disconnect from the database, DB2 will keep up to 64 idle agents in the pool to wait for reuse. If you had 100 applications connected, the first 64 would have their agents put into the pool as they disconnect. When the 65^th application disconnects, its agent process will be terminated since the idle agent pool is already full.