Send messages to the QSYSOPR message queue with SQL

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.