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:

Wednesday, May 5, 2021

Problem with long SQL object names when using IBM i commands

rename table with new long name

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.