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:

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.

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:

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

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:

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:

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.

 

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