Calculating the power and square root of a number

There are times I need to calculate the dimensions of objects, and to do that I need to use things like x squared, or the square root of y. It all brings back memories of sitting in mathematics lessons back in high school, a very long time ago.

It is simple in RPG, but I want to do it in SQL so that I can calculate the information I need in my SQL View.

I thought it would be a good reminder to show how to calculate the square root and how to use exponentiation (x^y, x to the power of y) in both RPG and SQL.

 

Determining the number of entries in a list parameter from a command

I was creating a command with what I am going to call a "list parameter", to allow the entry of up to ten libraries. While writing the Command Processing Program, CPP, I encountered something I could not find a good description of how to handle. Therefore, I am writing this post to give you a good example of how I did it.

I am not going to go into too much detail on how to create commands and their parameters as I covered many of the basics in two earlier posts: Creating your own commands, part 1 and Creating your own commands, part 2.

My example command, MYCMD, has just one parameter, which will be the list of up to ten libraries. The source for the command is as follows:

01 CMD PROMPT('List of libraries') 02 PARM KWD(LIBRARY) TYPE(*NAME) MIN(1) MAX(10) + 03 PROMPT('Libraries')

Line 2 and 3: The command parameter LIBRARY has a number of keywords:

• KWD:  The keyword name for the parameter
• TYPE:  It is a name type parameter, which means it is equivalent of ten long character
• MIN:  The minimum number of entries that must be given
• MAX:  The maximum number of allowed entries
• PROMPT:  The description that appears on the screen

New Job columns added to the Record Lock View

In my last post I explained how the parts of the IBM i job name had been added as separate columns to the OBJECT_LOCK_INFO SQL View. I am pleased to say that the same three columns have been added to the RECORD_LOCK_INFO View too:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Before these columns were added, if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause. With the addition of these three columns, I will show much easier this becomes.

If I wanted to check for record locks, not using RECORD_LOCK_INFO, I would use the Display Record Locks command, DSPRCDLCK. For example, if I wanted to check for record locks on TESTFILE, in my library, I would use the following:

01 DSPRCDLCK FILE(TESTFILE)

Which will show me the following:

Using SQL TRANSLATE in place of REGEXP_REPLACE

I encountered something last week that I think may be a "bug" in either RPG or in Db2 for i. I was using the REGEXP_REPLACE scalar function, and I received an error message that made no sense. I ran out of ideas of how to overcome this error using REGEXP_REPLACE, and I went on to use the TRANSLATE scalar function. Before I tell you what the error is I need to explain my situation.

I was working upon a partition which was running IBM i 7.5 with the latest PTFs for Technology Refresh 7, TR7, and the latest PTFs for Database and RPG:

• Database (SQL) = SF99950 Level 11
• RPG = 5770WDS SJ08064

The partition's CCSID is:

01 SELECT SYSTEM_VALUE_NAME,SYSTEM_VALUE 02 FROM QSYS2.SYSTEM_VALUE_INFO 03 WHERE SYSTEM_VALUE_NAME = 'QCCSID' SYSTEM_VALUE_NAME SYSTEM_VALUE ----------------- ------------ QCCSID 273

Better examples for creating and consuming JSON

Whenever I am asked for an example of how to generate a JSON array or how to consume JSON data I have to point people to different posts, rather than have one that covers both. I have written this post to be that, examples of how to create and how to consume JSON.

I will be using Db2 for i to generate JSON, as I find it simpler to use than doing the equivalent in RPG.

I am going to give four examples in this post to show how to generate a simple JSON array, one with labels and the other without. Then consume the generated JSON into a format that can be easily processed by RPG. All of these examples are going to use embedded SQL in RPG programs.

I will be using my PERSON DDL Table for the input for these examples. What I want to do is to create a JSON containing:

RPG BiFs that now convert CCSID

As the computing world has become more "open" we need to process data that comes in different character sets, not just the EBCDIC CCSID in the IBM i partitions we are working on. Modern RPG has been able to handle all of the character sets, with various Built in Functions, BiF, to convert the character set from one to another.

The RPG PTFs that accompanied new Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7, included an improvement to character handling. Some BiFs will now automatically match the character types of the data in the variables used. This post is going to explain what they are, and any errors I found.

The BiFs that do this implicit CCSID conversion are:

Find where a command is used in programs

A friend had been tasked to determine which programs used a particular CL command. When telling me about this she mentioned a CL command I had never used: Print Command Usage, PRTCMDUSG. She had used this command to check which CLP programs used the CL command.

Never having used this command, I wanted to try it for myself. I created a scenario where I wanted to find all the programs in my library that use the CL command DSPOBJD.

