Power Global

 View Only
Expand all | Collapse all

SQl and Printer

  • 1.  SQl and Printer

    IBM Champion
    Posted Mon July 17, 2023 12:07 PM

    Hi , i'm Luca verzicco from Italy (IBM Champion but not for SQL) and i'm spending time to find the best way to understand if a DEVICE has ONLINE(*YES) or (*NO).

    No SQL find... the only way is to retrive the confguration source with all command used for all DEVICE .. but SQL can be better.

    CRTDEVPRT DEVD(ZANUTTOTST) DEVCLS(*LAN) TYPE(3812) MODEL(1) LANATTACH(*IP) PORT(9100) ATTACH(*DIRECT) ONLINE(*YES) FONT(11 *NONE) FORMFEED(*AUTOCUT) SEPDRAWER(*FILE) +
    ???????????????? PRTERRMSG(*INQ) MSGQ(*CTLD) ACTTMR(170) INACTTMR(*SEC15)?? LINESPEED(19200) WORDLEN(8) PARITY(*NONE) STOPBITS(1) +
    ???????????????? TRANSFORM(*YES) MFRTYPMDL(*HP4000) PPRSRC1(*A4)?? PPRSRC2(*A4) ENVELOPE(*NUMBER10) ASCII899(*NO) +
    ???????????????? IMGCFG(*NONE) CHRID(*SYSVAL) RMTLOCNAME('10.73.214.125')?? SYSDRVPGM(*HPPJLDRV)?? TEXT('Configurazione di prova fine moduli')

    Do you have a suggestion ? i've to check 2500 printer device each LPAR (more then 10 LPAR).

    IBM at rel V7R1M0 with IBM extention and V7R3M0.

    Have a good day and thanks foryour time.

    --  Verzicco Luca Maurizio Mob. +39 347 4310398 verzicco@tiscali.it


  • 2.  RE: SQl and Printer

    IBM Champion
    Posted Mon July 17, 2023 09:45 PM
    Edited by Satid Singkorapoom Tue July 18, 2023 02:01 AM

    Dear Luca

    I have looked through these 2 lists of IBM i Services via SQL interface https://www.ibm.com/support/pages/ibm-i-services-sql and https://www.ibm.com/support/pages/ibm-i-services  and the result is that no HW device-related features are available at all.   I would say that retrieval of device description source is the only way to go but you can also use SQL to analyze the result of RTVCFGSRC and there is a sample code for you to get a rough idea on how to do this in this Technote :  Getting a List of All TCP/IP Addresses Used in RMTOUTQs or *LAN Printer Device Descriptions at https://www.ibm.com/support/pages/getting-list-all-tcpip-addresses-used-rmtoutqs-or-lan-printer-device-descriptions .  Note that you will use a lot of SQL string manipulation in such a case.

    Not sure if you are aware that, in IBM i, you can write an SQL script file that mixes CL and SQL together.  For each line of CL command, you need to code it with   CL: <CL command line> ;  

    So, your code should look somewhat like this (I tested it in Run SQL Script tool and it worked fine.) : 

    CL:  CRTSRCPF FILE(QTEMP/QTXTSRC) ;

    CL:  RTVCFGSRC CFGD(*ALL) CFGTYPE(*DEVD)  SRCFILE(QTEMP/QTXTSRC) SRCMBR(PRINTERS) RTVOPT(*OBJ) ;  

    create table liba.prtlist as (select SRCDTA from QTEMP.QTXTSRC WHERE SRCDTA like '%CRTDEVPRT%') with data ;

    select * from liba.prtlist where srcdta like '%ONLINE(*YES)%'  ;

    In the last line, you specify any text string that you want to check for. 

    Hope this is the nearest to what you asked for. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: SQl and Printer

    Posted Tue July 18, 2023 08:11 AM

    Hi,

    your post/answer sparked my interest - thank you very much.

    I am expanding on your answer with the SQL code created from the given IBM example, using the same CL calls.

    CL:  CRTSRCPF FILE(QTEMP/QTXTSRC) ;

    CL:  RTVCFGSRC CFGD(*ALL) CFGTYPE(*DEVD)  SRCFILE(QTEMP/QTXTSRC) SRCMBR(PRINTERS) RTVOPT(*OBJ) ;  

    --this way only lines containing CRTDEVPRT and ONLINE will be copied to the rows

    --added replace / on replace delete rows so only current data is available (if QTEMP should be replaced with another library)

    CREATE OR REPLACE TABLE QTEMP.prtlist as (
    select SRCDTA from QTEMP.QTXTSRC WHERE SRCDTA like '%CRTDEVPRT%' OR SRCDTA LIKE '%ONLINE(%'
    ) with data 
    ON replace DELETE rows
    ;

    --the IBM example was changed to searching the substring 'online'

    --the additional 'online' rows (CRTDEVDSP...) will be ignored

    --because it only searches for the 'online'-row following a 'CRTDEVPRT' row (RRN(A) + 1 = RRN(B))

    SELECT 
    cast( substr(A.srcdta, 16, position(')' in A.srcdta)-16) 
    as varchar(10)) as device
    , cast( substr(B.srcdta, position('ONLINE('in B.srcdta)+8, position( ')' in substr(B.srcdta,
    position('ONLINE(' in B.srcdta)+8, 5))-1)as varchar(3)) as
    status 
    FROM QTEMP.prtlist a
    , QTEMP.prtlist b 
    WHERE RRN(A) + 1 = RRN(B)
    and A.srcdta like 'CRTDEVPRT%'

    ;

    Now there should be a usable table with 2 columns - DEVICE and STATUS

    Change QTEMP to your own library for long-term data saving.

    Maybe you need to replace the varchar(xx) of the cast functions to char if you want to compare it with another list (additional spaces)

    Best regards,

    Stefan



    ------------------------------
    Stefan Rumersdorfer
    ------------------------------

    Original Message


  • 4.  RE: SQl and Printer

    IBM Champion
    Posted Wed July 19, 2023 05:06 AM

    Thanks guys.. i received a lot of very clever information from you and you save me a lot of time to understand how to do.

    I'll do some test on customer IBMi... on V7R1M0 .. and works fine. 

    I love this community. 



    ------------------------------
    Luca Maurizio Verzicco
    ------------------------------

    Original Message


  • 5.  RE: SQl and Printer

    IBM Champion
    Posted Tue July 18, 2023 08:02 AM

    I didn't see any sql either.

    There's also these:

    https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/apis/QDCLCFGD.html

    https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/apis/QDCRDEVD.html

    I didn't see anything here either:  http://www.sqliquery.com/SQLTools/ Basically a site for SQL tools written by a guy who is running a 7.3 machine and is that point in his career where he doesn't want to put money in an upgrade.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 6.  RE: SQl and Printer

    Posted Tue July 18, 2023 08:10 AM

    Hi,

    just in case somebody does not need the config setting but the CURRENT status of a printer device - this website offers good SQL scripts:

    https://www.ibmimonitoring.it/IBMiMonitoring_HowToAs400_WritersManager_EN.asp

    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')​



    Best regards,

    Stefan



    ------------------------------
    Stefan Rumersdorfer
    ------------------------------

    Original Message


  • 7.  RE: SQl and Printer

    Posted Wed July 19, 2023 11:52 AM

    Adding to this to include remote writers as well as print writers

    with writers as 
    (
    select objname as writer, 'DEV' as "Type"
    from TABLE (qsys2.OBJECT_STATISTICS('*ALL','*DEVD'))
    where objattribute in ('PRTLAN','PRTVRT', 'PRTLCL', 'PRTRMT', 'PRTSNP') 
    union
    select output_queue_name as writer, 'RMT' as "Type"
    from qsys2.output_queue_info
    where remote_system_name is not null
    )
    SELECT CASE 
           WHEN outq.writer_job_name is null THEN 'Off'
           Else 'On'
         END as status, "Type",
    writers.writer,outq.writer_job_name,outq.writer_job_status,
    dev.objtype,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 writers 
    left  join TABLE (qsys2.OBJECT_STATISTICS('*ALL','*DEVD')) AS dev
    on writers.writer = dev.objname 
    left  join qsys2.OUTPUT_QUEUE_INFO outq
    ON writers.writer=outq.output_queue_name
    order by 3;



    ------------------------------
    Michael Corbo
    ------------------------------

    Original Message


  • 8.  RE: SQl and Printer

    Posted Tue July 18, 2023 08:36 AM

    Simple example

     

     

    PGM &D                                                      

    DCL        VAR(&RTN) TYPE(*CHAR) LEN(100)                   

    DCL        VAR(&D) TYPE(*CHAR) LEN(10)                      

    CALL       PGM(QSYS/QDCRDEVD) PARM(&RTN X'0064' +           

                 'DEVD0100' &D X'00000000')                     

            SNDPGMMSG ('ONLINE() parm is: ' || %SST(&RTN 41 10))

                                                                 

    ENDPGM                                                       

     

    Call with DSP01 as a parm

     

     

    http://wwwdmz.cardinalhealth.com/content/dam/corp/web/logos/CAH-logo-solutions.png

     

     

     

     

     

     

    Bryan Dietz

    IBM i Server Engineer

    7000 Cardinal Place, Dublin, OH 43017

    614.757.3544

     

     


    _________________________________________________

    This message is for the designated recipient only and may contain privileged, proprietary
    or otherwise private information. If you have received it in error, please notify the sender
    immediately and delete the original. Any other use of the email by you is prohibited.

    Dansk - Deutsch - Espanol - Francais - Italiano - Japanese - Nederlands - Norsk - Portuguese - Chinese
    Svenska: http://www.cardinalhealth.com/en/support/terms-and-conditions-english.html




    Original Message


  • 9.  RE: SQl and Printer

    Posted Tue July 18, 2023 09:34 AM

    The SQL Tools have an SQL Table function for that.

    www.SQLiQuery.com/SQLTools

    The CFGSTS_LIST Table functions returns the current status for devices. 

    See the image from IBM ACS I used  to illustrate how it works. 



    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 10.  RE: SQl and Printer

    IBM Champion
    Posted Tue July 18, 2023 10:01 AM

    That doesn't list 

    Online at IPL  . . . . . . . . . . :   *YES

    It only lists the current status.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 11.  RE: SQl and Printer

    IBM Champion
    Posted Tue July 18, 2023 03:17 PM

    In the meantime you could also vote for this "IBM Idea" here: https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3282



    ------------------------------
    Steven Riedmueller
    Certified IBM i Admin
    Speaker, Mentor, and Advocate
    ------------------------------

    Original Message