Db2

 View Only
Expand all | Collapse all

DMC 3.1.5 Locks on monitored database from UC_MYMON application

  • 1.  DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Fri June 25, 2021 08:32 AM
      |   view attached
    Hi,

    There are locks on a monitored database. From DMC 3.1.5.

    The query in the file "query_capture.sql" runs for a long time. Application is UC_MYMON. The table IBM_RTMON.ACTIVITY_1624614058186 and IBM_RTMON.ACTIVITY_STMT_1624614058186 has only 2317 rows at this time.

    Another command from UC_MYMON application that wants to delete the table. Is unable to drop the table.

    How to solve the issue?

    ------------------------------
    Regards, Dennis
    ------------------------------

    #Db2

    Attachment(s)

    7z
    Query_capture.7z   2 KB 1 version


  • 2.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon June 28, 2021 09:48 PM
    Hi Dennis,

    So are there locks on these activity tables? could you help to check the lock information? We need to identify the specific lock, perhaps you can export lock data from the blocking and waiting connections monitor metrics.

    And what's the error message when the table is dropped?
    Thanks.



    ------------------------------
    Rui Li
    ------------------------------



  • 3.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Tue June 29, 2021 08:56 AM
      |   view attached
    Hi Rui,

    Here is a snip and logs. Attached is the data.csv.
    Is this the information you want?

    Today not are error message when the table is dropped. Mabye is a result from force applictions command, which I had run.



    This are logs from db2diag:

    2021-06-25-08.18.18.157000-240 I7525155F726 LEVEL: Error
    PID : 4612 TID : 1604 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : **
    APPHDL : 0-53943 APPID: *.64741.210625114112
    UOWID : 7 ACTID: 1
    AUTHID : ******** HOSTNAME: ****
    EDUID : 1604 EDUNAME: db2agent (**) 0
    FUNCTION: DB2 UDB, data management, sqldTableDrop, probe:758
    MESSAGE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from application"
    DIA8003C The interrupt has been received.
    DATA #1 : String, 61 bytes
    Unable to drop table.
    RC=80100003, Pool=4, Object=17, Class=0


    2021-06-29-06.47.01.015000-240 E7240715F1683 LEVEL: Info (Origin)
    PID : 4612 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : **
    APPHDL : 0-56175 APPID: *.62847.210629104848
    UOWID : 66 ACTID: 1
    AUTHID : ******** HOSTNAME: ****
    EDUID : 6440 EDUNAME: db2agent (**) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x80100003=-2146435069=SQLP_LINT "Interrupt from application"
    DIA8003C The interrupt has been received.
    DATA #1 : String, 62 bytes
    An unexpected error was detected during statement compilation.
    DATA #2 : Boolean, 1 bytes
    false
    DATA #3 : Boolean, 1 bytes
    false
    DATA #4 : Boolean, 1 bytes
    true
    DATA #5 : Boolean, 1 bytes
    false
    DATA #6 : Hex integer, 4 bytes
    0x00000000
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQL11054
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:
    DATA #8 : Hex integer, 4 bytes
    0x00000000
    DATA #9 : String with size, 60 bytes
    /* IBM_DSSNAP */ drop table IBM_RTMON.ACTIVITY_1624959119510
    DATA #10: String, 167 bytes
    Compiler error stack for rc = -2146435069:
    sqlnn_cmpl[300]
    sqlnp_main[250]
    sqlnp_parser[510]
    sqlnp_smactn[180]
    sqlnq_drop_db_object[700]
    sqlnq_drop_db_object_end[225]



    ------------------------------
    Regards, Dennis
    ------------------------------

    Attachment(s)

    csv
    data.csv   11 KB 1 version


  • 4.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon July 12, 2021 09:35 AM
    Hello Rui LI,

    I have the same Problem. As a result there are no responsiveness KPI's and the TS4MONITOR Tablespace for DMS is running out of space.





    Best regards,
    Joachim

    ------------------------------
    Joachim Müller
    ------------------------------



  • 5.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon July 12, 2021 10:37 PM
    Hi Joachim,

    Could you please help to execute the following query to find out the most consumed table in TS4MONITOR Tablespace? Then help to check the number of rows in this most consumption table?

    SELECT substr(a.TABSCHEMA,1,12) TABSCHEMA, substr(a.TABNAME,1,12) TABNAME, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE) TOTAL_SIZE_IN_KB,
    SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)/(1024) AS TOTAL_SIZE_IN_MB, sum(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/(1024*1024) AS TOTAL_SIZE_IN_GB,
    SUM(DATA_OBJECT_P_SIZE) as DATA_OBJECT_P_SIZE,
    SUM(INDEX_OBJECT_P_SIZE) as INDEX_OBJECT_P_SIZE, SUM(LONG_OBJECT_P_SIZE) as LONG_OBJECT_P_SIZE, SUM(LOB_OBJECT_P_SIZE) as LOB_OBJECT_P_SIZE, SUM(XML_OBJECT_P_SIZE) as XML_OBJECT_P_SIZE
    FROM SYSIBMADM.ADMINTABINFO a, SYSCAT.tables t
    where t.tabname=a.tabname and t.tabschema=a.tabschema --and t.tbspace='TSFMONITOR'
    and a.TABSCHEMA = 'IBM_RTMON' or (a.TABSCHEMA = 'IBMCONSOLE' and (a.TABNAME like 'STATS_%' or a.TABNAME like 'EVENT_%') )
    GROUP BY a.TABSCHEMA, a.TABNAME order by TOTAL_SIZE_IN_KB desc;

    And can you help to view detail of the lock with applicatioon handle 48920 and the number of locks held 2989? On the view detail page, we can find the exact blocking and waiting sql. Thank you.

    ------------------------------
    Rui Li
    ------------------------------



  • 6.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Tue July 20, 2021 03:41 AM
    Hello Rui,

    Sorry for the delay.
    It seems that Krishna has the same issues and opened a case with IBM, so I don't want to flood you with more data.

    I will waiting for feedback...

    Best regards,
    Joachim

    ------------------------------
    Joachim Müller
    ------------------------------



  • 7.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon July 12, 2021 05:40 PM

    Hi Rui, 

    Same locking issue found. I dont find the option here to upload the exported file but here is the snippet from "Blocking and waiting connections" monitor. Another issue, I think the issue also causing the TS4MONITOR tablespace to become full most of the times. (let us know if our understanding is wrong) 

    Locking Issue


    Tablespace full issue



    ------------------------------
    Krishna Murakonda
    ------------------------------



  • 8.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon July 12, 2021 10:52 PM
    Hi Krishna,

    Maybe you can use the Upload file in the bottom left corner of this enter panel. Or it will be great if you can open a case to upload the exported lock information and we can use it to track this issue. Simply view detail page can see more info of the locks, including blocking and waiting sql.

    Same thing, you can use the following query to check the most consumed table in TS4MONITOR Tablespace.

    SELECT substr(a.TABSCHEMA,1,12) TABSCHEMA, substr(a.TABNAME,1,12) TABNAME, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE) TOTAL_SIZE_IN_KB,
    SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)/(1024) AS TOTAL_SIZE_IN_MB, sum(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/(1024*1024) AS TOTAL_SIZE_IN_GB,
    SUM(DATA_OBJECT_P_SIZE) as DATA_OBJECT_P_SIZE,
    SUM(INDEX_OBJECT_P_SIZE) as INDEX_OBJECT_P_SIZE, SUM(LONG_OBJECT_P_SIZE) as LONG_OBJECT_P_SIZE, SUM(LOB_OBJECT_P_SIZE) as LOB_OBJECT_P_SIZE, SUM(XML_OBJECT_P_SIZE) as XML_OBJECT_P_SIZE
    FROM SYSIBMADM.ADMINTABINFO a, SYSCAT.tables t
    where t.tabname=a.tabname and t.tabschema=a.tabschema --and t.tbspace='TS4MONITOR'
    and a.TABSCHEMA = 'IBM_RTMON' or (a.TABSCHEMA = 'IBMCONSOLE' and (a.TABNAME like 'STATS_%' or a.TABNAME like 'EVENT_%') )
    GROUP BY a.TABSCHEMA, a.TABNAME order by TOTAL_SIZE_IN_KB desc;



    ------------------------------
    Rui Li
    ------------------------------



  • 9.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Mon July 12, 2021 11:09 PM
    Thanks Rui. Case - TS006170276 was opened and uploaded the exported file to case. 

    Also, Below is the o/p from the given query, my wild guess is same table - ACTIVITY_162 consuming the highest space in tablespace. 

    SELECT substr(a.TABSCHEMA,1,12) TABSCHEMA, substr(a.TABNAME,1,12) TABNAME, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE) TOTAL_SIZE_IN_KB, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+XML_OBJECT_P_SIZE)/(1024) AS TOTAL_SIZE_IN_MB, sum(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/(1024*1024) AS TOTAL_SIZE_IN_GB, SUM(DATA_OBJECT_P_SIZE) as DATA_OBJECT_P_SIZE, SUM(INDEX_OBJECT_P_SIZE) as INDEX_OBJECT_P_SIZE, SUM(LONG_OBJECT_P_SIZE) as LONG_OBJECT_P_SIZE, SUM(LOB_OBJECT_P_SIZE) as LOB_OBJECT_P_SIZE, SUM(XML_OBJECT_P_SIZE) as XML_OBJECT_P_SIZE FROM SYSIBMADM.ADMINTABINFO a, SYSCAT.tables t where t.tabname=a.tabname and t.tabschema=a.tabschema --and t.tbspace='TS4MONITOR'
    and a.TABSCHEMA = 'IBM_RTMON' or (a.TABSCHEMA = 'IBMCONSOLE' and (a.TABNAME like 'STATS_%' or a.TABNAME like 'EVENT_%') ) GROUP BY a.TABSCHEMA, a.TABNAME order by TOTAL_SIZE_IN_KB desc

    TABSCHEMA TABNAME TOTAL_SIZE_IN_KB TOTAL_SIZE_IN_MB TOTAL_SIZE_IN_GB DATA_OBJECT_P_SIZE INDEX_OBJECT_P_SIZE LONG_OBJECT_P_SIZE LOB_OBJECT_P_SIZE XML_OBJECT_P_SIZE
    ------------ ------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
    IBM_RTMON ACTIVITY_162 8192 8 0 2048 2048 0 4096 0
    IBM_RTMON ACTIVITY_STM 8192 8 0 2048 2048 0 4096 0
    IBM_RTMON SCSTATS 6144 6 0 2048 0 0 4096 0
    IBM_RTMON SUPERCLASSST 6144 6 0 2048 0 0 4096 0
    IBM_RTMON WLSTATS 6144 6 0 2048 0 0 4096 0
    IBM_RTMON HISTOGRAMBIN 2048 2 0 2048 0 0 0 0
    IBM_RTMON OSMETRICS 2048 2 0 2048 0 0 0 0
    IBM_RTMON QSTATS 2048 2 0 2048 0 0 0 0
    IBM_RTMON SCMETRICS 2048 2 0 2048 0 0 0 0
    IBM_RTMON SUPERCLASSME 2048 2 0 2048 0 0 0 0
    IBM_RTMON WLMETRICS 2048 2 0 2048 0 0 0 0

    11 record(s) selected.

    ------------------------------
    Krishna Murakonda
    ------------------------------



  • 10.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Tue July 13, 2021 12:58 AM
    Hi Krishna,

    Thank you for opening case, we will investigate to find out.



    ------------------------------
    Rui Li
    ------------------------------



  • 11.  RE: DMC 3.1.5 Locks on monitored database from UC_MYMON application

    Posted Tue July 13, 2021 03:23 AM

    Hi,

    I thought this deadlock issues are fixed in 3.1.5? I've opened TS005730185 for 3.1.4 in may because of that. I also receive these tablespace full alerts for TS4MONITOR as the other guys have mentioned. It is defined with 2 GB and only 366MB are occupied.

    TABSCHEMA TABNAME TOTAL_SIZE_IN_KB TOTAL_SIZE_IN_MB TOTAL_SIZE_IN_GB DATA_OBJECT_P_SIZE
    IBM_RTMON ACTIVITY_162 144640 141 0 125440
    IBM_RTMON ACTIVITY_162 90112 88 0 79616
    IBM_RTMON ACTIVITY_STM 85760 83 0 70400
    IBM_RTMON ACTIVITY_STM 54272 53 0 45568
    IBM_RTMON LOCK_PARTICI 1792 1 0 768
    IBM_RTMON SCSTATS 1536 1 0 512
    IBM_RTMON WLSTATS 1536 1 0 512


    In my case these deadlocks are caused by two DMC threads want to drop IBM_RTMON.ACTIVITY_STMT_1625896711845

    -------------------------------------------------------
    Event ID : 679
    Event Type : DEADLOCK
    Event Timestamp : 2021-07-10-08.26.41.137908
    Partition of detection : 0
    -------------------------------------------------------

    Deadlock Graph
    --------------
    Total number of deadlock participants : 2
    Participant that was rolled back : 2
    Type of deadlock : local

    Participant Participant Deadlock Member Application Handle
    Requesting Lock Holding Lock
    --------------- --------------- --------------- ------------------
    1 2 0 058911
    2 1 0 061579


    Participant No 2 requesting lock
    ----------------------------------
    Lock Name : 0x000098000C0F000040CF2AF8C3
    Lock wait start time : 2021-07-10-08.26.34.300327
    Lock wait end time : 2021-07-10-08.26.41.137908
    Lock Type : CATALOG
    Lock Specifics : ROWID=3852,uniqueBits:F82ACF40
    Lock Attributes : 00000000
    Lock mode requested : Exclusive
    Lock mode held : Share
    Lock Count : 1
    Lock Hold Count : 0
    Lock rrIID : 0
    Lock Status : Converting
    Lock release flags : 40000000
    Tablespace TID : 0
    Tablespace Name : SYSCATSPACE
    Table FID : 152
    Table Schema : SYSIBM
    Table Name : SYSTABLES

    Participant No 1 requesting lock
    ----------------------------------
    Lock Name : 0x000098000C000F000000000052
    Lock wait start time : 2021-07-10-08.26.34.292849
    Lock wait end time : 2021-07-10-08.26.41.137908
    Lock Type : ROW
    Lock Specifics : ROWID=12,DATA_PARTITION_ID=0,PAGEID=15
    Lock Attributes : 00400000
    Lock mode requested : Exclusive
    Lock mode held : Exclusive
    Lock Count : 0
    Lock Hold Count : 0
    Lock rrIID : 0
    Lock Status : Waiting
    Lock release flags : 00000000
    Tablespace TID : 0
    Tablespace Name : SYSCATSPACE
    Table FID : 152
    Table Schema : SYSIBM
    Table Name : SYSTABLES


    Attributes Requester Requester
    --------------------- ------------------------------ ------------------------------
    Participant No 2 1
    Application Handle 061579 058911
    Application ID 10.12.102.111.58239.210710062343 10.12.102.111.57204.210710060341
    Application Name DB2MgmtConsole DB2MgmtConsole
    Authentication ID DB2BATCH DB2BATCH
    Requesting AgentID 20616 19572
    Coordinating AgentID 20616 19572
    Agent Status UOW Executing UOW Executing
    Application Action No action No action
    Lock timeout value 120 120
    Lock wait value 30000 30000
    Tenant ID 0 0
    Tenant Name SYSTEM SYSTEM
    Workload ID 1 1
    Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
    Service subclass ID 13 13
    Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
    Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
    Current Request Execute Execute
    TEntry state 2 2
    TEntry flags1 00000000 00000000
    TEntry flags2 00000200 00000200
    Lock escalation no no
    Client userid
    Client wrkstnname 10.12.102.111 10.12.102.111
    Client applname
    Client acctng
    Utility ID



    Current Activities of Participant No 2
    ----------------------------------------
    Activity ID : 1
    Uow ID : 16
    Package Name : SYSSH100
    Package Schema : NULLID
    Package Version :
    Package Token : SYSLVL01
    Package Sectno : 1
    Reopt value : none
    Incremental Bind : no
    Eff isolation : UR
    Eff degree : 1
    Actual degree : 1
    Eff locktimeout : 120
    Stmt first use : 2021-07-10-08.26.33.493842
    Stmt last use : 2021-07-10-08.26.33.493842
    Stmt unicode : no
    Stmt query ID : 0
    Stmt nesting level : 0
    Stmt invocation ID : 0
    Stmt source ID : 0
    Stmt pkgcache ID : 3788161155676
    Stmt type : Dynamic
    Stmt operation : DDL, (not Set Constraints)
    Stmt no : -1
    Stmt text : /* IBM_DSSNAP */ drop table IBM_RTMON.ACTIVITY_STMT_1625896711845



    Past Activities of Participant No 2
    -------------------------------------
    Activities not available


    Current Activities of Participant No 1
    ----------------------------------------
    Activity ID : 1
    Uow ID : 7
    Package Name : SYSSH100
    Package Schema : NULLID
    Package Version :
    Package Token : SYSLVL01
    Package Sectno : 1
    Reopt value : none
    Incremental Bind : no
    Eff isolation : UR
    Eff degree : 1
    Actual degree : 1
    Eff locktimeout : 120
    Stmt first use : 2021-07-10-08.26.33.495736
    Stmt last use : 2021-07-10-08.26.33.495736
    Stmt unicode : no
    Stmt query ID : 0
    Stmt nesting level : 0
    Stmt invocation ID : 0
    Stmt source ID : 0
    Stmt pkgcache ID : 3788161155676
    Stmt type : Dynamic
    Stmt operation : DDL, (not Set Constraints)
    Stmt no : -1
    Stmt text : /* IBM_DSSNAP */ drop table IBM_RTMON.ACTIVITY_STMT_1625896711845



    Past Activities of Participant No 1
    -------------------------------------
    Activities not available



    Regards
    Gerald



    ------------------------------
    Gerald Zottl
    ------------------------------