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

Proactive DB2 database administrator

The proactive DBA will start from the very first moment with regular monitoring of database activity. Having more user activity and larger tables results in the transference of more data between partitions. More data increases the demand for the number of request blocks and FCM buffers. More intra-partition operations cause a higher demand on the number of request blocks and message anchors.

To protect the database from periodic occurrences of unsuccessful transactions and the associated rollbacks due the lack of FCM resources, a DBA has to collect the FCM snapshot information for the database manager instance in the following manner:

A demonstrative FCM snapshot, consisting of FCM buffers, message anchors, connection entries and request blocks statistics is shown above. This information has been collected during instance activity by FCM deamon, and stored in the internal catalog tables. The collected statistics have all the elements necessary for proper FCM diagnostic. A careful look at the statistics will uncover a lack in the FCM resources.

For our test we have following instance FCM settings:

  >> db2 get dbm cfg | grep FCM_NUM_BUFFERS
   No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024

The number of FCM buffers is 1024 in 4KB blocks, resulting in total of 4MB.

The reconstructed FCM buffers situation just before getting database errors:

2003-08-26-16.00.59.631414   Instance:artist   Node:002
PID:5506(db2agntp (ARTIST) 2)   Appid:820B6595.04DC.030826134443
buffer_Q_services  sqlkqget   Probe:20   Database:ARTIST
DIA9999E An internal error occurred. Report the following error code :
"alloc_buffer".

db2 => get snapshot for FCM FOR ALL NODES
...
Node FCM information corresponds to            = 0
Free FCM buffers                               = 1024
Free FCM buffers low water mark                = 204
...

DB2 was working regularly until the high water mark for the FCM buffers come to a low value of 20%, or 204,8. Once the limit was reached, the application was disconnected and the transaction rolled back. DBM has on disposal 204 FCM buffers, and the DB2 internal mechanism will handle the priority for the FCM buffer requests between agents. The remaining buffers will be used only for medium and high-level priority operations (for example, a rollback).

When a number of free FCM buffers fall more then 10% (102), only high priority requests are served. A shortage of FCM_NUM_BUFFERS can cause serious performance degradation (DB2 may stop responding), proven in practice not even documented in the IBM manuals.

A different error message, SQL6043, indicates that DBM is running out of FCM request blocks:

db2 => ? sql6043

SQL6043C No FCM request blocks are available.
Explanation:  No FCM request block is available.
The statement cannot be processed.
User Response:  Try the request again.
If the error persists, increase the number of FCM request blocks (fcm_num_rqb) 
  specified in the database manager configuration file, then try the request again.
sqlcode:  -6043
sqlstate:  57011

As indicated in the error description, a number of FCM request blocks have to be extended. The DBM parameter fcm_num_rqb is in strict relation with the max_coordagents parameter:

fcm_num_rqb >= max_coordagents*2,5

Setting the number of request blocks to a value lower than what is defined by the formula returns an error.

There is no need to persue this or other FCM parameters since all parameters except the FCM buffers are self-configurable starting in DB2 version 8.1.