For groups in CL

This comes under the category of: I thought I had already written about this. The DOFOR command in CL allows me to construct a For group in CL, like I can in RPG when I use its FOR operation code. The same information has to be given to both:

RPG === for Count = 1 to 10 by 1 ; CLLE ==== DOFOR VAR(&COUNT) FROM(1) TO(10) BY(1)

1. What I call the count variable
2. Starting value
3. End value
4. Increment value, this is optional as it is in RPG. If it is not given 1 is assumed

Determine if Exit points or programs have been changed

Exit points are a feature of the IBM i operating system. When certain processes are run an Exit point is where an Exit program can be called. After the Exit program has completed the process continues. You can create your own Exit programs, and insert them into these Exit points. Even though Exit points and programs have been around forever many people do not use them, nor do they monitor them. As they can capture system information, their misuse must be considered a security breach.

In this post I am not going to describe how to add or make changes to Exit points and programs. I am going to give a simple example how they can be monitored. Notifying me when they have been changed.

This work includes two new SQL Views that came as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9:

1. EXIT_POINT_INFO:  Lists all of the Exit points and information about them
2. EXIT_PROGRAM_INFO:  List all of the Exit programs and information about them, including the Exit point data

SQL table function to view all open files

One of the many additions to SQL in the latest round of Technology Refreshes, IBM i 7.3 TR3 and 7.3 TR9, is a Table function that displays a job's open files.

I can see the same information using the Display Job command, DSPJOB, option 14. But there are times I might want to access the same information in a manner that I can get to easily in a program.

The Open Files table function, OPEN_FILES, is found in the QSYS2 library. It has only one parameter, the job name. If you want to retrieve the information for the current job you can use "*" rather than give the full job. Retrieving the current job's name requires minimal effort as it is a "built in" global variable, JOB_NAME.

The following SQL statements would return the results for current job:

Stop SQL DROP statement object not found error

SQL's DROP statement is pretty universal. It is used to delete just about anything that can be created in SQL: tables, indexes, views, procedures, etc. As with all delete statements there will be an issue if you try to delete an object that does not exist. For example if I drop a table:

DROP TABLE QTEMP.DOES_NOT_EXIST

I will get an error message. This one is from ACS's "Run SQL Scripts":

SQL State: 42704 Vendor Code: -204 Message: [SQL0204] DOES_NOT_EXIST in QTEMP type *FILE not found.

While this is not a big deal in "Run SQL Scripts" as I can just ignore the message, it can be a significant issue if this situation is encountered in a program.

Fortunately this issue has been overcome in the latest Technology Refreshes released last week, IBM i 7.4 TR3 and 7.3 TR9, with the ability to add the words "IF EXISTS" to the Drop statement. For example if I use the following statement in "Run SQL Scripts":

Getting partition, database names, release and TR in one SQL statement

I am often asked how is it possible to find the IBM i release and Technology Refresh level on a partition. In previous posts I have given ways to display the release, which PTFs are loaded, and the partition name. So I thought it was time I rolled this all into one SQL statement, from which I would get:

1. Partition name
2. Database name
3. Release
4. Technology Refresh number

I can hear a number of you say: "Isn't the partition and database name the same?"

With the vast majority of IBM i and earlier models I have used when the partition was installed the database name was changed to be the same as the partition name. But there are occasions they are not, or it was given a different name. One of the IBM i partitions I use to write these posts has a different database name, and so do two of the four partitions at my work. This post will use RZKH's DEV730, which is the one where the partition and database names are different.

Happy New Year! 2021 is here!

All the celebrations to welcome in the New Year are drawing to a close, and we say farewell to an annus horribilis. I am sure I am not the only person wishing for 2021 to be the year where we can overcome this virus, and return to living more normal lives.

Despite the lock downs, social distancing, and working from home IBM has still brought us two Technology Refreshes for the supported releases of IBM i, 7.4 and 7.3. They have done a great job bringing many new features and functions. You can read about them here on this web site.

This brings me to the five most popular posts of 2020. These are the ones you thought were the most interesting: