Finding record locks using SQL

One of the new SQL Views introduced as part of IBM i 7.2 TR3 and 7.1 TR11 was one that allows us to check for record locks. Now I can execute a SQL query and return all of the jobs locking the file I inquired about. No longer do I need to use APIs or the CL command Display Member Record Locks, DSPRCDLCK.

The new View, RECORD_LOCK_INFO, is found in the library QSYS2. It returns one row of every lock in your IBM i. This is why I should be careful how I use this View. I am sure there are many thousands of record locks in any medium to large size applications at any one time, most of them are harmless as the record or row is locked, updated and released. Every once in a while something goes "bump" and a tries to lock a record/row that is already locked by another. An error message occurs and I have to work out what the lock is and how to release it, in a timely manner, before I get more record locks to the same record/row.

Happy New Year

At the start of a new year I am always filled with a feeling of excitement for what the new year will bring us, and a feeling of gratitude for all the good stuff that happened last year.

Last year's most popular posts covered a good variety of subjects:

1. Change date from one format to another using SQL
2. Char built in function added to CL
3. IBM i 7.1 TR10 and 7.2 TR2 announced at Common
4. Trim in CL and its Second Parameter
5. Getting the number of rows affected by SQL statement

We also have the following IBM i anniversaries to look forward to in 2016:

Searching for strings in source files, using RPG

In my previous post, Searching for strings in source files, I included a sample program written in CL. The day it was published I received a message asking me: "Could that all be written in RPGLE instead of CL?"

The answer is: of course it can be. I thought this would be a good excuse to show how I could write a RPGLE program to do this, which would include subjects I have written about before. The RPGLE program will function in the same way the CL one does. The program is passed three parameters:

Searching for strings in source files

I was recently asked how it is possible to search all the source members in QCLSRC to find which program called a particular Cobol program. I am sure most of you reading this will immediately shout "Find string PDM!", and you are right.

I thought that this would be a good excuse to explain the Find String PDM command, FNDSTRPDM, to those who are not familiar with it. And I will then give an example CL program to show of how I could search one or all of the source files in a library, which is really a reminder of things I have written about before: SQL, do loop, subroutine, changing name of spool files.

Is IBM i 7.3 already out?

I was looking for the latest PTFs for IBM i 7.2 TR3 when I found SE6395 for the RPG compiler. What caught my attention was not the fact that this was an APAR for fully free RPG, it was the releases, see below.

Click on image to see larger version

Does this mean that there are already PowerSystems servers running IBM i release 7.3 out there? Perhaps there are a few out there in the user community testing a new release. It begs the question: could this mean that a new release is imminent? We will just have to wait and see what 2016 brings.

You can see the page for this APAR on the IBM web site here.

Almost everything you wanted to know about files and tables

When looking through my previous posts I realized I had not written about one of what I consider to be one of the most useful SQL Views, SYSTABLES. I have come to use this View any time I need to retrieve information about files or tables in my IBM i environment, I say that as it does not include the files in the IFS. It is not a new View, it has been around for many years and releases. I find that by using SYSTABLES I can get information a lot quicker than I can using the Display Object Description command, DSPOBJD. When I join SYSTABLES with the View SYSTABLESTAT I have found that I rarely use DSPBOJD any more.

SYSTABLES is found in the library QSYS2, and it contains a row for each table, physical file, view, alias, and logical file. I am not going to list all of its columns as IBM does a good job of it here. I am going to mainly use the short names in my examples to save space in the code below. The columns I have most useful are:

Output queue entries information via SQL

When I heard what was coming in IBM i 7.2 TR3 and 7.1 TR11 I was excited to learn that two of the new introductions would be a SQL View and SQL Table Function to list spool files in output queues. Retention of spool files has always been a contentious issue in all the IBM i shops I have worked. The task of managing spool files is a thankless task, but without it I am always surprised how much disk space is lost to spool files after a few months.

I always wanted a quick and easy way to identify the following information about spool files:

• How old is the spool file?
• How big is it?
• Who generated it?
• What job generated it?

Trying fully free RPG

Thanks to the guys at RZKH.DE I was able to try the new fully free RPG this weekend. The latest Technology Refreshes to IBM i 7.2, TR3, and 7.1, TR11, finally brought what can only be described a completely free RPG. Even though "all free" RPG that came in IBM i 7.1 TR7, it was still restricted to be used between the eighth and 80th columns. With these TRs it is no limited by any column restrictions, and I can use any position from the first to the last of the source member.

I thought it would be a good idea for me to share my first impressions of what I have found using "fully free" RPG, covering what I think is good and not so good.

Getting Active Jobs data using SQL

With IBM i 7.2 released a whole lot of useful Views and Table functions that allowed us to gather, select, and view data that had otherwise only been available via a command or API. I have written about some of them, and today I am going to do so for another: ACTIVE_JOB_INFO, which basically gives me the save information as the Work With Active Jobs command, WRKACTJOB.

ACTIVE_JOB_INFO, found in library QSYS2, is a Table Function, therefore, it used in a slightly different way to a View. Perhaps the most obvious difference is that the Table Function has parameters, where a View does not.

Happy birthday RPG IV

This year contains a few significant anniversaries for RPG.

Today RPG IV has come of age, celebrating its 21st birthday. The first version of RPG IV came as part of OS400 V3R1, which was released on November 25 1994. Even though the code was still constrained by columns, the new Definition specifications (D-specs) was introduced, and I could now use variable names that were up to ten characters long. It also made it possible to use Date, Time, and Timestamp data types along with operations codes to be able to easily perform math with them. And I no longer had to use indicators for reads, chains, etc.

TR3 and TR11 available today

The latest Technology Refreshes for IBM i, TR3 for 7.2 and TR11 for 7.1, are available today. Both were announced last month, and you can learn what they contain here.

Technology Refreshes are delivered via PTFs from IBM. The PTFs for these TRs are:

Selective prompting of your CL commands

I have a number of CL programs I have written for myself to "speed up" things I do on a regular basis. For example, I work in an environment with multiple PowerSystems servers each with one or more IBM i partitions. If I need to move object from one to another I could type in the series of commands I use one at a time, find the parameters I need to change, enter the value I want into the parameter, and then press Enter. Being someone who likes to KISS (Keep It Simple Simon) I have developed programs were I use selective prompting on the commands to keep it simple.

What is selective prompting? It is when in a CL program a command is displayed prompted, and parameters can be seen and changed. I can have all the parameters displayed and changeable, or only some displayed and only some of those changeable. The other parameters remain hidden and unchangeable. It allows me to only display the parameters I want to, the ones I need to change. The screen shot below shows what I mean: