Wednesday, June 9, 2021

Send messages to the QSYSOPR message queue with SQL

send_message to qsysopr msgq

One of the many things that caught my eye in the latest round of Technology Refreshes for 7.4 and 7.3 is a way to send messages to the System Operator message queue, QSYSOPR, using a SQL procedure, SEND_MESSAGE.

Before I used the Send Message command, SNDMSG, to do this:

SNDMSG MSG('Message from SNDMSG command') TOUSR(*SYSOPR)


                        Additional Message Information

From . . . . . . :  SIMON         Severity . . . . . :  80
Message type . . :  Information
Date sent  . . . :  DD/DD/DD      Time sent  . . . . :  TT:TT:TT

Message . . . . :   Message from SNDMSG command

This new SQL procedure, SEND_MESSAGE, must use a predefined message that must have one substitution parameter that is defined as *CHAR *VARY 2.

My first thought was to use the CPF9898 message, but that has a severity of 40, and it is fixed length of 512.

I decided to create my own message I could use. To avoid any confusion with any existing messages in any messages files I created my own message file, and added my message to it:

CRTMSGF MSGF(MYLIB/MYMSGF)

ADDMSGD MSGID(MINE123) MSGF(MYMSGF) MSG('&1')
          FMT((*CHAR *VARY 2))
          TYPE(*NONE) LEN(*NONE) DMPLST(*NONE)

Now when check the messages information I see that is has a severity of zero:

                 Display Message Attributes

Message ID . . . . . :  MINE123
Message file . . . . :  MYMSGF
  Library  . . . . . :    MYLIB

Severity . . . . . . :  0

And the substitution variable is defined as desired:

                       Display Field Data

Message ID . . . . . :  MINE123
Message file . . . . :  MYMSGF
  Library  . . . . . :    MYLIB

                              Decimal     Vary  
Field   Data Type   Length   Positions   Length   Dump
 &1     *CHAR       *VARY                   2     *NO

SEND_MESSAGE has four parameters:

CALL QSYS2.SEND_MESSAGE(
  MESSAGE_ID => 'MINE123',
  MESSAGE_LENGTH => 14,
  MESSAGE_TEXT => 'This is a test',
  MESSAGE_FILE_LIBRARY => 'MYLIB',
  MESSAGE_FILE => 'MYMSGF'
)
  1. MESSAGE_ID:  Message id that is to be sent to QSYSOPR message queue
  2. MESSAGE_LENGTH:  Length of message
  3. MESSAGE_TEXT:  Text of the message to be sent
  4. MESSAGE_FILE_LIBRARY:  Library where the message file is. *LIBL can be used. This is optional if not given the value QSYS is used
  5. MESSAGE_FILE:  The message file that contains the message id. This is optional, if not given the default is the file QSQLMSG

When I execute the above statement in ACS's "Run SQL Scripts" I see the following message in the QSYSOPR message queue:

                         Display Messages

Queue . . . . :  QSYSOPR      Program . . . :  *DSPMSG
  Library . . :    QSYS         Library . . :
Severity  . . :  60           Delivery  . . :  *HOLD

Type reply (if required), press Enter.
This is a test

I can also display the message using the MESSAGE_QUEUE_INFO SQL View:

01  SELECT MESSAGE_ID,MESSAGE_TEXT,
02         LENGTH(MESSAGE_TEXT) AS "Length",
03         SEVERITY
04    FROM QSYS2.MESSAGE_QUEUE_INFO
05  WHERE MSGQ_NAME = 'QSYSOPR'
06    AND MESSAGE_ID = 'MINE123' 
07  ORDER BY MESSAGE_TIMESTAMP DESC
08  LIMIT 10 ;

Line 2: I created this column so that you can see the value I used in the Procedure's message length is reflected in the contents in the MESSAGE_TEXT column.

Line 5 and 6: I only want the messages from the QSYSOPR message queue and where the message id is the one I created and used.

Line 8: I am using the LIMIT so that only that number of results are returned. If there were more than ten results only the first ten would be returned.

The result is:

MESSAGE_ID MESSAGE_TEXT                 Length SEVERITY
---------- ---------------------------- ------ --------
MINE123    This is a test                   14        0

I can also use the SEND_MESSAGE without the parameter names:

CALL QSYS2.SEND_MESSAGE('MINE123',16,'Message 2','MYLIB','MYMSGF')

Notice that the length given, 16, is greater than the length of the message. This causes those extra positions to be filled with "junk".

When I use the MESSAGE_QUEUE_INFO I can see that "junk".

