Wednesday, August 17, 2016

Extracting data from journals

extract data from journal using dspjrn

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 1Flag 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:

 

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

9 comments:

  1. Hi Simon I'm sure that you are aware once you get your DSPJRN into an output file the easier way to extract your journal info is running a sql against that file not need to create program for this just simple and plain sql sentences, anyway I know you did it this way just as an illustration for us thank you so much.

    ReplyDelete
  2. I have received a number comments informing me of non-IBM tools that offer alternative ways to view and extract data from journals. This blog endeavors to give examples of how to do things like this just using IBM commands and tools so that anyone can do whatever without the need to purchase any additional software.

    And while I do thank you for your comments I will not be publishing ones listing non-IBM tools.

    ReplyDelete
  3. Simon,
    What about the DISPLAY_JOURNAL table function
    Could be a great topic for a follow-up post.

    ReplyDelete
    Replies
    1. All I am prepared to say is... "Coming soon..."

      Delete
  4. If you prepend your ORDDTL table with those journal fields, you can dump the DSPRN directly to it. The trick is to name the record format QJORDJE.

    http://www.itjungle.com/fhg/fhg071410-story02.html

    Ringer

    ReplyDelete
  5. errata: Should be DSPJRN not DSPRN in my post. Thanks. Ringer.

    ReplyDelete
  6. I wish there was an API that an RPG program can use to read directly from journal files, instead of having to first extract data from it, and then only read the extracted data

    ReplyDelete
    Replies
    1. You wish could have been answered, just wait for the next installment.

      Delete
  7. There is an api: Retrieve Journal Entries (QjoRetrieveJournalEntries)

    In conjunction with methods of obtaining field/column information (data type, size, etc) you can come up with a very flexible way to spin through journal receivers. This is very handy when reporting security-related information or when having have to recover deleted records/rows.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.