Accessing the table receives lock timeout after offline reorg is interrupted

Body

Symptom

Accessing the table received lock timeout:

db2 'select count(*) from MYTAB'
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001

Cause

This happens because after offline Reorg of table is interrupted at the index creation phase, indexes are marked invalid.

Following this event, any attempt to access the table will trigger index rebuilding for the table.

A typical stack of the agent working on index rebuilding is as follows:

  [1] 0x090000001904387C sqldCreateIndex__FP8sqeAgentP17SQLD_CREATEINX_CB + 0x95C
  [2] 0x090000001907C4F0 sqldIndexCreate__FP8sqeAgentP17SQLD_CREATEINX_CB + 0x370
  [3] 0x09000000175C4AE8 sqlrlRecreateSingleIndex__FP8sqlrr_cbUsT2P8sqlrg_idP8sqlrg_pdP25sqlyk_keyXform_threadListPUciT7T8UiT2P17SQLD_CREATEINX_CB + 0x268
  [4] 0x09000000175C4460 sqlrlRecreateIndexObject__FP8sqlrr_cbP8sqlrg_idP17SQLD_CREATEINX_CBUiUsUlP25sqlyk_keyXform_threadListP23SqlthJobProgressMonitor + 0x4E0
  [5] 0x09000000175C7084 sqlrlRecreateIndexes__FP8sqlrr_cbUsT2P8sqlrg_idiPUcT5T6T5UiPsUcP8SqlthJobT2P9SQLP_LSN8T2P14SQLP_LOCK_INFOT6 + 0x8A4
  [6] 0x09000000175C8E5C sqlrlrci__FP8sqlrr_cbUsT2UiT2 + 0x2FC
  [7] 0x09000000172CF034 sqlrinsr__FP8sqlrr_cbUsT2iT2PP10SQLD_VALUEQ3_10sqlri_iudo11t_iudoFlags17t_iudoFlagsKernelP8SQLZ_RIDPPv + 0x1D4
  [8] 0x090000001B73B964 sqlriisr__FP8sqlrr_cb + 0x2C4
  [9] 0x0900000016E82898 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x618
  [10] 0x0900000018035EBC sqlrr_process_execute_request__FP8sqlrr_cbib + 0x341C
  [11] 0x090000001694ECE8 sqlrr_array_input__FP14db2UCinterfacePP15db2UCdiagStructUlUs + 0x2868
  [12] 0x090000001BD78528 sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x828
  [13] 0x090000001BCFCBE0 sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinterface + 0x280

While indexes are rebuilding, exclusive lock "Z" will be held by the application and no other applications can access the table until the indexes are built.

You can  monitor the index rebuilding  progress by monitoring messages in db2diag.log. Note if you force off the application building indexes,

the index rebuilding will have to start from scratch next time the table is accessed.

Please see the following example from db2diag.log for the message you can monitor  :

EDUID   : 12345               EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data management, sqldIndexCreate, probe:1
MESSAGE : ADM5541W  Rebuilding index with IID "1" in object with ID "100" and
          table space ID "2" on table "DB2INST1.MYTAB".