Guru: use SQL to replace reports
June 14, 2021
Does anyone write more report programs? If yes, why? The reports are so twentieth century. The people we serve want spreadsheets, web pages, cellphone apps, and other modern conveniences. What we need is a simple way to have our reporting programs write the same information on modern interfaces without having to perform major surgery there.
About ten years ago, I created a scripting language to allow me to reformat spooled files into CSV format stream files. I called my utility BASS. It was and continues to be a successful project.
But BASS isn’t the only way to turn a spooled file into delimited data. Since then, some IBM database wizards have created another tool that we can use to read data from spooled files. It is a named table function SPOOLED_FILE_DATA, and it resides in the SYSTOOLS library.
SPOOLED_FILE_DATA reads a spooled file and produces one row for each row in the report. There are two columns – ORDINAL_POSITION, a sequential number and SPOOLED_DATA, the report data.
We can combine this tool with a tool that writes to IFS, such as Copy to Import File (CPYTOIMPF), to generate feed files. Let’s see how this might work.
Here is a columnar report that I generated on the QIWS / QCUSTCDT file 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 create a CSV file containing three fields: customer name, customer account number and balance due. Let’s put the column headers in the first row. We will label the columns “Name”, “Account” and “Balance”.
First, we need 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 headers and the second to retrieve the data for the report. UNION ALL brings the two together.
Of course, we don’t want all the rows in the report, just the ones that contain the detailed data. I notice these lines have a number in the last position of the customer account number, which is the twentieth position on the line, while the other lines in the report have something else.
In the second SELECT clause, I’m using the substring function to retrieve the fields I’m interested in. You can use additional functions (VARCHAR_FORMAT, TO_NUMBER, etc.) to convert the output data to non-character types, but this level of complexity is not necessary for our purposes.
This SQL query retrieves the columns of interest to us, but it does not put the data in a CSV format feed file. In fact, it doesn’t put the data anywhere. Let’s put the data in the physical QTEMP / SFD file.
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 the CUSTRPT member of the source physical file called SQLSRC.
Here’s the workflow 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)
Here is the process:
- The CALL produces the spooled file.
- RUNSQLSTM reads the spooled file and places the data in the physical QTEMP / SFD file.
- 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 that a spooled file is not relational. How, then, can SQL read the spooled file? To find the answer to this question, we use the Execute SQL Statements function of Access Client Solutions (ACS). The following steps retrieve the source code for the SPOOLED_FILE_DATA table function.
- Open the Edit menu
- Select Insert generated SQL …
- Click the Add … button
- Click on the Diagrams … button
- Enter SYSTOOLS in the “Enter schema names:” box
- Click the Add button ->
- Click on the OK button
- Expand SYSTOOLS
- Develop functions
- Check SPOOLED_FILE_DATA and click OK button
- Click on 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 ;
Note the two calls to QCMDEXC. This nifty table function turns out to be a wrapper on the Copy Spooled File (CPYSPLF) command. In other words, we were able to use SQL to read spooled files all the way through and we didn’t know! 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 needs to be changed slightly, as we are 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 wrote a tool that we can use to read spooled files.
- They taught us how to convert workflows into tabular data.
- They took away our excuses for not converting reports to more modern interfaces.
We may not have time to convert them all, but we can convert the most important ones.
BASS: create a spreadsheet