A while ago I came across an SQL statement that can be used on the iSeries/DB2 to extract data directly from database journals. It worked without having to use DSPJRN etc. It invoked a stored procedure and the results came directly back to the SQL session. Unfortunately I have since lost the info.
Does anyone know the stored procedure name and how to write the SQL statement?
Found it. It works via a UDTF - user defined table function - provided by IBM.
The format is as follows. Only the first 2 parameters are required. There is a decent blog about it here: http://ibmsystemsmag.blogs.com/i_can/2010/11/
select * from table (Display_Journal(
'journLib', 'Journal', -- Journal library and name --
'receiverLib', 'Receiver', -- Receiver library and name --
CAST(null as TIMESTAMP), -- Starting timestamp --
CAST(null as DECIMAL(21,0)), -- Starting sequence number --
'', -- Journal codes --
'', -- Journal entries --
'','', -- Object library, Object name --
'','', -- Object type, Object member --
'', -- User --
'', -- Job --
'' -- Program --
) ) as x