It appears that DMC causes LOCKTIMEOUT when LOAD is being executed:
-------------------------------------------------------
Event ID : 1
Event Type : LOCKTIMEOUT
Event Timestamp : 2024-01-15-18.44.44.763450
Partition of detection : 0
-------------------------------------------------------
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x07010400000000000000000054
Lock wait start time : 2024-01-15-18.44.04.673855
Lock wait end time : 2024-01-15-18.44.44.763450
Lock Type : TABLE
Lock Specifics :
Lock Attributes : 00440000
Lock mode requested : Super Exclusive
Lock mode held : Super Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 263
Tablespace Name : TBBGDTBALP0001
Table FID :
Table Schema :
Table Name :
Attributes Requester Owner
--------------------- ------------------------------ ------------------------------
Participant No 1 2
Application Handle 010138 010065
Application ID *N0.db2kons3.240115174427 10.158.56.57.46586.240115174314
Application Name db2bp UC_MYMON
Authentication ID ALNM.AN.RHEL.OS DB2KONS3
Requesting AgentID 969 296
Coordinating AgentID 969 296
Agent Status Data Fast Load UOW Executing
Application Action Performing Load No action
Lock timeout value 40 0
Lock wait value 0 0
Tenant ID 0 0
Tenant Name SYSTEM SYSTEM
Workload ID 1 1
Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
Service subclass ID 13 13
Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
Current Request Static Commit Open Cursor
TEntry state 1 1
TEntry flags1 00000000 00000000
TEntry flags2 00000208 00000200
Lock escalation no no
Client userid
Client wrkstnname 10.158.56.57
Client applname CLP /gpfs/FS_ALND/FS_ALND/ALND/
Client acctng
Utility ID
Current Activities of Participant No 1
----------------------------------------
Activity ID : 1
Uow ID : 3
Package Name :
Package Schema :
Package Version :
Package Token :
Package Sectno : 0
Reopt value : none
Incremental Bind : no
Eff isolation : RR
Eff degree : 1
Actual degree : 1
Eff locktimeout : 40
Stmt first use : 2024-01-15-18.44.04.797975
Stmt last use : 2024-01-15-18.44.04.797975
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 0
Stmt type : Static
Stmt operation : Statement not prepared
Stmt no : -1
Stmt text :
Past Activities of Participant No 1
-------------------------------------
Activities not available
Current Activities of Participant No 2
----------------------------------------
Activity ID : 1
Uow ID : 16
Package Name : SYSSH100
Package Schema : NULLID
Package Version :
Package Token : SYSLVL01
Package Sectno : 1
Reopt value : none
Incremental Bind : no
Eff isolation : UR
Eff degree : 1
Actual degree : 1
Eff locktimeout : 40
Stmt first use : 2024-01-15-18.42.58.745761
Stmt last use : 2024-01-15-18.42.58.745761
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 171798691842
Stmt type : Dynamic
Stmt operation : DML, Select (blockable)
Stmt no : 1
Stmt text : /* IBM_DSSNAP */ SELECT
ii.INDSCHEMA,
ii.INDNAME,
ii.TABSCHEMA,
ii.TABNAME,
ii.DBPARTITIONNUM,
ii.INDEX_OBJECT_L_SIZE,
ii.INDEX_OBJECT_P_SIZE,
ii.INDEX_REQUIRES_REBUILD,
ii.RECLAIMABLE_SPACE,
ts.TBSPACE,
tb.lastused
FROM
TABLE(SYSPROC.ADMIN_GET_INDEX_INFO('I', NULL, NULL)) ii
LEFT JOIN syscat.INDEXES i ON ii.INDSCHEMA = i.INDSCHEMA AND ii.INDNAME = i.INDNAME AND ii.TABSCHEMA = i.TABSCHEMA AND ii.TABNAME = i.TABNAME
LEFT JOIN syscat.tablespaces ts ON i.TBSPACEID = ts.TBSPACEID
LEFT JOIN syscat.tables tb ON i.TABSCHEMA = tb.TABSCHEMA AND i.TABNAME = tb.TABNAME
where ii.INDEX_OBJECT_L_SIZE is not null and ii.INDEX_OBJECT_P_SIZE is not null
ORDER BY ii.INDEX_OBJECT_L_SIZE DESC, ii.INDEX_OBJECT_P_SIZE DESC, tb.lastused desc
fetch FIRST 5000 rows only WITH UR
Any suggestions to mitigate those Lock Timeouts?
------------------------------
Jan Nelken
------------------------------