GetQueryDefinition.sql

Create or Replace Function siiia.getQueryDefinition (qrylibrary char(10), qryfile char(10))
Returns Table(qrylib char(10),
              qryfile char(10),
              qrydef varchar(500))
language sql
Modifies Sql Data 
not fenced
begin 
declare clstmt varchar(1000);
declare clstmtlen int;
declare MESSAGE_TEXT varchar(256);
Declare Continue Handler For Sqlexception
   SET MESSAGE_TEXT = 'Fehler';

set clstmt = 'DSPOBJD OBJ(' Concat Trim(qrylibrary) Concat '/' Concat Trim(qryfile) Concat ') OBJTYPE(*QRYDFN) OUTPUT(*OUTFILE) OUTFILE(QTEMP/a)';
set clstmtlen = length(clstmt);
CALL qsys2.qcmdexc (clstmt , clstmtlen );

create or replace table qtemp.b (line char(500));
--create table qtemp.b (qrylibrary char(10), qryfile char(10), line varchar(500));

for a as x cursor for select ODLBNM, ODOBNM from qtemp.a do
  call QSYS2.Print_Query_Definition (ODLBNM, ODOBNM, 1);

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

  for b as x cursor for select line from qtemp.b do
    Pipe(ODLBNM, ODOBNM, Trim(line));
  end for;

end for;

drop table qtemp.b;

Return;
end;
                              
SELECT * FROM TABLE(SIIIA.GETQUERYDEFINITION(CHAR('*ALLUSR'), CHAR('*ALL'))) AS t                        
;