Pages

Friday, November 27, 2020

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')

DATA_QUEUE_ENTRIES

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.
*LIBLLibrary List
*CURLIBCurrent Library

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

DATA_QUEUE_ENTRIES

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.
ALLAll messages in the Data Queue are to be returned and in the order Data Queue is created in (FIFO/LIFO).
FIRSTFirst message to be returned.
KEYMessages are to be returned based on the Key data entered. KEY_DATA and KEY_ORDER should be passed along with this.
LASTLast message to be returned.
REVERSEAll 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. 

DATA_QUEUE_RETRIEVE

  • 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. 

DATA_QUEUE_ENTRIES

  • 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.
EQAll messages with Key equal to Key data are to be returned.
GEAll messages with Key greater than or equal to Key data are to be returned.
GTAll messages with Key greater than Key data are to be returned.
LEAll messages with Key less than or equal to Key data are to be returned.
LTAll messages with Key less than Key data are to be returned.
NEAll 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 NameData TypeDescription
ORDINAL_POSITIONINTEGERPostition of the row in the result data set.
DATA_QUEUE_LIBRARYVARCHAR(10)The library in which the data queue was found.
DATA_QUEUEVARCHAR(10)The name of the data queue.
MESSAGE_DATACLOB(64512)The message received from the data queue as character data.
MESSAGE_DATA_UTF8CLOB(64512) CCSID 1208The message received from the data queue represented as character data in CCSID 1208.
MESSAGE_DATA_BINARYBLOB(64512)The message received from the data queue in binary form. This is the raw form of the data.
KEY_DATAVARCHAR(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_TIMESTAMPTIMESTAMPThe date and time that the message was placed on the data queue.
SENDER_JOB_NAMEVARCHAR(28)The qualified job name of the sender.Contains the null value if no sender information is available for the message.
SENDER_CURRENT_USERVARCHAR(10)The current user profile of the sender.Contains the null value if no sender information is available for the message.

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

No comments:

Post a Comment

Popular Posts