Results 1 to 4 of 4

Thread: deadlock

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: deadlock

    db2 9.5 fp8 on p/linux
    I have setup the tables for gathering the info from deadlock event monitor
    there are different tables created
    created a small query similar to
    select c.AUTH_ID ,d.APPL_ID_HOLDING_LK,d.DEADLOCK_ID,d.PARTICIPANT_ NO,PARTICIPANT_NO_HOLDING_LK, case when d.STMT_OPERATION=1 then 'PREPARE' when d.STMT_OPERATION=2 then 'EXECUTE' when d.STMT_OPERATION=3 then 'EXECUTE_IMM' when d.STMT_OPERATION=4 then 'OPEN' when d.STMT_OPERATION=5 then 'FETCH' when d.STMT_OPERATION=6 then 'CLOSE' when d.STMT_OPERATION=15 then 'SELECT' else 'UNDEF' end case ,substr(d.STMT_TEXT,1,100) from dba.CONNHEADER_DEADLOCK c ,dba.DLCONN_DEADLOCK d where c.AGENT_ID=d.AGENT_ID and c.APPL_ID=d.APPL_ID order by d.APPL_ID_HOLDING_LK


    does anybody has been using these tables ?
    do you already have a query that formats most relevant data ?
    Is there any detailed description of these tables as system monitoring guide does not even describes these. not even statement operation is explained and had to be looked up from sqlmon.h

    thanks for all update/help
    best regards, Guy
    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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    has anybody tried to use db2evmonfmt (java prg) from 9.7 on evmon data 9.5 ?
    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
    Jun 2007
    Location
    germany
    Posts
    155
    impatient?
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have implemented the java program with the new lock event monitor in 9.7. My understanding is that it is different than 9.5.

    Here is the LOCK_EVENTS table created by the 9.7 event monitor for locking (which includes locktimeouts and deadlocks) that is used by the java program:

    Code:
                                    Data type                     Column
    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    PARTITION_KEY                   SYSIBM    INTEGER                      4     0 No
    APPL_ID                         SYSIBM    VARCHAR                     64     0 Yes
    APPL_NAME                       SYSIBM    VARCHAR                    255     0 Yes
    EVENT_CORRELATION_ID            SYSIBM    VARCHAR                     32     0 Yes
    EVENT_DATA                      SYSIBM    BLOB                   4189000     0 No
    EVENT_ID                        SYSIBM    BIGINT                       8     0 No
    EVENT_TIMESTAMP                 SYSIBM    TIMESTAMP                   10     6 No
    EVENT_TYPE                      SYSIBM    VARCHAR                     32     0 No
    MEMBER                          SYSIBM    SMALLINT                     2     0 No
    RECORD_SEQ_NUM                  SYSIBM    BIGINT                       8     0 No
    RECORD_TYPE                     SYSIBM    BIGINT                       8     0 No
    SERVICE_SUBCLASS_NAME           SYSIBM    VARCHAR                    128     0 Yes
    SERVICE_SUPERCLASS_NAME         SYSIBM    VARCHAR                    128     0 Yes
    WORKLOAD_NAME                   SYSIBM    VARCHAR                    128     0 Yes
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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