Db2

  • 1.  Connection closes when fetching query results

    Posted Tue March 03, 2020 04:26 PM
    Hi team,

    I am facing intermittent errors in my monitored database. There are monitoring gaps in my graphs because the query DMC does in the database is being timed out.
    I can see the following message in db2diag.log file for the monitored database:
    FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5176
    MESSAGE : ZRC=0x80120003=-2146303997=SQLR_INTRP
    "Statement interrupt, detected at RDS"
    DIA8003C The interrupt has been received.
    DATA #1 : String, 31 bytes
    UCstate terminate bit is raised
    DATA #2 : Hexdump, 4 bytes
    0x00007F10C5B74D68 : C201 0000 ....
    DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQL1005A
    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:

    Then, when I take a look into monitor.% DMC file, the following message is recorded:
    ### Error querying database. Cause: com.ibm.db2.jcc.am.SqlNonTransientConnectionException: [jcc][t4][10335][10366][4.26.14] Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003
    ### The error may exist in com/ibm/aps/tools/alert/tbspace/container/AlertTableSpaceContainerMapper.xml
    ### The error may involve com.ibm.aps.tools.alert.tbspace.container.AlertTableSpaceContainerMapper.doCollect
    ### The error occurred while executing a query
    ### SQL: WITH UNICODE_FIX(X) AS (SELECT NULL FROM SYSIBM.SYSDUMMY1) SELECT DISTINCT T1.TBSP_ID AS TBSP_ID, SUBSTR(T1.TBSP_NAME, 1, 30) AS TBSP_NAME, SUBSTR(T1.CONTAINER_NAME, 1, 100) AS CONTAINER_NAME, T1.CON
    TAINER_ID AS CONTAINER_ID, T1.CONTAINER_TYPE, T1.TOTAL_PAGES AS TOTAL_PAGES, T1.USABLE_PAGES AS USABLE_PAGES, T1.FS_TOTAL_SIZE/1024 AS FS_TOTAL_SIZE_KB, T1.FS_USED_SIZE/1024 AS FS_USED_SIZE_KB,T1
    .ACCESSIBLE, T1.MEMBER, T2.STORAGE_GROUP_NAME, CAST(T1.FS_USED_SIZE AS DOUBLE) * 100/T1.FS_TOTAL_SIZE AS CONT_UTILIZATION_PERCENT FROM TABLE(MON_GET_CONTAINER('',-2)) AS T1, TABLE(MON_GET_TABLESPA
    CE('',-2)) AS T2, SYSIBMADM.MON_TBSP_UTILIZATION AS T3 WHERE T1.TBSP_ID = T2.TBSP_ID and T1.MEMBER=T2.MEMBER AND T3.TBSP_NAME = T2.TBSP_NAME and T3.MEMBER=T2.MEMBER AND (((T2.TBSP_AUTO_RE
    SIZE_ENABLED = 1) and (T2.TBSP_MAX_SIZE != -1) AND (T2.TBSP_MAX_SIZE = (T3.TBSP_TOTAL_SIZE_KB * 1024))) OR (T2.TBSP_AUTO_RESIZE_ENABLED = 0)) AND (T3.TBSP_TYPE <> 'SMS') with UR
    ### Cause: com.ibm.db2.jcc.am.SqlNonTransientConnectionException: [jcc][t4][10335][10366][4.26.14] Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003
    monitor (235) Mar 3, 2020 4:09:45 PM [dfd75e186d7a314643b2b18b161e9651] ERROR PRD_Siebel_235:Mymon MonitorCollectorRunnable::run: Monitor collector MonitorMetric{decription_name='null', isEventMonitorSetup=
    false, keep_result_for_mseconds=14515200000, isPersistIntoRepository=true, isCollect=true, metric_name='tableSpaceContainerQuery', order=0, propertis={isAlert=true, numberlimitation=true, keepdatafor=2592000000, isPersistIntoRepositor
    y=false, keepinmemory=660000, isopenedforcollect=true}} failed.
    org.apache.ibatis.exceptions.PersistenceException:

    Is this something I can change in my JDBC connection properties?

    Thanks in advance


    ------------------------------
    Felipe Alkain
    ------------------------------

    #Db2


  • 2.  RE: Connection closes when fetching query results

    Posted Wed March 04, 2020 01:36 AM
    Hi Felipe,
    We've received your issue, and it will be tracked. Our developers will investigate and update with you ASAP. Thanks for your patience.

    ------------------------------
    Li Hui Yan (Autumn Yan)
    From DMC Team
    ------------------------------

    Original Message


  • 3.  RE: Connection closes when fetching query results

    Posted Thu March 05, 2020 01:35 AM
    Hi Felipe,
    Could you please send out the log file here? So our developer can confirm. Thanks.

    ------------------------------
    Li Hui Yan (Autumn Yan)
    From DMC Team
    ------------------------------

    Original Message


  • 4.  RE: Connection closes when fetching query results

    Posted Thu March 05, 2020 01:47 PM
    Edited by System Fri January 20, 2023 04:34 PM
    Updated... I received your log from Lihui. I'm investigating...
    -----------------------------------------------------------------

    Hi Felipe,

    This is Ke from DMC development. We need the monitor logs(monitor.*) to find the root cause. I sent you a message to provide my working contact. If it's not convenient to paste the logs here, please send to me directly. Thanks.

    Ke

    ------------------------------
    KE CHEN
    ------------------------------

    Original Message


  • 5.  RE: Connection closes when fetching query results

    Posted Fri March 06, 2020 08:42 AM
    Hi Felipe,

    For the -4499 error, did you add any JDBC URL Attribute when you created the connection? eg. blockingReadConnectionTimeout.

    Set blockingReadConnectionTimeout = 0 in the JDBC connection properties may help to avoid the disconnection from the server.

    Thanks,
    Equal

    ------------------------------
    KE CHEN
    ------------------------------

    Original Message


  • 6.  RE: Connection closes when fetching query results

    Posted Mon March 09, 2020 12:04 PM
    Hi Ke Chen,

    Thanks for your input. I have tried blockingReadConnectionTimeout=0 and the issue still remains.

    It should be related to the following query:
    WITH APPL_INFO AS ( SELECT DISTINCT T.TBSP_ID, T.TAB_FILE_ID, RTRIM(T.TABSCHEMA) AS TABSCHEMA, RTRIM(T.TABNAME) AS TABNAME, T.DATA_PARTITION_ID AS DATA_PARTITION_ID, T.MEMBER AS MEMBER FROM TABLE( MON_GET_LOCKS(NULL,-2)) AS L LEFT OUTER JOIN TABLE( MON_GET_TABLE(NULL,NULL,-2)) AS T ON L.TBSP_ID=T.TBSP_ID AND L.TAB_FILE_ID = T.TAB_FILE_ID AND T.MEMBER = L.MEMBER ) SELECT L.APPLICATION_HANDLE AS APPLICATION_HANDLE, L.MEMBER AS MEMBER, C.SESSION_AUTH_ID AS SESSION_AUTH_ID, C.APPLICATION_NAME AS APPLICATION_NAME, C.WORKLOAD_OCCURRENCE_STATE AS WORKLOAD_OCCURRENCE_STATE, L.LOCK_STATUS AS LOCK_STATUS, L.LOCK_MODE AS LOCK_MODE, L.LOCK_NAME AS LOCK_NAME, L.LOCK_OBJECT_TYPE AS LOCK_OBJECT_TYPE, L.LOCK_CURRENT_MODE AS LOCK_CURRENT_MODE, C.NUM_LOCKS_HELD AS NUM_LOCKS_HELD, C.LOCK_WAITS AS LOCK_WAITS, C.LOCK_WAIT_TIME AS LOCK_WAIT_TIME, CASE WHEN L.LOCK_STATUS = 'G' THEN substr2(B.STMT_TEXT, 0, 32000) ELSE substr2(A.STMT_TEXT, 0, 32000) END AS STMT_TEXT, RTRIM(APPL_INFO.TABSCHEMA) AS TABSCHEMA, RTRIM(APPL_INFO.TABNAME) AS TABNAME, APPL_INFO.DATA_PARTITION_ID AS DATA_PARTITION_ID FROM TABLE( MON_GET_LOCKS(NULL,-2)) AS L LEFT OUTER JOIN TABLE( MON_GET_CONNECTION(L.APPLICATION_HANDLE,L.MEMBER)) AS C ON ( L.MEMBER = C.MEMBER AND L.APPLICATION_HANDLE = C.APPLICATION_HANDLE) LEFT OUTER JOIN TABLE( MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS B ON ( C.LAST_EXECUTABLE_ID = B.EXECUTABLE_ID AND B.MEMBER = C.MEMBER) LEFT OUTER JOIN TABLE( MON_GET_ACTIVITY(L.APPLICATION_HANDLE,L.MEMBER)) AS A ON ( L.MEMBER = A.MEMBER AND L.APPLICATION_HANDLE = A.APPLICATION_HANDLE) LEFT OUTER JOIN APPL_INFO ON L.TBSP_ID=APPL_INFO.TBSP_ID AND L.TAB_FILE_ID = APPL_INFO.TAB_FILE_ID AND L.MEMBER = APPL_INFO.MEMBER WHERE L.LOCK_STATUS IN ('G','W','C') FETCH FIRST 5000 ROWS ONLY WITH UR

    The performance is not good and somehow DMC is timed out.
    I have installed DSM again and I do not have this timeout issue. I was also not able to see the above query in the package cache anymore.

    Is this query related to some alert/monitoring that can be disabled ?

    ------------------------------
    Felipe Alkain
    ------------------------------

    Original Message


  • 7.  RE: Connection closes when fetching query results

    Posted Tue March 10, 2020 11:50 PM
    Edited by System Fri January 20, 2023 04:11 PM

    Hi Felipe,

    We did some investigation and got the summary for this issue. We will fix it in the coming release. 

    Problem:

    1. The query runs more than 20 seconds and get disconnected by time out. It will impact Locks page and Blocking and waiting page.
    2. The disconnected connection disable data collection for other metrics in the same cycle.


    Root cause:

    Monitor has a 20 seconds timeout for all metrics in order to avoid hanging queries and make sure the collection can finish in one cycle. For the case of the locks metric, the query itself requires joining multiple tables which is time consuming. And the network and large data amount will also make the query runs longer. So the query can not return before timeout.

    Solution in DMC:

    1. Increase the timeout time to 30 seconds for all metrics and for the locks query itself to 1 minute.
    2. Refine the logic to catch the -4499 message and resume the connection. Even the disconnection occurs, other metrics collection will not be disabled.


    To answer your questions:
    1. Make sure you enable system queries in the package cache page. If you still not see it, I need to view the logs to see what happened.
    2. The query doesn't relate to alert and event monitors. But less transactions in the database will produce less data for MON_GET_LOCKS() function. Thus the query could run fast. You may disable some alerts and monitors if they are not required.

    Thanks,
    Equal

    ------------------------------
    KE CHEN
    ------------------------------

    Original Message


  • 8.  RE: Connection closes when fetching query results

    Posted Wed March 11, 2020 10:06 AM
    Thank you very much :)

    ------------------------------
    Felipe Alkain
    ------------------------------

    Original Message