Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Why does snapshot_statement function returns nulls in statement column?

    Hi,
    using DB2 v10.1 on Linux. I would like to know which application SQL statements locks other applications SQL statements.

    I created simple test in db2cmd:
    Code:
    db2 CREATE TABLE ADMIN.TAB (COL1 INT NOT NULL PRIMARY KEY, COL2 CHAR(20));
    db2 +c INSERT INTO ADMIN.TAB VALUES (1, 'First row');
    Opened one more db2cmd and executed:
    Code:
    db2 +c SELECT * FROM ADMIN.TAB WITH RR
    Executing db2pd command to get locks:
    Code:
    db2pd -database <mydb> -locks -transactions -applications -dynamic
    I see locks are on the table and crawling from locks to transactions to applications to dynamic I can get exact SQL for both applications (application that is holding lock and application that is waiting for a lock).

    From my boss I got a task to do to display all info in 5 columns.
    1. Timestamp of executed report.
    2. Application_ID that is holding lock.
    3. Dynamic SQL statement from application that is holding lock.
    4. Application_ID that is in lock-wait.
    5. Dynamic SQL statement from application that is in lock-wait.

    One of the way would be to use bash commands to grep, join etc all of the db2pd reports, but that looks too time consuming...

    So I have tried using SQL snapshots functions like:
    Code:
    select * from table(snapshot_appl('',  -1));
    select * from table(snapshot_statement('', -1));
    select * from table(snapshot_appl_info('', -1));
    select * from table(snapshot_lock('', -1));
    select * from table(snapshot_lockwait('', -1));
    select * from table(snapshot_agent( '', -1 ));
    To my supprise, bellow SQL returns no row:
    Code:
    db2 select * from table(snapshot_lockwait('', -1))
    Bellow SQL returns 10 rows, but data for STMT_TEXT are all null.
    Code:
    db2 select snapshot_timestamp, agent_id, stmt_text from table(snapshot_statement('', -1))
    Do I need to turn on some setting or something to get this SQL snapshost functions to work?
    Does anyone have any other solution (script or something) to gain this info?
    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 get monitor switches ...
    (0)[db2inst1@plx00003 ~]$ db2 list tables for schema sysibmadm |grep LOCK
    LOCKS_HELD SYSIBMADM V 2013-01-15-10.17.51.430506
    LOCKWAITS SYSIBMADM V 2013-01-15-10.17.51.457498
    ...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by grofaty View Post
    Do I need to turn on some setting or something to get this SQL snapshost functions to work?
    Does anyone have any other solution (script or something) to gain this info?
    Hi,

    These SNAPSHOT_* functions are deprecated.
    You should use SYSIBMADM.MON_LOCKWAITS administrative view for that. Note that
    - you have to have your mon_act_metrics database parameter set to BASE (which is default) at least to make DB2 to collect this information
    - HLD_CURRENT_STMT_TEXT is not necessarily the statement causing the lock
    Regards,
    Mark.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    sorry for late response, have been busy with other administration tasks...

    I was looking into mon_lockwaits view and I am specially interested in SQL statement that holds the lock, so:
    Code:
    SELECT HLD_CURRENT_STMT_TEXT FROM SYSIBMADM.MON_LOCKWAITS
    but I get one row displayed with null value. Is there any other db parameter that I need to set form NONE to BASE or something else? I have set the MON_ACT_METRICS=BASE

    My current DB parameters:
    Code:
    db2 get db cfg for <my_db> | grep MON
     Request metrics                       (MON_REQ_METRICS) = NONE
     Activity metrics                      (MON_ACT_METRICS) = BASE
     Object metrics                        (MON_OBJ_METRICS) = NONE
     Unit of work events                      (MON_UOW_DATA) = NONE
       UOW events with package list        (MON_UOW_PKGLIST) = OFF
       UOW events with executable list    (MON_UOW_EXECLIST) = OFF
     Lock timeout events                   (MON_LOCKTIMEOUT) = NONE
     Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
     Lock wait events                         (MON_LOCKWAIT) = NONE
     Lock wait event threshold               (MON_LW_THRESH) = 4294967295
     Number of package list entries         (MON_PKGLIST_SZ) = 32
     Lock event notification level         (MON_LCK_MSG_LVL) = 1
    Thanks

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    If you want to investigate lock wait/timeout situations, you should set
    MON_LOCKWAIT = HISTORY and adjust MON_LW_THRESH (in microseconds).
    Then you create EVENT MONITOR FOR LOCKING.
    See Lock and deadlock event monitoring.
    Regards,
    Mark.

Posting Permissions

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