Extracting data from journals

Many of the posts in the blog come from questions asked by its readers, this is another example. I received a message asking if I would explain how to extract data about changes made to a file from a journal.

Journaling on files, simply put, is a record of all changes made to one or more files. This information can be used in commitment control operations, allowing for changes to be rolled back, or for replicating changes to a disaster recovery replica of the production system. As the files the questioner asked about are being journaled we can get a copy of the changes made to a file.

How can you tell if a file is being journaled?

I can use the Display File Description command, DSPFD, and scan the output for the word "journal", yes it does have to be in lower case. In my example I find that the Order Detail file, ORDDTL, is being journaled, see below:

File is currently journaled . . . . . . . . : Yes Current or last journal . . . . . . . . . . : ORDJRN Library . . . . . . . . . . . . . . . . . : PRODLIB

Once I have identified the journal I can see what other files are being included in the same journal by…

1. Type: WRKJRNA PRODLIB/ORDJRN and press Enter
2. On the "Work with Journal Attributes" screen to display the journaled objects press F19
3. On the "Display Journaled Objects" screen enter 1 and press Enter
4. The "Display Journaled Files" screen lists all of the files included in this journal

Or I can type what is shown below, and press Enter.

WRKJRNA JRN(PRODLIB/ORDJRN) OUTPUT(*PRINT) DETAIL(*JRNFILE)

The spool file QPDSPJMA contains a list of all the journaled files.

To copy data from a journal I use the Display Journal command, DSPJRN. Below is my example of the DSPJRN command to copy data from the ORDJRN journal for the file ORDDTL:

01 DCL VAR(&DATE) TYPE(*CHAR) LEN(6) 02 CHGVAR VAR(&DATE) VALUE('073116') 03 DSPJRN JRN(PRODLIB/ORDJRN) + 04 FILE((PRODLIB/ORDDTL)) + 05 RCVRNG(*CURAVLCHN) + 06 FROMTIME(&DATE 000000) + 07 TOTIME(&DATE 235959) + 08 JRNCDE((R)) + 09 OUTPUT(*OUTFILE) + 10 OUTFILFMT(*TYPE4) + 11 OUTFILE(QTEMP/@DSPJRN) + 12 ENTDTALEN(*CALC)

Line 1: As I am going to copy by a date and time range I need to have a variable for the date.

Line 2: And there is my date. As I am in the USA I use the MDY format, so this date is July 31 2016.

lines 3 – 12: I have broken out the DSPJRN keywords onto separate lines to make it easier for you to see each one, and for me to describe them.

Line 3: First parameter is the name of the journal.

Line 4: This is the name of the file I want the data for.

Line 5: The RCVRNG stands for "Range of journal receivers". The default value is *CURRENT which will retrieve the data from the journal receiver that is currently attached. I always use the *CURAVLCHAIN, this looks at all in all of the journal receivers from the oldest in the "chain" to the youngest, the one currently attached. If a receiver in the "chain" is missing it is ignored and processing continues with the next available. While *CURAVLCHN does take more time than *CURRENT, it does ensure that if data for the range I desire is in more than one journal receiver it will all be retrieved.

Line 6 and 7: This is the date and time range I wish to copy the data form.

Line 8: There are different types of data within a journal. "R" stands for "Operation on Specific Record", in other words add, changes, and deletes to records in a file.

Line 9: I want the output to go into an output file.

Line 10: The data from the journal can come in five different formats. The format that is "*TYPE4" contains the data of the changed record.

Line 11: The name of the output file.

Line 12: The "ENTDTALEN" is the length of the data we want retrieved from the journal. In true K.I.S.S. (Keep It Simple Simon) style I use the value *CALC that allows the command program to determine the length of the file.

There are some other variations I have used.

In the example below I have not given the file parameter (was line 3 in the previous example), therefore, data for all the file in the journal that qualify will be included in my outfile. I have a new parameter on line 8, this will only extract the record type UP and UX, I will explain what those are later.

03 DSPJRN JRN(PRODLIB/ORDJRN) + 04 RCVRNG(*CURAVLCHN) + 05 FROMTIME(&DATE 000000) + 06 TOTIME(&DATE 235959) + 07 JRNCDE((R)) + 08 ENTTYP(UP UX) 09 OUTPUT(*OUTFILE) + 10 OUTFILFMT(*TYPE4) + 11 OUTFILE(QTEMP/@DSPJRN) + 12 ENTDTALEN(*CALC)

In this example I want information from two files only, which I give in the FILE keyword.

03 DSPJRN JRN(PRODLIB/ORDJRN) + 04 FILE((PRODLIB/ORDDTL) (PRODLIB/ORDHDR))

