Locking Event Monitors and Compression

Jim

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

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

You may also like...

6 Responses

  1. Scott Hayes says:

    Great story Jim, thanks for sharing. Others might have just sent IBM a check for Compression. You just saved people money! Cheers, Scott @srhayes

  2. Paul Bird says:

    Actually, this should not be a license violation as we deliberately chose to use compression and got permission for this type of event monitor output (UFE). We got permission to do this and there is deliberately no license check in the code.

    if this is being surfaced as a licencing violation or if support is telling you it is one, then there is a problem in our processes which we need to fix.

    Note that this is only true for UFE output and not normal table output.

    • Ember Crooks says:

      Paul,

      Jim opened a PMR with support and was told he must alter the table to remove compression or he’d be out of licensing compliance. Jim emailed you the PMR number and details.

      • Paul Bird says:

        Hi Ember, Jim already emailed it to me and I am chasing down the source of this information… and if the IBM machine has changed its mind, we will change the code so that the default behaviour to no longer use compression. We do NOT play this game of forcing you to pay for unintentional usage 🙂

    • Paul Bird says:

      Hi, I have now tracked down this issue and we need to apologize to you for giving you bad information with your PMR 🙂

      Tables implicitly created by event monitors can legally use compression without putting you out of compliance (i.e. you do not need the storage optimization licence for this usage of compression).

      Unfortunately, a regression was introduced which started having this flagged as a violation in 9.7.9, 10.5.3, and 10.1.4.

      It has been fixed in 10.1.5 (APAR IT06270) and 9.7.11 (APAR IT06434). It is not yet fixed in 10.5 (APAR IT06435) but it is already in the next fix pack which is not yet released.

      I am sorry about the unnecessary concern you were caused and please let me know if you hear of anyone else being told that they are out of compliance because of implicitly created tables from event monitors. I will try to clear up those situations as well.

      Thanks.

  1. November 3, 2016

    […] Locking Event Monitors and Compression […]

Leave a Reply

Your email address will not be published. Required fields are marked *