In this article I will be taking a look at how DB2 SQL Services can help with using data-queues.
Data-queues are a great way for open source to communicate with traditional objects on the IBM i.
For example, an RPG program may place order details on a data-queue, where a Node.js program will pick up the order details then send the supplier an email, SMS text message etc.
What are Data-queues?
If you are a seasoned developer, I’m sure you know everything about data-queues and have been using them for many years.
For the rest, a data-queue is an object that you can feed information into, and that data can be picked up and processed by another program, or method.
Once a data-queue entry is received, it is removed from the queue (A nightmare for testing!)
Traditionally, if our RPG program had a requirement to send, or receive information, from a data-queue it had to use IBM APIs to achieve this.
We used the Send Data-queue (QSNDDTAQ) API to transmit (Tx) data to a specified data-queue.
And to follow on, we used the Receive Data-queue (QRCVDTAQ) API to receive (Rx) data from the specified data-queue.
Please note that a data-queue can grow to a maximum size of 2 gigabytes.
Going back, many years, the only way to see what a data-queue contained, was to do a dump object and interrogate the hex values in the spool file.
While these API were considerably easier to use than some other APIs that IBM provided, IBM has now made tapping into data-queues even easier. Fantastic!
The DB2 Way
As part of DB2 services, all the traditional methods of using data-queues can now be accomplished by using SQL.
If we want to transmit info to a data-queue, we can use SQL – Great for setting up test data.
If we want to receive info from a data-queue, we can use SQL.
And we can, using SQL, interrogate a data-queue to see what we have without compromising the entries.
In the SQL script below, I’ll show how we can use a keyed data-queue to store order information. A client-server module will then pick up these entries and do its relevant processing.
The way we either create, or delete, a data-queue has not changed. We have CL commands.
I’ll place the two commands within my Run SQL Script, shown below.
As we can see, the ORDERS data-queue is keyed, with the company number being used for this purpose, then any receiving processes can just process their companies’ orders.
I’ll place a couple of entries onto the queue.
Here we can see four entries transmitting information to our data-queue.
We use the SEND_DATA_QUEUE stored procedure to achieve this.
We are passing four parameters.
- The data string, comprising of part number, part description and quantity, delimited by the #
- The name of the data queue
- The data queue library
- Key data. In this example, the company number.
Next, we call a table function DATA_QUEUE_ENTRIES to list the entries in our data queue.
In the MESSAGE_DATA column is the information we transmitted to the data-queue, ready for processing.
For completeness, I’ll also show the SQL statement to receive the entries on our queue. For this we use the RECEIVE_DATA_QUEUE table function.
Remember, by receiving the data queue entries, it will remove them from the queue.
The receive data-queue statement can be seen in the figure below.
You can see, using the KEY_DATA parameter of 1 with the KEY_ORDER specifying EQ (Equal) it will only process company 1 entries, all other company order details will remain on the queue.
Please note, as you can see from all the examples, these data-queue functions reside in QSYS2, so the SQL statements must be qualified.
Hopefully, this article has shown how easy it now is to utilise data-queues within our applications, without the need to struggle with the traditional APIs.
In my next article, I’ll show how easy it is to pick up these data-queue entries, and process them, in a node.js program.
All the SQL scripts I have written for this article can be found on my open-source repository on GitHub, which can be found at https://github.com/AndyYouens/f_Learning
If you have any questions, either on this article, or anything else on the IBM i, use the comments below, or send me a message on twitter @AndyYouens
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience.
IBM Champion 2021