Validating CL commands with SQL

There has been an API to validate CL commands for as long as I can remember, but is not simple to use. Fortunately, as part of the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a Db2 for i scalar function was introduced that performs the same function.

CHECK_COMMAND_SYNTAX requires one parameter, the command string to be validated. The string can be up to 32,000 characters, which should cover most command strings I use. It returns a Boolean value:

• True = the command string is valid
• False = the command string is invalid

Here are a couple of examples I made and ran in ACS's Run SQL Scripts, RSS:

Convert number to hexadecimal with SQL

In an earlier post I wrote about how to convert a character string to hexadecimal, and back to character again. The obvious question followed:

What if wanted to convert a number to hexadecimal with SQL?

One of the issues is that numbers come in different forms in modern RPG and in SQL. Here are the common types of numbers I encounter:

Type	RPG	SQL
Packed	PACKED	DECIMAL, DEC
Signed	ZONED	NUMERIC, NUM
Integer	INT	INTEGER, INT, SMALLINT, BIGINT

In this post I am going to give examples of how to convert each of these RPG number types to hexadecimal, and then back again.

This is my program:

Program to alert when elapsed CPU percentage becomes too high

When I published my article about Program to alert when jobs' CPU percentage becomes too high someone messaged me asking could I think of something that would do the same when the total CPU exceeded a certain percentage. The answer is, of course, "Yes".

Before I create a program we need to consider the whether the partition that the program will be run on is "capped" or "uncapped". What does that mean?

When a partition is capped it cannot use more than its allocated processing units, in other words it can never exceed 100% CPU usage.

While an uncapped partition has its base entitlement of the processing units, but can borrow unused processing power from the other partitions if available. This means that its CPU usage can exceed 100% of the partition's resources.

This means that 90% CPU utilization could be more concerning on a capped than an uncapped. Keep this in mind when you look at my example program.

I always use ACS's Run SQL Scripts, RSS, to develop the SQL statements I will be including in a RPG program. I can get the elapsed CPU percentage from the SYSTEM_STATUS SQL table function:

Using SQL to retrieve information from the internet

There are many different web sites that offer information that would be useful to consume in IBM i. I have seen several examples of doing this, and I thought I would show my example, which I think, shows how simple it can be.

In my example I want to retrieve the share prices for the three biggest technology companies in the city I live in:

• Dell, stock symbol: DELL
• IBM, symbol: IBM
• Tesla, symbol: TSLA

Program to alert when jobs' CPU percentage becomes too high

I was asked if there was a way to alert if a job exceeded a certain threshold of CPU percentage. The IBM i operating system collects data through Collection Services. Navigator for i and Performance Data Investigator, PDI, can be used to analyze CPU heavy jobs, but this is all historical data there is no real time alerting.

There are various tools from ISV that can do this. For this website I always write about solutions using just what comes in the IBM i operating system, with no third party software.

This problem allows me to use one of my favorite Db2 for i table function, ACTIVE_JOB_INFO. This table function allows me to retrieve information about all of the active jobs on my partition.

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