We reduce and optimize the times of our activities

Monitor and manage printers with SQL

In this article I will talk about how to analyze the status of both local and remote printers with normal SQL queries. This is possible thanks to the IBM i Services SQL that IBM has made available since version 7.2 of the operating system and which is growing more and more.
As always, the purpose of these articles is to help find shortcuts to speed up some activities that the daily routine of our work requires.

So let's see how to answer questions such as the following on the fly:

  • which printers are on and which are off?
  • what are the IP addresses and queues associated with remote printers?
  • which printers are at fault and what is the error?
  • are there any spools related to printer X that still need to be printed or have been saved?

    Printer Device (os version 7.2+)

    The printing devices created on the system are recoverable with this simple SQL query:

    select * from table (qsys2.OBJECT_STATISTICS('*ALL','*DEVD') ) AS dev
where dev.objattribute in ('PRTLAN','PRTVRT', 'PRTLCL', 'PRTRMT', 'PRTSNP')​


    In this way, however, we cannot know which are on and which are off and in what state they are. To do this we must extract the data of the output queues related to the devices and see their properties. Here's how to combine the two pieces of information with a query:

    SELECT *
FROM TABLE (qsys2.OBJECT_STATISTICS('*ALL','*DEVD') ) AS dev left outer join qsys2.OUTPUT_QUEUE_INFO outq
ON
(dev.objname=outq.printer_device_name)
where dev.objattribute in ('PRTLAN','PRTVRT', 'PRTLCL', 'PRTRMT', 'PRTSNP')​

    And if we just want the main information with the indication of active device or not, here's how we can improve the query:

    SELECT CASE 
       WHEN outq.writer_job_name is null THEN 'Off'
       Else 'On'
     END as status,
dev.objname,outq.writer_job_name,outq.writer_job_status,
dev.objtype,dev.objowner,dev.objcreated,dev.objlongname,dev.last_used_timestamp,dev.days_used_count,dev.iasp_number,dev.objattribute,
outq.output_queue_name, outq.output_queue_library_name, outq.number_of_files, outq.number_of_writers, outq.writers_to_autostart,
outq.printer_device_name,outq.output_queue_status,outq.text_description,outq.manufacturer_type_and_model,outq.MESSAGE_QUEUE_LIBRARY,outq.MESSAGE_QUEUE_NAME
FROM TABLE (qsys2.OBJECT_STATISTICS('*ALL','*DEVD') ) AS dev left outer join qsys2.OUTPUT_QUEUE_INFO outq
ON
(dev.objname=outq.printer_device_name)
where dev.objattribute in ('PRTLAN','PRTVRT', 'PRTLCL', 'PRTRMT', 'PRTSNP')​



    If we want to extract only the devices in an error state we can add the condition outq.writer_job_status = 'MSGW' to the query

    List of spools associated with the Device (os version 7.2+)

    A device turned on has the fields OUTPUT_QUEUE_LIBRARY_NAME and OUTPUT_QUEUE_NAME set in the previous query. They can be used to extract the list of spools associated with a particular Device with this query:

    SELECT *
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('output_queue_library_name', 'output_queue_name', 'YES')) A
​
    Even a powered off device may have associated and saved spools, but in this case we do not have the queue associated with its library. We can try the default one or look for it in another way with IBM i interactive commands.

  • Default OUTPUT_QUEUE_LIBRARY_NAME: QUSRSYS
  • Default OUTPUT_QUEUE_NAME: the device name (dev.objname)

    Viewing the contents of the spool (os version 7.3+)

    Once we have extracted the list of spools, it can become interesting to see its contents directly, always with a query that uses the JOB_NAME and SPOOLED_FILE_NAME fields of the previous one.

    SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
JOB_NAME          =>'campo JOB_NAME',
SPOOLED_FILE_NAME =>'campo SPOOLED_FILE_NAME')) as x
ORDER BY ORDINAL_POSITION

    Remote output queue (os version 7.2+)

    Unlike devices, there may be remote output queues that are always used for printing. We can extract their list with this query:

    select *
from QSYS2.OUTPUT_QUEUE_INFO outq
where not remote_system_name is null and printer_device_name is null

    Again, if we want to have the status of the remote printers and other main information, we could reduce the query to the following fields:

    select CASE
       WHEN outq.writer_job_name is null THEN 'Off'
       Else 'On'
     END as status,
outq.output_queue_name as objname, outq.writer_job_name,outq.writer_job_status,
outq.output_queue_name, outq.output_queue_library_name, outq.number_of_files, outq.number_of_writers, outq.writers_to_autostart,
outq.printer_device_name,outq.output_queue_status,outq.text_description,outq.message_queue_librar,outq.message_queue_name,
outq.host_print_transform, outq.manufacturer_type_and_model, outq.network_connection_type, outq.destination_type, outq.remote_system_name,
outq.remote_printer_queue,outq.MESSAGE_QUEUE_LIBRARY,outq.MESSAGE_QUEUE_NAME
from QSYS2.OUTPUT_QUEUE_INFO outq
where not remote_system_name is null and printer_device_name is null

    If we want to extract only the Devices in an error state we can add the condition outq.writer_job_status = 'MSGW' to the query

    As before, to then extract any spools associated with the remote queue, I can repeat the queries already described above

    Conclusions
    We have seen how easy it is to quickly extract information that interactively required more time and knowledge. The queries above use the . as a separator between libraries and filenames because done with an ODBC editor. The same queries can be executed with the "Execute SQL script" item of the IBM Access Client or with the interactive tool "strsql" by replacing the separator . with the separator /

    The tool
    These and other shortcuts have been used and optimized in a tool by the company smeup.
    A low-cost product with enormous potential for rapid management, analysis and monitoring of IBM i systems: IBM i Monitoring Facility - Check

    Marco Moret Monitoring Project Manager at smeup ICS
     You can also find the article on LinkedIn


    All articles
     Writers Manager: Monitor and manage printers with SQL
     Checking security on IBM i systems: the Audit Journal
     The first JDBC connection from C# to IBM i
     JTOpen, develop applications in .Net for IMB i
     DB2 for i: manage dates in SQL
     Get system info in SQL

    Manage, analyze and control your Systems IBM i
    Download, install and use in seconds
      IBM i Monitoring Facility - Check is completely free until the end of the month
    • Update cookies preferences