Guru: Use SQL To Replace Reports

June 14, 2021 Ted Holt

Does anybody write report programs anymore? If so, why? Reports are so twentieth-century. The people we serve want spreadsheets, Web pages, cell-phone apps and other modern conveniences. What we need is an easy way to make our report programs write the same information to modern interfaces without having to perform major surgery on them.

Some 10 years or so ago, I built a scripting language to enable me to reformat spooled files into stream files in CSV format. I called my utility BASS. It was, and continues to be, a successful project.

But BASS is not the only way to turn a spooled file into delimited data. Since then, certain of the database wizards of IBM have created another tool that we can use to read the data in spooled files. It’s a table function named SPOOLED_FILE_DATA, and it resides in library SYSTOOLS.

SPOOLED_FILE_DATA reads a spooled file and produces one row for each line of the report. There are two columns — ORDINAL_POSITION, a sequential number, and SPOOLED_DATA, the data from the report.

We can combine this tool with a tool that writes to the IFS, such as Copy to Import File (CPYTOIMPF), to generate stream files. Let’s see how this might work.

Here’s a columnar report I generated over file QIWS/QCUSTCDT using a simple RPG program named CUSTRPT.

Customer Report                                     Page   1

Name -------  Customer City ----------   Balance     Credit
Henning  G K  938472   Dallas TX 75217     37.00        .00
Jones    B D  839283   Clay   NY 13041    100.00        .00
Vine     S S  392859   Broton VT 05046    439.00        .00
Johnson  J A  938485   Helen  GA 30545  3,987.50      33.50
Tyron    W E  397267   Hector NY 14841       .00        .00
Stevens  K L  389572   Denver CO 80226     58.75       1.50
Alison   J S  846283   Isle   MN 56342     10.00        .00
Doe      J W  475938   Sutter CA 95685    250.00     100.00
Thomas   A N  693829   Casper WY 82609       .00        .00
Williams E D  593029   Dallas TX 75218     25.00        .00
Lee      F L  192837   Hector NY 14841    489.50        .50
Abraham  M T  583990   Isle   MN 56342    500.00        .00

**End of report**

Let’s build a CSV file containing three fields — customer name, customer account number, and the balance due. Let’s put column headings in the first row. We’ll label the columns “Name”, “Account”, and “Balance”.

First, we need some SQL to read the data from the spooled file.

select * from (values (0, 'Name', 'Account', 'Balance'))
                  as h (ordinal_position,Name, Account, Balance)
union all
select d.ordinal_position,
       substr(d.spooled_data, 1, 12)  as Name,
       substr(d.spooled_data, 15, 6)  as Account,
       substr(d.spooled_data, 40, 8)  as Balance
  from table(
        systools.spooled_file_data(job_name => '*',
                           spooled_file_name => 'QSYSPRT')
       ) as d
 where substr(d.spooled_data, 20, 1) >=  '0')
select Name, Account, Balance
  from temp
 order by ordinal_position

How does this work? I have two SELECT clauses — the first to produce the column headings and the second to retrieve the report data. UNION ALL puts the two together.

Of course, we don’t want all the lines from the report, just those that have the detail data. I notice that those lines have a digit in the last position of the customer account number, which is the twentieth position of the line, while the other lines of the report have something else there.

In the second SELECT clause, I use the substring function to retrieve the fields that I am interested in. You can use additional functions (VARCHAR_FORMAT, TO_NUMBER, etc.) to convert the output data to types other than character, but that level of complexity is not necessary for our purposes.

This SQL query retrieves the columns we’re interested in, but it doesn’t put the data into a stream file in CSV format. In fact, it doesn’t put the data anywhere. Let’s put the data into physical file QTEMP/SFD.

declare global temporary table SFD as
(with temp as
   (select * from (values (0, 'Name', 'Account', 'Balance'))
                      as h (ordinal_position,Name, Account, Balance)
    union all
    select d.ordinal_position,
           substr(d.spooled_data, 1, 12)  as Name,
           substr(d.spooled_data, 15, 6)  as Account,
           substr(d.spooled_data, 40, 8)  as Balance
      from table(
            systools.spooled_file_data(job_name => '*',
                                       spooled_file_name => 'QSYSPRT')
           ) as d
     where substr(d.spooled_data, 20, 1) >=  '0')
select Name, Account, Balance
  from temp
 order by ordinal_position
)
with data
with replace;

I stored this SQL statement in member CUSTRPT of source physical file called SQLSRC.

Here’s the job stream to put it all together.

call        CustRpt

RunSqlStm   SrcFile(SQLSRC) SrcMbr(CustRpt) Commit(*NONE) +
            option(*ERRLIST)

CpyToImpF   FromFile(QTEMP/SFD) +
            ToStmf('Customer-list.csv') +
            MbrOpt(*REPLACE) +
            StmFCCSID(*PCASCII) RcdDlm(*CRLF) +
            RmvBlank(*TRAILING)

DltSplF     file(QSYSPRT) job(*) SplNbr(*LAST)

This is the process:

• The CALL produces the spooled file.
• RUNSQLSTM reads the spooled file and puts the data into physical file QTEMP/SFD.
• CPYTOIMPF copies QTEMP/SFD to the IFS.

Very simple.

Now let’s dig a little deeper. We know that SQL only reads data from relational tables, either directly or through views, and a spooled file is not relational. How, then, can SQL read the spooled file? To find the answer to that question, we use the Run SQL Scripts facility of Access Client Solutions (ACS). The following steps retrieve the source code for the table function SPOOLED_FILE_DATA.

