Retrieving Printer File information using SQL

This is one of the additions that came as part of last round of Technology Refreshes that I was excited about. Previously If I need to get information about printer files I would either have to use an API or the DSPFD command. Now I have an easy way to get information about any printer file using a SQL View.

The new SQL View is called PRINTER_FILE_INFO and is found in the SYSTOOLS library.

I always recommend that you run the run the SQL statement to see all of the columns in the SQL View for yourself. That way you can make your determination which columns you find useful to your situation. You can do that with the following statement. I have a limit of returning ten results as I found that if you want to return all the results from the View it may take some time to do so.

01 SELECT * FROM SYSTOOLS.PRINTER_FILE_INFO LIMIT 10

Having looked at the results of the above statement I decided which columns were important to me, and I constructed the following Select statement:

01 SELECT PRINTER_FILE_LIBRARY AS "Library", 02 PRINTER_FILE AS "File", 03 OWNER AS "Owner", 04 DEFINER AS "Definer", 05 DATE(LAST_USED_TIMESTAMP) AS "Last used", 06 CASE 07 WHEN MAXIMUM_RECORDS IS NULL THEN '*NOMAX' 08 ELSE TO_CHAR(MAXIMUM_RECORDS,'999G999G999') 09 END AS "Max recs", 10 SAVE_AFTER_WRITE AS "Save", 11 FORM_TYPE AS "Form", 12 PAGE_LENGTH AS "Length", 13 PAGE_WIDTH AS "Width", 14 LINES_PER_INCH AS "LPI", 15 CHARACTERS_PER_INCH AS "CPI", 16 EXTERNALLY_DESCRIBED AS "Ext desc", 17 NUMBER_RECORD_FORMATS AS "Rec fmts", 18 RECORD_FORMAT 19 FROM SYSTOOLS.PRINTER_FILE_INFO 20 WHERE PRINTER_FILE_LIBRARY = 'QSYS' 21 AND PRINTER_FILE IN ('QSYSPRT','QPAEDSP')

Lines 1 – 18: These are the columns I think I will use the most. I have given them all short column headings so that they would be easier to display below.

Lines 6 – 9: The MAXIMUM_RECORDS will contain null if the printer file size is *NOMAX. I am using a Case statement to convert the null to *NOMAX. That will make the resulting column character rather than numeric, therefore, I am using the TO_CHAR Scalar function to convert any numbers to a formatted character representation of that number.

Lines 20 and 21: For these examples I am only going to list two printer files that everyone should have in their partitions, QSYSPRT and QPAEDSP from the library QSYS.

I have had to break the results of the above statement into three lines:

