Wednesday, June 16, 2021

Getting results from dynamically built SQL statements

get results from dynamic sql

I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.

This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.

I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.

All of the examples have three steps:

Tuesday, June 15, 2021

Getting to message queue data faster

getting data from message queue using sql table function faster than view

Prior to the latest round of Technology Refreshes for 7.4 and 7.3 if I wanted to get the data from a particular message queue I would need to use the MESSAGE_QUEUE_INFO SQL View. Being a View it contains all the data from all of the message queues, not just the one I would be interested. If I wanted to retrieve the messages from one message queue it would take a while for the results to be found and returned.

There is now a MESSAGE_QUEUE_INFO Table function. Table functions are faster than Views as they are sort of like an API, I pass the message queue name to the Table function and only that message queue is searched for the information I want.

The results returned by the View and Table function are almost identical. The only difference is that the View returns columns for the name of the message queue and the library it resides. As I pass that information to the Table function it is irrelevant to its results.

MESSAGE_QUEUE_INFO Table function has four parameters:

Thursday, June 10, 2021

Happy 8th birthday!

Wow! Another year has passed and RPGPGM.COM celebrates its eighth birthday today. I know I act surprised every year, I do so for two reasons:

  1. I cannot believe that another year has passed!
  2. There is more interesting IBM i stuff I have not written about yet

Despite lockdowns and COVID I have an exciting year:

Wednesday, June 9, 2021

Send messages to the QSYSOPR message queue with SQL

send_message to qsysopr msgq

One of the many things that caught my eye in the latest round of Technology Refreshes for 7.4 and 7.3 is a way to send messages to the System Operator message queue, QSYSOPR, using a SQL procedure, SEND_MESSAGE.

Before I used the Send Message command, SNDMSG, to do this:

SNDMSG MSG('Message from SNDMSG command') TOUSR(*SYSOPR)


                        Additional Message Information

From . . . . . . :  SIMON         Severity . . . . . :  80
Message type . . :  Information
Date sent  . . . :  DD/DD/DD      Time sent  . . . . :  TT:TT:TT

Message . . . . :   Message from SNDMSG command

This new SQL procedure, SEND_MESSAGE, must use a predefined message that must have one substitution parameter that is defined as *CHAR *VARY 2.

My first thought was to use the CPF9898 message, but that has a severity of 40, and it is fixed length of 512.

I decided to create my own message I could use. To avoid any confusion with any existing messages in any messages files I created my own message file, and added my message to it:

Wednesday, June 2, 2021

Creating PDF using SQL

generate pdf file from spool file using new sql

Several years ago I wrote about how to convert spool files to PDF in the IFS. It still remains one of the most popular posts in this blog. With the latest round of Technology Refreshes for 7.4 and 7.3 there is now a way I can generate PDF from spool files using SQL.

I have several spool files I can see using the Work With Spool Files command, WRKSPLF:

                         Work with All Spooled Files


Opt  File        User        Queue       User Data   Sts
 _   QPRTSPLQ    SIMON       MYOUTQ                  RDY
 _   QSYSPRT     SIMON       MYOUTQ      Testing     RDY
 _   QPQUPRFIL   SIMON       MYOUTQ                  RDY
 _   QSYSPRT     SIMON       MYOUTQ                  RDY

I can get the same information using SQL too using the View OUTPUT_QUEUE_ENTRIES_BASIC, which gives me a subset of the columns that are available in OUTPUT_QUEUE_ENTRIES, which makes it faster to return the results:

Wednesday, May 26, 2021

New RPG built in function to split apart a string

split bif to break apart a string into array elements

Two years ago IBM introduced a SQL function, SPLIT, that would break apart a string into parts. In the latest Technology Refreshes for IBM i 7.4 and 7.3 introduces into RPG a Built in Function, BiF, that does a similar thing.

The Split BiF, %SPLIT, breaks apart data from a string into a temporary array.

Let me started with some examples:

01  **free
02  dcl-s String char(100) 
      inz('RPGPGM is a website that provides stories about IBMi') ;

03  dcl-s wkArray char(10) dim(10) ;

04  wkArray = %split(String) ;

Wednesday, May 19, 2021

Upper and Lower built in functions come to RPG

new rpg %upper and %lower bif

When I saw these mentioned in the list of new enhancements made to RPG in IBM i 7.2 TR and And 7.3 TR10 the word "Finally" escaped my lips. Now we have built in functions, BiF, that would allow us to easily convert upper case letters to lower case, and vice versa.

For years I have been able to convert the case of characters in CL:

  CHGVAR VAR(&STRING1) VALUE(%UPPER(&STRING1))

  CHGVAR VAR(&STRING2) VALUE(%LOWER(&STRING2))

And in SQL too:

Monday, May 17, 2021

Technology Refresh PTFs now available

ibm i 7.4 and tr3 7.3 tr10 ptfs out now

I am not sure why this did not publish on Friday, which is when all the PTFs became available.

On Friday (May 14, 2021) the PTFs for IBM i 7.4 TR4 and 7.3 TR10 are now available for download from IBM.

The PTFs for these TRs can be found here:

You will need to get and install latest PTFs for Database to get the TR enhancements for Db2 for i/SQL.

And check for any new RPG PTFs. Some of them were released in April, see here for information on those.

For information on what is in these new TRs check out the post I wrote when the announcement was made last month.

I am pleased to say that the guys at RZKH have already loaded the new PTFs, a big Vielen Dank to them. I will writing about all new features and functions them in the following weeks.

Wednesday, May 12, 2021

Using a result of a substring for the length value of another substring

nested substring in sql

I am sure that the person who asked me if this was possible is not the first person to have tried, and was unable to get it to "work".

They had been presented with a "flat" file where the first two characters denoted the length of the key value. What they need to do was to extract the variable length key into another value they could use.

The file was like this:

DATA
----------------------------------
10<-- 10 -->XXXXXXXXXXXXXXXXXXX...
05<-5->XXXXXXXXXXXXXXXXXXXXXXXX...
20<------- 20 ------->XXXXXXXXX...

It is easy to extract the first two characters from DATA using the following SQL Select statement:

01  SELECT SUBSTR(DATA,1,2) AS "Length",
02         DATA
03    FROM TESTFILE ;

Which returns:

Tuesday, May 11, 2021

Seeing which index or logical file is being used by a SQL statement

which lf or sql indexed is used in sql select

I was asked if it was possible to see which DDS file or SQL index was used by a SQL statement?

Fortunately it is easy to see. But before I show how to do it, let me set up my "test data".

I have my physical file, TESTFILE, which has no key:

A          R TESTFILER          
A            FLD001         6P 0
A            FLD002        10A
A            FLD003          L

I built two logical files built over this physical file. TESTFILEL0 has the field FLD001 as its only key:

A          R TESTFILER                 PFILE(TESTFILE)
A          K FLD001

And TESTFILEL1, which has the key field FLD003: