Wednesday, August 28, 2024

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.

1 comment:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.