Library File Owner Definer Last used Max recs Save Form ------- ------- ----- ------- --------- -------- ---- ---- QSYS QPAEDSP QSYS *IBM <NULL> *NOMAX NO 1W QSYS QSYSPRT QSYS *IBM 2024-07-30 *NOMAX NO 1W Length Width LPI CPI Ext desc Rec fmts ------- ------- ---- ----- -------- -------- 88.000 132.000 8.0 10.0 YES 11 88.000 132.000 8.0 10.0 NO 1 RECORD_FORMAT ----------------------------------------------------- {"RCDFMT":[{"FORMAT_NAME":"PAGEHEADER","FORMAT_FIE... {"RCDFMT":[{"FORMAT_NAME":"QSYSPRT","FORMAT_FIELDS...

IMHO the interesting column is the last one, RECORD_FORMAT. This is a JSON array that lists all of the record formats in the printer file and various attributes about it. Fortunately, IBM gives an example of how to format this JSON array's data into columns, which I have taken and adapted into a SQL statement I like:

01 SELECT PRINTER_FILE_LIBRARY,PRINTER_FILE,RFD.* 02 FROM SYSTOOLS.PRINTER_FILE_INFO, 03 JSON_TABLE(RECORD_FORMAT, 'lax $.RCDFMT' 04 COLUMNS(FORMAT_NAME VARCHAR(10), 05 FORMAT_FIELDS INT, 06 FORMAT_LENGTH INT, 07 FORMAT_LEVEL_ID CHAR(13), 08 FORMAT_TEXT VARCHAR(50) 09 )) RFD 10 WHERE PRINTER_FILE_LIBRARY = 'QSYS' 11 AND PRINTER_FILE IN ('QSYSPRT','QPAEDSP')

Line 1: I want the printer file's library and name to be the first two columns. The RFD I use as the prefix for the columns returned by the JSON_TABLE function. I chose RFD as it stands for "Record Format Data".

Lines 3 – 9: The JSON_TABLE function that does all the work. It take the JSON array in the RECORD_FORMAT and breaks it apart into five columns.

Lines 10 and 11: Like my previous example I am only interested in the printer files QSYSPRT and QPAEDSP.

The results are:

PRINTER _FILE_ PRINTER FORMAT_ FORMAT_ FORMAT_ FORMAT_ FORMAT LIBRARY _FILE NAME FIELDS LENGTH LEVEL_ID _TEXT ------- ------- ---------- ------- ------- ------------- ------ QSYS QPAEDSP PAGEHEADER 4 40 16DBA6613DD16 <NULL> QSYS QPAEDSP CMDPARMS 3 30 1B61CF46E6D70 <NULL> QSYS QPAEDSP HEXLSTHDR 0 0 08D9F27D3E3AC <NULL> QSYS QPAEDSP HEXCOLHDR 0 0 08D9F27C3D79C <NULL> QSYS QPAEDSP HEXLSTRCD 6 56 1F81545CE2538 <NULL> QSYS QPAEDSP HEXLSTTRL 0 0 0A29927D3E3C7 <NULL> QSYS QPAEDSP SRCLSTHDR 0 0 0A7B303D3E3AC <NULL> QSYS QPAEDSP SRCCOLHDR 0 0 0A7B303C3D79C <NULL> QSYS QPAEDSP SRCLSTRCD 2 85 15C3068797A34 <NULL> QSYS QPAEDSP SRCLSTTRL 0 0 0BCAD03D3E3C7 <NULL> QSYS QPAEDSP ENDOFLSTNG 0 0 0A9AB8BD6C7B6 <NULL> QSYS QSYSPRT QSYSPRT 0 0 0000000000000 <NULL>

This is a good example of a SQL Statement that it make sense to make into SQL View, as no-one wants to work out the format of the JSON array in more than one program. Below is my definition of this new View:

01 CREATE OR REPLACE VIEW MYLIB.PRINTER_FILE_RECORD_FORMAT 02 FOR SYSTEM NAME "PRTFRCDFMT" 03 (PRINTER_FILE_LIBRARY,PRINTER_FILE, 04 FORMAT_NAME,FORMAT_FIELDS,FORMAT_LENGTH,FORMAT_LEVEL_ID,FORMAT_TEXT) 05 AS 06 SELECT PRINTER_FILE_LIBRARY,PRINTER_FILE,RFD.* 07 FROM SYSTOOLS.PRINTER_FILE_INFO, 08 JSON_TABLE(RECORD_FORMAT, 'lax $.RCDFMT' 09 COLUMNS(FORMAT_NAME VARCHAR(10), 10 FORMAT_FIELDS INT, 11 FORMAT_LENGTH INT, 12 FORMAT_LEVEL_ID CHAR(13), 13 FORMAT_TEXT VARCHAR(50) 14 )) RFD

Line 2: As I have given the SQL View a long name I want to give it a short system name too.

Lines 3 and 4: The names of the View's columns.

Lines 6 – 14: The same statement I used for the JSON array as before, without the Where clause.

Now I can just use this new View and get the data from the JSON array formatted into columns:

01 SELECT * FROM PRTFRCDFMT 02 WHERE PRINTER_FILE_LIBRARY = 'QSYS' 03 AND PRINTER_FILE IN ('QSYSPRT','QPAEDSP')

This statement will return the same results as my first SQL statement for the JSON array.

By having the printer file's library and name I can now join this to the PRINTER_FILE_INFO View. For example:

01 SELECT A.PRINTER_FILE_LIBRARY,A.PRINTER_FILE,A.TEXT_DESCRIPTION,B.FORMAT_NAME 02 FROM SYSTOOLS.PRINTER_FILE_INFO A 03 LEFT OUTER JOIN PRINTER_FILE_RECORD_FORMAT B 04 ON (A.PRINTER_FILE_LIBRARY,A.PRINTER_FILE) = (B.PRINTER_FILE_LIBRARY,B.PRINTER_FILE) 05 LIMIT 5

Line 1: I want the printer file's library, name, and description from PRINTER_FILE_INFO, and the record format name from the View I just created, PRINTER_FILE_RECORD_FORMAT.

Line 3: I used a LEFT OUTER JOIN to join the two Views together.

Line 5: I limited the results to five as I just want to illustrate how this would work.

The results are:

PRINTER _FILE_ PRINTER FORMAT_ LIBRARY _FILE TEXT_DESCIPTION NAME ------- ------- --------------------------------------- ----------- QSYS QDFUPRT DFU PRINT FILE QDFUPRT QSYS QEAPRTL SYSTEM DELIVERED EDUCATION PRINTER FILE QEAPRTL QSYS QPAEDSP Printer file for Display CSP Object PAGEHEADER QSYS QPAEDSP Printer file for Display CSP Object CMDPARMS QSYS QPAEDSP Printer file for Display CSP Object HEXLSTHDR

I can also use the PRINTER_FILE_INFO see, for example, which printer files use a certain form type:

01 SELECT PRINTER_FILE_LIBRARY AS "Library", 02 PRINTER_FILE AS "File", 03 OWNER AS "Owner", 04 DEFINER AS "Definer", 05 DATE(LAST_USED_TIMESTAMP) AS "Last used", 06 FORM_TYPE AS "Form" 07 FROM SYSTOOLS.PRINTER_FILE_INFO 08 WHERE LAST_USED_TIMESTAMP IS NOT NULL 09 AND FORM_TYPE = '*STD'

Line 8: I am checking if the column LAST_USED_TIMESTAMP is not null as I only want to return printer files that have been used.

Line 9: I am looking for all files that would use the standard form.

The result is:

Library File Owner Definer Last used Form ------- -------- ----- ------- ---------- ---- QTCP QPTMPLPD QSYS *IBM 2024-02-22 *STD

I have to be aware that I can override any other printer file to use the standard form using the Override Printer File command, OVRPRTF, so this list may not be the only printer file that prints on the standard form.

I can certainly see myself using this View as an uncomplicated way to investigate printer files in the partitions I am responsible for.

 

You can learn more about the PRINTER_FILE_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.