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

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
• QUSER
• COMMON India

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:

Faster way to find who answered a message

Six years ago I wrote a post about using the MESSAGE_QUEUE_INFO SQL View for finding jobs that had errored, and who had answered those messages. What I would like to do today is to give an example of more efficient, faster, way to get the same results using the MESSAGE_QUEUE_INFO Table Function.

On the whole retrieving results from Table Functions tend to be faster than getting the same results from a View. Most Table Functions have parameters that are used to narrow down the results to a smaller set of results, which can then be interrogated with the Where clause. With a View I have all the results there can be, and then the Where clause has to search through all of those to find what I want.

The MESSAGE_QUEUE_INFO Table Function has four parameters. I am going to be using three in these examples:

Copying save file from PC to PUB400.COM

If like me you are a user of PUB400.COM you will probably find this video useful. There are times where I have a save file I copied from another IBM i partition that I want to restore onto PUB400.COM, this video explains how to do it.

Click on the image below for it to open in YouTube.