Results 1 to 3 of 3

Thread: sql lock-wait

  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: sql lock-wait

    Hi All !

    Is it possible to determine the exact SQL that's waiting for locks (db2 ver 8 on AIX) ?
    Using the list application, I managed to get the appl id waiting for locks.
    I can determine the locks held using the get snapshot.
    but i was wondering if there was a way to determine the exact sql (sql is in a java code using sqlj) that's waiting for locks ?

    thanks

    Anil

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    Doing a lock snapshot you can find out about the objects being locked, that might help you getting to the SQL using the SQL snapshot ...
    Juliane

  3. #3
    Join Date
    Apr 2005
    Posts
    41
    To capture the SQL, you will either need to have the DFT_MON_STMT parameter set to ON, or else have a session running with the statement monitor switch on (db2 update monitor switches using statement on).

    You can then either use the snapshot output, or the db2pd command to determine the application ID holding the lock (e.g. db2 get snapshot for locks on $DBNAME).

    Once you know the ID, you can run a:

    db2 get snapshot for application agentid XXX

    which should detail the SQL text.

    You can also do this via the Control Centre GUI - right click database name -> Applications -> Show Lock Chains.

    Graham Martin
    http://www.ibm.com/software/data/db2/migration/

Posting Permissions

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