The output file contains the following fields:

'

Field	Length	Description	Example
JOENTL	5,0	Length of entry	621
JOSEQN	10,0	Sequence number	1,897,730
JOCODE	1	Journal Code	R
JOENTT	2	Entry Type	PT
JOTSTP	Timestamp	Timestamp of Entry	2016-07-31-07.00.41.570736
JOJOB	10	Name of Job	TESTJOB
JOUSER	10	Name of User	TESTPRF
JONBR	6,0	Number of Job	61,018
JOPGM	10	Name of Program	TESTPGM1
JOOBJ	10	Name of Object	ORDDTL
JOLIB	10	Objects Library	PRODLIB
JOMBR	10	Name of Member	ORDDTL
JOCTRR	10,0	Count or relative record number changed	363
JOFLAG	1	Flag	0
JOCCID	10,0	Commit cycle identifier	2,218,258
JOUSPF	10	User Profile	TESTPRF
JOSYNM	8	System Name	THISSYS
JOJID	10	Journal Identifier	D^ *Ì*pæ *
JORCST	1	Referential Constraint	0
JOTGR	1	Trigger	0
JOINCDAT	1	Incomplete Data	0
JOIGNAPY	1	Ignored by APY/RMVJRNCHG	0
JOMINESD	1	Minimized ESD value	0
JORES	5	Reserved
JONVI	52	Null Value Indicators
JOESD	Variable	Record's data	File dependent

If I am looking for the Entry Types that are relevant to records being added, changed, and deleted from the Journaled file I am going to be looking for:

Entry type	Description
PT	Record added
PX	Record added directly by RRN
UB	Update - before image
UP	Update - after image
DL	Delete

I could have entered these in the ENTTYP keyword for my outfile to only contain those entries. Or I could handle it within a RPG program:

01 dcl-f @DSPJRN extfile('QTEMP/@DSPJRN') ; 02 dcl-ds File extname('PRODLIB/ORDDTL') ; 03 end-ds ; 04 dcl-s Status char(15) ; 05 dow (1 = 1) ; 06 read @DSPJRN ; 07 if (%eof) ; 08 leave ; 09 elseif ((JOENTT = 'PT') or (JOENTT = 'PX')) ; 10 Status = 'ADD' ; 11 elseif (JOENTT = 'UB') ; 12 Status = 'UPDATE BEFORE' ; 13 elseif (JOENTT = 'UP') ; 14 Status = 'UPDATE AFTER' ; 15 elseif (JOENTT = 'DL') ; 16 Status = 'DELETE' ; 17 else ; 18 iter ; 19 endif ; 20 File = JOESD ; // Do more stuff 21 enddo ;

Fixed format definitions for the file, data structure, and variable can be found here.

Line 1: This is the definition for my Journal output file. I have used the EXTFILE to hard coded its location. For more information about this see Useful keywords for your F-specs.

Line 2 and 3: I am defining a data structure based to be the same layout as the file ORDDTL in the library PRDLIB. I go into this in more details in Externally described Data Structures.

Line 4: This variable, Status, will contain what kind of operation was performed to the file's record.

Lines 5 and 21: I have a Do-loop to be used to read all the records in file.

Line 6: The file is read.

Line 7 and 8: If the end of file is reached then the program leaves the Do-loop.

I use the IF-ELSEIF a lot in all of my RPG, if you are not familiar with it you should read Alternative to SELECT is IF-ELSEIF.

Lines 9 – 10: These lines are performed if Journal file record is an add.

Lines 11 – 12: If the record is a before update image.

Lines 13 – 14: An after update record causes this.

Lines 15 – 16: If the record was deleted.

Lines 17 – 18: If the record is some other kind.

Line 20: I have found the quickest/easiest way to move the data from the Record's data field to the file's fields is to move the value in JOESD to the data structure File. If you look at the value of File in debug after this line you will see that the data that is in JOESD has been broken out into ORDDTL's fields, which are the sub fields of the data structure File. Petty cool, just one line of code to do all of that.

I can then do whatever kind of reporting I want. For example list all of the added, changed, and deleted orders from the date used.

You can also extract data from a Journal using SQL, see here.

You can learn more about this from the IBM website:

• DSPJRN command
• Reading Entry Specific Data (JOESD) from a journal receiver
• All journal entries by code and type

 

This article was written for IBM i 7.2, and should work for earlier releases too.

 

Fixed format definitions

01 F@DSPJRN IF E DISK extfile('QTEMP/@DSPJRN') 02 D File E DS extname('PRODLIB/ORDDTL') 04 D Status S 15 /free

Return