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:

Changing how Run SQL Scripts displays null

I have now had a little bit of time to play with the new version of ACS, 1.1.8.6, and I have found a couple of things I thought were worth sharing with you. These are how null is displayed in the results and the connection time out.

The default null value has always been a hyphen ( - ), but as character fields/columns could contain just a hyphen how do I tell the difference between column that has just a hyphen and null in my results?

ACS 1.1.8.6 is replaced

 

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

 

New SQL table function lists all dependent objects

This is one of my favorites in the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9. The RELATED_OBJECTS table function lists the objects dependent upon the one given in its parameters.

I am not going to give the source code for all the objects I mention here. I will give you links to other posts where I describe how to create and use these different objects types.

I can get lists of dependent objects on a physical files using various CL commands, but this gives me a complete set of results in one place. And I am all for keeping things simple, KISS.

RELATED_OBJECTS has two mandatory parameters that need to be passed to it:

Formatting numbers in SQL

I am sure I am not the only person when I get a number returned in my SQL results I have to look at it carefully to determine is that number millions, billions, trillions, or even bigger?

SELECT SUPPORTED_VALUE FROM QSYS2.SQL_SIZING WHERE SIZING_ID = 18303 SUPPORTED_VALUE --------------------- 18446744073709551600

For an explanation of what the SQL View SQL_SIZING shows see here.

Those of us who have been programming for any time in IBM i have formatted numbers in DDS files using edit codes and edit words in the source: