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:

List journal inheritance rules with SQL

Having brought up journal inheritance in my previous post I thought it would be wise to follow that up with this post. This post describes how to view the journal inheritance rules in your IBM i partition.

Like the JOURNALLED_OBJECTS SQL View this View JOURNAL_INHERIT_RULES was introduced as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9. It gives a list of all the libraries on your partition with the journal inheritance rules. The only library on the IBM i partition I use for writing these posts that has any inheritance rules is the library I wrote about in my previous post, MYLIB2.

Being a SQL View the statement to retrieve results from it is very simple. In its simplest form it is just:

List all of objects that are being journaled by a specific journal

While this post is not going to be one of my longer ones, it is going to demonstrate another useful addition to Db2 for i within the latest round of Technology Refreshes, IBM i 7.2 TR3 and 7.3 TR9.

I often want to know which objects are being journaled by a particular journal. In the past I gave an example of how to do this using the Work Journal Attribute command, WRKJRNA. But this addition makes it so much easier.

The new SQL View JOURNALED_OBJECTS give a list of all objects that are related to the journal. This is not just the files, it also returns names of the journal receivers. This is something that my prior example did not do.

As this is a View I can list all of the objects that are involved with all journals:

More special options for RPG debugging

Last week I wrote about a new way in debug of changing the value returned from a procedure in a RPG program. Having had some time to look around I have found some other of these special debug features. These others have been around for several releases, therefore, if you are not on IBM i 7.4 or 7.3 with the latest Technology Refreshes you might be able to use some or all of them.

These "special names for debugging" are:

• _QRNU_NULL_name:  See or change null indicator for a variable
• _QRNU_TABI_name:  See or change the index of a RPG table
• _QRNU_DSI_name:  See or change index of multiple occurrence data structures
• _QRNU_RETVAL_name:  See change value returned from a procedure
• _QRNU_XMLSAX:  View names of the associated with the events for the XML-SAX operation code

Two new control option to help with awkward numbers

Many of us handle data coming from non IBM i sources, whether it be files sent from other types of systems or from web applications. While RPG has strict rules of what can be done with numbers, other systems do not. In my experience it is not uncommon to receive blank numeric data, or a number that contain thousand separators. We have all come up with our own ways of handling this kind of issues, but it is nice to see that IBM have introduced a couple new expression options to make this easier for us to work with.

Both come as part of the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9. The these additions have been added to the existing Expression Options control option, EXPROPTS:

• EXPROPTS(*ALWBLANKNUM):  Allows RPG to handle a blank in a numeric variable
• EXPROPTS(*USEDECEDIT):  Gives RPG the ability to handle numeric strings containing thousand separators

How to manipulate the value returned from a RPG procedure

Sometimes it can be hard in testing to change values returned from procedures. I don't want to change the code in the procedure. But how can I ensure that the returned value is what I want? In debug I can change the value of a variable, and this will change the change value returned if the variable is returned from the procedure. What can I do if returned value is hard coded? I cannot change that.

return *on ;

Fortunately this is no longer an issue due to new enhancement added to RPG as part of the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9.

There is a new control option value that can be inserted into the DEBUG keyword.

SQL to read Data Queue

One of biggest complaints I have always had about using data queues is that there is no easy way to see what is inside them, without removing the data queue entries.

Contained within the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9, is a SQL table function that allows me to view the contents of a data queue without the entries from being removed.

In this post I am not going to repeat descriptions of the other SQL data queue views, procedures, and table functions as all of that is described in detail here.

The new table function is DATA_QUEUE_ENTRIES, and is found in the QSYS2 library. Being a table function it has a number of parameters: