Clear Data Queue from SQL - IBM i

Clear 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. In the last post we have seen How to Retrieve data from Data Queue using SQL. 

In this post, we will see how to clear data from Data Queue in SQL using 'CLEAR_DATA_QUEUE' Procedure in QSYS2 with some examples. This would work similar to API 'QCLRDTAQ'. 

Let's look at an example of clearing all the entries from a Standard Data Queue. 

E.g.: 

In the above statement, We are passing two parameters.

• 1st Parameter: Data Queue - Name of the Data Queue that needs to be cleared. 
• 2nd Parameter: Data Queue Library - Name of the Library Data Queue is present in. This parameter accepts special variables like '*LIBL' or '*CURLIB'. 

Let's look at another example of Keyed Data Queue and clearing only set of records based on the Key. 

E.g.: 

Before we actually clear the Data Queue, Below are the messages present in Data Queue. 

Now, Let's clear the messages in the Data Queue with Key Data greater than '02'. This should clear the messages with Key data '03' and '04'. 

In the above statement, We are passing two additional parameters to control what data we clear based on the Key data. These parameters can only be specified for Keyed Data Queue.

• 3rd Parameter: Key Data - String containing the Key value. This should be of same length defined while creating Data Queue.
• 4th Parameter: Key Order - Comparison criteria to determine which messages to be cleared from Data Queue. 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.

This has cleared the messages from Data Queue with Key data greater than '02'. 

This procedure can be called from Interactive SQL, Run SQL scripts, Other SQL Procedures or Embedded SQL in RPG programs. 

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