Hi team,

We recently compressed a table in db2 and did reorg on the table to save space using ALTER TABLE TABLENAME COMPRESS YES statement. This worked and we saved a lot of space. Now we want to increase the size of a specific column using ALTER TABLE, but after a short time the query is failing with the following:

The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".. SQLCODE=-911, SQLSTATE=40001, DRIVER=4.27.25 SQL Code: -911, SQL State: 40001

We previously did try the same ALTER TABLE on non compressed tables and it did go through properly.

Can this be because the table has been compressed, if so how can I handle it without undoing the compression?

------------------------------
George Chatama
------------------------------

Hi George,

I work with a lot of compressed tables and have never experienced the same problem, which leads me to believe your problem might be elsewhere.

Did you check the active locks at the time when you issued the ALTER TABLE statement (before it failed)?

For example:

-- Currently active LOCKs:
db2 "select L.APPLICATION_HANDLE, L.LOCK_OBJECT_TYPE, L.LOCK_MODE, L.LOCK_STATUS, T.TABSCHEMA, T.TABNAME from TABLE (MON_GET_LOCKS(NULL, -2)) as L left outer join SYSCAT.TABLES T on L.TBSP_ID = T.TBSPACEID and L.TAB_FILE_ID = T.TABLEID where L.LOCK_OBJECT_TYPE in ('ROW','TABLE')";

-- Who is CURRENTLY waiting (REQ_APP_HANDLE) on whose (HLD_APP_HANDLE) active locks:
db2 "select L.REQ_APPLICATION_HANDLE, L.HLD_APPLICATION_HANDLE, L.LOCK_OBJECT_TYPE, L.LOCK_MODE, L.LOCK_STATUS, T.TABSCHEMA, T.TABNAME from  TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2)) L left outer join SYSCAT.TABLES T on L.TBSP_ID = T.TBSPACEID and L.TAB_FILE_ID = T.TABLEID";

Hope this helps!

Regards, Damir

------------------------------
Damir Wilder
Senior Consultant
Triton Consulting
London
------------------------------

Hello George,

additional to Damirs comment I would recommend setting up a LOCKING EVENT MONITOR with WRITE TO TABLE to track Deadlocks (-911 RC2)  and Lock Timeouts(-911 RC68) for later analysis - for more details see https://www.ibm.com/docs/en/db2/11.5?topic=events-lock-deadlock-event-monitoring

Unless you have hundreds or thousands of Deadlocks or Lock Timeouts in a short period of time the overhead is neglible, but of course you must think about some housekeeping for the Event Monitor tables to delete older data

------------------------------
Joachim Klassen
------------------------------

Additional to Douglas' comment:
An ALTER TABLE requires a Z-Lock (Super-exclusive) on the table which is not compatible to any other locks even IN-Lock (Intent None). So even a SELECT using isolation level UR (Uncommitted read) will block the ALTER.
You may SET CURRENT LOCK TIMEOUT WAIT in your ALTER TABLE transaction to prevent a lock timeout. 
But keep in mind that this can cause long lasting Lock chains as the waiting ALTER TABLE transaction would block any other new transaction that want to access this table (even when their required lock is compatible with the lock of the transaction that blocks the ALTER TABLE session) until the Z-Lock for the ALTER is granted. 

A bit off-topic: An ALTER of a table's column can leave the table in REORG Pending which means the table cannot be accessed with DML operations after the ALTER is committed (see https://www.ibm.com/docs/en/db2/11.5?topic=tables-altering for more details). 
To check if this could happen to you you might want to look into View SYSIBMADM.ADMINTABINFO before altering and check the value of column NUM_REORG_REC_ALTERS for the table. If its value is > 1 you should be prepared to run a reorg on the altered table afterwards depending of the kind of ALTER operations (drop column, alter data type ..).
Also if you have to apply multiple ALTERs on the same table do them in one transation - this counts as one ALTER for NUM_REORG_REC_ALTERS 

------------------------------
Joachim Klassen
------------------------------

Original Message:
Sent: Thu October 31, 2024 07:35 AM
From: Joachim Klassen
Subject: Deadlock or timeout on a compressed table

Hello George,

additional to Damirs comment I would recommend setting up a LOCKING EVENT MONITOR with WRITE TO TABLE to track Deadlocks (-911 RC2)  and Lock Timeouts(-911 RC68) for later analysis - for more details see https://www.ibm.com/docs/en/db2/11.5?topic=events-lock-deadlock-event-monitoring

Unless you have hundreds or thousands of Deadlocks or Lock Timeouts in a short period of time the overhead is neglible, but of course you must think about some housekeeping for the Event Monitor tables to delete older data

------------------------------
Joachim Klassen
------------------------------

The only I see this happening is due to a transaction not being release.     The error would got should a reason code attached to it that can provide more information.

This happens if a program holding resource has not issued any commits (unit of work) .   Here a common mistake which can also cause this the same user having multiple connections open in IBM dataStudio.  So one blocks the other out.    there a lot of ways to find out what you are contending with.   Db2 luw has a free locking tool that will show the statement's blocking each other.  
Also remember that just using read sql will cause locking because some locks will not allow DDL statements to be clear.. These type of locks occur at the high object level as place holder so no DDl work can take place.   This happens a lot with db2 on Zos.   

First track down what was blocking you and have them  code for Commit, Rollback, or Restart events in thier programs and issue frequent commits.

------------------------------
Douglas Partch
------------------------------