Using SQL to view AS400 Journal data - Entry_Data in particular

Question

I'm using SQL to view journal entries on my AS400. All is great except that the data in the field ENTRY_DATA is returned as

800400000000020F000000130000000000000000000000010000000700000046F0F0F0F0F0F0F00000000E0040000000000000000000000000B0F89A7EAC380E9D

HOW do I convert this into a useful string of data?? I have been trying casting and creating functions and about 50 other things... Any little clue would be appreciated.

These among lots of others

SELECT
    cast(ENTRY_DATA  as varchar(2000) ccsid EBCDIC for sbcs data)
FROM TABLE (
    QSYS2.DISPLAY_JOURNAL( 'WVJRNLIB', 'WVPRDAJRN')
) AS JT
WHERE journal_entry_type in ('DL', 'PT', 'PX', 'UP')
and OBJECT LIKE Char('%ITEMMAST%' );

select
    ( CAST(ENTRY_DATA  as VARCHAR(32000) FOR BIT DATA))  As New_Column
FROM TABLE (
    QSYS2.DISPLAY_JOURNAL( 'WVJRNLIB', 'WVPRDAJRN')
) AS JT
WHERE journal_entry_type in ('DL', 'PT', 'PX', 'UP')
and OBJECT LIKE Char('%ITEMMAST%' ) ;

Answer 1

You are looking for the INTERPRET function; which was added to the OS in v7.3 TR 18. When used with the SUBSTRING function you have a way to say "take this many bytes and treat it as an integer, character, packed decimal, or zoned decimal."

A good example of usage can be found in IBM's support doc How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function

SELECT
-- general Info
ENTRY_TIMESTAMP, SEQUENCE_NUMBER, RECEIVER_LIBRARY, RECEIVER_NAME, journal_code, journal_entry_type, COUNT_OR_RRN,
"CURRENT_USER" AS User, (JOB_NUMBER CONCAT '/' CONCAT TRIM(JOB_USER) CONCAT '/' CONCAT JOB_NAME) as JOB,
PROGRAM_NAME, PROGRAM_LIBRARY, REFERENTIAL_CONSTRAINT, TRIGGER,
-- entry_data = ESD = Field data
INTERPRET(substring(entry_data, 1, 32) as CHAR(32) CCSID 1141) as F1, -- char
INTERPRET(substring(entry_data, 33, 130) as VARCHAR(128) CCSID 1141) as F2, -- varchar
INTERPRET(substring(entry_data, 163, 258) as NVARCHAR(128)) as F3, -- nvarchar
INTERPRET(substring(entry_data, 421, 6) as DECIMAL(11,2) ) as N1, -- decimal
INTERPRET(substring(entry_data, 427, 11) as NUMERIC(11,2) ) as N2, -- numeric
INTERPRET(substring(entry_data, 438, 4) as INTEGER) as N3 -- int
FROM TABLE (
                 QSYS2.DISPLAY_JOURNAL( 'MYDB', 'QSQJRN', OBJECT_NAME=>'VARTABLE',
                 STARTING_RECEIVER_NAME => '*CURAVLCHN',
                 OBJECT_LIBRARY=>'MYDB',
                 OBJECT_OBJTYPE=>'*FILE',
                 OBJECT_MEMBER=>'*ALL',
                 JOURNAL_ENTRY_TYPES =>'*RCD')) AS JT
where INTERPRET(substring(entry_data, 1, 32) as CHAR(32) CCSID 1141) = 'A'; -- filtering for F1 data

-----Note-----
If you table as nullable columns, the task gets a little more complex. Basically you have to check the NULL_VALUE_INDICATORS column before using the value. Something like so for each nullable field (this assume the first 4 are nullable):

case INTERPRET(substring(NULL_VALUE_INDICATORS, 4, 1) as char(1) )
  when '0' 
    then INTERPRET(substring(entry_data, 421, 6) as DECIMAL(11,2) ) 
  else cast(NULL as decimal(11,2)) 
end as N1,

If your journal is configured for "minimized entry specific data" the task is very complex. You'd need to dynamically process each journal entry.