Wednesday, December 30, 2020

List journal inheritance rules with SQL

sql view to view journal inheritance

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:

Tuesday, December 29, 2020

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

view objectts being journaled by a 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:

Wednesday, December 23, 2020

More special options for RPG debugging

rpg debug special fields qrnu

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:

Wednesday, December 16, 2020

Two new control option to help with awkward numbers

new expropts to handle numbers of blank and with thousand separators

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:

Tuesday, December 15, 2020

How to manipulate the value returned from a RPG procedure

new debug control option to all changing of values returned from subprocedure

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.

Wednesday, December 9, 2020

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:

Tuesday, December 8, 2020

Changing how Run SQL Scripts displays null

ac change how null displayed and connection timeout time

I have now had a little bit of time to play with the new version of ACS,, 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?

Monday, December 7, 2020

New version of ACS,


Update April 19, 2021: New version is currently available for download here

IBM has launched a new version of their Access Client Solution tools on December 1. ACS is so much more than 5250 emulation, it also includes:

  • Data transfer to and from IBM i
  • IFS explorer tool
  • Navigator for i
  • Printer output control
  • Run SQL scripts
  • And a lot more!

It is offered for free, and can be downloaded from IBM's website at:

Wednesday, December 2, 2020

New SQL table function lists all dependent objects

related_objects for pf and sql table 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:

Tuesday, December 1, 2020

Formatting numbers in SQL

adding thousand separators in SQL numbers

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?



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: