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.