Wednesday, September 27, 2023

Deleting records from a file without the delete trigger executing

I have a file that has a "delete trigger" on it. I need to delete all of the records in the file without the "delete trigger" inserting data into its output file. How could I do that without removing the trigger from the file?

I am going to show you how.

Before I do I am going to need a file with data. I have created a file, TESTFILE, in my library, MYLIB, that has ten records in it:

Tuesday, September 26, 2023

Creating a spool file from a modern RPG program with no output specs or custom printer file

I am not sure if the title really describes what this post is about. The germ for this post came from an email I received from Elbert Cook. He asked me about emulating output specifications in a modern RPG program. Modern free format RPG does not support output specifications. I am pleased about this. I can get far more functionality and make changes many times faster by creating my own printer file than I would making changes to an output specification. Some have argued to me that printer files are too new for them to use, which is not true as I first used them when this operating system was OS/400 and it was running one of the releases of version 2.

Let me stress that what I describe in this post is NOT a case for creating something like output specifications. Use custom printer files.

There are times where I have created printed output from a program I was testing or developing that was so "down and dirty" I did not want to spend my time creating a printer file for. This post is based upon the methods I have used, and some examples from Elbert.

Thursday, September 21, 2023

Creating a spool file from a SQL Select

I was asked if it possible to create a spool file from the results of a SQL statement. It most certainly is! I have done it several times, and I was surprised I have not written about it here. I know I could do this with SQL embedded in RPG, but I wanted to do this not using RPG and have SQL do most of the work.

I have written about Query Management queries, QM queries, before. They are, basically, a compiled SQL statement. I execute them using the Start Query Management Query command, STRQMQRY. One of the parameters of the command is an option to *PRINT.

Before I show examples of QM queries I need a file of data to use. It will come to not surprise to you that I am going to use one called TESTTABLE. The source code for the Table is:

Wednesday, September 20, 2023

New functions added to assist with Boolean data type

The Boolean data type was introduced as part of IBM i 7.5, and does not work for lower releases.

With the last couple of Technology Releases, TRs, a number of SQL functions have been added to make using the Boolean data type easier. These are:

  • BOOLEAN:  Returns Boolean value for parameter
  • ISTRUE:  Returns "true" if the parameter is Boolean true, and if not returns "false" or null
  • ISFALSE:  Returns "true" if the parameter is Boolean false, and "false" or null if it is not
  • ISNOTTRUE:  Returns "true" if the parameter is not Boolean true, and returns "false" or null if not
  • ISNOTFALSE:  Returns "true" if the parameter is not Boolean false, and if not then "false" or null

Wednesday, September 13, 2023

Creating a pivot table with SQL

Alas, if I wanted to create the equivalent of a pivot table using Db2 for i there is not the equivalent of the PIVOT relational operator that there is in Microsoft SQL Server.

A pivot table is taking a table's data that is in rows and "turning on its side" to become data in columns. This is best demonstrated with an example. I have a file that lists sales by color, month, and year in separator rows. I want to convert that to be one row per column and year. If I am little creative I can do pivot data with Db2 for i.

I have a SQL DDL Table, TESTTABLE that contains the data I want to pivot:

Tuesday, September 12, 2023

Selecting data I just inserted into a Table

This piece of SQL was brought to my attention by a friend who works with Db2 for z/OS (mainframe). He said that at times he uses what I am going to explain to validate that the data he inserted into Table has the values he expected.

He refers to it as "Select final table", which will be the name I am going to use for it. I did find this referred to in the IBM Documentation web site, there is a link to this page at the bottom of this post. The Db2 for z/OS version has more functionality than one for Db2 for i. This has me puzzled, why they are not the same?

With Db2 for i "Select final table" can only be used with a SQL Insert subselect. It returns the rows that were inserted into the table.

The Table, TESTTABLE, I will be using in these examples has two columns:

Thursday, September 7, 2023

AS/400 launch TV news story

It has been a few months since the IBM i's 35th anniversary, I wanted to share this old news story from KTTC TV, Rochester Minnesota, broadcast on the day of the launch of the AS/400, June 21, 1988.

Click on the image below to open the video of the news story.


Wednesday, September 6, 2023

Reading from multiple members in a CL program

It has been a while since I wrote a post about CL. I have been given an opportunity to do so again when I received this question:

If my file has 5 members and I want to read only first record of each member in CL how can we do that ?

Yes, this is entirely possible using CL, with no help from RPG.

Before I start showing CL code I am going to need a file, which you will not be surprised I called TESTFILE, and it is in my library, MYLIB. The DDS code for this file is:

01  A          R TESTFILER
02  A            TEXT          45A

Friday, September 1, 2023

September's presentations

After having a month with no presentations I have three I am involved with this month.

  • Central Texas IBM i User Group
  • COMMON India

Thursday, August 31, 2023

Cancelling a SQL job

Have you ever been in the position of having a SQL statement running in ACS's Run SQL Scripts, RSS, and you realize that the reason it is taking so long is something in your selection criteria is too large. I am sure the same thought has crossed all of our minds: "Wouldn't it be nice if I could just cancel this statement!"

There is a way to do this using the procedure: CANCEL_SQL. The documentation describes this as:

CANCEL_SQL() procedure provides an alternative to end job immediate

This is true I could cancel the RSS job with the End Job command, ENDJOB, with the *IMMED option: