IBM Support

Reading Entry Specific Data (JOESD) from a journal receiver

Troubleshooting


Problem

Using the DSPJRN command to display the image of a record (for example, record added, before image, after image, and so on) the output is often difficult to work with.   

Cause

The entire record is contained in a single field / column (JOESD) in the output file generated by the DSPJRN.

Resolving The Problem

NOTES
The journal must be set to Minimize entry data of *NONE or *FLDBDY for these steps to work.   WRKJRNA can be used to see the setting.
The SQL Table or physical file must have Journal Images (IMAGES) of *BOTH.   DSPFD can be used to see the setting.
When a user displays a journal entry for an image of a record (for example, record added, before image, after image, and so on) the output is often difficult to work with.
This is because the entire record is contained in a single field in the output file.
It is difficult to read because there are no column separators nor column headings.
Even more of a problem is packed numeric data because the numeric value is not displayed.
This document describes how to regain the field descriptions that allow you to use the output in a query and how to add detail information. 
For example, the user that added or changed the record, time stamp, and so on may be useful to show.
The CMPJRNIMG (Compare Journal Images) command is also useful.

The DSPJRN output file contains fields describing the journal entry.
The last field, JOESD, is the entry-specific field.
To clarify, in the case of updated records, it contains the actual record image.
It appears in the same form as though the DSPPFM command were used.
The packed fields are unrecognizable and there are no column separation nor headings.
To see what changed you must write an HLL program or do a series of copy files.

In both cases, use the DSPFFD command for the DSPJRN output file and the original file to determine field types and lengths. (You can also use SQL to create the tables) 
Using RPG as an example, program describe the files rather than using the external file descriptions.
Using RPG allows you to deal with multiple files displayed at the same time because you can specify multiple record types in the I specs.
When program describing the file start with the field description from the DSPJRN output file followed by the field description for the data file.
Using the CPYF method for use with query requires the file is an externally described files.
Therefore, you must specify only one file on the DSPJRN command.

Note: If the JOESD is stored in a variable length field in the outfile from DSPJRN, you need to account for the two extra bytes in front of the field that represent the exact length of the data. You can do a DSPFFD of your outfile to see if JOESD is a variable length field. If it is you need to make changes to account for the extra bytes in JOESD.
Example 1  (only using SQL)
a. STRSQL or Run SQL Scripts
CALL QSYS.CREATE_SQL_SAMPLE ('DBSAMPLE')
and create an example UPDATE statement to review in step g.
update DBSAMPLE.STAFF
set SALARY = SALARY + 2000
where ID = 40
 
b. IBM i command line -  generate the OUTFILE from DSPJRN.   We'll put this OUTFILE in QTEMP and name it DSPJRN.
See All journal entries by code and type for more information and options for JRNCDE and ENTTYP.
DSPJRN JRN(DBSAMPLE/QSQJRN)
       FILE((DBSAMPLE/STAFF))
       JRNCDE((R))
       ENTTYP(UB UP)
       OUTPUT(*OUTFILE)
       OUTFILFMT(*TYPE4)
       OUTFILE(QTEMP/DSPJRN)
       ENTDTALEN(*CALC)
      
c. STRSQL or Run SQL Scripts - create an intermediate table to use in the first CPYF step.
This step does not require any changes on your part.
create table qtemp.dspjrn_2 as                
(select JOENTL, JOSEQN, JOCODE,               
        JOENTT, JOTSTP, JOJOB,                
        JOUSER, JONBR, JOPGM,                 
        JOOBJ, JOLIB, JOMBR,                  
        JOCTRR, JOUSPF, JOSYNM,               
        JOESD                                 
  from qtemp.dspjrn) with no data  
d. IBM i command line - Use CPYF to go from DSPJRN's OUTFILE to table you created in step c
This step does not require any changes on your part.
CPYF FROMFILE(QTEMP/DSPJRN)            
     TOFILE(QTEMP/DSPJRN_2)            
     MBROPT(*REPLACE)                  
     FMTOPT(*MAP *DROP)    
e. STRSQL or Run SQL Scripts - create a output file that merges the output from DSPJRN and converts the JOESD column into the columns of your journaled table.
create table qtemp.dspjrnout as                       
(select j1.JOENTL, j1.JOSEQN, j1.JOCODE,              
        j1.JOENTT, j1.JOTSTP, j1.JOJOB,               
        j1.JOUSER, j1.JONBR,  j1.JOPGM,               
        j1.JOOBJ,  j1.JOLIB,  j1.JOMBR,               
        j1.JOCTRR, j1.JOUSPF, j1.JOSYNM,              
        f1.*                                          
   from qtemp.dspjrn_2 j1,                            
        dbsample.staff f1) with no data        
f. IBM i command line - Use CPYF to go from the intermediate table you created in step c and populated in step d to the final output file you created in step e.
This step does not require any changes on your part.
CPYF FROMFILE(QTEMP/DSPJRN_2)      
     TOFILE(QTEMP/DSPJRNOUT)       
     MBROPT(*REPLACE)              
     FMTOPT(*NOCHK)        
A CPF5029 "Data mapping error" message on this step likely means your FILE  (from step b in the DSPJRN) doesn't have Journal Images (IMAGES) of *BOTH.
g. STRSQL or Run SQL Scripts
SELECT *
  FROM qtemp.dspjrnout
You can visually see what changed between the TYPE of UB (Before-image of a record that is updated in the physical file member) and the TYPE of UP (After-image of a record that is updated in the physical file member).   These rows were generated via the update in step a.
SALARY_changed
and you can see who made that change:
WhoChangedSALARY

Example 2  
1. Display the journal, selecting the output file format that includes the information you need. On the operating system command line, type the following:

DSPJRN JRN(FILES) FILE((V2KEA234/FILE1)) +
JRNCDE((R)) ENTTYP(UB UP) OUTPUT(*OUTFILE) +
OUTFILFMT(*TYPE4) OUTFILE(JRNOUT) ENTDTALEN(*CALC)

Press the Enter key. This command displays journal entries of records that have been updated. The image before the update (UB) and after the update (UP) are displayed. Depending on the requirements, you might prefer to display only one entry type at a time. You could then go through these steps two times into files with different names. This would allow you to use join processing to compare the before and after image of the files. Otherwise, as in this example, the before image precedes the after image on your report. Review Appendix C of the Advanced Backup and Recovery manual for relevant journal codes. Some journal implementations do not include both images. In addition, you can use other codes if you are using commitment control or if you are interested only insert records (PT and PX).
2.
Use the DSPFFD command on the output file created in Step 1. Use this report to determine which fields are required for your report (user, timestamp, entry type, and so on). Also, note the last field, JOESD, which is the image of your record. Depending on the requirements, you may not be interested in viewing anything other than the record itself. In that case, select only the JOESD field. If comparing before and after images in the same report, you would want to include JOENTT field to indicate whether the image is a before or after image .
3. Create a source member used to create a temporary file. Consult the DSPFFD output from Step 2 for the field names, lengths, and field types of the journal output file. The fields are those selected in Step 2. It is important to use the exact name, length, and field types. Do not forget to include JOESD. It is advisable to keep them in the same order. If you are not interested in any of the journal entry information, the file can contain the JOESD field only.

A          R JRNWRKFM
A          JOENTT         2A
A          JOTSTP          Z
A          JOUSER        10A
A          JOESD        304A
4.
Create the physical file from the source created in Step 3. Create it in QTEMP because it is used only as an interim step. Files created in QTEMP are available only to the job that created them and are automatically deleted when you end the job. This can eliminate old, unnecessary work files consuming valuable disk space.
5. Copy the journal output file into the work file by using the *MAP *DROP options to retain only the fields you need. On the operating system command line, type the following:

CPYF FROMFILE(JRNOUT) TOFILE(QTEMP/JRNWRK) +
MBROPT(*REPLACE) FMTOPT(*MAP *DROP)

Press the Enter key.
6.
Make a copy of the source for the file you are working with. Add to the source the fields you selected in Step 2. Do not include JOESD. You must use the same order, and you must put them before your database file fields. It might be best to not key the file or be prepared to handle duplicate keys.
.
A          R FILE1FM
A          JOENTT         2A
A          JOTSTP          Z
A          JOUSER        10A
A          USER          10A ------These fields from database file
A          NAME          15A -----/
 
7. Do another copy file with option *NOCHK. This forces the work file to lay down into the field descriptions that include those from your original file.

CPYF FROMFILE(QTEMP/JRNWRK) TOFILE(FILE1X) +
MBROPT(*REPLACE) FMTOPT(*NOCHK)
The resulting file is one with the fields from the DSPJRN that describes the entry along with the fields from the database file itself. Because the file is now externally described, Query/400, and other HLL programs that use externally described files can access the file with field descriptions and column headings. This example showed how to view before and after images that enable you to see both images and compare which values changed.

Although more difficult because of the requirement to code the field descriptions, program describing the files in RPG allows you to process multiple files in one program. This allows you to display all files in the journal or list the specific ones you want to display in a single DSPJRN command. Using different record types enables you to distinguish between different files and their corresponding fields.

In the following example, two files are displayed in a single DSPJRN command: ORDHDR and ORDDTL. The following RPG specifications allow you to distinguish between them:

The first field in the DSPJRN output file is JOOBJ (the file name), so we compare starting in position one:

IORDHDR  AA  01   1 CO   2 CR   3 CD
    I       AND       4 CH   5 CD   6 CR
     I   field specs
     I     "     "

     IORDDTL  AA  02   1 CO   2 CR   3 CD
    I       AND       4 CD   5 CT   6 CL
     I   field specs
     I     "     "

ORDHDR is now record type 01 and ORDDTL is now record type 02.

 
Example using CPYF

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"","label":""}],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

N1018443

Document Information

Modified date:
31 January 2023

UID

nas8N1018443

Page Feedback