Thanks for quick reply. We will try your suggestions and let you know :-)
Original Message:
Sent: Thu January 18, 2024 05:19 PM
From: Cintia Ogura
Subject: DMC vs LOAD
Hello Jan,
Happy New Year!
This DMC query is known to be a very long query when the database has several tables and indexes.
One way to avoid this query or to schedule the query to run when the database is not so busy is to change the following configuration in the monitoring profile:
I hope that helps!
------------------------------
Cintia Ogura
Original Message:
Sent: Thu January 18, 2024 02:35 PM
From: Jan Nelken
Subject: DMC vs LOAD
It appears that DMC causes LOCKTIMEOUT when LOAD is being executed:
-------------------------------------------------------Event ID : 1Event Type : LOCKTIMEOUTEvent Timestamp : 2024-01-15-18.44.44.763450Partition of detection : 0-------------------------------------------------------Participant No 1 requesting lock ----------------------------------Lock Name : 0x07010400000000000000000054Lock wait start time : 2024-01-15-18.44.04.673855Lock wait end time : 2024-01-15-18.44.44.763450Lock Type : TABLELock Specifics : Lock Attributes : 00440000Lock mode requested : Super ExclusiveLock mode held : Super ExclusiveLock Count : 0Lock Hold Count : 0Lock rrIID : 0Lock Status : WaitingLock release flags : 00000000Tablespace TID : 263Tablespace Name : TBBGDTBALP0001Table 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 : 1Uow ID : 3Package Name : Package Schema : Package Version : Package Token : Package Sectno : 0Reopt value : noneIncremental Bind : noEff isolation : RREff degree : 1Actual degree : 1Eff locktimeout : 40Stmt first use : 2024-01-15-18.44.04.797975Stmt last use : 2024-01-15-18.44.04.797975Stmt unicode : noStmt query ID : 0Stmt nesting level : 0Stmt invocation ID : 0Stmt source ID : 0Stmt pkgcache ID : 0Stmt type : StaticStmt operation : Statement not preparedStmt no : -1Stmt text : Past Activities of Participant No 1-------------------------------------Activities not availableCurrent Activities of Participant No 2----------------------------------------Activity ID : 1Uow ID : 16Package Name : SYSSH100Package Schema : NULLID Package Version : Package Token : SYSLVL01Package Sectno : 1Reopt value : noneIncremental Bind : noEff isolation : UREff degree : 1Actual degree : 1Eff locktimeout : 40Stmt first use : 2024-01-15-18.42.58.745761Stmt last use : 2024-01-15-18.42.58.745761Stmt unicode : noStmt query ID : 0Stmt nesting level : 0Stmt invocation ID : 0Stmt source ID : 0Stmt pkgcache ID : 171798691842Stmt type : DynamicStmt operation : DML, Select (blockable)Stmt no : 1Stmt 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 URAny suggestions to mitigate those Lock Timeouts?
------------------------------
Jan Nelken
------------------------------