Wednesday, December 9, 2020

SQL to read Data Queue

One of biggest complaints I have always had about using data queues is that there is no easy way to see what is inside them, without removing the data queue entries.

Contained within the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9, is a SQL table function that allows me to view the contents of a data queue without the entries from being removed.

In this post I am not going to repeat descriptions of the other SQL data queue views, procedures, and table functions as all of that is described in detail here.

The new table function is DATA_QUEUE_ENTRIES, and is found in the QSYS2 library. Being a table function it has a number of parameters:

  1. DATA_QUEUE:  Name of the data queue. Mandatory
  2. DATA_QUEUE_LIBRARY:  The library where the data queue is located. *LIBL and *CURLIB are allowed. Optional, default is *LIBL
  3. SELECTION_TYPE:  How are the results to be returned:
    • ALL All entries are returned in the order based on the type of data queue. This is the default value
    • FIRST Only return the first entry. This is not allowed for a keyed data queue
    • LAST Only the last entry is returned. This not allowed for keyed data queues
    • KEY Only valid with keyed data queues, of course. Only entries matching the key criteria are returned. Must be used in conjunction with the KEY_DATA and KEY_ORDER parameters
    • REVERSE If the data queue is FIFO then the results are shown in LIFO order, and vice versa. Not allowed with keyed data queues
  4. KEY_DATA:  For keyed data queues only. Character string of the key that used for retrieving the entries from the data queue
  5. KEY_ORDER:  Comparison criteria used to selected entries using the key in a keyed data queue. Allowed values: EQ GE GT LE LT NE

I confess that I have never knowingly used and have never created a keyed data queue. All the data queues I have created and used have been FIFO types ones, and that is what I am going to use in these examples.

I have my data queue TESTDTAQ in my library MYLIB. I used the SEND_DATA_QUEUE SQL procedure to add 10 entries to my data queue. Using the new DATA_QUEUE_ENTRIES table function I can show the contents of the data area with the following statement:

SELECT * FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES(
           DATA_QUEUE => 'TESTDTAQ',
           DATA_QUEUE_LIBRARY => 'MYLIB')) ;

SELECT * FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ','*LIBL')) ;

SELECT * FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ')) ;

All three of these statements return the same results. Some of columns in the results I am not interested in for my data queue, and I am going to exclude them from my examples. This statement includes everything I am interested in.

01   SELECT ORDINAL_POSITION AS "POS",
02          MESSAGE_DATA,
03          MESSAGE_ENQUEUE_TIMESTAMP AS "ENTERED TIME"
04    FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ')
06  ORDER BY ORDINAL_POSITION
  1. ORDINAL_POSITIONS:  Calculated, relative position in the results
  2. MESSAGE_DATA:  The data for the entry in character format. This is in a CLOB which is 64,512 characters
  3. MESSAGE_ENQUEUE_TIMESTAMP:  Data and time the entry was added to the data queue

The results look like:

POS  MESSAGE_DATA                             ENTERED TIME
---  ---------------------------------------  -------------------
  1  00000011     10                  100...  2020-11-30 20:43:00
  2  00000022     20                  200...  2020-11-30 20:43:00
  3  00000033     30                  300...  2020-11-30 20:43:00
  4  00000044     40                  400...  2020-11-30 20:43:00
  5  00000055     50                  500...  2020-11-30 20:43:00
  6  00000066     60                  600...  2020-11-30 20:43:00
  7  00000077     70                  700...  2020-11-30 20:43:00
  8  00000088     80                  800...  2020-11-30 20:43:00
  9  00000099     90                  900...  2020-11-30 20:43:00
 10  000001010    100                 1000..  2020-11-30 20:43:00

The dots at the end of the MESSAGE_DATA column denote that this column (64,512 characters) is larger than is shown here.

The ENTERED_TIME is all the same timestamp value as the SEND_DATA_QUEUE SQL procedure was so fast it added all of these entries to the data queue in the same time.

The data structure I used to aggregate the data I output to the data queue looks like:

   // Data queue data structure
01  dcl-ds Snd qualified ;
02    Counter zoned(7) ;
03    Field1 char(6) ;
04    Field2 char(20) ;
05    Field3 char(20) ;
06    QData char(100) pos(1) ;
07  end-ds ;

With that information I can substring the subfields out of the MESSAGE_DATA column:

01  SELECT ORDINAL_POSITION AS "POS",
02         MESSAGE_DATA,
03         CAST(SUBSTR(MESSAGE_DATA,1,7) AS NUMERIC(7,0)) 
                AS "COUNTER",
04         SUBSTR(MESSAGE_DATA,8,6) AS "FIELD1",
05         SUBSTR(MESSAGE_DATA,14,20) AS "FIELD2",
06         SUBSTR(MESSAGE_DATA,34,20) AS "FIELD3"
07   FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ'))
08  ORDER BY ORDINAL_POSITION

Which gives me the results:

POS COUNTER  FIELD1  FIELD2  FIELD3
--- -------  ------  ------  ------
  1       1  1       10      100
  2       2  2       20      200
  3       3  3       30      300
  4       4  4       40      400
  5       5  5       50      500
  6       6  6       60      600
  7       7  7       70      700
  8       8  8       80      800
  9       9  9       90      900
 10      10  10      100     1000

What else can I do with this table function?

I can get a count of how many entries there are in my data queue:

SELECT COUNT(*) AS "No. entries"
  FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ'))


No. entries
-----------
         10

I can get a break down of the number of entries by date. I ran the program that contains the SEND_DATA_QUEUE SQL procedure again to add another 10 entries on a different day.

01  SELECT DATE(MESSAGE_ENQUEUE_TIMESTAMP) AS "Entered date",
02         COUNT(*) AS "No. entries"
03    FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ'))
04   GROUP BY DATE(MESSAGE_ENQUEUE_TIMESTAMP)


Entered date  No. entries
------------  -----------
2020-11-30             10
2020-12-01             10

If I want to see the first entry in the data queue I can use the SELECTION_TYPE parameter:

01  SELECT MESSAGE_DATA,MESSAGE_ENQUEUE_TIMESTAMP
02    FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ',
03                        SELECTION_TYPE => 'FIRST'))


MESSAGE_DATA                             ENTERED TIME
---------------------------------------  -------------------
00000011     10                  100...  2020-11-30 20:43:00

By changing the selection type to “LAST" I can get the most recent addition to the data queue:

01  SELECT MESSAGE_DATA,MESSAGE_ENQUEUE_TIMESTAMP
02    FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES('TESTDTAQ',
03                         SELECTION_TYPE => 'LAST'))


MESSAGE_DATA                             ENTERED TIME
---------------------------------------  -------------------
000001010     100               1000...  2020-12-01 13:38:27

If you work in an environment that uses data queues you are going to find this SQL table function very useful for analyzing the data within them.

 

You can learn more about the DATA_QUEUE_ENTRIES SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

7 comments:

  1. Thank you Simon; I do use a lot Data Queues. Great article with a lot of samples. Happy Holidays. Regards. Carlos

    ReplyDelete
  2. Just recently implemented SQL based data queue processing. Kudos to the IBM i SQL team adding another much more intuitive alternative to the outdated API interfacing.

    ReplyDelete
    Replies
    1. I agree the additions to Db2 for i are making all kinds of information within easy reach.

      Delete
  3. Hello Simon,

    Thanks for wonderful examples. I need some help here. When I run the SQL statement I am getting Message data as "*POINTER". What could be the reason.

    ---
    ORDIN00001 DATA_00001 DATA_QUEUE MESSA00001 MESSA00002
    1 *LIBL TESTDTA *POINTER *POINTER
    2 *LIBL TESTDTA *POINTER *POINTER
    3 *LIBL TESTDTA *POINTER *POINTER
    4 *LIBL TESTDTA *POINTER *POINTER
    5 *LIBL TESTDTA *POINTER *POINTER
    6 *LIBL TESTDTA *POINTER *POINTER
    --

    ReplyDelete
    Replies
    1. Hi,
      Casting would be help to see the values as follow...

      SELECT CAST(MESSA00001 AS CHAR(200) CCSID 37)
      FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES( DATA_QUEUE =>
      'TESTDTAQ', DATA_QUEUE_LIBRARY => 'MESUTLIB'))

      Delete
  4. Two options:

    1. My preferred... stop using STRSQL and learn to use ACS's "Run SQL scripts" it is a far better tool.

    2. If you must use STRSQL CAST the *POINTER to character.

    ReplyDelete
  5. Anyone know if there is a (SQL API) way to see what jobs are sitting on a DTAQ waiting for DATA?

    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.