IBM Support

Example: How to extract and read ENTRY_DATA (JOESD) journal receiver field using DISPLAY_JOURNAL table function

How To


Summary

This as-is example is meant to show how to extract and view ENTRY_DATA (JOESD) journal field data in a readable format using the DISPLAY_JOURNAL table function.

Objective

Provide an example script for customers on how to display journal receiver ENTRY_DATA field using DISPLAY_JOURNAL table function.
 
Note this example is for fixed length fields, variable length fields are more complicated.

Environment

IBM i for all supported versions

Steps

The following lines, needs to be copied / pasted into IBM i ACS Run SQL Scripts.
Then you can simply run the statements.
 
-- Example showing how to extract and view ENTRY_DATA (JOESD) journal field data in a readable format.
-- We will use QIWS/QCUSTCDT file which is usually available in any IBM i system
 
 CREATE SCHEMA IBMSCH;
 
 -- IBMSCH is created with the following journal objects:
  -- Object               Type            Library           Attribute   Text                       
 -- QSQJRN0001  *JRNRCV    IBMSCH        COLLECTION - created by SQL
 -- QSQJRN            *JRN            IBMSCH        COLLECTION - created by SQL                             
    
 CREATE TABLE IBMSCH.QCUSTCDT AS
 (SELECT * FROM QIWS.QCUSTCDT) WITH DATA;    
 
-- Let's look into the file content

SELECT * FROM IBMSCH.QCUSTCDT;
 
-- CUSNUM  LSTNAM   INIT STREET                CITY   STATE ZIPCOD CDTLMT CHGCOD BALDUE CDTDUE
-- 938472    Henning   G K   4859 Elm Ave     Dallas    TX    75217    5000             3           37.00      0.00
-- 839283    Jones        B D   21B NW 135 St  Clay        NY   13041    400               1         100.00      0.00
-- 11111      Vine           S S    PO Box 79           Broton    VT   5046       700               1         439.00      0.00
-- 938485    Johnson   J A    3 Alpine Way      Helen     GA   30545    9999             2      3987.50    33.50
-- 397267    Tyron        W E   13 Myrtle Dr       Hector    NY   14841    1000             1              0.00      0.00
-- 389572    Stevens    K L    208 Snow Pass  Denver   CO   80226    400                1           58.75      1.50
-- 846283    Alison       J S    787 Lake Dr         Isle         MN  56342    5000             3           10.00       0.00
-- 475938    Doe           J W   59 Archer Rd       Sutter    CA    95685    700               2         250.00  100.00
-- 693829    Thomas   A N    3 Dove Circle      Casper   WY   82609    9999             2              0.00       0.00
-- 593029    Williams  E D     485 SE 2 Ave      Dallas    TX    75218    200                1           25.00       0.00
-- 192837    Lee            F L     5963 Oak St        Hector   NY   14841    700                2         489.50       0.50
-- 583990   Abraham  M T    392 Mill St           Isle         MN  56342    9999             3          500.00       0.00
 
-- Select those records for which CDTLMT = 5000
 
SELECT * FROM IBMSCH.QCUSTCDT WHERE CDTLMT = 5000;
 
--  CUSNUM  LSTNAM    INIT STREET                 CITY   STATE ZIPCOD CDTLMT CHGCOD BALDUE CDTDUE
-- 938472    Henning     G K    4859 Elm Ave     Dallas    TX    75217    5000               3        37.00    0.00
-- 846283    Alison         J S    787 Lake Dr         Isle         MN    56342   5000               3        10.00    0.00
 
--  Delete these two rows
 
DELETE FROM IBMSCH.QCUSTCDT WHERE CDTLMT = 5000;
--  Statement ran successfully   (678 ms) 2 rows were affected by the statement
 
-- Verify that there are not more rows for CDTLMT = 5000

SELECT * FROM IBMSCH.QCUSTCDT WHERE CDTLMT = 5000;
-- 0 rows retrieved.
 
-- Now, look for the two deleted rows in the journal: Journal Code 'R' Journal Entry typ 'DL'
-- Send the DSPJRN output to an outf first.
 
CL:DSPJRN JRN(IBMSCH/QSQJRN) FILE((IBMSCH/QCUSTCDT)) JRNCDE((R)) ENTTYP(DL) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE5) OUTFILE(IBMSCH/JRNOUTF);    
-- CPC7060: 2 entries converted from journal QSQJRN in IBMSCH.
-- SQL7985: CALL statement complete.
 
SELECT JOSEQN "Seq_Number", JOCODE "Jrn_Code", JOENTT "Jrn_Entry_Type", JOESD "Entry_Data"
FROM IBMSCH.JRNOUTF;
 
