Locking Event Monitors and Compression

UPDATE, 10/21/2015: Please see the comment from IBM at the end stating that despite the information they gave Jim via PMR, this compression does not cause a lack of license compliance even if you are not licensed for compression.

Abby Rosmarin said, “Hard work, determination and talent are key for any successful venture. But sometimes you need that fourth ingredient: dumb luck”.

We are in the process of upgrading our entire DB2 footprint from v8.2 to v10.1. These DB2 databases support WebSphere Commerce and as such, we are also upgrading to a new Commerce version. Having been on DB2 v8.2 for sooo long, we could not wait to dig into the new DB2 features. Lock timeout reporting is one of the areas we were excited about. DB2 was really good about sharing the victim’s information resulting from lock timeout or a deadlock. With a bit of digging, we could usually track down a deadlock culprit without too much trouble. But the lock timeout culprit? Not so much.

Locking Event Monitors that Write to Unformatted Tables

Along come the new event monitors. Now I can create an event monitor to gather the victim and the culprit’s information. This is of great benefit to my organization. We created event monitors and send the data to unformatted event tables.

At this point, let me pause and say you will find everything you need, including the database configuration parameters and guidance for how to set event monitors in another post on this blog.

I ran the following statement to create the locking event monitor which included the table name and, to isolate it, the tablespace name.

create event monitor cbi_locks for locking write to unformatted event table (table dba.cbi_locks in user_32k) autostart

The Problem

Now the dumb luck comes into play. As part of our DB2 v8.2 to v9.7 to v10.1 upgrade process (there isn’t a direct upgrade path from v8.2 to v10.1), we run db2look to capture the DDL in the database.  This is a particularly important step as it occurs before we upgrade the database objects from WebSphere Commerce 6 to Commerce 7. We also run it after the migration for comparison purposes. Now one day I was browsing through a db2look output file when I noticed the following:

-- DDL Statements for Table "DBA     "."CBI_LOCKS"

CREATE TABLE "DBA     "."CBI_LOCKS"  (
"PARTITION_KEY" INTEGER NOT NULL ,
"APPL_ID" VARCHAR(64) ,
"APPL_NAME" VARCHAR(255) ,
"EVENT_CORRELATION_ID" VARCHAR(32) FOR BIT DATA ,
"EVENT_DATA" BLOB(4189000) INLINE LENGTH 2988 LOGGED COMPACT NOT NULL ,
"EVENT_ID" BIGINT NOT NULL ,
"EVENT_TIMESTAMP" TIMESTAMP NOT NULL ,
"EVENT_TYPE" VARCHAR(128) NOT NULL ,
"MEMBER" SMALLINT NOT NULL ,
"RECORD_SEQ_NUM" BIGINT NOT NULL ,
"RECORD_TYPE" BIGINT NOT NULL ,
"SERVICE_SUBCLASS_NAME" VARCHAR(128) ,
"SERVICE_SUPERCLASS_NAME" VARCHAR(128) ,
"WORKLOAD_NAME" VARCHAR(128) ,
"MON_INTERVAL_ID" BIGINT )
COMPRESS YES ADAPTIVE
IN "USER_32K" ;

This is the unformatted event table created by the create event monitor statement I showed previously. Do you notice the problem here? Maybe a clue for you, remember the title includes “Compression”. Maybe another clue for you, we are not licensed for Storage Optimization.

[ ]$ db2licm -l | grep -i storage
DB2 Storage Optimization:         "Not licensed"

I then checked the catalog to confirm the table generated by the create event monitor statement was enabled for adaptive compression.

[ ]$ db2 "select compression, rowcompmode from syscat.tables where tabname = 'CBI_LOCKS' and tabschema = 'DBA' with ur"

COMPRESSION ROWCOMPMODE
----------- -----------
R           A

The CREATE EVENT MONITOR to unformatted table must use the COMPRESS YES option, which when specified, defaults to ADAPTIVE compression. Unfortunately according to IBM, there is no way to avoid the use of that option when creating the event monitor in v10.1. Simply creating this event monitor causes my database to be out of licensing compliance.

The Resolution

IBM’s advice is to alter the unformatted event monitor table to COMPRESS NO. Here are the statements I used:

[ ]$ db2 set event monitor cbi_locks state 0
DB20000I  The SQL command completed successfully.

[ ]$ db2 "alter table dba.cbi_locks compress no"
DB20000I  The SQL command completed successfully.

[ ]$ db2 "reorg table dba.cbi_locks"
DB20000I  The REORG command completed successfully.

[ ]$ db2 set event monitor cbi_locks state 1
DB20000I  The SQL command completed successfully.

I then re-checked the catalog to make sure the table no longer used adaptive compression.

[ ]$ db2 "select compression, rowcompmode from syscat.tables where tabname = 'CBI_LOCKS' and tabschema = 'DBA' with ur"

COMPRESSION ROWCOMPMODE
----------- -----------
N

Finally, I did check the “formatted” event tables and they are not created with the COMPRESS YES option. Whew!

[ ]$  db2 "select cast(tabname as char(40)) tabname, compression, rowcompmode from syscat.tables where tabschema = 'DBA' with ur"

TABNAME                                  COMPRESSION ROWCOMPMODE
---------------------------------------- ----------- -----------
CBI_LOCKS                                N
LOCK_ACTIVITY_VALUES                     N
LOCK_EVENT                               N
LOCK_PARTICIPANTS                        N
LOCK_PARTICIPANT_ACTIVITIES              N

Jim Reutener has been an IT professional for 25 years. Starting out as a mainframe COBOL Programmer, then a SQL/DS DBA, moving to DB2 z/OS Database Administrator and now for the last 8 years as a DB2 LUW Database Administrator. He has IT experience with public utilities, state taxation, banking and most recently in the fast-paced retail space. He has been supporting DB2 LUW running behind WebSphere Commerce for the last 4 years. Jim can be reached via LinkedIn at www.linkedin.com/in/JimReutener.

• Author Details

Jim Reutener

Jim Reutener has been an IT professional for 25 years. Starting out as a mainframe COBOL Programmer, then a SQL/DS DBA, moving to DB2 z/OS Database Administrator and now for the last 8 years as a DB2 LUW Database Administrator. He has IT experience with public utilities, state taxation, banking and most recently in the fast-paced retail space. He has been supporting DB2 LUW running behind WebSphere Commerce for the last 4 years.