Reading IBM i SPOOLED Files

We have been copying SPOOLED files to database files and reading them in RPG since the stone age (circa early 1980s), and even back in the 1970s on mainframes. Always the same old story, CPYSPLF (Copy SPOOLED File) to a "flat" database file then read that data into our program. In fact, one of my first assignments was to read a SPOOLED invoice and grab the Customer number and total, and produce a summary to show via the WORKSTN/Display to a Manager.

I thought this was a weird assignment. Why not create the summary when the invoices were generated? It turned out not to be possible because it was a 3rd-party vendor package and, at that time, they did not have source code available.

Today most shops get data when and where they need it and in a variety of formats. Our own SQL iQuery customers constantly create printed reports and also routinely route those reports to other formats, such as PDF, Excel, CSV, or even XML and JSON where needed. This is part of our "write once, output to anything" design. But there is still a strong need to read SPOOLED file content from time to time. We have a client that has nearly 12 years of archived reports produced on a daily basis. They're required by the laws in their country to keep said information for at least 5 years. Previously they were printing these reports and storing them in boxes. Now, the SPOOLED files are their archive.

The Language of Print

SNA Character Stream output is more commonly referred to as SCS. It is what is used for SPOOLED files that are not AFP SPOOLED files; that is, traditional character-based printed output.

The ability to read SCS printed output continues to be important today. There are myriad legacy printed reports that may need to be searched, analyzed, or in some cases used as a source of data. One way to achieve this is to use the aforementioned CPYSPLF CL command.

When you use CPYSPLF to copy a spooled file to a file some information is lost, including:

• Blank lines are omitted.
• Graphics data is lost.
• Bar code data is lost.

In the last 20 years or so Workstation Customization Objects (WSCST) have been used with CPYSPLF to convert existing SPOOLED files to either ASCII Text files, or PDF files stored on the IFS. In recent years IBM made this easy by allowing users to specify WSCST(*PDF) on the CPYSPLF command. While you could always do this another way, this small improvement greatly enhanced the ease-of-use of doing so.

Reading SPOOLED File Content

Back before the days of ILE, IBM introduced a set of APIs that allow developers to open and read the raw content in a SPOOLED file "object". Remember that SPOOLED files are one of the oldest objects on the systems, in fact they're one of the original CPF object types, along with SPACE objects. SPOOLED files are actually stored in physical file members, but direct access to those members is taboo for sure. In fact there is no documented direct relationship between a SPOOLED file and its member that is exposed to end-users or developers. So the preferred method to access a SPOOLED file is via CPYSPLF or the SPOOLED file APIs.

These APIs have been used in some 3rd party software, such as high-availability software, to read and duplicate existing SPOOLED files. In fact, my own COZTOOLS has several CL commands that utilize these APIs:

• SAVSPLF - Save a SPOOLED file to a database file
• RSTSPLF - Restore a SPOOLED file from a database file
• CPYPRTF - Duplicates a SPOOLED file to another SPOOLED file or OUTQ
• SENDSPLF - Simplifies transmitting a SPOOLED file to another IBM i partition

So I've had a bit of experience using these SPOOLED File APIs. But since my focus for the last 8 to 10 years has been SQL functionality, I wanted to create a true "Read SPOOLED File" SQL function, not one that just wraps the CPYSPLF command, directs the results to an outfile and then returns that outfile content (although I did write one of those a few years ago named SPOOLED_DATA, and IBM ships a sample of a similar function in the SYSTOOLS library).

READSPLF (Read SPOOLED File Data) SQL Table Function

The READSPLF table function has been added to my SQL Tools product. It complements the existing READCSV (Read CSV file), READSRC (Read Source File Member), and READIFS (Read IFS Text File) table functions by providing a solution that is needed by most RPG IV developers. Here's a simple example of it reading the contents from the DSPLIBL OUTPUT(*PRINT) CL command:

READSPLF reads the content of an existing SPOOLED file and returns the printed rows of that SPOOLED file along with the following additional information:

1. A row counter (i.e., ORDINAL_POSITION)
2. Page Number
3. Line Number on the page for the printed data

In addition, you can customize the returned content, controlling all of the following options:

• Convert to Upper or Lower Case
• Left adjust each line (trim off left-side blanks)
• Filter or Return blank lines
• Embed a positional "ruler" for easier analysis

Convert to upper/lower case

The OPTIONS=>'*TOLOWER' or '*TOUPPER' parameter causes the SPOOLED file data to be converted to all lower or all upper case when it is returned. This allows users to more easily search the SPOOLED file with the need to perform that conversion in the SQL statement itself.

Left-Adjust Content

The OPTIONS=>'*LEFTADJ' or simply '*LEFT' parameter causes the function to remove leading blanks on the left-side of the SPOOLED file row. This comes in handy when, again, searching for content in the SPOOLED file and you are unsure of whether something starts in position X or Y... just left-adjust the results and always have a fixed location to start.

Filter Blank Lines

The BLANK_LINES=>'*OMIT' or '*KEEP' parameter filters out blank SPOOLED file rows from the resultSet. This provides compatibility with the CPYSPLF CL command. However if you need the original SPOOLED file content with all blank lines included, then BLANK_LINES=>'*KEEP' may be specified.

Positional Ruler Guide

The OPTIONS=>'*RULER' parameter embeds a positional ruler, similar to that seen in SEU. This allows you to easily see where SPOOLED data appears in each row without the need to count each character.

Example READSPLF SQL Function

Suppose you're using IBM ACS Run SQL Scripts (ARS) and you need to look at someone's joblog. You can do that directly from within ARS using READSPLF as follows:

select * from TABLE(sqlTools.READSPLF('QPJOBLOG',163,JOB=>'459033/BOBBY/QDSP000012'));        

Of course the IBM Service named JOBLOG_INFO also returns the job log information, as does our own JOBLOG_LIST table function; but for either of these functions to work, the job has to be active. Once the job has ended and the joblog is now a SPOOLED file, the READSPL function is the only SQL option.

Next, let's read another, much uglier SPOOLED file. The Service Dump SPOOLED file that we sometimes see in jobs and often don't know when or why they happened. Let's explore the Service Dump SPOOLED file for SQL issues (QSQSVCDMP in this example).

Yikes! Well, that's a lot to digest!. Perhaps if we include the blank lines, it might be a bit more human-friendly. Let's run it again, but this time use the BLANK_LINES=>'*KEEP' option:

That didn't help at all. But you can see the additional blank lines were returned on page 1, lines 3, 4, and page 2, lines 2 and 3. The nice thing about READSPL is that the LINENBR column is consistent whether or not blank lines are removed. For example, if a piece of data appears on line 12, then LINENBR returns 12 regardless of the BLANK_LINES parameter setting. Which is what you would expect. But perhaps a service dump isn't the best candidate for READSPL. ??

Suppose you manage 2 or more IBM i partitions that are connected. You can use a combination of SQL Tools with IBM ACS Run SQL scripts (ARS) to both list the spooled files and read their contents to check things out. For example, if I'm on Partition 1 and on Partition 12 there is a SPOOLED file named 'REPORT' and I need to review, I could do something like this:

select?*?from?TABLE(sqlTools.READSPLF('REPORT',1,JOB=>'105230/ROBERT/DSP01'));        

Hmm... that won't work because it will, be default, look for the REPORT and the job on the local partition. To access it on partition 12, which is named CHICAGO in this example, we have 3 choices:

1. Use the SQL command: CONNECT TO CHICAGO USER xxxxx USING 'password';
2. Add a WHERE EXISTS clause to the statement referencing the CHICAGO partition which needs to also be a Remote Database Directory Entry (RDBDIRE). Something like: WHERE EXISTS (SELECT * FROM CHICAGO.SYSIBM.SYSDUMMY1)
3. If you're on V7R5, you can use the REMOTE keyword and wrap the TABLE keyword in it (SQL loves keyword wrapping).

Let's illustrate each of these:

Using CONNECT TO:

connect to CHICAGO User Bobby Using 'rosebud';
select * from TABLE(sqlTools.READSPLF('REPORT',1,JOB=>'105230/ROBERT/DSP01'));        

Remember when using CONNECT TO that once you have run the CONNECT TO, you don't run it again while on that system. That is, if you run it a second time by pressing the "Run ALL" icon/button in ARS, the 2nd CONNECT TO will fail.

Using WHERE EXISTS:

select?*?from?TABLE(sqlTools.READSPLF('REPORT',1,JOB=>'105230/ROBERT/DSP01'))
WHERE EXISTS (Select * from CHICAGO.SYSIBM.SYSDUMMY1);        

This is my preferred method today. Once the vast majority of users are running a release with the REMOTE keyword, perhaps I'll move to that style.

Using REMOTE Keyword:

select?*?from?REMOTE TABLE( CHICAGO.sqlTools.READSPLF('REPORT',1,JOB=>'105230/ROBERT/DSP01'));        

While I'm not a fan of having to use the REMOTE keyword, it does provide the capability to specify the 3-part name for functions, and that's a great feature to have.

Conclusions

Now I can sit on my ACS screen all day monitoring and managing the IBM i partitions I'm responsible for, and never look at a green screen. All because of the vast library of SQL functions available in SQL Tools (www.SQLiQuery.com/SQLTools) which I've developed over the last few years.

To download READSPL along with all the SQL function in SQL Tools that I've built over the last few years, visit www.SQLiQuery.com/SQLTool and click on the "Download trial" button. If you'd like more information on my SQL Tools functions for ACS and RPG IV, send me an email or message me here on linked in.