Lock Monitoring |
| Lock Monitoring |
간단히 만들어 보았어요 .... Tree 구조 꾸미기 귀챦아서리 --;
아래 쿼리를 이용하여 모니터링을 하시기 위해서는 모니터 스위치를 ON 해주셔야 합니다 .
Default charge-back account (DFT_ACCOUNT_STR) =
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = ON
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
최소한 이 정도로 맞춰 놓으셔야 ..
SELECT lockholder.AGENT_ID_HOLDING_LK as Holder,
' ' as Waiter,
' ' as lock_mode,
' ' as TABLE_SCHEMA,
' ' as TABLE_NAME,
' ' as lock_object_type,
' ' as lock_mode_requested ,
CASE info.appl_status
WHEN 1
THEN 'connect pending'
WHEN 3
THEN 'UOW executing'
WHEN 5
THEN 'lock wait'
WHEN 6
THEN 'commit active'
WHEN 7
THEN 'rollback active'
WHEN 8
THEN 'recompiling a plan'
WHEN 9
THEN 'compiling a SQL statement'
WHEN 10
THEN 'request interrupted'
WHEN 11
THEN 'disconnect pending'
WHEN 12
THEN 'Prepared transaction'
WHEN 13
THEN 'heuristically committed'
WHEN 14
THEN 'heuristically rolled back'
WHEN 15
THEN 'Transaction ended'
WHEN 16
THEN 'Creating Database'
WHEN 17
THEN 'Restarting a Database'
WHEN 18
THEN 'Restoring a Database'
WHEN 19
THEN 'Performing a Backup'
WHEN 20
THEN 'Performing a fast load'
WHEN 21
THEN 'Performing a fast unload'
WHEN 22
THEN 'Wait to disable tablespace'
WHEN 23
THEN 'Quiescing a tablespace'
WHEN 24
THEN 'Waiting for remote node'
WHEN 25
THEN 'Pending results from remote request'
WHEN 26
THEN 'App has been decoupled from coord'
WHEN 27
THEN 'Rollback to savepoint'
ELSE CHAR( info.appl_status )
END AS appl_status,
info.appl_name ,
CASE
WHEN stmt.stmt_type = 1 THEN char(( SELECT text FROM syscat.statements stmt WHERE PKGNAME = stmt.PACKAGE_NAME AND SECTNO = stmt.SECTION_NUMBER ))
ELSE char(stmt.stmt_text)
END as stmt_text,
appl.UOW_ELAPSED_TIME_S as ElapsedTime ,
appl.APPL_CON_TIME ,
appl.INBOUND_COMM_ADDRESS
FROM ( SELECT distinct AGENT_ID_HOLDING_LK
FROM table(snapshot_lockwait('',-1)) as lock_wait
) lockholder ,
table(snapshot_appl('',-1)) as appl ,
table(snapshot_appl_info('',-1)) as info
LEFT OUTER JOIN
table(snapshot_statement('',-1)) as stmt
ON info.agent_id = stmt.agent_id
WHERE appl.agent_id = info.agent_id
AND info.agent_id = lockholder.AGENT_ID_HOLDING_LK
UNION ALL
SELECT lockholder.AGENT_ID_HOLDING_LK as Holder ,
char(lockholder.agent_id) as Waiter ,
CASE lockholder.lock_mode
WHEN 1
THEN 'Intention Share Lock'
WHEN 2
THEN 'Intention Exclusive Lock'
WHEN 3
THEN 'Share Lock'
WHEN 4
THEN 'Share with Intention Exclusive Lock'
WHEN 5
THEN 'Exclusive Lock'
WHEN 6
THEN 'Intent None (For Dirty Read)'
WHEN 7
THEN 'Super Exclusive Lock'
WHEN 8
THEN 'Update Lock'
WHEN 9
THEN 'Next-key Share Lock'
WHEN 10
THEN 'Next-key Exclusive Lock'
WHEN 11
THEN 'Weak Exclusive Lock'
WHEN 12
THEN 'Next-key Weak Exclusive Lock'
ELSE char(lockholder.lock_mode)
END AS lock_mode,
lockholder.TABLE_SCHEMA,
lockholder.TABLE_NAME,
CASE lockholder.lock_object_type
WHEN 1
THEN 'table lock type'
WHEN 2
THEN 'table row lock type'
WHEN 3
THEN 'Internal lock type'
WHEN 4
THEN 'Tablespace lock type'
WHEN 5
THEN 'end of table lock'
WHEN 6
THEN 'key value lock'
WHEN 7
THEN 'Internal lock on the sysboot table'
WHEN 8
THEN 'Internal Plan lock'
WHEN 9
THEN 'Internal Variation lock'
WHEN 10
THEN 'Internal Sequence lock'
WHEN 11
THEN 'Bufferpool lock'
WHEN 12
THEN 'Internal Long/Lob lock'
WHEN 13
THEN 'Internal Catalog Cache lock'
WHEN 14
THEN 'Internal Online Backup lock'
WHEN 15
THEN 'Internal Object Table lock'
WHEN 16
THEN 'Internal Table Alter lock'
WHEN 17
THEN 'Internal DMS Sequence lock'
WHEN 18
THEN 'Inplace reorg lock'
WHEN 19
THEN 'Block lock type'
ELSE CHAR( lockholder.lock_object_type )
END AS lock_object_type,
CASE lockholder.lock_mode_requested
WHEN 1
THEN 'Intention Share Lock'
WHEN 2
THEN 'Intention Exclusive Lock'
WHEN 3
THEN 'Share Lock'
WHEN 4
THEN 'Share with Intention Exclusive Lock'
WHEN 5
THEN 'Exclusive Lock'
WHEN 6
THEN 'Intent None (For Dirty Read)'
WHEN 7
THEN 'Super Exclusive Lock'
WHEN 8
THEN 'Update Lock'
WHEN 9
THEN 'Next-key Share Lock'
WHEN 10
THEN 'Next-key Exclusive Lock'
WHEN 11
THEN 'Weak Exclusive Lock'
WHEN 12
THEN 'Next-key Weak Exclusive Lock'
ELSE char(lockholder.lock_mode_requested)
END AS lock_mode_requested ,
CASE info.appl_status
WHEN 1
THEN 'connect pending'
WHEN 3
THEN 'UOW executing'
WHEN 5
THEN 'lock wait'
WHEN 6
THEN 'commit active'
WHEN 7
THEN 'rollback active'
WHEN 8
THEN 'recompiling a plan'
WHEN 9
THEN 'compiling a SQL statement'
WHEN 10
THEN 'request interrupted'
WHEN 11
THEN 'disconnect pending'
WHEN 12
THEN 'Prepared transaction'
WHEN 13
THEN 'heuristically committed'
WHEN 14
THEN 'heuristically rolled back'
WHEN 15
THEN 'Transaction ended'
WHEN 16
THEN 'Creating Database'
WHEN 17
THEN 'Restarting a Database'
WHEN 18
THEN 'Restoring a Database'
WHEN 19
THEN 'Performing a Backup'
WHEN 20
THEN 'Performing a fast load'
WHEN 21
THEN 'Performing a fast unload'
WHEN 22
THEN 'Wait to disable tablespace'
WHEN 23
THEN 'Quiescing a tablespace'
WHEN 24
THEN 'Waiting for remote node'
WHEN 25
THEN 'Pending results from remote request'
WHEN 26
THEN 'App has been decoupled from coord'
WHEN 27
THEN 'Rollback to savepoint'
ELSE CHAR( info.appl_status )
END AS appl_status,
info.appl_name ,
CASE
WHEN stmt.stmt_type = 1 THEN char(( SELECT text FROM syscat.statements stmt WHERE PKGNAME = stmt.PACKAGE_NAME AND SECTNO = stmt.SECTION_NUMBER ))
ELSE char(stmt.stmt_text)
END as stmt_text,
appl.UOW_ELAPSED_TIME_S as ElapsedTime ,
appl.APPL_CON_TIME ,
appl.INBOUND_COMM_ADDRESS
FROM ( SELECT AGENT_ID_HOLDING_LK , agent_id ,LOCK_MODE,TABLE_SCHEMA,TABLE_NAME,LOCK_OBJECT_TYPE,LOCK_MODE_REQUESTED
FROM table(snapshot_lockwait('',-1)) as lock_wait
) lockholder ,
table(snapshot_appl('',-1)) as appl ,
table(snapshot_appl_info('',-1)) as info
LEFT OUTER JOIN
table(snapshot_statement('',-1)) as stmt
ON info.agent_id = stmt.agent_id
WHERE appl.agent_id = info.agent_id
AND info.agent_id = lockholder.agent_id
ORDER BY 1 , 2