Monitoring by using SQL queries

While the replication console provides extensive monitoring capability, you can also use the following SQL statements to monitor replication set status such as inactive table subscriptions, latency, data bytes applied, and disk storage usage.

SELECT QASN.IBMQREP_RECVQUEUES.MCGNAME AS REPLICATION_SET, QASN.IBMQREP_APPLYMON.RECVQ, MAX(QASN.IBMQREP_APPLYMON.OLDEST_TRANS) AS LAST_CONSISTENCY_POINT, QASN.IBMQREP_RECVQUEUES.STATE FROM QASN.IBMQREP_APPLYMON INNER JOIN QASN.IBMQREP_RECVQUEUES ON QASN.IBMQREP_APPLYMON.RECVQ=QASN.IBMQREP_RECVQUEUES.RECVQ GROUP BY QASN.IBMQREP_APPLYMON.RECVQ, QASN.IBMQREP_RECVQUEUES.MCGNAME, QASN.IBMQREP_RECVQUEUES.STATE";

SELECT RECVQ, STATE, COUNT(*) COUNT FROM  QASN.IBMQREP_TARGETS  GROUP BY RECVQ, STATE";

SELECT SUBNAME, STATE FROM  QASN.IBMQREP_TARGETS WHERE STATE <> 'A' ORDER BY SUBNAME";

SELECT A.RECVQ AS RECVQ, A.MONITOR_TIME AS MONITOR_TIME, END2END_LATENCY, QDEPTH, ROWS_PROCESSED, OLDEST_TRANS 
FROM QASN.IBMQREP_APPLYMON A ,  
(SELECT RECVQ, MONITOR_TIME, ROW_NUMBER() OVER (PARTITION BY RECVQ ORDER BY MONITOR_TIME DESC) AS MONITOR_TIME_LATEST 
FROM QASN.IBMQREP_APPLYMON ) B  
WHERE A.RECVQ = B.RECVQ AND A.MONITOR_TIME=B.MONITOR_TIME AND B.MONITOR_TIME_LATEST=1 
ORDER BY RECVQ WITH UR ";

DB2  "SELECT NOW() AS CURRENT_TS, A.MONITOR_TIME, A.RECVQ,CURRENT_MEMORY, QDEPTH, END2END_LATENCY, QLATENCY,ROWS_APPLIED, ROWS_PROCESSED, TRANS_APPLIED, APPLY_LATENCY, OLDEST_TRANS, CAPTURE_LATENCY, DBMS_TIME, TRANS_STREAMING  
FROM QASN.IBMQREP_APPLYMON A ,  
(SELECT RECVQ, MONITOR_TIME, ROW_NUMBER() OVER (PARTITION BY RECVQ ORDER BY MONITOR_TIME DESC) AS MONITOR_TIME_LATEST 
FROM QASN.IBMQREP_APPLYMON ) B  
WHERE A.RECVQ = B.RECVQ AND A.MONITOR_TIME=B.MONITOR_TIME AND B.MONITOR_TIME_LATEST=1 
ORDER BY RECVQ WITH UR ";

SELECT RECVQ, MAX(END2END_LATENCY)/1000 MAX_END2END_LATENCY_SEC, AVG(END2END_LATENCY)/1000 AVG_END2END_LATENCY_SEC FROM QASN.IBMQREP_APPLYMON WHERE DATE(MONITOR_TIME) > CURRENT DATE - 1 DAY GROUP BY RECVQ  WITH UR";

SELECT RECVQ, MAX(ROWS_APPLIED) MAX_ROWS_APPLIED FROM QASN.IBMQREP_APPLYMON WHERE DATE(MONITOR_TIME) > CURRENT DATE - 1 DAY GROUP BY RECVQ  WITH UR";

SELECT RECVQ, SUM(ROWS_APPLIED) TOTAL_ROWS_APPLIED FROM QASN.IBMQREP_APPLYMON WHERE DATE(MONITOR_TIME) > CURRENT DATE - 1 DAY GROUP BY RECVQ  WITH UR";

SELECT SUM(MQ_BYTES)/1000000 TOTAL_MQ_MB FROM QASN.IBMQREP_APPLYMON WHERE DATE (MONITOR_TIME) > CURRENT DATE - 1 DAY" ;
SELECT SUM(FILES_BYTES_RECEIVED)/1000000 TOTAL_FILES_MB FROM QASN.IBMQREP_FILERECV_MON WHERE DATE (MONITOR_TIME) > CURRENT DATE - 1 DAY";

SELECT MAX(DISK_USAGE) MAX_DISK_USAGE_MB, MAX(QDEPTH) MAX_QDEPTH FROM QASN.IBMQREP_FILERECV_MON WHERE DATE(MONITOR_TIME) > CURRENT DATE - 1 DAY";

SELECT MAX(DISK_USAGE) MAX_DISK_USAGE_MB, MAX(QDEPTH) MAX_QDEPTH FROM QASN.IBMQREP_FILERECV_MON WHERE DATE(MONITOR_TIME) > CURRENT DATE - 7 DAY ";