Env: DB2 V8.2/AIX 5.3
Snapshot_lock function and lock snapshot provides different result when I monitor a particular agentid. Any specific reason ?
Quote:
/tmp > db2 "select \
> LK.AGENT_ID AS AGENT_ID, \
> CASE(LK.LOCK_OBJECT_TYPE) \
> WHEN 1 THEN 'Table' \
> WHEN 2 THEN 'Row' \
> ELSE '' \
> END AS LOCKED_OBJECT , \
> SUBSTR(LK.TABLE_SCHEMA,1,15) AS TAB_SCHEMA, \
> SUBSTR(LK.TABLE_NAME,1,25) AS TABLE_NAME, \
> SUBSTR(LK.TABLESPACE_NAME,1,10) AS TABSPACE_NAME \
> from \
> TABLE(SNAPSHOT_LOCK('',-2)) as LK \
> WHERE \
> LK.TABLE_NAME IS NOT NULL AND \
> LK.AGENT_ID=23594"
AGENT_ID LOCKED_OBJECT TAB_SCHEMA TABLE_NAME TABSPACE_NAME
-------------------- ------------- --------------- ------------------------- -------------
23594 Table CIGWP4N1 SIBOWNER USERSPACE1
23594 Table CIGWP3N2 SIBOWNER USERSPACE1
23594 Table CIGWP3N1 SIBOWNER USERSPACE1
23594 Table CIGWP2N2 SIBOWNER USERSPACE1
23594 Table CIGWP2N1 SIBOWNER USERSPACE1
23594 Table CIGWP1N2 SIBOWNER USERSPACE1
23594 Table CIGWP1N1 SIBOWNER USERSPACE1
7 record(s) selected.
|
Snapshot_lock table function shows 7 entries for the same application handle..which I find hard to understand. However the following lock snapshot provides only one entry.
Quote:
/tmp >
/tmp > db2 get snapshot for locks for application agentid 23594 global > lock.snap.out
/tmp > cat lock.snap.out
Application Lock Snapshot
Snapshot timestamp = 02/23/2009 21:21:24.289477
Application handle = 23594
Application ID = GA3940A7.AABA.090223023603
Sequence number = 0003
Application name = db2jcc_application
CONNECT Authorization ID = CCASIB
Application status = UOW Waiting
Status change time = 02/23/2009 21:21:10.339554
Application code page = 1208
Locks held = 3
Total wait time (ms) = 0
List Of Locks
Lock Name = 0x00000002000059063DE4902043
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Catalog Cache Lock
Mode = S
Lock Name = 0x535953534E323030C7D2493C41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S
Lock Name = 0x00020081000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x7FFFFFFF
Lock Count = 255
Hold Count = 0
Lock Object Name = 129
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = CIGWP4N1
Table Name = SIBOWNER
Mode = S
Prod daldb243:/tmp >
|
dBforums
>
SNAPSHOT_LOCK Vs get lock snapshot
02-23-09, 21:25
Linear Mode