-- Seq_Number                           Jrn_Code Jrn_Entry_Type  Entry_Data
-- 00000000000000000024    R               DL          
--F9F3F8F4F7F2C885959589958740C740D2F4F8F5F940C5939440C1A58540C481939381A2E3E7F7F5F2F1F7F5F0F0F0F3F0F0F3F7F0F0F0F0F0F0F0F0
-- 00000000000000000025    R               DL           --F8F4F6F2F8F3C19389A296954040D140E2F7F8F740D381928540C4994040C9A293854040D4D5F5F6F3F4F2F5F0F0F0F3F0F0F1F0F0F0F0F0F0F0F0F0
 
-- We would like to see the information in Entry_Data field in a readable format. For that purpose,
-- we will run the following SELECT statement:
 
SELECT JOSEQN "Seq_Number", JOCODE "Jrn_Code", JOENTT "Jrn_Entry_Type",
CAST(CAST(JOESD AS VARCHAR(6594) FOR BIT DATA) AS
VARCHAR(6594) CCSID 37) entry_data
FROM IBMSCH.JRNOUTF;
 
-- Seq_Number                          Jrn_Code Jrn_Entry_Type  Entry_Data
-- 00000000000000000024      R           DL                          938472Henning G K4859 Elm Ave --DallasTX7521750003003700000000
-- 00000000000000000025      R           DL                          846283Alison  J S787 Lake Dr  Isle  --MN5634250003001000000000
 
-- We have been able to read the data in Etnry_Data.
-- But, the sql statement returns Entry_Data field as one big data string.
-- The following steps will show you a way to split it into each of the original file fields.
 
-- We will use the SELECT statement from technote
-- How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function
-- https://www.ibm.com/support/pages/node/958529
 
-- We will write a simplified version of it according to our example
 
SELECT
SEQUENCE_NUMBER, journal_code, journal_entry_type,
-- entry_data field split in the original file fields
CAST(cast(substring(entry_data, 1, 6) as char(6) for bit data) as CHAR(6) CCSID 1141) as CustomerNum,
CAST(cast(substring(entry_data, 7, 14) as char(8) for bit data) as CHAR(8) CCSID 1141) as LastName,
CAST(cast(substring(entry_data, 15, 18) as char(3) for bit data) as CHAR(3) CCSID 1141) as Init,
CAST(cast(substring(entry_data, 18, 30) as char(13) for bit data) as CHAR(13) CCSID 1141) as Street,
CAST(cast(substring(entry_data, 31, 36) as char(6) for bit data) as CHAR(6) CCSID 1141) as City,
CAST(cast(substring(entry_data, 37, 38) as char(2) for bit data) as CHAR(2) CCSID 1141) as STate,
CAST(cast(substring(entry_data, 39, 43) as char(5) for bit data) as CHAR(5) CCSID 1141) as Zipcode,
CAST(cast(substring(entry_data, 44, 47) as char(4) for bit data) as CHAR(4) CCSID 1141) as CDTLMT,
CAST(cast(substring(entry_data, 48, 48) as char(1) for bit data) as CHAR(1) CCSID 1141) as CHGCOD,
CAST(cast(substring(entry_data, 49, 44) as char(6) for bit data) as CHAR(6) CCSID 1141) as BALDUE,
CAST(cast(substring(entry_data, 45, 50) as char(6) for bit data) as CHAR(6) CCSID 1141) as CDTDUE
FROM TABLE (
                 QSYS2.DISPLAY_JOURNAL( 'IBMSCH', 'QSQJRN', OBJECT_NAME=>'QCUSTCDT',
                 STARTING_RECEIVER_NAME => '*CURAVLCHN',
                 OBJECT_LIBRARY=>'IBMSCH',
                 OBJECT_OBJTYPE=>'*FILE',
                 OBJECT_MEMBER=>'QCUSTCDT',
                 JOURNAL_ENTRY_TYPES =>'DL'));

--Seq# Jrn  Entry Cust        Last          Init   Street                  City      State   Zip    CDTLMT CHGCOD   BALDUE   CDTDUE
--     Code Type     Nbr          Name                                              code
--24     R       DL    938472   Henning   G K   4859 Elm Ave   Dallas  TX    75217    5000             3       003700    000300
--25     R       DL    846283   Alison       J S    787 Lake Dr       Isle      MN    56342   5000             3       001000    000300
 
-- end
 

Note this example is for fixed length fields, variable length fields are more complicated, see the note in:

https://www.ibm.com/docs/en/i/7.5.0?topic=services-display-journal-table-function

LOB data considerations: For journal code R (any entry type) and code F (IZ entry type), when a LOB data type is encountered that is a zero-length string, 16 'Q's are placed in the Entry Specific Data, followed by the LOB data.

This requires logic be put in, to handle the variable length fields.

Additional Information

Reading Entry Specific Data (JOESD) from a journal receiver
https://www.ibm.com/support/pages/node/642805
 
How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function
https://www.ibm.com/support/pages/node/958529
 
 

 

Document Location

Worldwide

[{"Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CGIAA2","label":"IBM i Db2-\u003EJournal"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]

Historical Number

N1018443

Document Information

Modified date:
10 December 2025

UID

ibm16194721

Page Feedback