Those of you that took a look at my updating of open source packages on IBM i, will know that I have always been one for recommending the conversions of Spool files to PDFs when ever possible.

My open source updating article can be found here https://powerwire.eu/open-source-updates/

This article created a spool file of all the open source packages that needed updating on the server.

This program was written in CL, back on version V5.4 of the IBM i Operating System, all those years ago.

So, has there been any advancement in the methods we now have available to generate PDFs?

The first place to start looking was IBMs impressive list of SQL services.  I’m sure Scott Forstie and his team would have thought of this!

The first SQL statement to find, was one to give me a list of my spool files. 

Easily found, this statement looks ideal.

SELECT *
FROM Qsys2.Output_Queue_Entries_Basic
WHERE Status = 'READY'
  AND User_Name = 'ANDY';

I’ll give it a run (Ctrl-R on windows), in the ACS Run SQL Scripts, and we can see the output in the figure below.

And yes, a dozen or so spool files to try to convert to PDFs.

Before trying to convert these, I need somewhere to place them on our IFS.

I’ll create a new directory off my home directory.  The home directory is often refered to as the ~ directory in the open source world.  So to create a directory off my home directory called PDF, in a shell session I would use the MKDIR (Make directory command).

Some where in the many files and directories, I can see the newly created PDF directory.  This is where we will place our PDFs.

Now we have a SQL way of generating a list of all my spool files and  I have a directory on my IFS for the generated PDFs to reside. Finally, the SQL statement below will convert all my spool files into PDFs and store them on an IFS directory called PDF under my home directory.

SELECT job_name,
       spooled_file_name,
       file_number,
       SYSTOOLS.Generate_PDF(job_name => job_name, spooled_file_name => spooled_file_name, spooled_file_number => file_number, path_name => '/home/andy/pdf/' CONCAT regexp_replace(job_name, '/', '_') CONCAT '_' CONCAT spooled_file_name CONCAT '_' CONCAT file_number CONCAT '.pdf') AS "pdf_created?", '/home/andy/pdf/' CONCAT regexp_replace(job_name, '/', '_') CONCAT '_' CONCAT spooled_file_name CONCAT '_' CONCAT file_number CONCAT '.pdf' AS pdf_path
FROM qsys2.output_queue_entries_basic
WHERE status = 'READY'
  AND user_name = 'ANDY';

Testing

By using the ifs_object_statistics table function below, it will list out all the objects (PDFs) that are in my home/PDF directory.

SELECT * FROM TABLE 
  (qsys2.ifs_object_statistics(START_PATH_NAME => '/home/andy/pdf/', SUBTREE_DIRECTORIES => 'YES'));

This statement produces the output seen below.

All my spool converted to PDFs; job done!

Conclusion

In this conclusion, I must thank Scott Forstie and his team for providing the excellent examples found in ACS run SQL scripts.

Yes, I could have easily used the CL method in my original article to create the PDFs in this example, but this is a great example of all the fantastic alternatives we have these days.  Keep them coming IBM.

For the specific SQL statements within this article, they can be found at this location https://github.com/FormaServe/f_Learning/blob/master/DB2/Create_PDFs.sql

A better way to write the text is:

Please feel free to comment below if you have any questions about this article or any other topic related to the IBM i.