Hi,

I am looking in the monitor.0 file and this query keeps showing up with Sqlcode -802, no matter which database is executes against (v10.5.8)

 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 WITH UR)    
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,
  (TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000) AS LOCK_ELAPSED_TIME,
  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    
  INNER JOIN TABLE(MON_GET_CONNECTION(NULL,-2)) 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(NULL,-2))  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')
  AND (C.LOCK_WAIT_TIME >= 30000 OR TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000 >= ?)    
FETCH FIRST 1000 ROWS ONLY
WITH UR

Hi,

I am looking in the monitor.0 file and this query keeps showing up with Sqlcode -802, no matter which database is executes against (v10.5.8)

 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 WITH UR)    
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,
  (TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000) AS LOCK_ELAPSED_TIME,
  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    
  INNER JOIN TABLE(MON_GET_CONNECTION(NULL,-2)) 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(NULL,-2))  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')
  AND (C.LOCK_WAIT_TIME >= 30000 OR TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000 >= ?)    
FETCH FIRST 1000 ROWS ONLY
WITH UR

Hi,

I am looking in the monitor.0 file and this query keeps showing up with Sqlcode -802, no matter which database is executes against (v10.5.8)

 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 WITH UR)    
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,
  (TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000) AS LOCK_ELAPSED_TIME,
  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    
  INNER JOIN TABLE(MON_GET_CONNECTION(NULL,-2)) 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(NULL,-2))  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')
  AND (C.LOCK_WAIT_TIME >= 30000 OR TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000 >= ?)    
FETCH FIRST 1000 ROWS ONLY
WITH UR

Hi,

I am looking in the monitor.0 file and this query keeps showing up with Sqlcode -802, no matter which database is executes against (v10.5.8)

 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 WITH UR)    
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,
  (TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000) AS LOCK_ELAPSED_TIME,
  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    
  INNER JOIN TABLE(MON_GET_CONNECTION(NULL,-2)) 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(NULL,-2))  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')
  AND (C.LOCK_WAIT_TIME >= 30000 OR TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - C.UOW_START_TIME))*1000 >= ?)    
FETCH FIRST 1000 ROWS ONLY
WITH UR