MESSAGE_ID MESSAGE_TEXT                 Length SEVERITY
---------- ---------------------------- ------ --------
MINE123    Message 2 test N                 16        0
MINE123    This is a test                   14        0

This "junk" can also be seen when I display the QSYSOPR messages in the 5250 session:

                         Display Messages

Queue . . . . :  QSYSOPR      Program . . . :  *DSPMSG
  Library . . :    QSYS         Library . . :
Severity  . . :  60           Delivery  . . :  *HOLD

Type reply (if required), press Enter.
This is a test
Message 2?test?N

Next example has been using this Procedure in a CL program with the Run SQL command, RUNSQL:

01  PGM

02  DCL VAR(&STRING) TYPE(*CHAR) LEN(100)

03  CHGVAR VAR(&STRING) +
04           VALUE('CALL QSYS2.SEND_MESSAGE(+
05                          ''MINE123'',27,+
06                          ''This is from a CLLE program'',+
07                          ''*LIBL'',''MYMSGF'')')

08  RUNSQL SQL(&STRING) COMMIT(*NC)

09  ENDPGM

Personally I prefer to build SQL statements in a variable before using them in the RUNSQL. If the RUNSQL command errors I can dump the program and check the contents of the variable for anything I have done wrong.

The message is written to the QSYSOPR message queue:

MESSAGE_ID MESSAGE_TEXT                 Length SEVERITY
---------- ---------------------------- ------ --------
MINE123    This is from a CLLE program      27        0
MINE123    Message 2 test N                 16        0
MINE123    This is a test                   14        0

I think I am going to use this more in a RPG program than I would in a CL program. This is my example RPG program:

01  **free
02  dcl-s String char(250) ;
03  dcl-s Length int(5) ;

04  String = 'This is from a RPG program' ;
05  Length = %len(%trimr(String)) ;

06  exec sql CALL QSYS2.SEND_MESSAGE(
07                   'MINE123',:Length,:String,
08                   '*LIBL','MYMSGF') ;

09  *inlr = *on ;

Line 1: Why would I not use totally free format RPG?

Lines 2 and 3: Definitions for the variables for the message text and its length.

Line 4: Moving the message text to the String variable.

Line 5: Determining the length of the message text within the variable.

Lines 6 – 8: Executing the SEND_MESSAGE with the length and string variables.

The returned results show that the message from the RPG program was successfully sent:

MESSAGE_ID MESSAGE_TEXT                 Length SEVERITY
---------- ---------------------------- ------ --------
MINE123    This is from a RPG program       26        0
MINE123    This is from a CLLE program      27        0
MINE123    Message 2 test N                 16        0
MINE123    This is a test                   14        0

Just playing with this make me think I will be using SEND_MESSAGE in my RPG programs in the future for sending warning messages to the QSYSOPR message queue.

 

You can learn more about the SEND_MESSAGE SQL Procedure from the IBM website here.

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.

11 comments:

  1. Trying to see why sending the message to the QSYSOPR message queue would be more helpful than sending an email with an error or warning message...

    ReplyDelete
    Replies
    1. That is a valid point to note.

      I have written processes that send texts, rather than emails, when something has gone awry.

      I am illustrating how you can use this feature. I can see where it would be useful at times to write a message to the QSYSOPR queue as a log that something has happened, whether it be good or bad. For example the invoice generating program has finished generating 152 invoices.

      Delete
    2. If you have message monitoring software in place, then triggering an action based on a message on QSYSOPR makes this potentially a very useful function too.

      Delete
  2. Hi,
    is there a way to send a message not to qsysopr using SQL?
    Best Regards
    Andrea

    ReplyDelete
  3. Excelent! Simon, thanks for sharing 👍

    ReplyDelete
  4. Since it's been a year since you wrote this article, I'm wondering if IBM has created a message that we can use of if we still have to create our own message. I would prefer an IBM message ID.

    ReplyDelete
    Replies
    1. If you use this SQL statement it will return to you a list of IBM messages that you might be able to use:

      SELECT MESSAGE_ID,MESSAGE_TEXT,SEVERITY
      FROM QSYS2.MESSAGE_FILE_DATA
      WHERE MESSAGE_FILE = 'QCPFMSG'
      AND MESSAGE_FILE_LIBRARY = 'QSYS'
      AND MESSAGE_TEXT = '&1'
      AND SEVERITY = 0 ;

      Delete
  5. Hello, Simon.
    Thank you so much for sharing this.
    Is it possible to respond to a message in QSYSOPR in this manner?

    ReplyDelete
  6. Is there way we can send message as needing reply i.e as *INQ or *Notify Instead of informational message?

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.