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.
    ------------------------------



  • 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
    ------------------------------



  • 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
    ------------------------------



  • 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
    ------------------------------



  • 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
    ------------------------------



  • 7.