Retrieving file's keys with SQL, having to use DSPFD

In my last post I wrote about how to list a file's key fields using the file QADBKATR to retrieve the key fields' information from.

I mentioned that there may be times that you are not authorized to use the QADBKATR file. If you are not authorized how else can you get the key fields? And present them as I did in the previous example?

I have to fall back and use the Display File Description command, DSPFD, using the Access Path value in the "Type of information" parameter:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH)

New IBM Power 10 model for AI

On Tuesday, May 7 2024, IBM announced a new model IBM Power 10, S1012, which caught my eye. It is smaller than the other IBM Power rack mounted servers, and it also comes in a cool looking tower version too.

The following statements in the announcement caught my eye:

• "This 1-socket, half-wide Power10 processor-based system delivers up to 3X more performance per core versus Power S812"
   
• "delivers the lowest entry price point in the Power port[f]olio to run core workloads for small and medium-sized organizations"
   
• "provides clients the flexibility to run AI inferencing workloads in remote office and back office (ROBO) locations outside mainstream datacenter facilities"
   

Retrieving file's keys with SQL

I was asked how I could present the following information in an "easy to use" manner for all of the "files" in a library:

• Library name
• File name, or Table, Index, etc. name
• Type of file
• When it was last used
• File's key fields

The easiest way to get this information is using SQL. The first three pieces of information can be retrieved from the SYSFILES View. The last used information can come from the OBJECT_STATISTICS Table function. Getting the file's key fields is not in any of the usual SQL Views about fields/columns or files/tables.

There is a file that contains the keys for all files: QADBKATR. This is a logical file built over the file QADBKFLD. On all the IBM i partitions I use for testing this posts I found that I am not authorized to the physical file on any, and I am authorized to the logical file on all but one. Therefore, my examples will use QADBKATR.

Spring 2024 TRs announced

Today, at a later time than previous announcements, a new set of Technology Refreshes were announced by IBM for the currently supported releases of IBM i. These are IBM i TR4 and IBM i 7.4 TR10.

Doing a quick count of what is new and enhanced I find the following:

• RPG
  • 2 enhancements
• Db2 for i (SQL)
  • 17 additions
  • 9 enhancements

You can read all about them on these pages on IBM's websites:

Converting fetched columns into an array

Coming up with a meaningful title for this post proved harder than normal. I was asked a question about how to fetch data, using a SQL Cursor, and convert some of the fetched columns into an array. The questioner had tried various approaches, but was always receiving an error in the SQL pre-compile step of his program.

The file in question contained 35 fields he was interested in. The record would be for an account, by year and month, 31 balance fields one for every day of the month (yes, I know not all months have 31 days), and finally a total monthly change balance. The questioner wanted to put all of the 31 day fields into one array.

Let me start with my version of his file, that I called TESTFILE:

More about getting information about SQL Views

Several years ago I wrote a post about the Db2 for i (SQL) Views about Views:

• SYSVIEWS:  Information about Views themselves
• SYSVIEWDEP:  Information about Views' dependencies

One of the things I talk about during presentations I give to Local User Groups, LUG, is the use of SQL Views and how they make my programming simpler. Therefore, I wanted to revisit these two Views and explain how they help me learn more about the Views in the partitions I use.

Before I can show what kinds of information I can get from SYSVIEWS and SYSVIEWSDEP I am going to need some Views to play with.

I am going to join a DDS file, TESTFILE, and a DDL table, TESTTABLE to create my first View. While it is not essential to know what the file and table look like I am just going to include their source code below.

First TESTFILE:

Generate spreadsheet with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

CRTSRCPF FILE(QGPL/STDOUT) RCDLEN(266) + TEXT('Standard output file')

End to free format directive

What I call totally free RPG and what IBM calls modern RPG has been around for nine years. This type of RPG always needs the **FREE directive starting in the first position of the source line. And what follows is the free form RPG, where the source line can start in any position.

...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 01 **free 02 dcl-s Statement1 varchar(100) inz('Entered in free format RPG') ;

Marc Raynor informed me of another directive that "ends" the free format RPG, **END-FREE. After that directive all valid RPG must be entered in the sixth position for fixed format code, and later for the not completely free format code.

Having been told this I had to try it:

Using Select Into with Execute Immediate

The idea for this post came from a question I was asked about using the Execute Immediate SQL statement. The questioner asked why he was unable to execute a Select Into statement using Execute Immediate. His RPG source code compiled, but when called the program would error with a SQL code of -312, or message id SQL0312:

Variable RESULT not defined or not usable for reason code 8. 8 -- The host variable is specified in dynamic SQL. Host variables are not valid in dynamic SQL. Parameter markers must be used instead.

I found a solution for him. I could resist playing with what I found and created a far more complicated scenario that I am sharing with you in this post.

Send email with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

Send email with SQL

We have been able to send email from our IBM i partitions for several releases, using IBM commands and various third party tools. I use the Send SMTP Email command, SNDSMTPEMM, that comes within the IBM i operating system. Within the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, comes another way, with SQL.

A new scalar function was added to the SYSTOOLS library called SEND_EMAIL. It has four parameters:

1. TO_EMAIL:  Recipients email address. Mandatory
2. SUBJECT:  Email's subject line. Mandatory
3. BODY:  Email's body. Mandatory
4. ATTACHMENT: IFS path name of the file to be sent as an attachment. Optional, if omitted no attachment is sent

For example:

Using three part name when the user profile or passwords are different

This post is to take care of an oversight that was brought to my attention. I have written several posts about using the SQL three part name, all of the examples assumed that the user name and password were the same on all of the partitions. What happens if they are not? This post will explain what to do.

For these examples I am using three partitions:

1. DEV740: This partition is running IBM i 7.4, and is the "local" partition, the one I am performing all the calls from
2. DEV730: Runs IBM i 7.3. When the connection was configured on DEV740 to DEV730 the serial number was used, therefore, DEV730 is known as G102A08R.
3. DEV720: This is the oldest partition with IBM i 7.2. My using it proves that the three part name has been around for longer than just the last few releases

I am not going to do anything too fancy for these examples. I have a file, called WHERE_AM_I, on all of these partitions. It has just one field, FLD001 that contains a unique message in each file. All I am going to do is to access these files, and show the contents on DEV740.