Re: Reading the results of a stored procedure with a UDTF and/or a view.

On 2/13/2019 7:26 AM, Rob Berendt wrote:

My question then becomes "Is it possible to make a general UDTF to wrap around qsys2.DISPLAY_JOURNAL_ENTRY_INFO?".

I took a stab at an all-SQL solution (auditors are happy) and ended up
using a pipelined function. It has one tremendous disadvantage: one
needs to declare the columns of the returned table in advance. So... not
generic. The good news is that one can specify a subset of columns; it
doesn't need to be every column in the journalled table.

I post it in the hopes that someone else will get an aha!



create or replace function opensource.dspjrndta (
journal_lib_in char(10),
journal_name_in char(10),
table_lib_in char(128),
table_name_in char(128),
table_mbr_in char(10),
user_name_in char(10)
)
returns table (
sequence_number int,
journal_code char(10),
program_name char(10),
program_library char(10),
receiver_name char(10),
receiver_library char(10),
rs_col1 char(3),
rs_col2 char(9),
rs_col3 char(3),
rs_col4 char(24),
rs_col5 char(27)
-- ...
)
deterministic
modifies sql data

set option commit = *NONE

--
begin

declare journal_code char(10);
declare receiver_name char(10);
declare receiver_library char(10);
declare sequence_number int;
declare rs RESULT_SET_LOCATOR varying;

declare rs_col1 char(3);
declare rs_col2 char(9);
declare rs_col3 char(3);
declare rs_col4 char(24);
declare rs_col5 char(27);

-- cursor of journal entries
for je cursor for

SELECT X.*
FROM TABLE (
QSYS2.Display_Journal(
journal_lib_in, journal_name_in,
OBJECT_LIBRARY=>table_lib_in, OBJECT_NAME=>table_name_in,
OBJECT_OBJTYPE=>'*FILE', OBJECT_MEMBER=>table_mbr_in
) ) AS X
WHERE journal_entry_type in ('DL', 'PT', 'PX', 'UP')
AND "CURRENT_USER" = user_name_in
ORDER BY entry_timestamp DESC
for read only
do
-- 'translate' the entry_data into a row
call qsys2.display_journal_entry_info(2,
journal_lib_in,
journal_name_in,
sequence_number,
receiver_library,
receiver_name,
journal_code
);

associate result set locators (rs)
with procedure qsys2.display_journal_entry_info;

allocate jedetails cursor for result set rs;

-- each column needs to be specified here, and declared
-- above, in the 'return table' clause, and also
-- each one needs to be declared the proper type and size, too.
fetch jedetails into rs_col1, rs_col2, rs_col3, rs_col4, rs_col5;

pipe(sequence_number, journal_code, program_name, program_library,
receiver_name, receiver_library,
rs_col1, rs_col2, rs_col3, rs_col4, rs_col5);

close jedetails;

end for;

return;

--
end;