service program procedure exports

service_program_procedure_exports.sql

-- List service program procedure exports
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either expressed or implied.
----------------------------------------------------------------------------------------------

-- Since we need the to process the print from DSPSRVPGM, and is is 
-- produced by the QPRTJOB, the we need the to wrap the API to get the id of last spool 
create or replace procedure qusrsys.Last_Spooled_File_Created (
    out receiver char(256) for bit data,
    in rcv_len int default 256,
    in fmt char(10) default 'SPRL0100',
    in err bigint default 0
)
language RPGLE
parameter style general
modifies sql data
external action 
not fenced
external name QSYS/QSPRILSP
set option dbgview = *source , output=*print , commit=*NONE, datfmt=*iso; 

-- test it: 
call qusrsys.Last_Spooled_File_Created( receiver => ?);

-- To let i compile:
cl: crtpf qtemp/xxprint rcdlen(132);


create or replace function qusrsys.service_program_procedure_exports (
    library  char(10),
    service_program char(10)
) 
returns table (
   line_number int,
   export_id char(4),
   procedure_name char(256)   
)
modifies sql data
external action

begin
    declare info char(256) for bit data default '';
    
    declare continue handler for sqlstate '38501' begin end;
    call qcmdexc('DSPSRVPGM SRVPGM(' || rtrim(library) || '/' || rtrim(service_program) || ') OUTPUT(*PRINT) DETAIL(*PROCEXP)');
    call qcmdexc('crtpf qtemp/xxprint rcdlen(132)');
    call qusrsys.Last_Spooled_File_Created( receiver => info);
    
    call qcmdexc ('CPYSPLF FILE(QSYSPRT) TOFILE(QTEMP/XXPRINT) SPLNBR(*LAST) JOB(' ||
        trim(substr(info , 39 ,6)) || '/' ||
        trim(substr(info , 29 ,10)) || '/' ||
        trim(substr(info , 19 ,10)) || ')'
    );
    return 
        select  
            rownumber() over(),
            substr(hex(rownumber() over()) , 13),
            substr( xxprint , 2, 50)
        from qtemp.xxprint
        where  substr(xxprint ,77, 1 ) = '*';
 
end;

  
-- Usecase:
select * from table (
    qusrsys.service_program_procedure_exports ( 
        library => 'NOXDB',
        service_program => 'JSONXML'
    )
);