Data Queues using SQL

The latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, included four additions to SQL that allow us to do things with Data Queues.

In this post I will be describing how I would do things with data queues pre-the latest TRs and post. These are:

1. DATA_QUEUE_INFO view
2. CLEAR_DATA_QUEUE procedure
3. SEND_DATA_QUEUE procedures (there is more than one and I will describe the differences below)
4. RECEIVE_DATA_QUEUE table function

But, before I start describing any of the above I am going to need to have a data queue I can use. Here is a CL program I wrote to create my data queue:

01 PGM 02 DLTDTAQ DTAQ(MYLIB/TESTDTAQ) 03 MONMSG MSGID(CPF0000) 04 CRTDTAQ DTAQ(MYLIB/TESTDTAQ) MAXLEN(100) AUTORCL(*YES) 05 ENDPGM

Line 2: I delete any existing data queue with the name and in the library I want to create my new one in. I do this to make sure that the data queue I am using is the one I want, not one left over from another project.

Line 4: My data queue is called TESTDTAQ and in my library. The maximum length of an entry that can be sent to the data queue is 100 characters. The AUTORCL ensures that when the data queue is empty the storage allocated to it is released.

I can use the DATA_QUEUE_INFO SQL view for several types of results. In this first example I want to produce a list of all the data queues on this partition:

SELECT DATA_QUEUE_LIBRARY AS "Lib", DATA_QUEUE_NAME AS "Name", DATA_QUEUE_TYPE AS "Type", SEQUENCE FROM QSYS2.DATA_QUEUE_INFO

I am only using a few columns from the results, if you are interested in all the columns from this view click on the link to the IBM documentation at the bottom of this page.

I have renamed the some column headings of some the columns in the results to ensure that the results will fit on this page.

Lib Name Type SEQUENCE -------- ---------- -------- -------- QPFRDATA PFRDTAQ001 STANDARD FIFO QSERVICE QSRVCTL STANDARD KEYED QSERVICE QSRVMON STANDARD KEYED QSYS QSRSYNCM STANDARD FIFO QUSRIJS QIJSDTAQ STANDARD FIFO QUSRIJS QIJSOQM STANDARD FIFO MYLIB TESTDTAQ STANDARD FIFO

DATA_QUEUE_TYPE, column "Type", shows that all of these data queues is a "standard" data queue, not a DDM data queue.

SEQUENCE shows me the order in which the messages are retrieved from the data queue. I think all the ones I have created and worked with have been FIFO, as my example one, TESTDTAQ, is.

I can also use this view to retrieve the number of messages there are in the data queue with the following statement:

SELECT CURRENT_MESSAGES FROM QSYS2.DATA_QUEUE_INFO WHERE DATA_QUEUE_LIBRARY = 'MYLIB' AND DATA_QUEUE_NAME = 'TESTDTAQ'

As I have just created the data queue is does not contain any messages:

CURRENT_MESSAGES ---------------- 0

Below is an example I wrote using the pre-latest TRs method. I have to use two APIs:

1. QCLRDTAQ
2. QSNDDTAQ

In my opinion the names of the APIs explain their purpose. Let me put them in a program:

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-pr QCLRDTAQ extpgm ; 04 *n char(10) const ; //Data queue name 05 *n char(10) const ; //Library ; 06 end-pr ; 07 dcl-pr QSNDDTAQ extpgm ; 08 *n char(10) const ; //Data queue name 09 *n char(10) const ; //Library 10 *n packed(5) ; //Length of data 12 *n char(100) ; //Data 13 end-pr ; 15 dcl-ds QData qualified ; 16 Counter packed(7) ; 17 Field1 char(6) ; 18 Field2 char(20) ; 19 Field3 char(70) ; 20 end-ds ; 21 dcl-s LengthOfData packed(5) ; 22 QCLRDTAQ('TESTDTAQ':'MYLIB') ; 23 LengthOfData = %len(QData) ; 24 for QData.Counter = 1 to 10 ; 25 QData.Field1 = %char(QData.Counter) ; 26 QData.Field2 = %char(QData.Counter * 10) ; 27 QData.Field3 = %char(QData.Counter * 100) ; 28 QSNDDTAQ('TESTDTAQ':'MYLIB':LengthOfData:QData) ; 29 endfor ;

Line 1: You know any RPG program I write is in totally free RPG.

Line 2: My favorite control options. I am also being lazy today and not using a Main procedure in these example programs. If this was a "live" program I would use a Main procedure.

Lines 3 – 6: My definition of a procedure to call the QCLRDTAQ API program. Notice how the two parameters have been defined as CONST this will allow me to pass a literal to the API, rather than a variable.

Lines 7 – 13: Procedure definition for the QSNDDTAQ API program. In this definition only the first two parameters have been defined with CONST.

Line 14 - 20: I am going to be loading a data structure and then using that as my message. I have made a very simple data structure definition as this is only a simple example program.

Line 21: I need to define a variable to contain the length of the data structure, therefore, the length of the message I will be sending to the data queue.

Line 22: As this is only an example program I included the API to clear any existing data out of the data queue. I just placed this here to make sure I had no data left over from my previous tests. In a live program I would not include this API.

Line 23: I need to determine the length of the message, data structure, I will be sending to the data queue.

Lines 24 – 29: I am using a FOR group to perform the following code ten times.

Line 28: This is the call to the API to send the message to the data queue. As the first two parameters were defined as CONST I can use the literal for the name of the data queue and the library. The other two parameters were not defined with CONST, therefore, I have to use a variable.

