Pages

Wednesday, December 16, 2020

Working with Data Queues in SQL - IBM i

Data Queues

Data Queues (DTAQ) has been very useful for communication between different Jobs on IBM i.

There have been various APIs to help working with data queues by calling these from CL or RPGLE programs. 

With the use of SQL Procedures and Functions, this has been made easy to work with Data Queues from SQL without having to write a program to call APIs. 

We can now create data queues, send data to data queue and receive data and more. Let's have a look.

  • Create Data Queue.
  • Retrieve Data Queue info.
  • Send data to Data Queue. 
  • Receive data from Data Queue.
  • Retrieve data from Data Queue.
  • Clear Data Queue.

Create Data Queue:

Create Data Queue (CRTDTAQ) is more of an interactive command and best suited to execute from either command line or CL program. 

Sometimes, we get to create Data Queues in run time. QCMDEXC is extremely useful in such cases. QCMDEXC API is useful for running commands from programs and QCMDEXC procedure is useful for running commands from SQL. 

Data Queue can be created in SQL procedure in run time as below. 

Run CL commands from SQL

Or, even from a single SQL statement. 

Run CL commands in SQL

Read more about running CL commands in SQL

Retrieve Data Queue info:

Checking the attributes of Data Queue becomes essential when investigating problems around data queues. Or, If we just need to know all the Data Queues that are present in a specific library and more. 

Whatever is the requirement, 'DATA_QUEUE_INFO' view becomes handy to retrieve the info quickly through a single SQL statement. 


Send data to Data Queue:

Data can be sent to Data Queue with a single SQL statement without having to write code to call the API (QSNDDTAQ). 

And, data can be sent to Data Queue in different formats (Character, UTF8 and Binary). There are different procedures to send the data to data queue in each format. 

'SEND_DATA_QUEUE' procedure can be used to send the data to Data Queue in character format. 

Send data to Data Queue in SQL

Read more about Sending data to Data Queues in different formats in SQL. 

Receive data from Data Queue: 

Receiving data from Data Queue using 'RECEIVE_DATA_QUEUE' table function returns the data in different formats (Character, UTF8 and Binary). 

This gives the choice to the programmer to get the data directly in the required format. Just simply choose the column you need. 

One other advantage of using this function is, Data can be received without having to delete from the Queue. 

Receive data from Data Queue

Read more about Receiving data from Data Queues and how to Receive without removing the data from the Queue.

Retrieve data from Data Queue:

Checking what data is present in Data Queue becomes essential when investigating problems around data queues. Or, We may just need to retrieve the data from Data Queue without removing from the Queue. 

'DATA_QUEUE_ENTRIES' becomes extremely useful in these cases. Like 'RECEIVE_DATA_QUEUE', This function would return the data in different formats. 

Retrieve data from Data Queue

Read more about Retrieve data from Data Queue and different selection criteria we can apply. 

Clear Data Queue:

Clearing data from Data Queue from SQL has been made easy with procedure 'CLEAR_DATA_QUEUE'. 

Clear data from Data Queue

Read more about Clearing data from Data Queue and how we can clear a set of data. 


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

No comments:

Post a Comment

Popular Posts