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.
» 
