Pages

Tuesday, December 1, 2020

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

CLEAR_DATA_QUEUE

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. 

DATA_QUEUE_ENTRIES

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

CLEAR_DATA_QUEUE

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

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

DATA_QUEUE_ENTRIES

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.

No comments:

Post a Comment

How to Iterate Over a List and Get Indexes with 'enumerate' Function in Python

'enumerate' function There are different ways of accessing the data from a list like using range and then index or accessing the ele...