Reader Feedback and Insights: Spool Files and SQL

Hey, Ted:

Your solution to Mike's spool file questions seems to be the only one I've come across, too. Needing an alternative to STRSQL is another problem I've encountered.

Many of our *SCS reports have underlined headings and totals, etc., which made printing them out the only way to view them properly. When I wrote my WRKUSROUTQ utility, I added a view formatted option that attempts to display the spool file data more correctly. It uses the output of CPYSPLF with control characters, and outputs that to another file, putting in blank lines where needed. Highlights are ignored and underlines are shown by underscores, but only in the spaces in the highlighted field (for example, TOP_TEN_CUSTOMERS__JULY_2003). It's not rocket science, but at least it gives an idea of the report's layout and you get to see all the text.

Needing an alternative to STRSQL is another problem I've come across and solved with a very similar solution to Mike's. I've taken it a bit further and created a permanent *QMQRY object that just consists of substitution variables (&sql1&sql2&sql3, etc). That lets you pass in any SQL statement as the parameters, with just a bit of parsing (embedded spaces on parameter boundaries are a bit tricky). That way I can use all the features of STRQMQRY, like output to *, *PRINT, and *OUTFILE, that RUNSQLSTM lacks. It's particularly handy for submitting ad hoc SQL statements to batch. I've added a few tweaks since the original release that I've found handy, so it adds function to STRQMQRY as well.

As usual, it's an open source utility, with just one each of a *CMD, *PNLGRP, *PGM (CLP), and *QMQRY object to it. Source code and install routine are at www.dbg400.net/excsql.html.

--Martin Rowe
www.dbg400.net

Thanks for sharing your utilities, Martin.

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL