Creating many different dates, days, months, and years in just one SQL statement

The germ for this post came from a program I found that was used in a job I was making changes to. The program made a data structure of information about various dates, you'll see exactly what below, that was passed to various programs. The program was several thousand lines of unnecessarily complicated RPG code. Looking at the results it was passing back to the calling program I knew it could be done a lot simpler using SQL, rather than the complex calculations this program contained.

Never one to turn down a challenge over the weekend I wrote a SQL RPG program that generates the same information in the data structure just using one SQL Select statement. I thought this would be an interesting thing to share due to some of the SQL functions I used, some I have written before others I have not, and you might find useful if you have to do something similar.

When called this program returned a data structure that contained the following:

Using SQL to rank results

When I wrote about using a SQL Sequence to place sequential values in a column I used the example of ranking students based upon on their test results. Looking back this may not have been the best example, as if two students had the same score they would have been given a different sequence number.

Fortunately SQL does have a function I can use to properly rank these students, based on their test scores.

Let me start with the students and their test scores:

LAST_NAME FIRST_NAME MID_INITIAL SCORE --------- ---------- ----------- ----- FUESTER MAX 25.69 ECKERT TRISTAN M 49.86 APUZZO CAROL C 14.89 TORRES JOSE 94.96 PENA JUANITA M 74.09 HONG XIA 18.54 REED STEVE J 65.83 OKEEFE VINCENT 6.02 KING CATHERINA K 18.76 WILEY CHRISTIAN J 60.77 MORALES MARIA A 65.83

Steve Reed and Maria Morales have the same test score, therefore, they need to have the same ranking. Let me show the SQL statement I would use to rank these eleven students by their score.

End of support for IBM i 7.2 is announced

On September 10 IBM announced the end of marketing of IBM i 7.2 on April 30, 2020, and the end of support for this release effective April 30, 2021.

IBM i 7.2 was released in May 2014, and by 2021 it will have had a life of 7 years.

If you have partitions running IBM i 7.2 it is time to determine if your Power server can support the newer releases 7.3 and 7.4, and if it does to start making plans upgrade to one of those newer releases.

You can read the announcement document from IBM here.

Using SQL to determine which subsystems are active or not

When I wrote about how it is possible to check if a subsystem is active or not someone asked me if there is a way to list all the subsystems and whether they are active or not.

Having had some time to "play" I have developed a way of doing this by combining data from the following two SQL table functions:

• OBJECT_STATISTICS
• ACTIVE_JOB_INFO

I can get a list of all subsystem description objects by using the OBJECT_STATISTICS table function:

Variable length arrays in RPG

One of the new additions to RPG in IBM i 7.4, and not to the equivalent TR of 7.3, was the ability to have variable length arrays.

Having read the documentation, and having had a chance to "play", my findings mean I can divide this post three sections:

1. Setting the number of elements in the array
2. Expanding number of elements
3. Compile time array

Before I get started I want to show what the maximum size of an array, this number has not changed since at least IBM I 7.2 . It is not the number of elements in the array that is the limit, it is the total size of the array. The array must not exceed 16,773,104 bytes. For example, this is valid as an array of 16,773,104 elements of one character is valid.

Parms built in function added to CL

With every new release I go through all the "What's new for this release" section of IBM's KnowledgeCenter. For IBM i 7.4 and the CL programming language there is only one addition this release: %PARMS built in function. It appears to be identical to the %PARMS built in function in RPG. It is the way to determine how many parameters have been passed to a program or procedure, and from that stop the program from erroring if too few parameters are passed.

In the past I wrote about coping with to few parameters being passed to a CL program, but the new BiF is a lot neater and easier for someone else to understand.

As I said above %PARMS is available in RPG:

Executing CL commands using RUNSQLSTM

The Run SQL Statements command, RUNSQLSTM, runs all of the SQL statements that are in a particular source member. I use the command a lot as I put all of my SQL statements to create Tables, Views, Indexes, and the statements to Alter them into source members, and I update the member whenever I make a change to the object. I also put miscellaneous groups of SQL statements into source members, that I can then execute whenever I want.

I use RUNSQLSTM so much I have a PDM option defined so I don't have to type in the command and all the parameters I care about.

Opt Command RS RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) ERRLVL(20)

In the past if I ever wanted to run a CL command in one of these member I would use SQL's QCMDEXC procedure.

More about SQL Sequences

Last week I wrote a post introducing what SQL Sequences are. Having had a chance to "play" with them some more I wanted to write about what I have discovered.

Before I go into any examples I need to have some Sequences to "play" with:

CREATE SEQUENCE MYLIB.BY_ONE START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_TEN START WITH 10 INCREMENT BY 10 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_100 START WITH 100 INCREMENT BY 100 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_THREE START WITH 30 INCREMENT BY 3 NO MAXVALUE NO CYCLE ;

Using a SQL Sequence to renumber a column

The title sounds a bit strange, but so is the subject of this post. There is a thing in Db2 for i that I can create called a Sequence. A Sequence uses the rules I give it to return to me a sequential value I can use to update column in a table.

I am sure it will become a bit clearer when I give my example.

I have ten students who have completed taken a course. At the end of the course the students take a test. The results of this test are contained in a SQL table I created, along with their name.

Extracting the job's name from the Job Name

Update

If you are using IBM i 7.5, or greater, you ought considering using JOB_NAME, JOB_NUMBER, JOB_USER scalar functions or the JOB_NAME_DETAILS table function instead.

---

I was asked was there an easy way to extract the name from the Job Name column in the Db2 for i table function ACTIVE_JOB_INFO.

The IBM i Job Name consists of three parts:

1. Number
2. User profile
3. Name given to the job when it was started

For example:

741232/SIMONH/QPADEV0001

The issue when extracting the name from the Job Name is it does not always start in the same position. The number is always six long, but the user profile can be any length from one to ten. This means that the name could start anywhere from the tenth to the nineteenth position in the Job Name.

Using SQL to determine if subsystem is started

The question came in two parts:

1. Is it possible to know whether a subsystem has been started?
2. If it is started how can I determine the number of jobs running in it?

To answer these questions I decided to use a Db2 for i table function I have written about before ACTIVE_JOB_INFO.

One of the parameters that can be used with ACTIVE_JOB_INFO is one that narrows the results to just all active subsystems. If I wanted to know if QINTER subsystem was active I could use the following SQL statement in my favorite SQL client:

Easiest way to retrieve the program's name in a CL program

It has always been a bit of a pain to get the name of the CL current program using the Receive Message command, RCVMSG. Therefore, I was excited when I received an example program showing a much simpler way of getting the same information by using a Machine Interface program.

The program I am going to show needs to be created as a CLLE member, and compiled using the Create Bound CL program , CRTBNDCL. As the call procedure command, CALLPRC, is not permitted in CLP source members.

My CLLE program will be calling the MI program _MATPGMNM, it is important to notice that the program name starts with an underscore ( _ ). I cannot use a CALL command, I must use the CALLPRC command instead.

My first example is the simplest program I could write to call this MI program, and have the current program name and library returned.