Search code examples
db2ibm-midrangeaudit-logging

as400 (IBMi) journals for analysis


IBM i journals seem primarily designed for recovery and replication.

We can display or print contents of journal (DSPJRN) or apply changes back to database in case of a restore (APYJRNCHG).

examples:

Display Journal:

DSPJRN JRN(JRN001) FILE((CUST)) 
JRNCDE((R)) OUTPUT(*OUTFILE) 
OUTFILFMT(*TYPE4) OUTFILE(QTEMP/JRN001_OUT) 
ENTDTALEN(*CALC)    

Apply Journal Changes

APYJRNCHG JRN(journal-library/journal-name) FILE(file-library/file-name) 
RCVRNG(receiver-range-library/receiver-name) FROMENTDT(yymmdd) TOENTDT(yymmdd)

Reading or querying journal data is not very user friendly as 'data' is stuffed into a single field.

How can I use journals to answer questions like "who changed this record last week" or "what programs are frequently updating this file"?


Solution

  • I know you asked for tools, which is contrary to this site rules, but this question can be answered without providing tools as long as you are able to display the contents of a file using any number of system commands or programming languages.

    As you showed, the DSPJRN command lets you retrieve journal entries for a table. The output file contains a number of fields in addition to the single field that contains change data information. So you could simply query the output file resulting from the DSPJRN command that you already gave us. You might need to change the output file library away from QTEMP since that is not visible except to the one job that executed the DSPJRN command. So if your table query tool of choice is a SQL client that resides on a PC, the output file in QTEMP will not be visible to that. But IBM provides a number of query tools even for a green screen session like WRKQRY, or STRSQL, or even DFU. Whatever you are used to.

    So, take a look at that output file again, it contains the information you are looking for, in discrete fields that are easy to query.