Using SQL in MSG rules

Products

• OPS/MVS Event Management & Automation

Issue/Introduction

I'm creating a MSG rule that selects all messages of the type ACF*

I have a table that lists messages to exclude.  For example:

COL--> MSGID     
****** **********
000001 ACF79040  
000002 ACF99913  
000003 ACFD2409  
000004 ACF12345

 

So I would like the rule to check the table for the message and exit if its in the table otherwise do some processing of the message. 

My code is this:

msg = MSG.ID              
        
"SELECT * FROM TABLE_NAME",  
  "WHERE MSGID=:"msg""   
If SQLCODE <> 100 Then    
  Exit                    

But I get return code 100 no matter if the message id is there or not.

Cause

The referenced MSGID variable in the SQL statement was missing quotes around the variable, thus interfering with matching on the table. 

Resolution

As a general practice, it is best to avoid running a SQL query in a wildcarded MSG rule. Using SQL queries in a rule will incur using an additional process block, and the region could run out of pre-allocated process blocks which can open you up to more issues. For a small amount of message IDs to reference, you could list the IDs in a global variable and then use a POS/WORDPOS to check and see if the ID is in the list.

 

Alternatively, you could use the SELECT-WHEN-OTHERWISE construct at the beginning of the rule to determine the disposition of the message as soon as possible based on the higher occurring messages being processed first in the WHEN instructions, not by alphabetic order. This is described in further detail in the AOF Rule Coding Techniques section of the following product documentation:

Coding Guidelines

Lastly, if there is another underlying reason why the SQL query is necessary for this use case for a particular rule, the SQL query could be fixed by appending quotes to the beginning and end of the MSG variable and then removing the colon from the SELECT statement:

MSG = "'"MSG.ID"'"                         
ADDRESS SQL                                
"SELECT * FROM TABLE_NAME WHERE MSGID = "MSG""
SAY 'SQLCODE = ' SQLCODE