IBM AS400 - iSeries - Power i Systems. How to extract and map journal entries to the original physical file.

You will have been searching online to find out how to convert journal entries back into readable data.

You’ll have looked at extracting journal entries with the DSPJRN command to outfile.

You’ll have noticed that the field JOEDS contains flat data. If there’s packed numerics, binaries and variable length fields, it is almost impossible to read.

The first challenge is to map these back to the original physical file. This ticks the box.

The second challenge is speed. Journaling of physical files is a very quick, efficient process. Any process to unscramble the journal must also be very quick so that it doesn’t lag behind. In other words, convert in (almost) real time. This ticks the box.

The third challenge is to have a mechanism to analyse the output and ensure that there are no missing journal sequence numbers relevant to the journal conversion for physical files. This ticks the box.

The fourth challenge is to ensure that not only record level changes are captured but so are file level changes. The record level changes must continue to be captured for the new structure. This ticks the box.

The fifth challenge is to be able to rerun any conversion process should there be some event that interrupts the process. This ticks the box.

The sixth challenge is to capture roll-backs as well as normal data processing. This ticks the box.

Everything is here.

Download the shortened documents: Data Transformation From Journals (pdf) and Work With Transformed Data (pdf)

The savefile ISHARE400 is at V7R3. This merely needs restoring, set up the scheduled jobs and you’re ready to go.

The savefile ISHARE4SRV is at V7R1 (same applies to V7R2). Once restored, the program sources will need to be recompiled and then you’re ready to go.

For V7R1 users, there’s just one “gotcha”. V7R1 does not support *CURAVLCHN in journal commands. Therefore it necessary to compile CLLE source EXTJRNC001 to object EXTJRNC00.

This is a complete ready-to-go package. It contains all sources. You can modify and use them as you wish.

The two accompanying documents provide a lot of useful information about the function of the package. They both contain all the source code too.

I have to say that this has kind of evolved over many years. I have not been a programmer for decades. Though I keep my hand in. Some of the code is “old-school” (I apologise in advance) but it functions perfectly well. If you feel the need to modernise it, then be my guest.

How to Restore

Create an IFS folder (I used my mail folder imail400) and copy the appropriate file to it. For V7R3 users this is ISHARE400. For V7R1/2 users this is ISHARE4SRC.

Copy from the stream file to a savefile in QUSRSYS.

1. V7R3 Users: 

CPYFRMSTMF FROMSTMF('/imail400/ishare400.file') TOMBR('/qsys.lib/qusrsys.lib/ishare400.file/')

RSTLIB SAVLIB(ISHARE400) DEV(*SAVF) SAVF(QUSRSYS/ISHARE400) – ready to go

Remove library list entry IWATCH400 (my basic monitoring/alerts tool) from jobd ISHJOBD (or create library IWATCH400)

Set up your job schedules jobs to start subsystem ISHARE400, EXTJRN (Extract Journal) commands and ENDEXTJRN (End Extract Journal) commands as appropriate.

2. V7R1/2 Users: 

CPYFRMSTMF FROMSTMF('/imail400/ishare4src.file') TOMBR('/qsys.lib/qusrsys.lib/ishare4src.file')

RSTLIB SAVLIB(ISHARE4SRC) DEV(*SAVF) SAVF(QUSRSYS/ISHARE4SRC) RSTLIB(ISHARE400)

Copy  file EXTJRNOUT to QTEMP. Compile all source in QCLSRC, QRPGSRC and QRPGLESRC (alwnull(*yes) for all SQLRPGLE sources)

Remove library list entry IWATCH400 (my basic monitoring/alerts tool) from jobd ISHJOBD (or create library IWATCH400)

Set up your job schedules jobs to start subsystem ISHARE400, EXTJRN (Extract Journal) commands and ENDEXTJRN (End Extract Journal) commands as appropriate.

3. Use WRKTDTA for each of your extracts and check the results. 

Download the Save Files (with source) and Full Documentation    £297