When 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. This document describes how to use RPG program described files to work with journal entries.
Resolving The Problem
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 very 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 information; for example, the user that added or changed the record, time stamp, and so on. The CMPJRNIMG (Compare Journal Images) command is also useful; however, that command has several restrictions and limitations.
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. 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 Once these have been entered, continue with the field description for data file. Using the CPYF method for use with query requires using 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 and it will tell you if JOESD is a variable length field that is being used to hold the record data. If it is, you should make changes accordingly to the extra bytes in JOESD.
Following is an example using CPYF:
|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. The above 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 may 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 in records that have been added (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 probably want to include JOENTT to indicate if 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 very 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 may 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) +
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.
Can I do this with SQL rather than DDS?
To use the "Generate SQL" function of iSeries Navigator, do the following:
|1.||Within iSeries Navigator, right click on your JRNOUT table, and select General SQL.|
|2.||Remove the JOESD field from the bottom of the SQL source.|
|3.||Generate SQL from your database file.|
|4.||Append the SQL source from your database file to the SQL from your JRNOUT table.|
|5.||Specify a NEW table name, change the RCDFMT a NEW format name, and change the|
LABEL ON TABLE and LABEL ON COLUMN statements accordingly.
|6.||Run this new CREATE TABLE statement.|
Now use the CPYF command from Step 7 above to bring in the data from your JRNOUT file. This forces the entry-specific data to go into separate fields that simulate your database file.
18 December 2019