Do a DSPJRN to an outfile, and field JOESD is inscrutable. Learn how to make it readable.

You know that journal entries contain useful "who did what to whom and when" information and that they can also help you understand data flow and changes in a suite of unfamiliar programs. You've probably used the DSPJRN command, and on the screen you can easily see the date and time the transaction occurred. You can also see the job name, user, and number. You can even see the program that made the transaction. But you can't (easily) see what changed! Usually, you see something like Figure 1, with a mishmash of special characters in the "Entry specific data" field.

Figure 1: This is a typical journal display of a record with non-character fields. (Click images to enlarge.)

Yes, "Entry specific data" shows you the record, but it is difficult to parse this string into fields and extremely time-consuming when there are non-character fields, as above. You quickly lose your enthusiasm when you need to review more than a handful of records.

Writing the data to an outfile allows you to scan using Query, or SQL, or your own program, but JOESD is still largely impenetrable unless you can reassemble it into fields.

The good news is that, with a little effort, the reassembly can be done. This article shows how.

Article Setup

For this article, I created a simple file named ORDLNP using DDS like this:

A               R ORDLINF                   

A                 ORDNUM         7P 0       

A                 LINE           3P 0       

A                 SKU            5P 0       

A                 ORDDUE          L         

A                 QTY            3P 0       

A                 OFFER          2S 0        

I started journaling to journal MYJRN, added a few records to ORDLNP, and changed one of them. (If you want to follow along, you can download the DDS and a CLP program that creates everything, adds the records to the file, and does the updating.) 

I then sent the journal records to QTEMP/WKORDLNP using this command:

DSPJRN JRN(MYJRN) FILE((ORDLNP)) JRNCDE((R)) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE2) OUTFILE(QTEMP/WKORDLNP) ENTDTALEN(*CALC)                         

Note the OUTFILFMT(*TYPE2) to get both the job user and the current user profile, as these will be different if the job has switched profiles. Note also that we are selecting only JRNCDE(R), outputting only the journal entries that contain record data. The ENTDTALEN(*CALC) is also important; it ensures that you get all the record data.

If you query QTEMP/WKORDLNP, you will find at the beginning the date, time, job information, program, etc. as in Figure 2.

Figure 2: These first fields on a journal record have clear, readable data.

If you scroll all the way to the right, you will see the record image in the SPECIFIC DATA column, as in Figure 3. This is field JOESD, and the record data is hard to decipher, though you can recognize the character and zoned fields.

Figure 3: Here's an example of a common DSPJRN output of changes to a file with non-character fields.

Exposing JOESD

In library QSYS, IBM provides template files for DSPJRN output: files QADSPJRN and QADSPJR2 through QADSPJR5, corresponding to OUTFILFMT values *TYPE1 through TYPE5. If you display the fields in these formats, you will find many common fields at the beginning of each record, and the last field is always JOESD, the infamous "Entry Specific Data."

What we are going to do is create a file in which field JOESD is replaced by definitions for the fields in ORDLNP.

Here's the DDS for QADSPJR2, with JOESD commented out:

A               R QJORDJE2                                             

A                 JOENTL         5S 0       COLHDG('ENT' 'LGTH')      

A                 JOSEQN        10S 0       COLHDG('SEQ' 'NUMBER')    

A                 JOCODE         1A         COLHDG('CODE')            

A                 JOENTT         2A         COLHDG('TYPE')            

A                 JODATE         6A         COLHDG('DATE')            

A                 JOTIME         6S 0       COLHDG('TIME')            

A                 JOJOB         10A         COLHDG('JOB' 'NAME')      

A                 JOUSER        10A         COLHDG('USER' 'NAME')     

A                 JONBR          6S 0       COLHDG('JOB' 'NUMBER')    

A                 JOPGM         10A         COLHDG('PROGRAM' 'NAME')  

A                 JOOBJ         10A         COLHDG('OBJECT' 'NAME')   

A                 JOLIB         10A         COLHDG('LIBRARY' 'NAME')  

A                 JOMBR         10A         COLHDG('MEMBER' 'NAME')   

A                 JOCTRR        10S 0       COLHDG('COUNT/' 'RRN')    

A                 JOFLAG         1A         COLHDG('FLAG')            

A                 JOCCID        10S 0       COLHDG('COMMIT' 'CYCLE ID')

A                 JOUSPF        10A         COLHDG('USER' 'PROFILE')  

A                 JOSYNM         8A         COLHDG('SYSTEM' 'NAME')   

A                 JOINCDAT       1A         COLHDG('INCOMPLETE' 'DATA')

A                 JOMINESD       1A         COLHDG('MINIMIZED' 'ESD') 

A                 JORES         18A         COLHDG('RESERVED')        

*************     JOESD        100A         COLHDG('SPECIFIC' 'DATA') 

Using your favorite editor, be it SEU, WDSc, RDi, or something else entirely, add the DDS field definitions for ORDLNP to the end:

*************     JOESD        100A         COLHDG('SPECIFIC' 'DATA')

A                 ORDNUM         7P 0                                

A                 LINE           3P 0                                

A                 SKU            5P 0                                

A                 ORDDUE          L                                  

A                 QTY            3P 0                                

A                 OFFER          2S 0                                

Then compile this to create a new file named XORDLNP. I created my expanded file in library LENNONS1.

Now we simply have to copy QTEMP/WKORDLNP to XORDLNP using the *NOCHK option, and the JOESD data will be mapped to fields:

CPYF FROMFILE(QTEMP/WKORDLNP) TOFILE(LENNONS1/XORDLNP) MBROPT(*REPLACE) FMTOPT(*NOCHK)

Query XORDLNP and scroll to the right, and all the fields are now nicely visible, as in Figure 4:

Figure 4: DSPJRN output now shows data mapped to fields.

That wasn't too hard, was it? You can do this for any file. All you need is the DDS for QADSPJR2, which I have provided, and, of course, you need the DDS for the journaled file.

An Easier Way

If you want to make this whole process much more convenient, let me suggest the freeware command EXPJRNE, Export Journal Entries, provided by Thomas Raddatz. This really smart tool saves you from having to worry about creating DDS as I did above. You don't have to execute the DSPJRN command either, or the CPYF. It does it all for you. You don't even need to know which journal is being used. Simply tell EXPJRNE the physical file, and it will create the output file and copy the journal records to it.

A prompted EXPJRNE command to do what I did above looks like this:

EXPJRNE/EXPJRNE OBJ(LENNONS1/ORDLNP) FROMDATE(120909) FROMTIME(000000) TO DATE(120909) TOTIME(235959) OUTFILE(QTEMP/XORDLNP) CRTFILE(*YES)         

There are additional options, but this gives you a flavor of how easy it is to use.

You need to download and install EXPJRNE, but after that it is straightforward command execution. And the price is right.

Take Advantage

There is a wealth of really useful information in journal entries. The data is not hard to mine, so don't let it go to waste.

Sam Lennon is an analyst, developer, consultant and IBM i geek. He started his programming career in 360 assembly language on IBM mainframes, but moved to the AS400 platform in 1991 and has been an AS400/iSeries/i5/IBM i advocate ever since.