Excluding a Table from Db2’s Automatic Runstats
Corrected on 9/8/2017 to reflect correct syntax to eliminate just the one table.
The application in this case – SPSS – maintains an exclusive lock at all times on one table. I think this is poor application design, but IBM was not willing to change it when we brought it to their attention in a PMR. This leads to dozens of lock timeouts a day when automatic statistics evaluation tries to get a lock on the table to evaluate whether the table needs runstats or not.
This problem didn’t show up quite the way I would expect it to. Using my favorite locking event monitor, the lock timeouts show up as being against SYSIBM.SYSTABLES and NULL:
select substr(lp.table_schema,1,18) as table_schema , substr(lp.table_name,1,30) as table_name , substr(le.event_type,1,18) as lock_event , count(*)/2 as count from DBA.LOCK_PARTICIPANTS lp, DBA.LOCK_EVENT le where lp.xmlid=le.xmlid group by lp.table_schema, lp.table_name, le.event_type order by lp.table_schema, lp.table_name, le.event_type with ur; TABLE_SCHEMA TABLE_NAME LOCK_EVENT COUNT ------------------ ------------------------------ ------------------ ----------- SYSIBM SYSTABLES LOCKTIMEOUT 26765 - - LOCKTIMEOUT 26765 2 record(s) selected.
This confused me at first, and with help from IBM Support, we identified that the lock timeouts are related to the SIBOWNER table. In the db2 diagnostic path, there is a subdirectory called events. This directory contains records related to statistics collection – both manual and automatic. Within the files in this directory, we see messages like this:
2017-08-24-23.11.08.695965-420 I886320E1035 LEVEL: Severe PID : 30986 TID : 140729082963712 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SPSSDB APPHDL : 0-20515 APPID: *LOCAL.db2inst1.170825060338 AUTHID : DB2INST1 HOSTNAME: host1.example.com EDUID : 24993 EDUNAME: db2agent (SPSSDB) 0 FUNCTION: DB2 UDB, relation data serv, sqlrLocalRunstats, probe:12276 MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT "LockTimeOut - tran rollback Reason code 68" DATA #1 : unsigned integer, 8 bytes 11528 DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -911 sqlerrml: 2 sqlerrmc: 68 sqlerrp : SQLRC02B sqlerrd : (1) 0x80100044 (2) 0x00000044 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 2017-08-24-23.11.08.696601-420 E887356E784 LEVEL: Event PID : 30986 TID : 140729082963712 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SPSSDB APPHDL : 0-20515 APPID: *LOCAL.db2inst1.170825060338 AUTHID : DB2INST1 HOSTNAME: host1.example.com EDUID : 24993 EDUNAME: db2agent (SPSSDB) 0 FUNCTION: DB2 UDB, relation data serv, sqlrLocalRunstats, probe:220 COLLECT : TABLE AND INDEX STATS : Object name with schema : AT "2017-08-24-23.11.08.696578" : BY "User" : DUE TO "Error" : failure OBJECT : Object name with schema, 17 bytes IBMWSSIB.SIBOWNER IMPACT : None DATA #1 : String, 30 bytes RUNSTATS command not available DATA #2 : String, 26 bytes ZRC=0x80100044=-2146435004
In this specific case, the table in question very rarely changes anyway, so we’re not terribly worried about actually catching runstats on it. We’re more worried about the needless overhead of the constant lock timeouts and associated logging. To deal with this, what we need to do is to remove the table from consideration by our automatic runstats.
Generating the XML Policy File
The first step is to get the current XML policy file so that we can then update the policy. This is relatively easy to do with the
AUTOMAINT_GET_POLICYFILE stored procedure:
$ db2 "call AUTOMAINT_GET_POLICYFILE ('AUTO_RUNSTATS', 'spssdb_auto_runstats1.xml')" Return Status = 0
When you use this stored procedure, the policy file is placed in the tmp sub-directory of sqllib. For a vanilla DB2 installation, it is likely to look something like this:
Updating the XML
The place to update the file is with elements within the FilterCondition attribute. Here we want to place what we would put in a where clause to eliminate this table. In this case, I’m updating it to look like this:
(tabschema, tabname) not in (values('IBMWSSIB', 'SIBOWNER'))
Setting the XML Policy File
After I have the file defined the way I want it, I use the
AUTOMAINT_SET_POLICYFILE procedure to set this file as the new policy, like so:
$ db2 "call AUTOMAINT_SET_POLICYFILE ('AUTO_RUNSTATS', 'spssdb_auto_runstats1.xml')" Return Status = 0
After making this change, I went from about 12 lock timeouts per hour on this database to about 1 per day.