Now when I use the Select statement I gave for the DATA_QUEUE_INFO I can see that the data queue contains ten messages:

SELECT CURRENT_MESSAGES FROM QSYS2.DATA_QUEUE_INFO WHERE DATA_QUEUE_LIBRARY = 'MYLIB' AND DATA_QUEUE_NAME = 'TESTDTAQ' CURRENT_MESSAGES ---------------- 10

Before I use the SEND_DATA_QUEUE I need to explain that it comes in three versions:

1. SEND_DATA_QUEUE the message will be converted to a character string using the job's CCSID
2. SEND_DATA_QUEUE_BINARY the message will be converted to a binary string
3. SEND_DATA_QUEUE_UTF8 the message will be converted to a UTF-8 string

In this example I am using the first one of the three as I just want to keep this simple.

Let me replace the APIs with the SQL procedures, and change any code to make them perform that way I want them too.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-ds Snd qualified ; 04 Counter packed(7) ; 05 Field1 char(6) ; 06 Field2 char(20) ; 07 Field3 char(70) ; 08 QData char(100) pos(1) ; 09 end-ds ; 10 exec sql CALL QSYS2.CLEAR_DATA_QUEUE('TESTDTAQ','MYLIB') ; 11 for Snd.Counter = 1 to 10 ; 12 Snd.Field1 = %char(Snd.Counter) ; 13 Snd.Field2 = %char(Snd.Counter * 10) ; 14 Snd.Field3 = %char(Snd.Counter * 100) ; 15 exec sql CALL QSYS2.SEND_DATA_QUEUE(:Snd.QData, 'TESTDTAQ', 'MYLIB') ; 16 endfor ;

First, and most obvious difference, is there is no longer the procedure definitions for the APIs.

I have renamed the data structure that contains the data that is written to the data structure. The SEND_DATA_QUEUE does not allow me to use a data structure, as the equivalent API did. Therefore, I have made QData a subfield of the data structure. It is defined as 100 characters and starts at the first position, therefore, this data structure subfield overlays all the other subfields.

Alter running this program I can use the DATA_QUEUE_INFO to confirm that the data queue contains ten messages.

Now to get the messages from the data queue and into a format I can use. Let me start with a program that uses the API.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-pr QRCVDTAQ extpgm ; 04 *n char(10) const ; //Data queue name 05 *n char(10) const ; //Library ; 06 *n packed(5) ; //Length of data 07 *n char(100) ; //Data 08 *n packed(5) const ; //Wait seconds 09 end-pr ; 10 dcl-ds Data qualified ; 11 Counter packed(7) ; 12 Field1 char(6) ; 13 Field2 char(20) ; 14 Field3 char(70) ; 15 end-ds ; 16 dcl-s LengthOfData packed(5) ; 17 dcl-s Counter packed(4) ; 18 LengthOfData = %len(Data) ; 19 for Counter = 1 to 10 ; 20 QRCVDTAQ('TESTDTAQ':'MYLIB':LengthOfData:Data:5) ; 21 endfor ;

Lines 3 – 9: Is the procedure definition for the API to retrieve messages from the data queue. Notice how the first two and the last parameters are defined with CONST.

Lines 10 - 15: This data structure is identical to the one in the program that sent messages to the data queue.

Line 20: The API returns the next message from the data queue into the Data data structure. The "5" that follows is how many second to delay the API for if not data to receive from the data queue.

This is my equivalent using RECEIVE_DATA_QUEUE table function.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-ds Rcved qualified ; 04 Counter packed(7) ; 05 Field1 char(6) ; 06 Field2 char(20) ; 07 Field3 char(70) ; 08 Data char(100) pos(1) ; 09 end-ds ; 10 dcl-s i packed(4) ; 11 for i = 1 to 10 ; 12 exec sql SELECT MESSAGE_DATA INTO :Rcved.Data FROM TABLE(QSYS2.RECEIVE_DATA_QUEUE( DATA_QUEUE => 'TESTDTAQ', DATA_QUEUE_LIBRARY => 'MYLIB', REMOVE => 'YES', WAIT_TIME => 5)) ; 13 endfor ;

There is no need for the procedure definition for the API.

As I had to do with the previous SQL program I had to make the variable RECEIVE_DATA_QUEUE a data structure subfield, line 8.

Lines 12: I am using a SELECT INTO statement to retrieve the message from the data queue and into the data structure's subfield. In the table functions parameters I am using their names as the parameters I want to use are not contiguous. I want to remove the message from the data queue when I retrieve it, and if there is no messages to retrieve then wait five seconds before trying again.

If I use debug I can see that the values in the data structure are what I loaded in the other program:

> EVAL Rcved RCVED.DATA = ....5...10...15...20...25...30...35...40...45...50...55...60 1 ' ?1 10 100 ' 61 ' ' RCVED.COUNTER = 0000001. RCVED.FIELD1 = '1 ' RCVED.FIELD2 = '10 ' RCVED.FIELD3 = ....5...10...15...20...25...30...35...40...45...50...55...60 1 '100 ' 61 ' '

Would I go and rip the APIs out of all existing programs and replace them with their SQL equivalents? Definitely not. But if I had to write a new program using data queues I would use these SQL equivalents.

 

You can learn more about this from the IBM website:

• DATA_QUEUE_INFO view
• CLEAR_DATA_QUEUE procedure
• SEND_DATA_QUEUE procedure
• RECEIVE_DATA_QUEUE table function

 

This article was written for IBM i 7.4 TR1, and should work for IBM i 7.3 TR7 too.