Question & Answer
Question
How does DB2_KEEP_AS_AND_DMS_CONTAINERS_OPEN variable works and what is the possible impact in setting it?
Answer
This variable was introduced on DB2 v9.5 Fix Pack 1. By enabling this registry variable, DMS containers will start to occupy permanent space in the internal open file handle table (one slot per DMS container) and query performance might improve because the overhead to open the containers will be eliminated. This table contains entries for both SMS and DMS tablespaces. The maximum size of the table is controlled by MAXFILOP database parameter.
The setting values can be either Yes or No (please check the reference link for more details).
The default value for MAXFILOP starting at DB2 9.5 version is 61440. However if the parameter is set to a very small value, it may cause performance issue. DB2 will try to dynamically grow the file table one slot at a time, to prevent the instance from panic/sleep.
There isn't a way to predict what will be the impact in performance when setting the registry variable DB2_KEEP_AS_AND_DMS_CONTAINERS_OPEN but a recommended way would be by measuring the performance before and after setting it in a test environment.
If you want to check if you MAXFILOP parameter has a good setting, check the DATABASE SNAPSHOT "files_closed" monitor value to see how many files had to be victimized:
db2 "SELECT FILES_CLOSED FROM TABLE(SNAP_GET_DB_V97('', -2)) AS T"
FILES_CLOSED
--------------------
0
1 record(s) selected.
Additionally, db2diag.log will have logs for functions such as getFileHandle()routine like the ones below:
Example:
- xxxx-xx-xx-xx.xx.xx.xxxxx+xxx I251670885A519 LEVEL: Event
PID : 49754312 TID : 64834 PROC : db2sysc 0
INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxxxx
APPHDL : 0-49669 APPID: xxxxxxxx
AUTHID : xxxxxxxxx
EDUID : 64834 EDUNAME: db2agent (xxxxxxxx) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbFhdlTbl::doVictimSelection,
probe:20
DATA #1 : <preformatted>
Victim Selection Finished.
Current Open Files = 0
Total Victims = 1
xxxx-xx-xx-xx.xx.xx.xxxxx+xxx I251674905A644 LEVEL: Severe
PID : 49754312 TID : 64834 PROC : db2sysc 0
INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxxxx
APPHDL : 0-49669 APPID: xxxxxxxx
AUTHID : xxxxxxxxx
EDUID : 64834 EDUNAME: db2agent (xxxxxxxx) 0
FUNCTION: DB2 UDB, buffer pool services, SqlbFhdlTbl::getFileHandle,
probe:10
MESSAGE : ZRC=0x85020087=-2063466361=SQLB_NO_HANDLES
"SqlbFileTbl out of file handles."
DATA #1 : <preformatted>
WARNING : Maximum number of file handles have been consumed, no open BPS file handles to victimize.
xxxx-xx-xx-xx.xx.xx.xxxxx+xxx I251675550A543 LEVEL: Severe
PID : 49754312 TID : 64834 PROC : db2sysc 0
INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxxxx
APPHDL : 0-49669 APPID: xxxxxxxx
AUTHID : xxxxxxxxx
EDUID : 64834 EDUNAME: db2agent (xxxxxxxx) 0
FUNCTION: DB2 UDB, buffer pool services, SqlbFhdlTbl::getFileHandle,
probe:20
DATA #1 : unsigned integer, 8 bytes
0
DATA #2 : unsigned integer, 8 bytes
61440
DATA #3 : unsigned integer, 8 bytes
61440
xxxx-xx-xx-xx.xx.xx.xxxxx+xxx I251676094A519 LEVEL: Severe
PID : 49754312 TID : 64834 PROC : db2sysc 0
INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxxxx
APPHDL : 0-49669 APPID: xxxxxxxx
AUTHID : xxxxxxxxx
EDUID : 64834 EDUNAME: db2agent (xxxxxxxx) 0
FUNCTION: DB2 UDB, buffer pool services, SqlbFhdlTbl::getFileHandle,
probe:10
RETCODE : ZRC=0x85020087=-2063466361=SQLB_NO_HANDLES
"SqlbFileTbl out of file handles."
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21683499