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:
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.
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:
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:
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:
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:
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.
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 |
After having a month with no presentations I have three I am involved with this month.
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: