Re: Utility to get current PTF Groups levels for All OS versions into a table.

Nice...just to share, I use the following Macro in interactive SQLWorkbench/J to get a quick simple "currency level" of a partition... this will produce a open an excel file ready to be shared ....Wb* are tool specific, the other are normal SQL usable everywhere...


WbVarDef -variable='ts' -query="(VALUES VARCHAR_FORMAT(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'))"
WbVarDef -variable='ts' -query='(VALUES CURRENT_DATE)';
WbVarDef fo='c:\temp\as400_';
CALL QSYS/QLZARCAPI;
WbExport -file=$[fo]$[ts].xlsx -title='QLZARCAPI';
SELECT ELEMENT FROM TABLE(QSYS2.JOBLOG_INFO('*'))
CROSS JOIN LATERAL (SELECT * FROM TABLE(SYSTOOLS.SPLIT(MESSAGE_TEXT, '.')))         
WHERE FROM_PROGRAM = 'QLZARCAPI';
WbExport -file=$[fo]$[ts].xlsx -title='DEFECTIVE' -append=true;
SELECT * FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY;
WbExport -file=$[fo]$[ts].xlsx -title='PTF_CURRENCY' -append=true;
SELECT * FROM SYSTOOLS.GROUP_PTF_CURRENCY
 ORDER BY PTF_GROUP_LEVEL_AVAILABLE - PTF_GROUP_LEVEL_INSTALLED DESC;
WbExport -file=$[fo]$[ts].xlsx -title='FIRMWARE_CURRENCY' -append=true;
SELECT * FROM SYSTOOLS.FIRMWARE_CURRENCY;
WbSysOpen $[fo]$[ts].xlsx









On Thursday, October 9, 2025 at 04:16:26 PM GMT+2, Bryan Dietz <bdietz400@xxxxxxxxx> wrote:

I like the XML idea.

I do a similar thing with the CSV from there using Scotts HTTPAPI
command to get the file, then use CPYFRMIMPF to put the data into a PF.

i'll use ACS to show the data.  i recently added the "loaded" field

select a.Product
        ,a.Osrelease
        ,a.Ptf
    ,COALESCE((select b.ptf_loaded_status from QSYS2.PTF_INFO b where
a.product = b.Ptf_product_id and a.ptf = b.ptf_identifier), 'NOT ON
SYSTEM') as loaded
        ,a.Pkg
        ,a.Availdate
        ,a.Mrifeature
        ,a.Replacedby
        ,a.Categories
        ,a.Abstract from _MYLIB_.sf97750 a
order by availdate desc;

Bryan

Gavin Inman wrote on 10/9/2025 8:54 AM:

I couldn't find anything on the web like this, so I wrote my own.

Enjoy,  Gavin

==============================
Destination Table
==============================

CREATE TABLE QTEMP.PTFIN (

                          RELEASE CHAR(4) NOT NULL,

                          NUMBER  CHAR(7) NOT NULL,

                          TITLE   CHAR(50) NOT NULL,

                          LEVEL   CHAR(5) NOT NULL,

                          DATE    CHAR(10) NOT NULL

           )

                          RCDFMT R@PTFINFO;

LABEL ON TABLE QTEMP.PTFIN   IS 'PTF Information from IBM Website';

============================
SQLRPGLE Program
============================

**Free

Ctl-Opt Debug Option(*SrcStmt:*NoDebugIO);

Ctl-Opt ActGrp(*Caller);

DCL-DS psp dim(500) qualified ;

    release char(4) ;

    number char(7)  ;

    title char(50)  ;

    level char(5)   ;

    date  char(10)  ;

END-DS;

DCL-DS psp1 ;

    release char(4) ;

    number char(7)  ;

    title char(50)  ;

    level char(5)   ;

    date  char(10)  ;

END-DS;

Dcl-s PTFINFO VarChar(32000);

dcl-s i int(5)     ;

Dcl-s Web varchar(256)

inz('https://public.dhe.ibm.com/services/us/igsc/PSP/xmldoc.xml');

EXEC SQL

      TRUNCATE PTFIN ;

Exec SQL

SELECT RESPONSE_MESSAGE

    into :PTFINFO

    FROM TABLE(QSYS2.HTTP_GET_VERBOSE(

     (:web),

    '{"sslTolerate":"true","header": "Accept,application/json"}')) ;

    XML-INTO psp %XML(ptfinfo:'case=any') ;

    for i = 1 to 500 ;

      if psp(i).release <> *blanks ;

        eval psp1=psp(i) ;

Exec SQL

    INSERT into PTFIN (release, number, title, level, date)

     values(:release, :number, :title, :level, :date) ;

      ENDIF;

    ENDFOR;

    *INLR=*ON ;