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.
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;
-- 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
-- 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
-- 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.
-- 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.
-- 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;
FROM IBMSCH.JRNOUTF;
-- Seq_Number Jrn_Code Jrn_Entry_Type Entry_Data
-- 00000000000000000024 R DL
-- 00000000000000000024 R DL
--F9F3F8F4F7F2C885959589958740C740D2F4F8F5F940C5939440C1A58540C481939381A2E3E7F7F5F2F1F7F5F0F0F0F3F0F0F3F7F0F0F0F0F0F0F0F0
-- 00000000000000000025 R DL --F8F4F6F2F8F3C19389A296954040D140E2F7F8F740D381928540C4994040C9A293854040D4D5F5F6F3F4F2F5F0F0F0F3F0F0F1F0F0F0F0F0F0F0F0F0
-- 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:
-- 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;
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
-- 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.
-- 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
-- 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'));
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
Additional Information
Reading Entry Specific Data (JOESD) from a journal receiver
How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function
https://www.ibm.com/support/pages/node/958529
https://www.ibm.com/support/pages/node/958529
Document Location
Worldwide
[{"Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CGIAA2","label":"IBM i Db2->Journal"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]
Historical Number
N1018443
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->Journal
Software version:
All Version(s)
Operating system(s):
IBM i
Document number:
6194721
Modified date:
12 November 2020
UID
ibm16194721
Manage My Notification Subscriptions