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:

01 CREATE TABLE MYLIB.TESTTABLE ( 02 MY_COLUMN VARCHAR(20), 03 MY_TIMESTAMP TIMESTAMP 04 ) ; -- Column headings 05 LABEL ON COLUMN TESTTABLE ( 06 MY_COLUMN IS 'My 1st column', 07 MY_TIMESTAMP IS 'My 2nd column' 08 ) ;

Lines 1 – 4: This is the CREATE TABLE statement that builds my Table, which contains two columns.

Lines 5 – 8: This is an important part. If I do have this statement then the column headings in the spool file will be the column names. The LABEL ON adds column headings to the columns. You do need that large gap between the "My 1st                    column" as the first part of the column heading is the first 20 characters of the string, the rest is the second line of the column heading.

In this first example I want to list all the rows in my spool file.

First, I create a source member, in this example I created a source member, TESTQMRY, in my source file, DEVSRC, in my library, MYLIB.

Second, I add the following statement to that source member:

01 SELECT * FROM TESTTABLE ORDER BY 1

Line 1: I want all the rows from TESTTABLE and the results need to be in order of the first column, MY_COLUMN.

Third, I created the QM query using the Create Query Management Query command, CRTQMQRY.

CRTQMQRY QMQRY(MYLIB/TESTQMQRY) SRCFILE(MYLIB/DEVSRC) SRCMBR(TESTQMQRY)

This creates a *QMQRY type object in my library with the name TESTQMQRY.

I can evaluate if my Select statement within the QM query is correct by using the Start Query Management Query, STRQMQRY, with its default parameters.

01 STRQMQRY QMQRY(MYLIB/TESTQMQRY)

I was happy that the results looked ideal on the screen. Now I want to execute the same QM query, and send its output to a spool file:

01 STRQMQRY QMQRY(U4142SH/TESTQMQRY) 02 OUTPUT(*PRINT)

Line 2: This is what is passed to the command for it to generate a spool file.

After executing the above command I use the WRKSPLF command to show what spool files I have. Here I only have one:

Work with All Spooled Files Device or Total Cur Opt File User Queue User Data Sts Pages Page Copy _ QPQXPRTF SIMONH PRT01 RDY 1 1

The spool file name for QM queries is QPQXPRTF. When I display its contents I see:

My 1st My 2nd column column -------------------- -------------------------- ALPHA 2023-09-12-13.15.19.982405 FOXTROT 2023-09-12-13.15.19.982405 LIMA 2023-09-12-13.15.19.982405 MIKE 2023-09-12-13.15.19.982405 SIERRA 2023-09-12-13.15.19.982405 07/20/23 13:24:44 1

On the last record of the spool file I have the date and time on the left, and the page number on the right.

IMHO QPQXPRTF is not a friendly name. I always like my spool files to have the name of the QM query that generated it. To achieve that I created a CL program to override the default spool file's name, and then execute the QM query:

01 PGM 02 OVRPRTF FILE(QPQXPRTF) + 03 MAXRCDS(*NOMAX) + 04 USRDTA('QM query') + 05 SPLFNAME(TESTQMQRY) + 06 OVRSCOPE(*CALLLVL) 07 STRQMQRY QMQRY(TESTQMQRY) OUTPUT(*PRINT) 08 ENDPGM

Lines 2 – 6: The Override spool file command, OVRPRTF.

Line 4: I changed the User data to be something descriptive.

Line 5: I have changed the spool file to be TESTQMQRY.

Line 6: This override is at the call level, therefore, when the program ends the override is removed from the file.

When I call this program and WRKSPLF I see two spool files:

Work with All Spooled Files Device or Total Cur Opt File User Queue User Data Sts Pages Page Copy _ QPQXPRTF SIMON PRT01 RDY 1 1 _ TESTQMQRY SIMON PRT01 QM query RDY 1 1

The second spool file was created by the CL program.

Almost all of the times a user comes to me for a report they don't want a full list of a file. They want to be able to select which rows of data they want. How do I do this?

SQL comes to the rescue again. I create a clone the file the data is in into the library QTEMP. If the data is to come from more than one file I create a View and clone that. The cloned file is called OUTPUT.

I can then use CL's Run SQL command, RUNSQL, to insert data from the original Table or View into the clone.