I created three programs with the DSPOBJD. The first I called TESTCL1, as a modern CLLE program:

01 DLTF FILE(QTEMP/OUTFILE) 02 MONMSG MSGID(CPF2105) 03 DSPOBJD OBJ(MYLIB/*ALL) + 04 OBJTYPE(*FILE) + 05 OUTPUT(*OUTFILE) + 06 OUTFILE(QTEMP/OUTFILE)

The second program, TESTCL2, I created as an OPM CLP program.

Determine if a number is even or odd

Have you ever had the need to determine if a number is even or odd? I have had to in various scenarios. This has been made a lot easier with the addition of a couple of scalar functions that have been added as part of the latest Technology Refreshes.

In the past I would need to check the remainder of dividing a number by two.

If I was to do this in RPG my program could look like:

01 **free 02 dcl-s Number packed(1 : 0) ; 03 dcl-s Remainder packed(1 : 0) ; 04 Number = 6 ; 05 Remainder = %rem(Number : 2) ; 06 dsply ('1. Remainder = ' + %char(Remainder)) ; 07 Number = 7 ; 08 Remainder = %rem(Number : 2) ; 09 dsply ('2. Remainder = ' + %char(Remainder)) ; 10 *inlr = *on ;

Improvement to RPG compile listing

One thing that has always annoyed me within RPG compiler listings is how it handles long variables names. In the "Additional diagnostics messages" section of the listing it did not list the entire variable name, just the first seven characters followed by an ellipsis ( ... ). This could lead to some confusion if there is more than one variable that have the identical first seven characters.

I know this is an extremely simple piece of code, but it illustrates what happened:

01 **free 02 dcl-s Really_not_this_one char(1) ; 03 Really_long_variable_name = 'X' ; 04 *inlr = *on ;

Line 2: A variable is defined with the name Really_not_this_one.

Line 3: I then use a variable that has not been defined, Really_long_variable_name, that has the same first seven characters as the variable I defined on line 2.

When I compile this it fails with a level 30 error, as the variable on line 3 has not been defined.

New date formats for RPG

Included within the new Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7, comes three new date formats to help us with the 2040 date problem. All of the new data formats include the century in the date:

• *DMYY:  DD/MM/YYYY format
• *MDYY:  MM/DD/YYYY format (isn't this the same as the *USA format?)
• *YYMD:  YYYY/MM/DD format

These, like the other date formats, can be used with following Built in Functions, BiFs, and operation codes:

• %CHAR:  Convert to character BiF
• %DEC:  Convert to decimal number BiF
• %DATE:  Convert character or decimal to date BiF
• MOVE and MOVEL:  Move and move left operation codes
• TEST(D):  Test date operation code

Let me give some examples of using these new data formats:

Create a program to change the size of a file, reuse deleted records, and remove deleted records

This is the final part of the following trilogy:

1. Change the file's size and reuse deleted records
2. Remove the deleted records from the file
3. Create a program to perform both of the above

The earlier posts described how to manually perform the SQL statements needed. In this post I am going to show a program that combines both of the SQL statements, and makes a program that can be run time and again.

I am not going to repeat a lot of what I said in those posts, therefore, I recommend you read them before you start with this one.

I will show this program in three parts, as I think that will make it easier to explain and for you to understand. This is the first part:

Retrieving the printer name from a Query

The question was posed if it possible to retrieve the name of printer that has been entered into a Query for all the Queries in a library?

I already knew how to retrieve the SQL statement from a Query. Alas, the information about the printer is not found that way.

After some searching I found a SQL procedure that will give me the information I need: PRINT_QUERY_DEFINITION. For some reason there is no mention of this in IBM's documentation portal. I found reference to it in the IBM Support portal.

PRINT_QUERY_DEFINITION generates a spool file that lists all the information about the Query, including the choice of output.

My scenario is that I want a list of the Queries in a library and which printers they are defined to use.

Not all Queries use printers. Some will only display, and others will output to an output file. For this example, I created four Queries. The files they are built over, fields selected, selection criteria, column formatting, etc. is irrelevant. All that matters is the output type. This can have three values:

1. = Display the results on the screen
2. = Direct the output to a spool file
3. = Direct the output to an output file

Deleting QAUDJRN's journal receivers if they are in QSYS

A couple of weeks ago I wrote about deleting the System Audit Journal's receivers. The scenario had the journal's receivers not in the QSYS library, but in QGPL. Someone messaged me saying that in their IBM i partition QAUDJRN's receivers are in QSYS. When they ran the SQL procedure to delete old journal receivers, DELETE_OLD_JOURNAL_RECEIVERS it returned no results.

01 CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS( 02 DELETE_OLDER_THAN => CURRENT_TIMESTAMP, 03 JOURNAL_RECEIVER_LIBRARY => 'QSYS', 04 JOURNAL_RECEIVER => 'QAUD%', 05 DELETE_UNSAVED => 'NO', 06 PREVIEW => 'YES')

I checked the last save information for the journal receivers in QSYS. The SQL table function OBJECT_STATISTICS's SAVE_TIMESTAMP column was null. Using Display Object Description, DSPOBJD, the save date field, ODSDAT, was blank. How could I determine how old each of the receivers were?

Deleting the journal receivers of QAUDJRN

QAUJRN is the system's audit journal that captures various pieces of information that you want it to. A friend has a job that uses the Display Journal command, DSPJRN, to retrieve the data from QAUDJRN.

01 DSPJRN JRN(QSYS/QAUDJRN) RCVRNG(*CURCHAIN) + 02 FROMTIME(&FROMTIME) TOTIME(&TOTIME) + 03 ENTTYP(AF) + 04 OUTPUT(*OUTFILE) OUTFILE(SOMELIB/JRN_AF)

One day his job errored, and he reached out to me for help as it returned an error he had never seen before. He sent me the job log, and I found the following within it:

How to view the top 10 jobs using the most CPU

The germ of the idea for this post came from a question I was asked. The question was for screen that would show the top ten jobs consuming the most CPU, which would refresh on a regular basis. In previous posts I have written about the parts needed to achieve the desired result, here I am going to put it all together.

How do I get the jobs that are consuming the most CPU? I can get the elapsed CPU percent and CPU time from one of my favorite Db2 for i Table functions, ACTIVE_JOB_INFO.

The statement I will be using is:

01 SELECT JOB_NAME, 02 ELAPSED_CPU_PERCENTAGE, 03 ELAPSED_CPU_TIME 04 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 05 RESET_STATISTICS => 'NO', 06 DETAILED_INFO => 'NONE')) 07 ORDER BY ELAPSED_CPU_PERCENTAGE DESC,ELAPSED_CPU_TIME DESC 08 LIMIT 10

Another way to check if file exists in IFS

In June I wrote about how to use SQL to check if a file existed in the IFS. I received a communication from Rich Diedrich offering an alternative, using the access C procedure. I have taken his code and developed it into this example.

I created an external procedure that I made into a service program to perform the checking, and then wrote a small RPG program to call it to validate if certain files exist.

I am going to start by showing the source code for the external procedure, which resides in a module I called TESTMOD1.

Moving one person's spool files to a different output queue

I was asked if there was a simple way to check an output queue, and if there is a spool file of a certain name for one user, to move it to another output queue.

Fortunately this is not as complicated as it sounds as I can use a SQL Table function to retrieve a list of those spool files, and a scalar function to move the spool file.

In this example I am going to be the user, my profile is SIMON, and whenever I find a spool file QPQUPRFIL in the output queue MYOUTQ I want to move it to the output queue OUTQ2.

First I need to produce a list of eligible spool files. Here I can use the SPOOLED_FILE_INFO SQL Table function:

Increment a number held in a character field

The title is simplistic for what this post is really about. The question that was asked was:

I have a character field in a DDS physical file that is 20 characters long. There can be various numbers in that field of any length. I want to retrieve the value from the field, increment it by one, and update the field in the file.

If all the "numbers" in the field in the file were 20 characters long it would be easy, but as the "numbers" can be of different lengths it makes the problem more interesting.

First I need a file for this 20 character field. I decided to call it TESTFILE and the DDS code for it is:

01 A UNIQUE 02 A R TESTFILER 03 A KEY 5A 04 A FIELD1 20A 05 A K KEY

Quickest way to determine which day of the week today is

In a program I wrote recently I needed to return which day of the week today is. My first thought was to use SQL, as it would only be one line of code. But is that the fastest way to return today's day name?

I thought about writing my own RPG routine to calculate the day name, but decided to use Google to see if there was a better example. I found an article by Rafael Victoria-Pereira for MC Press from 2015 that gave what I think is an efficient way to do this, you can read his article here.

Using his example I refactored the RPG code up to 2025 standards and created this procedure:

Retrieve procedure name in the ON-EXIT section

Prior to the latest Technology Refreshes, IBM i 7.5 TR5 and 7.4 TR11, it was not possible to get the procedure's name using the %PROC built in function in the ON-EXIT section of any procedure.

IBM explained to me that the ON-EXIT section of a procedure is another procedure, which is why they would not allow the procedure name to be returned, as it would not be what I expected.

In the latest TR IBM has provided us with a solution. There are now three versions of the %PROC BiF: