Copy SPLF From OUTQ To IFS

I've had the ability to produce PDF files on IBM i for as long as I can remember. Before IBM added support through the OVRPRTF command's TOSTMF('/myifsfilename.pdf') WSCST(*PDF) I had been using both a 3rd party option and a PDF file template.

The IBM i solution was long overdue but we've had it for quite a while now (at least since V7R1, but likely as early as V5R4) so why not leverage it?

Starting with V7R2 IBM enhanced the ability for the C language to be used with SQL Functions and Procedures. After 4 full releases of IBM i and nearly a dozen TR's, IBMers have told me "you're the only one" who uses this enhanced capability. In a world where the word literally doesn't mean literal, while nearly everything anyone says is often interpreted as either a one-off or globally-pervasive, let me say that what these IBMers meant is that I am one of a handful (meaning some number under 20) who bothered to learned how to use this enhanced C capability with SQL.

I helped designed and certainly have been using the RPG IV language literally before it was announced; well over 25 years ago. I've been using variations of RPG since the fall of 1977. I have also been a C language developer on this platform for over 3 decades. Starting with the original C/400, then the short-lived but hugely powerful System C compiler, and now the C and C++ compilers. Sadly, the current IBM ILE C/C++ compilers are to the C/C++ language world what RPGII is to the RPG IV language. They are over 2 decades behind the rest of the world.

When I started building our SQL Tools (2COZ-ST2) product for #IBMi two years ago, I wasn't a strong SQL guy. Sure I've been using and leveraging SQL in RPG III and RPG IV for 35 years, but never really got deep into learning it as had with RPG or C. I was skilled at SELECT, DELETE, UPDATE, and INSERT, but that was about it. Things like triggers, stored procedures, or user-defined table functions? Fuggedaboutit.

But when V7R2 was shipped, IBM included an enhancement to SQL Functions and procedures that enabled inline C code. Well this is the kind of cool new capability I live to exploit, so I started building a few functions to try it out. The first few included FROMHEX, TOHEX, and of course several DATE conversion routines.

Once I got good enough at it and figured out the myriad idiosyncrasies (there are many) I tried my first major Table Function named OBJECT_LIST. This is a function that produces a list of objects along with their information. You pass in a library, generic or full object name, and object type and you get the list returned in an SQL ResultSet.

As someone who strongly influenced IBM's move to create the so called "Openness APIs" (today they are referred to as IBM i APIs) back in the 1980s, I have used the QUSxxxxx APIs for over 30 years, so building OBJECT_LIST was a pretty easy task, that I used as a learning experience.

The next project was MBR_LIST. I refined what I had learned with OBJECT_LIST and enhanced and improved MBR_LIST with additional parameters and options. Today I use MBR_LIST to locate source members by name anywhere on the system, and to locate source members there were changed within the last XX days.

Next I decided to implement a ton of RTVxxxx SQL functions to mimic existing RTVxxx CL commands. Most RTVxxx CL commands have APIs associated with their functions, but a few do not. So I started the long journal of building RTVOBJD, RTVMBRD, RTVSBSD, RTVFD, etc. dozens were in the pipeline. After finishing those I went on to building more and more LIST functions. As you can see from the directory of the SQL Functions and procedures I've created over on SQL Tools functions, there's no dust on me.

SQL Procedures

While I've never had too much experience writing SQL Stored Procedures (procedures or SQL procedures hereinafter) after studying them for a while I realized they are implemented similar to SQL Functions but with the option to have input-output parameters and by default there is no resultSet but you can return a resultSet. So I decided to create a few.

The first one I did was one that converts an existing SPOOLED file to a PDF file and saves it to the IFS. That procedure name is CPYTOPDF and was designed to mimic my COZZI TOOLS (COZTOOLS) product's CPYTOPDF CL command.

After using it for a few months, customer's asked if I could provide a way to copy a bunch of SPOOLED files at once. Notably they wanted the ability to selectively convert all or some of the SPOOLED files on an output queue to PDF and store them in the IFS. I accepted that challenge.

In our COZTOOLS product we've had the CPYOUTQ CL command for over 20 years. But I didn't want to simply use 20+ year old RPGIV code in this new tool kit. Instead I wanted to use just the SQL/PL language with embedded C. But certainly no RPGIV or CL programs.

I had already created the SPLF_LIST SQL function that produces a list of SPOOLED files for a user or job. But it didn't quite fit what I wanted. It uses the QUSLSPL API to produce the list, but I wanted a bit more capability. So I selected our SPOOLED_LIST SQL Function.

The SPOOLED_LIST SQL Function uses the QGYOLSPL (Open List of SPOOLED Files) API and has a lot more selective capabilities, plus it allows us to generate a pseudo command as one of the resultSet columns. That is, you can generate a CL command string that can be used to do something with the SPOOLED file. For example, I could generate a CPYSPLF command. So I selected it as the foundation for a new CPYOUTQ SQL Stored Procedure.

The SQL Tools CPYOUTQ SQL Stored Procedure copies each entry from an output queue to the IFS as a PDF or ASCII Text file.

Here's a scenario: We have a nightly run named "End of Day" that produces several SPOOLED files/Reports into an output queue named LASTEOD. In the olden days, they would print those SPOOLED files and distribute copies to the half dozen or so individuals who read most of them.

Now, with the CPYOUTQ procedure, we simple issue the following using whatever SQL interface you use. We obviously use RUNiQRY (SQL iQuery) but you can use RUNSQL, or store it in a source file member and use RUNSQLSTM, or even embed it within RPG IV.

call?sqltools.cpyoutq(outq_name=>'LASTEOD',outq_lib=>'QUSRSYS',
        PATH_NAME?=>?'/home/eod');        

When calling this SQL procedure from any SQL interface, the CPYOUTQ procedure produces a list of SPOOLED file names for the output queue and generaes PDF files on the IFS for each of them. Here's an IBM ACS Run SQL Scripts screen shot of this procedure along with the results on the IFS.

One key advantage of using our CPYOUTQ procedure is that it first creates the PDF file as a stream file (STMF) on the IFS using CCSID 1208. The resulting IFS files are in PC ASCII or specifically UTF-8 encoding. So sending them as attachments to your Users via EMAIL is easy.

Because we have embraced SQL Tools for IBM i, we can now do cool things using SQL interfaces including IBM ACS Run SQL Scripts or RPG IV, without the need to deal with complex/vexting APIs to the mix. Need a list of object names while in your RPG IV applications? No problem, OBJECT_LIST allows you to read the list of objects directly into your RPG IV app; no APIs needed. Need to retrieve the text for a specific MSGID? SQL Tools RTVMSGD function does it for you.

The list goes on. For more details an each of the Functions and Procedures, visit the SQL Tools product website today.

To subscribe to this free weekly newsletter, visit Bob Cozzi over on LinkedIn.