1. Open the Edit menu
2. Select Insert Generated SQL …
3. Click the Add … button
4. Click the Schemas … button
5. Enter SYSTOOLS in the “Enter schemas names:” box
6. Click the Add –> button
7. Click the OK button
8. Expand SYSTOOLS
9. Expand Functions
10. Check SPOOLED_FILE_DATA and click the OK button
11. Click the Generate button

This is the generated SQL/PL source code.

--  Generate SQL 
--  Version:                   	V7R4M0 190621 
--  Generated on:              	06/13/21 08:00:00 
--  Relational Database:       	S10604YM 
--  Standards Option:          	Db2 for i 
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM" ;

CREATE FUNCTION SYSTOOLS/SPOOLED_FILE_DATA ( 
	JOB_NAME VARCHAR(28) , 
	SPOOLED_FILE_NAME VARCHAR(10) DEFAULT  'QPJOBLOG'  , 
	SPOOLED_FILE_NUMBER VARCHAR(6) DEFAULT  '*LAST'  ) 
	RETURNS TABLE ( 
	ORDINAL_POSITION INTEGER , 
	SPOOLED_DATA VARCHAR(200) FOR SBCS DATA )   
	LANGUAGE SQL 
	SPECIFIC SYSTOOLS/SPOOL_FILE 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SYSTEM_TIME SENSITIVE NO 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = QSYS2 , 
	DLYPRP = *NO , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	MONITOR = *SYSTEM , 
	SRTSEQ = *HEX   
	BEGIN 
DECLARE ERROR_V BIGINT DEFAULT 0 ; 
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_V = 1 ; 
CALL QSYS2 / QCMDEXC ( 
'QSYS/CRTPF FILE(QTEMP/QIBM_SFD) RCDLEN(200) ' CONCAT 
' MBR(*NONE) MAXMBRS(*NOMAX) SIZE(*NOMAX)' ) ; 
END ; 
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_V = 2 ; 
CALL QSYS2 / QCMDEXC ( 'QSYS/CPYSPLF     FILE(' CONCAT SPOOLED_FILE_NAME CONCAT 
') TOFILE(QTEMP/QIBM_SFD) JOB(' CONCAT JOB_NAME CONCAT 
') MBROPT(*REPLACE) SPLNBR(' CONCAT SPOOLED_FILE_NUMBER CONCAT ') OPNSPLF(*YES)' ) ; 
END ; 
IF ERROR_V > 1 THEN 
SIGNAL SQLSTATE '42704' 
SET MESSAGE_TEXT = 'FAILURE ON CPYSPLF' ; 
END IF ; 
RETURN SELECT RRN ( JL ) , JL . * FROM QTEMP / QIBM_SFD JL ORDER BY RRN ( JL ) ASC ; 
END  ; 
  
COMMENT ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE 
	IS 'DB2 FOR IBM i SUPPLIED OBJECT VERSION 07400010002' ; 
  
GRANT EXECUTE   
ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE 
TO PUBLIC ; 
  
GRANT ALTER , EXECUTE   
ON SPECIFIC FUNCTION SYSTOOLS/SPOOL_FILE 
TO QSYS WITH GRANT OPTION ;

Notice the two calls to QCMDEXC. It turns out that this nifty table function is a wrapper over the Copy Spooled File (CPYSPLF) command. In other words, we’ve been able to use SQL to read spooled files all along and we didn’t know it! We could have done this a long time ago:

CrtPF       qtemp/splfdata   rcdlen(200)
MonMsg      cpf7302 /* already exists */

CpySplF     file(QSYSPRT) ToFile(QTEMP/splfdata) job(*) +
            SplNbr(*LAST) MbrOpt(*REPLACE)

RunSqlStm   SrcFile(SQLSRC) SrcMbr(CustRpt) Commit(*NONE) +
            option(*ERRLIST)

CpyToImpF   FromFile(QTEMP/SFD) +
            ToStmf('Customer-list.csv') +
            MbrOpt(*REPLACE) +
            StmFCCSID(*PCASCII) RcdDlm(*CRLF) +
            RmvBlank(*TRAILING)

DltSplF     file(QSYSPRT) job(*) SplNbr(*LAST)

The SQL has to be tweaked slightly, since we’re reading a file in QTEMP instead of going through the SPOOLED_FILE_DATA table function.

declare global temporary table SFD as
(with temp as
   (select * from (values (0, 'Name', 'Account', 'Balance'))
                      as h (ordinal_position,Name, Account, Balance)
    union all
    select rrn(d) as ordinal_position,
           substr(d.splfdata, 1, 12)  as Name,
           substr(d.splfdata, 15, 6)  as Account,
           substr(d.splfdata, 40, 8)  as Balance
      from qtemp.splfdata as d
     where substr(d.splfdata, 20, 1) >=  '0')
select Name, Account, Balance
  from temp
 order by ordinal_position
)
with data
with replace;

But the result is the same.

"Name","Account","Balance"
"Henning  G K","938472","    37.0"
"Jones    B D","839283","   100.0"
"Vine     S S","392859","   439.0"
"Johnson  J A","938485"," 3,987.5"
"Tyron    W E","397267","      .0"
"Stevens  K L","389572","    58.7"
"Alison   J S","846283","    10.0"
"Doe      J W","475938","   250.0"
"Thomas   A N","693829","      .0"
"Williams E D","593029","    25.0"
"Lee      F L","192837","   489.5"
"Abraham  M T","583990","   500.0"

IBM has done us three favors.

• They have written a tool that we can use to read spooled files.
• They have taught us how to convert job streams into tabular data.
• They have taken away our excuses for not converting reports to more modern interfaces.

We may not have time to convert all of them, but we can convert the most important ones.

RELATED STORIES

SPOOLED_FILE_DATA

BASS: Build A Spreadsheet