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.
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.
- Open the Edit menu
- Select Insert Generated SQL …
- Click the Add … button
- Click the Schemas … button
- Enter SYSTOOLS in the “Enter schemas names:” box
- Click the Add –> button
- Click the OK button
- Expand SYSTOOLS
- Expand Functions
- Check SPOOLED_FILE_DATA and click the OK button
- 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.