Parsing SQL statements using a table function

Someone brought to my attention a Db2 for i table function that parses the names of the tables and columns used in string passed to it. I have to admit I had not heard of this, therefore, I decided to test out what kinds of information it could return.

The PARSE_STATEMENT appears to have been introduced as part of the IBM i release 7.2 . It takes a string that contains a SQL statement and returns the columns, objects, etc. contained within. The basic syntax is:

SELECT * FROM TABLE(<sql string>)) AS A

For example:

This version of ACS is replaced

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

Conditioning display file size

In the past couple of week I have been asked by several people how to condition whether a display file is shown in *DS3 (24 rows x 80 columns) or *DS4 (27 x 132) format. Rather than writing the same message to each one, I have decided to write this post so I can direct them to it.

These days I doubt if any of us IBM i developers are still using "dumb" workstations, we are all using PCs. In the settings of our 5250 emulator there is a configuration setting so we can set our emulated session to have a screen size of either 24x80 or 27x132. Personally I always configure my sessions to be 27x132.

You can see, and change, this setting depending on the emulator application you use.

More about IBM i version 7.4 and 7.3 TR6

After all of yesterday’s excitement with the announcement of a new release of IBM i 7.4, and a Technology Refresh for 7.3 TR6, I have had a chance to review the documentation and these are the things that caught my eye:

The links below are to the relevant pages in the KnowledgeCenter.

SQL: New

IBM i version 7.4 is announced

Update at 5:00AM (following day)

I went through and picked out what I thought be my favorites here

---

Update at 10:09AM

Today is announcement day as IBM has announced a new release of IBM i, 7.4, and a new Technology Refresh for IBM i 7.3 .

Since my first post this is what I have found:

• Steve Wills' post on his "You and I" blog
• IBM i 7.4 base
• IBM i 7.3 TR6

The new release, 7.4, will only be supported on the PowerSystem servers with the Power8 and Power9 processors.

The availability dates for these are:

Discover size of library using SQL

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

Validating SQL statements

This post is about an API I stumbled across when looking for another. The Syntax Check SQL Statement API, QSQCHKS, will validate any SQL statement passed to it. I can see this being useful when I build a SQL statement in a variable before executing it. If I was to validate the statement it would allow me identify any errors I may have made.

When I show my code, below, you might think it looks complicated, with several strangely names data structures, which is why I have placed it in its own procedure with just one parameter passed to it and one returned. K.I.S.S. .

I have created two procedures to achieve my goal: to return a character variable containing the SQL error message's text. To this end I have created two procedures, the first in RPG, the second in CL. I could have combined them into a single service program, but as this is an example I left them as modules, RPGMOD001 and CLMOD001, to be bound to the calling program at program creation (compile) time.

Using FTP with IFS files

In previous posts I have written about using FTP to copy files from one IBM i partition to another. Almost all of the examples I gave were for transferring of a file, or files, in what I call the "IBM i environment", good old libraries and files.

Earlier this week one of my colleagues came to me and asked if there was a way to use FTP to copy a file in the IFS of one IBM i to the IFS of another partition. I looked back at the posts I had written and realized that I did not have a good example of how to do this. I thought I would share what we played with so he could understand what to do.

Before we get started I need to explain that FTP with IBM i has two naming conventions for the file's path:

Delaying a job for a fraction of a second

Someone asked me if there was a way to pause a job for a fraction of a second. Their colleague had advised them to create a Do-loop that would be performed a certain number of times and that would be "good enough". I was very pleased to learn that the person who asked this question was not satisfied with that answer, and correctly thought that there must be a better way.

I am sure we are all know of the Delay Job command, DLYJOB, but it will only delay a job for whole seconds and not fractions. Fortunately IBM i has two external C functions that can be used to delay a job:

API to check if debug is active

There are some APIs I stumble across and I am left wondering why they were created? The API I will be writing about today, QteRetrieveDebugAttribute, is one of those. This API is used to determine what the debug attribute values are for a job when it is being debugged. I am not going to list which debug attributes they are as it is not relevant to what I am trying to determine. If you are interested in learning what they are click on the link to IBM's documentation at the bottom of this post.

In this example I am just interest to know if the job is being debugged.

I am going to have a procedure within a module call the API, this way I can either bind it into the calling program or included it in a service program, which is my preference. Let me start with the module:

IBM i 7.4 section now in KnowledgeCenter

In January I found signs that a new release of IBM i would happen this year. This week I found another sign when I discovered that the KnowledgeCenter now has a section for IBM i 7.4.

Alas, it is just a "place holder", with no information is within it, but it is another sign of the pending new release.

If you are interested in visiting the new section click here.

Using SQL to check if a batch job is active

A colleague asked me if there was an easy way, using SQL, to tell if a job was active in a subsystem. This subsystem, I am going to call it TESTSBS, should contain two jobs that remain active all day, copying and sending data between various IBM i partitions. During the day-end process the jobs are ended, and then resubmitted after the backups.

Recently there have been problems with these jobs. If one of these jobs errored the system operators would answer the message with "C", which would end the job and the data would not be transferred until someone else noticed. Or the jobs would not end during the day-end process, therefore, after the backups completed two new versions of the job would be submitted resulting in four jobs, two of each.

My colleague had looked into ways of determining which jobs were active in the subsystem. During the day if one of the jobs was missing it could be resubmitted. After the backup finished only if the job was not active would it be submitted. All of the methods she had come up with to determine if the jobs were active were, in her opinion, too complicated. Which is why she came to me.