Retrieve Data from DTAQ using SQL - IBM i

 

Retrieve data from Data Queue: 

Data Queues (DTAQ) has been very useful for communication between different Jobs on IBM i. 

There are multiple APIs to work with Data Queues. 

• Send Data Queue (QSNDDTAQ) - Write data to Data Queue
• Receive Data Queue (QRCVDTAQ) - Read data from Data Queue, This would clear the message from the Data Queue.
• Retrieve Data Queue Message (QMHRDQM) - Retrieve data from Data Queue, Multiple entries can be retrieved at once. This does not clear the retrieved messages from Data Queue.

In this post, we will see how to retrieve data from Data Queue in SQL using 'DATA_QUEUE_ENTRIES' Table function in QSYS2 with some examples. This would return the data similar to API 'QMHRDQM'. 

E.g.: 

Let's consider a Standard Data Queue created using below command with sequence 'FIFO'.

CRTDTAQ DTAQ(REDDYP1/TESTDTAQ) MAXLEN(20) SEQ(*FIFO) TEXT('Test Data Queue - FIFO')

This would return the Data in DTAQ along with the position. 

• Line 1 - We are only retrieving ORDINAL_POSITION (Position of the row in result data set) and MESSAGE_DATA (Message Received from Data Queue) in this example. We will see full list of columns available towards the end. 
• Line 2 - Table Function 'DATA_QUEUE_ENTRIES' is present in QSYS2. We don't always need to prefix it with library name if QSYS2 is present in SQL Path. 
• Line 3 - Parameter 'DATA_QUEUE', Data Queue Name to be passed.
• Line 4 - Parameter 'DATA_QUEUE_LIBRARY, Library Name in which Data Queue is present is to be passed. Below are the other two special values allowed.

*LIBL	Library List
*CURLIB	Current Library

Let's use the same example and retrieve the data in reverse order. 

This has returned the data in reverse order. If you notice, Ordinal Position hasn't changed because this would only represent how the data is retrieved in Result set and not how the data is present in DTAQ.

• Line 5 - Parameter 'SELECTION_TYPE' (Indicates how the messages are retrieved). This is an optional parameter with default value 'ALL'. Passing 'REVERSE' would return the data in reverse order. Below are some of the allowed values.

ALL	All messages in the Data Queue are to be returned and in the order Data Queue is created in (FIFO/LIFO).
FIRST	First message to be returned.
KEY	Messages are to be returned based on the Key data entered. KEY_DATA and KEY_ORDER should be passed along with this.
LAST	Last message to be returned.
REVERSE	All messages in the Data Queue are to be returned and in the reverse order. If Data Queue is created in FIFO, Data is returned in LIFO and vice versa.

Let's look at another example with a Keyed Data Queue. 

E.g.: 

Create a standard Data Queue with KEYED sequence with Key length of '2' using the below command. 

CRTDTAQ DTAQ(REDDYP1/KEYEDDTAQ) MAXLEN(20) SEQ(3KEYED) KEYLEN(2) TEXT('Test Data Queue - Keyed')

Data sent to data queue in the different order than the Keyed sequence. However, Data is retrieved in Keyed Sequence. To Retrieve the data without any Key comparison, Just passing Data Queue Name and Library Name should be sufficient. 

• Line 1 - Retrieving another column 'KEY_DATA' for Keyed Data Queue. 

In the above example, Third Entry has been written with Key Data '04' and Fourth Entry has been written with Key Data '03'. 

When the data is retrieved, Data is retrieved in the order of Key rather than the sequence data is entered into. 

Let's use the Selection Type, Key data and Key Order to retrieve the data based on the Key data passed. 

• Line 5 - Value 'KEY' is passed against the 'SELECTION_TYPE' to indicate the data to be retrieved based on the Key passed.
• Line 6 - Key data to be passed against 'KEY_DATA' parameter. We have passed '02' in this example.
• Line 7 - Comparison criteria to be passed against KEY_ORDER. We have passed 'GE' (Greater than or Equal To) in this example and all messages with Key data greater than or equal to '02' is retrieved. Below are the other values that can be passed to this parameter.

EQ	All messages with Key equal to Key data are to be returned.
GE	All messages with Key greater than or equal to Key data are to be returned.
GT	All messages with Key greater than Key data are to be returned.
LE	All messages with Key less than or equal to Key data are to be returned.
LT	All messages with Key less than Key data are to be returned.
NE	All messages with Key not equal to Key data are to be returned.

We have only retrieved columns ORDINAL_POSITION, MESSAGE_DATA and KEY_DATA in our examples above. Below are the other columns this function would return. 

Column Name	Data Type	Description
ORDINAL_POSITION	INTEGER	Postition of the row in the result data set.
DATA_QUEUE_LIBRARY	VARCHAR(10)	The library in which the data queue was found.
DATA_QUEUE	VARCHAR(10)	The name of the data queue.
MESSAGE_DATA	CLOB(64512)	The message received from the data queue as character data.
MESSAGE_DATA_UTF8	CLOB(64512) CCSID 1208	The message received from the data queue represented as character data in CCSID 1208.
MESSAGE_DATA_BINARY	BLOB(64512)	The message received from the data queue in binary form. This is the raw form of the data.
KEY_DATA	VARCHAR(256)	For a keyed data queue. the key value of the returned message. This is the actual key value, which could be different than the key-data parameter value.Contains the null value if this is not a keyed data queue.
MESSAGE_ENQUEUE_TIMESTAMP	TIMESTAMP	The date and time that the message was placed on the data queue.
SENDER_JOB_NAME	VARCHAR(28)	The qualified job name of the sender.Contains the null value if no sender information is available for the message.
SENDER_CURRENT_USER	VARCHAR(10)	The current user profile of the sender.Contains the null value if no sender information is available for the message.