Excluding a Table from Db2’s Automatic Runstats

Corrected on 9/8/2017 to reflect correct syntax to eliminate just the one table.

The Problem

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:

<?xml version="1.0" encoding="UTF-8"?>

<DB2AutoRunstatsPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <RunstatsTableScope>
  <FilterCondition/>
 </RunstatsTableScope>
</DB2AutoRunstatsPolicy>

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:

<?xml version="1.0" encoding="UTF-8"?>

<DB2AutoRunstatsPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <RunstatsTableScope>
  <FilterCondition>
        (tabschema, tabname) not in (values('IBMWSSIB', 'SIBOWNER'))
  </FilterCondition>
 </RunstatsTableScope>
</DB2AutoRunstatsPolicy>

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

Resolution

After making this change, I went from about 12 lock timeouts per hour on this database to about 1 per day.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home