Receive Data from Data Queue in SQL - IBM i

Receive 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. These have been very much helpful to Send and/or Receive the Data from Data Queues by calling these APIs. 

Working with Data Queues has been made easier with SQL Procedures/Functions. In this post, we will see how to receive the data from Data Queues using SQL table function 'RECEIVE_DATA_QUEUE'. This would work similar to 'QRCVDTAQ' API.

Let's see how to receive data from a Standard Data Queue with no Key data. 

In the above SQL, 

• Line - 1: Column 'MESSAGE_DATA' would receive the data from Data Queue in Character format. 
• Line - 2: Column 'MESSAGE_DATA_UTF8' would receive the data from Data Queue in UTF8 format.
• Line - 3: Column 'MESSAGE_DATA_BINARY' would receive the data from Data Queue in Binary format. 
• Line - 5: Parameter 'DATA_QUEUE', Name of the Data Queue to receive the data.
• Line - 6: Parameter 'DATA_QUEUE_LIBRARY', Name of the Library Data Queue is present in. *LIBL and *CURLIB can be used instead based on where the Data Queue is present.

We don't need to explicitly mention the parameter names while running the SQL statement and can pass the parameters similar to the usual CALL statement.

Data is received as below and the message is cleared from Data Queue. 

Above query would remove the message from Data Queue as soon as the data is received. There is another parameter where we can specify if the message needs to be removed from the Data Queue or not. 

• Line - 7: Parameter 'REMOVE' can be used to specify if the message needs to be removed. 'NO' would not remove the message from DTAQ and 'YES' would remove the message from DTAQ.

For Keyed Data Queue, Key data and Key order are the mandatory parameters. 

In the above SQL, 

• Line - 2: column 'KEY_DATA' would receive the key data of the message received from Data Queue. 
• Line - 7: Parameter 'KEY_DATA', Key data of the message that would need to be received from Data Queue. 
• Line - 8: Parameter 'KEY_ORDER', Comparison criteria to be specified. Receive Data Queue procedure would compare the Key data passed with the Key data in the message. Below are the possible values for 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.

One other important parameter to consider is 'Wait time'. This is applicable for both Keyed and Non-Keyed data queues.

• Line - 9: Parameter 'WAIT_TIME' is used to specify how much time to wait for if there is no message to receive from Data Queue. Valid values are from -99,999 to 99,999.
• Any negative value would make the procedure to wait forever for the message to receive.
• Zero would continue the processing immediately.
• Any positive value would make the procedure wait for the number of seconds passed in this parameter. 

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