DB2 Instance and Fast Communication Manager - Part 2 - Page 4

FCM Tuning

With DB2 version 7, the DBA has to collect the snapshot statistics and adjust the parameters accordingly. Fortunately, in DB2 version 8.1, most of the FCM configuration parameters are self-configurable besides FCM buffer parameter.

FCM_NUM_BUFFERS

FCM resources can be monitored via database snapshot monitoring by frequently taking snapshots and storing the output in a log file. When the number of free buffers falls below 25% of free FCM buffers, adding new FCM buffers is urgently needed. My opinion in this is in direct opposition to the IBM documentation, which states to add additional FCM buffers at a a low limit of 15%. Because a shortage of FCM buffers can cause database crashes and serious performance degradation, it is my opinion that additional FCM buffers should be added if the free buffers fall below 25%. IBM documentation, for example does not mention the possibility of a database crash due to the lack of FCM buffers. In reality this is happening.

Let's look at a DBM configuration with 204.1 buffers, where the level of 512 free buffers would signal the necessity of making a change.

Online, the FCM buffers extension for DB2 version 8.1:

db2 => update dbm cfg using FCM_NUM_BUFFERS 4096
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

Changing the FCM_NUM_BUFFERS parameter in DB2 version 7 is similar, with only one exception. We need to restart DBM for changes to become active.

FCM_NUM_RQB

The following is an additional hint for DB2 version 7. The Parameter fcm_num_rqb has to be configured to support the maximum number of connections.

Listing DBM parameters related to the maximum connections:

$  db2 get dbm cfg |grep AGENTS
 Max number of existing agents               (MAXAGENTS) = 400
 Agent pool size                        (NUM_POOLAGENTS) = 40 (calculated)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
 Max no. of concurrent coordinating agents  (MAXCAGENTS) = MAX_COORDAGENTS
 Max number of logical agents          (MAX_LOGICAGENTS) = MAX_COORDAGENTS
 Max number of DARI processes                  (MAXDARI) = MAX_COORDAGENTS

According to the actual DBM setting, the calculation is next:

MAX_COORDAGENTS= MAXAGENTS-NUM_INITAGENTS = 400 0 = 400

My test system is configured to support the maximum number of 400 connections.

fcm_num_rqb >= max_coordagents*2,5= 400*2,5=1000

The current setting is sufficient for DBM defined agents.

# db2 get dbm cfg | grep FCM_NUM_RQB
 Number of FCM request blocks              (FCM_NUM_RQB) = 2048

Conclusion

With this article, Fast Communication Manager has been fully explained. There are some differences in FCM behavior between AIX and SUN platform in memory allocation. Intentionally I did not mix AIX into my article, leaving IBM to cover this area with some document in the future. Some things that every DBA should know before starting with any kind of tuning:

• current instance and database settings
• design/implementation structure
• number of database partitions
• number of active instance users
• level of application code complexity

Once armed with the above information, a DBA's view becomes sharpened and he can predict potentional hot spots in self-tuned and high intelligence DB2 databases.

» See All Articles by Columnist Marin Komadina