Then as before I override the spool file's name, and execute a QM query to create a spool file from the contents of the output Table.

I need a new QM query, I will call TESTQMQRY2:

01 SELECT * FROM QTEMP.OUTPUT ORDER BY 1

I create this QM query with the following:

CRTQMQRY QMQRY(MYLIB/TESTQMQRY2) SRCFILE(DEVSRC) SRCMBR(TESTQMQRY2)

Now I need to create my CL program. In this scenario a value is passed to the program, and at the end of the program only the rows match this value are in the spool file.

01 PGM PARM(&PARM_IN) 02 DCL VAR(&PARM_IN) TYPE(*CHAR) LEN(20) 03 DCL VAR(&SQL) TYPE(*CHAR) LEN(200) 04 DLTF FILE(QTEMP/OUTPUT) 05 MONMSG MSGID(CPF0000) 06 CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(*LIBL) OBJTYPE(*FILE) + TOLIB(QTEMP) NEWOBJ(OUTPUT) CST(*NO) TRG(*NO) 07 CHGVAR VAR(&SQL) VALUE('+ 08 INSERT INTO QTEMP.OUTPUT + 09 (SELECT * FROM TESTTABLE + 10 WHERE MY_COLUMN = ''' || &PARM_IN |< ''') ') 11 RUNSQL SQL(&SQL) COMMIT(*NC) 12 OVRPRTF FILE(QPQXPRTF) + MAXRCDS(*NOMAX) + USRDTA('QMQRY sel') + SPLFNAME(TESTQMQRY2) + OVRSCOPE(*CALLLVL) 13 STRQMQRY QMQRY(TESTQMQRY2) OUTPUT(*PRINT) 14 ENDPGM

Line 1: The parameter is passed to this program, which is defined on line 2.

Line 3: This variable will contain the SQL statement for the selection of data. I always build the SQL statement I am going to execute in a variable. When I run the statement in the RUNSQL command if it fails I can look in the program dump to what the statement was. I cannot do that if I build the statement in the RUNSQL command.

Lines 4 and 5: If the output file already exists delete it.

Lines 6: Use the Create Duplicate Object command, CRTDUPOBJ, to create a copy of the Table in QTEMP with the name OUTPUT.

Lines 7 – 11: This is my SQL statement that I will executing to insert the data I want into the output Table.

Line 10: This is the part of the code that adds the passed parameter into the string. If you are unfamiliar with the || and |< shortcuts you should read about them here.

After line 10 the variable &SQL is:

> EVAL &SQL &SQL = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'INSERT INTO QTEMP.OUTPUT (SELECT * FROM TESTTABLE WHERE MY_C' 61 'OLUMN = 'LIMA') ' 121 ' ' 181 ' '

Line 11: I execute this SQL statement in the RUNSQL. I do need to tell it not to commit any of the changes.

Line 12: Printer override.

Line 13: The QM query is executed.

I call the program thus:

CALL TESTCL 'LIMA'

When the program has completed I WRKSPLF, I see my new spool file.

Work with All Spooled Files Device or Total Cur Opt File User Queue User Data Sts Pages Page Copy _ TESTQMQRY2 SIMONH PRT01 QMQRY sel RDY 1 1

I display the contents of the spool file, and I see only the result for "LIMA":

My 1st My 2nd column column -------------------- -------------------------- LIMA 2023-09-12-13.15.19.982405 07/20/23 14:31:00 1

This is a very simple example. If I wanted something more complex I would use the same methods used above. The only difference would be to use a View which would be created to gather and calculate all of the data elements I would want.

If I want to customize the appearance of the spool file I would need to create a QM form. When I use the STRQMRY I say which QM form to use in in the QMFORM parameter:

Start Query Management Query (STRQMQRY) Type choices, press Enter. Query management query . . . . . QMQRY > TESTQMQRY Library . . . . . . . . . . . > MYLIB Output . . . . . . . . . . . . . OUTPUT > *PRINT Query management report form . . QMFORM *SYSDFT <== Library . . . . . . . . . . .

I not going to describe how to build QM forms. What I am going to do is refer you to the Query Management Programming manual. Query Management has not been enhanced since version 6.1, which is why this is the only copy of the manual. You can view it by clicking here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.