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>