MQ Table Monitoring in 12.3.6

[Amit Sharma]

Dear Experts,

We have a recent issue in our Engine where we were not able to login to AWI, and we saw below error for our SQL Data base logs,

U00003594 UCUDB-Ret: '3590' Opcode: 'INSR' SQL-Anweisung: 'INSERT INTO MQ2OWP (MQOWP_System, MQOWP_CAddr, MQOWP_CSRName, MQOWP_CAcv, MQOWP_BAddr, MQOWP_BSRName, MQOWP_BAcv, MQOWP_FAddr, MQOWP_LogAddr, MQOWP_PhysAddr, MQOWP_BTable, MQOWP_SchedTime, MQOWP_Status, MQOWP_Priority, MQOWP_DRole, MQOWP_LAddr, MQOWP_Len, MQOWP_Msg) VALUES (?, ?, ?, ?, NULL, NULL, ?, ?, NULL, NULL, ?, convert(datetime,convert(varchar(max),getutcdate(),20),20), ?, ?, ?, NULL, ?, ?)'
U00003590 UCUDB - DB-Fehler: 'SQLExecDirect', 'ERROR   ', '01000', 'Arithmetic overflow occurred.'

Which means we have to do cold restart of our Engine to get the MQ tables truncated and this issue is fixed there after.

However we wanted to know if there is any way we can monitor our MQ tables to avoid this issue in future. Some inhouse monitoring or any SQL query to monitor the MQ table threshold ?

It would be great help to run prod smoothly.

Thanks
Amit Sharma

[Markus Embacher]

Hi Amit,
this issue will not happen anymore with version 12.3.8 or higher. 
Regards, Markus

[Mihail Cheie]

Create a dummy job and add this in Process tab:

:SET &MAIL_RCPT# = [e-mail]

! "MQ1WP" - The table is used to store processing messages. Because processing is handled on a highly dynamic basis, the corresponding tables are mostly empty.
! After a certain limit (we don't know yet the precise limit - most probably over 20k-30k), if this table gets filled, the system crashes.
! The rest of the counts dont have the same importance and can be checked from SystemOverview. You can read more about them here: https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/All%20Guides/help.htm#ucabfn.htm%3FTocPath%3DUser%2520Guide%7CUserInterface%7CSystem%2520Overview%7C_____11
:SET &MQ_PWP_COUNT# = SYS_INFO(MQPWP, COUNT)
:SET &MQ_WP_COUNT# = SYS_INFO(MQWP, COUNT)
:SET &MQ_DWP_COUNT# = SYS_INFO(MQDWP, COUNT)
:SET &MQ_OWP_COUNT# = SYS_INFO(MQOWP, COUNT)
:SET &MQ_RWP_COUNT# = SYS_INFO(MQRWP, COUNT)
:SET &MQ1WP_COUNT# = GET_VAR(VARA.SQL.MQ1WP)
:SET &MQ1CP_COUNT# = GET_VAR(VARA.SQL.COUNT_MQ1CP_TABLES)
!send email if various values are out of boundaries

:IF &MQ1WP_COUNT# >= 1000
: SET &RETVAL# = SEND_MAIL(&MAIL_RCPT#,,"CRITICAL ISSUE - MQ1WP Count exceeds 1000 on &$SYSTEM#","MQ1WP message queue count on &$SYSTEM# is currently &MQ1WP_COUNT#. Please immediately follow the steps outlined in the AE Troubleshooting Guideline")
: IF &RETVAL# <> 0
: PRINT "ERROR: Sending Email failed with code &RETVAL#!"
: ENDIF
:ENDIF


:IF &MQ1CP_COUNT# > 100000
: set &send_email# = activate_uc_object (CALL.CONDITION.SYS_MONITOR_CP_COUNT)
:ENDIF



-------------------------------------------------
VARA.SQL.MQ1WP = select COUNT(*) from MQ1WP

Query inside VARA.SQL.COUNT_MQ1CP_TABLES is:

SELECT ( (SELECT COUNT (*) FROM mq1cp001)
+ (SELECT COUNT (*) FROM mq1cp007)
+ (SELECT COUNT (*) FROM mq1cp003)
+ (SELECT COUNT (*) FROM mq1cp004)
+ (SELECT COUNT (*) FROM mq1cp005)
+ (SELECT COUNT (*) FROM mq1cp006))
AS All_MQCP
FROM DUAL

You can put whatever you want in your CALL object - desired recipients and custom text. 

we use for subject: Informational email - MQ1CP table count is {VARA.SQL.COUNT_MQ1CP_TABLES}
Of course, you can use a single way of sending the alerts or multiple alerts for multiple thresholds. Hope this helps!

[Olgun Onur Ozmen]

Hi,

you can monitor it via automic internal webservice and add to third party alarm tool.

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------