si_select_and_view_spool_data.sql

CREATE or replace function siiia.select_spool_data (in_user char(10), in_file char(10), in_startdatum char(8), in_enddatum char(8))
Returns Table (spooldata Varchar(32656))
Language Sql
Specific siiia/SISFSSD
Not Deterministic
modifies sql data
Returns Null On Null Input
Set Option Dbgview = *Source
Begin 
Declare Continue Handler For Sqlexception
   SET MESSAGE_TEXT = 'Fehler';

set clstmt = 'WRKSPLF SELECT(' concat trim(in_user) concat ' *ALL *ALL *ALL *ALL ' concat trim(in_file) concat ') ' +  
                     'PERIOD((*AVAIL ' concat trim(in_startdatum) concat ') (*AVAIL ' concat trim(in_enddatum) concat ')) ' +
                     'OUTPUT(*PRINT)';
set clstmtlen = length(clstmt);
CALL qsys2.qcmdexc (clstmt , clstmtlen );

create or replace table qtemp/splfdata (line char(170));

set clstmt = 'CPYSPLF FILE(QPRTSPLF) TOFILE(QTEMP/SPLFDATA) SPLNBR(*LAST)';
set clstmtlen = length(clstmt);
CALL qsys2.qcmdexc (clstmt , clstmtlen );

for a as x cursor for 
    select substr(line, 2, 10) as splf_file,        
           substr(line, 13, 10) as splf_user,       
           substr(line, 24, 10) as splf_queue,      
           substr(line, 35, 10) as splf_userdata,   
           substr(line, 46,  3) as splf_status,     
           substr(line, 51,  5) as splf_ges,        
           substr(line, 57, 10) as splf_head,       
           substr(line, 68, 10) as splf_formularart,
           substr(line, 79,  3) as splf_priority,   
           substr(line, 83,  8) as splf_datum,      
           substr(line, 92,  8) as splf_zeit,       
           substr(line, 101,  6) as splf_splfnr,    
           substr(line, 108, 10) as splf_jobname,   
           substr(line, 119,  6) as splf_jobnr,     
           substr(line, 126, 10) as splf_queuename, 
           substr(line, 137, 10) as splf_queuelib,  
           substr(line, 149,  3) as splf_asp,       
           substr(line, 153,  8) as splf_date,              
           substr(line, 162, 10) as splf_size               
      from qtemp.splfdata a 
     where length(trim(line)) = (select max(length(trim(line))) 
                                   from qtemp.splfdata) do       
     set clstmt = 'CPYSPLF FILE(' concat trim(in_file) concat ') JOB(' concat trim(splf_jobnr) concat '/' concat trim(splf_user) concat '/' concat trim(splf_jobname) concat ') TOFILE(QTEMP/SPLFDATA) SPLNBR(' concat trim(splf_splfnr) concat ') MBROPT(*ADD)';
     set clstmtlen = length(clstmt);
     CALL qsys2.qcmdexc (clstmt , clstmtlen );

     insert into srajukalk.allqry select ODLBNM, ODOBNM, b.* from qtemp.b b ;
     
end for;

return select * 
         from qtemp.select_spool_data;

end;