Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel
4 comments
Read more

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke
Post a Comment
Read more

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i
2 comments
Read more