Creating PDF using SQL

Update Novmber 23, 2022: Simplification of the values I can use for the parameters, see here.

---

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:

New RPG built in function to split apart a string

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) ;

Upper and Lower built in functions come to RPG

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:

Technology Refresh PTFs now available

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:

• 7.4 TR4
• 7.3 TR10

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.

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

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:

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

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:

Problem with long SQL object names when using IBM i commands

In my opinion the long SQL names I can give to Tables, Views, and Indexes allows me to give these objects good descriptive names. I also give them short system names, so I can easily use IBM i commands with them. But this can cause me problems.

I have a table, you will not be surprised to learn I have called it TESTTABLE and it resides in my library MYLIB, that I want to make a copy of to the library MYLIB2. I am sure I am not the only person who would use the Create Duplicate Object command, CRTDUPOBJ, to do this:

CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(MYLIB2) NEWOBJ(@CRTDUPOBJ) CST(*NO) TRG(*NO)

I get the following message to confirm that the file was created in MYLIB2.

Object @CRTDUPOBJ in MYLIB2 type *FILE created.

Support for IBM i 7.2 comes to an end

Today is the end of regular support for IBM i version 7.2.

Launched in May 2014, 7.2 has been a supported operating system for seven years. With its coming off support there remains only two releases of IBM i that are still supported, versions 7.3 and 7.4.

If you are still using 7.2 and you want continue support ask your IBM Business Partner if there is a Service Extension agreement. I have seen one mentioned as "TBA", but I have not seen any details. As an incentive to move to a more modern release IBM extended support tends to be more expensive than support was before.

If are using 7.2 and you can upgrade to a new release then you ought to consider doing so sooner rather than later, so that you will remain on IBM support. Being off support is not a good idea as you can no longer expect PTFs if any security updates, etc., are needed to keep your business safe and secure.

Handling variable length variables in CL

More and more of us are using SQL to modernize our databases from DDS files to SQL tables, view, and indexes. When you rework your files into tables you quickly realize the benefit of converting that 50 alphanumeric field in the DDS file to a variable length character, VARCHAR, column in the SQL equivalent. This conversion saves disk space as the data in that column is only stored as the length it is, not the whole 50 characters.

RPG and SQL easily copes with this type of column. Alas, CL does not. If I try to declare file, DCLF, that contains variable length columns the program will not compile without using a special option in DCLF.

Before I start showing CL code I need to start with a SQL table, that contains a VARCHAR column:

Display values of SQL variables in ACS Run SQL Scripts

Db2 for i comes with a host of built-in global variables and special registers that provide us with some of the basic information about the environment of the IBM i we are using.

When I am using ACS's Run SQL Scripts it is useful to be able to quickly check these values. Or if I am doing a presentation I need to be able to show which IBM i partition I am using to help people to understand what I am doing.

If I wanted to see which partition I am using I could use the following SQL statement:

I am sure more of you are familiar using the SYSDUMMY1 table in the library SYSIBM to do this kind of thing.

I am all for shortcuts and making things easier for myself. I need to give credit to Paul Tuohy for showing this particular shortcut.

Rather than using the Select statement I can use the VALUES statement in its place. By using the VALUES I do not have to define a file. If I want to display which database server my Run SQL Scripts session is attached to I could just use the following:

Unlocking objects locked by ACS Run SQL Scripts

It is 2021 and I hope we should all be using IBM's ACS with our IBM i partitions. I have the Run SQL Scripts tool open all day as it makes it so easy to develop SQL code that I will copy and paste into in a program, procedure, etc. or to test any SQL Views or Tables I have built.

In my opinion the Run SQL Scripts tool is so much better than the STRSQL command as I can see my statement and in the case of a Select my results on the same screen.

The problem is that this locks the object(s) I used in the Select statement. If I need to recreate those objects I get a CPF3202 message:

New ACS 1.1.8.7

 

The original contents of this page have become obsolete, go to this page for up-to-date information.