Retrieving the source of SQL objects

GENERATE_SQL_OBJECTS is a SQL procedure I have used which I have found very useful, but have not written about. It allows me to recreate the SQL statement that was used to create a SQL object or objects into a source file member. I could change many of SQL objects using the ALTER statement. But, in my opinion, it is easier to change the statement in a source member. I make sure that the create statement is CREATE OR REPLACE, create the changed object and old one is replaced.

There are two things I consider minor inconveniences to using this SQL procedure:

1. I cannot pass the name of the object to the procedure. All of the objects I wish to retrieve the create statement for must be placed in a DDL Table, that procedure then "reads".
2. The source member that is generated contains the create statements for all of the objects listed in the above Table.

I can use this with most SQL object types. In this example I am only using it with a few I have mentioned in previous posts.

Delaying a CL program by a fraction of a second with USLEEP

I have already written about how to delay a RPG program by less than a second by using the USLEEP procedure. Now I need to do the same in a CL program.

As we are in 2021 I hope that all of your CL source members are all the type CLLE. The program I am going to show must be compiled using the Create Bound CL program command, CRTBNDCL, not using the Create CL program command, CRTCLPGM, which is the default for type CLP.

USLEEP is a UNIX type API. This means that it is case sensitive. When I use it in a program I must use usleep, and not USLEEP or Usleep. I am just going to refer to it in the text of this post as USLEEP as it is easier for you to see its name amongst the rest of the text.

Creating an auto-scrolling subfile

The idea for this post came from a question that was asked on Facebook. Someone wanted to create a subfile that would be projected onto a screen at an event. The subfile would scroll to the next screen of results every so many seconds without the need for someone to press a key on the keyboard.

I was surprised by the replies to the question, some said it was not possible, others gave over complicated examples of how they thought it could be achieved. I knew that to do this was simple, and all the information needed to do this exists in various posts on this website.

I decided to write a program to do what was asked before. I decided to make this a little more interesting:

New SQL View gives DSPFD information

I have wanted to have a way to get to the same information via SQL that I can by using the Display File Description command, DSPFD. This absence has often led me to having to use the DSPFD command to create an output file, that I would then use SQL to gather the results I desired.

My wishes were answered in the Fall 2021 Technology Refreshes, IBM i 7.4 TR5 and IBM i 7.3 TR11, with the introduction of a new SQL view: SYSFILES.

Unlike the DSPFD command SYSFILES only contains data about what I would call "data files", what the DSPFD calls "*PF" and "*LF". Source files are included, but the column FILE_TYPE allows me to differentiate between source and data files.

The simplest Select statement to use this view is:

Using Lateral in SQL

In the past I have only given examples of using LATERAL in SQL Select statements to get data from Table function. As such I have only mentioned it in passing. In this post I wanted to go into more detail so you can see that it can be used for more than just getting data from a Table function.

Having searched for the best description I could find to explain what a LATERAL is, I found that IBM does best with the following:

A lateral join is essentially a foreach loop in SQL. A lateral join is represented by the keyword LATERAL with an inner subquery in the FROM clause, as shown in the following simple representation:

SELECT <columns>
  FROM <tableReference>
  LATERAL <innerSubquery>

More Audit Journal table functions added

As part of the last Technology Refresh four SQL table functions were added that allowed me to extract certain journal entry types from the system audit journal. In the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, twelve new Table functions to extract other journal entries types were introduced:

• Command string (CD)
• Create object (CO)
• User profile changes (CP)
• Delete operations (DO)
• Environment variable (EV)
• Generic record (GR)
• Action to system value (SV)

New Navigator for i available

When the new version of ACS was released we were informed that a new version of "Navigator for i" would be forthcoming.

Today I find that this new "Navigator for i" has been released. It is only available via PTF for the latest releases of IBM i:

• IBM i 7.4 SI76981
• IBM i 7.3 SI76982

You do not access the new "Navigator for i" via ACS, it has its own URL. On the partition I am using the URL is:

Debugging constants in RPG

There have been times when I am debugging a multi-thousand line program when I find a line of code that can look something like:

1414.00 dou (X = Const1) ;

I can see what value is in variable X. But my attempts to see what is in Const1 is met with:

EVAL Const1 Identifier does not exist.

New BiF added to SORTA to make sorting of data structure arrays easier

I use data structure arrays in many of my RPG programs. It has always been impossible to sort the data in the data structure array by more than subfield, that is until the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11.

Included within the new TRs is a Built in Function, BiF, for RPG's Sort Array operation code, SORTA, that allows me to sort by more than one data structure array subfield. The syntax for using %FIELDS with a data structure array is:

ACS 1.1.8.8 released

It has been a busy couple of weeks in the IBM i world. We have had the announcement of the IBM Power 10 chips, new E1080 server, and new Technology Refreshes. On Friday all of these were joined by the announcement of an updated version of Access Client Solutions, ACS.