IBM Support

How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function

How To


Summary

Field ENTRY_DATA in DISPLAY_JOURNAL table function is returning the raw record data as BLOB(2G)

Objective

How can specific fields of the record data in ENTRY_DATA be extracted or even searched for?

Steps

This sample table is created in an SQL schema and such automatically journaled to QSQJRN:
create or replace table MYDB.vartable
( F1 char(32),
  F2 VARCHAR(128) ALLOCATE(16),
  F3 NVARCHAR(128) ALLOCATE(16),
  N1 DECIMAL(11, 2),
  N2 NUMERIC(11, 2),
  N3 INT
);

insert into MYDB.vartable values
('A', 'ABCDEFGHIJ', 'ÄÖÜäöüߧ{][]}', 12.34, 56.78, 90);

commit;
Run this SQL statement in 'Access Client Solutions' / 'Run SQL scripts' to take a look and filter for field contents:
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
CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) as F1, -- char
CAST(cast(substring(entry_data, 33, 130) as char(130) for bit data) as CHAR(130) CCSID 1141) as F2, -- varchar
HEX(cast(substring(entry_data, 163, 258) as char(258) for bit data)) as F3, -- nvarchar
HEX(cast(substring(entry_data, 421, 6) as char(6) for bit data)) as N1, -- decimal
HEX(cast(substring(entry_data, 427, 11) as char(11) for bit data)) as N2, -- numeric
HEX(cast(substring(entry_data, 438, 4) as char(4) for bit data)) 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 CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) = 'A'; -- filtering for F1 data

Additional Information

Only CHAR and VARCHAR data can be shown directly. Other data types like NVARCHAR, GRAPHIC, DECIMAL, or NUMERIC require HEX representation and filtering.
With the introduction of DB PTF Groups IBM i 7.4 SF99704 Level 7 and IBM i 7.3 SF99703 Level 18, a new tool was added.  
The INTERPRET SQL function can be used to get specific column information from BLOB field ENTRY_DATA:
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

Document Location

Worldwide

[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]

Document Information

Modified date:
22 January 2025

UID

ibm10958529

Page Feedback