Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: SNAPSHOT_LOCK Vs get lock snapshot

    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 ?

    /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.

    /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 >

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The snapshot functions have so many bugs and limitations that I have given up on them. I use the old fashion snapshots.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Hi,

    the table SIBOWNER exists in 7 schemas. Your snapshot selects all 7 schemas and GET selects only the schema CIGWP4N1. I don't know why, but the difference is in schemas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •