Get information about a job, including current SQL statement

I knew of the GET_JOB_INFO table function, but having played with it I did not see a reason why I would use it, I preferred the ACTIVE_JOB_INFO table function.

It was not until someone pointed out to me that I could see the current SQL statement being executed in a job that my interested with GET_JOB_INFO was piqued, especially as this would allow me to see what those data base server jobs are doing.

I decided to compare what GET_JOB_INFO would show me if I executed the following statement via two ways:

1. Using Operations Navigator's "Run SQL Scripts" client
2. Using the STRSQL command

Today is TR6 day

Today, Friday May 10, 2019, the new Technology Refresh for IBM i 7.3 is available!

When it was announced last month I went and picked what I thought my favorites would be. You can read about them here.

The PTFs for TR6 are:

• SF99727 730 Technology Refresh - level 6

At present I cannot find any mention if this PTF included the Db2 for i, RPG, etc enhancements too.

I will update this post as I find more information.

These are what i thought look to be the most interesting enhancements with TR6:

• The initial 7.4 and 7.3 TR6 annoucement
• My picks for the biggest things in 7.4 and 7.3 TR6

We have to wait until Friday June 21, 2019 for the the new release, IBM i 7.4 .

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:

Time to download ACS

April 30 marked the end of IBM's support for one piece of IBM i software we have all used, Client Access. Its replacement is Access Client Solutions, ACS. It does everything that Client Access did, including 5250 emulation.

Why have I mentioned this?

I often get asked which 5250 emulator people should to attach to free IBM i partitions like RZKH's free IBM i partition PUB400.COM. The problem has been that the best emulators were not free.

ACS can be downloaded for free. And once installed its 5250 emulator can easily be activated to connect to servers like PUB400. Now you can have a quality 5250 emulator that does so much more than those others you have used in the past.

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:

Presenting at Ocean UG

Tomorrow, Tuesday April 16, I will be one of four presenters at the Ocean Users' Group meeting. This is the first time I will be presenting to an IBM i user group, and I am looking forward to it.

If you can attend the event is at the National University in Costa Mesa. Dinner is at 5:30 PM, with the presentations starting at 6:30 PM.

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.