Wednesday, December 4, 2019

Data Queues using SQL

using data queues with 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:

 

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

5 comments:

  1. That is really cool. I wonder what the performance is, compared to the standard APIs. Thanks Simon!

    ReplyDelete
  2. Thanks Simon, really appreciate the details you have included in the article. Cool feature indeed!

    ReplyDelete
  3. What about performance? Is there any comparative data on the execution speed and, most importantly, the processor load?

    ReplyDelete
  4. I just started using SQL with data queues. Good usage for on platform interfacing. Really easy to use. I take you mean the IBM i API's and not Restful API's?.

    ReplyDelete
    Replies
    1. I mean, it is preferable to use from the point of view of processor load - SQL or QCLRDDTAQ / QCLSNDDTAQ

      Delete

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.