analyzing data from DSPJRN journaled files in IBM i

Question

How to analize extracted data from DSPJRN journaled Database files in IBM i?. Output extracted from journal with command DSPJRN is composed of metadata and a block of data in native format (a stream of text and binary data). Even though some info could be extracted, I suppose there are some rules to read them, but out of my knowledge. Not seen info in documents, yet. Anyone working with this data?

Answer 1

Documentation for the journal entry information

The meta data is easy, since it's in standard columns.

It's the variable length entry specific data that's problematic.

The basics aren't to difficult to deal with, assuming no nullable columns in the file and that the journal is configure not to minimize data. Then you can simply build a table with the metadata columns + the columns from the journaled table and simply copy the data to it; like so:

1. DSPJRN JRN(MYJRN) FILE((MYPF)) ENTTYP(*RCD) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE1) OUTFILE(JRNOUT) ENTDTALEN(*CALC)
2. Build the table, I use SQL CREATE TABLE MYJRNDATA as (SELECT <meta columns>,<PF columns> FROM JRNOUT, MYPF) with no data
3. CPYF FROMFILE(JRNOUT) TOFILE(MYJRNDATA) FMTOPT(*NOCHK) to copy the journal data to it.

Moving beyond the basics get complicated.

There are a few commercial tools that you can use, I use the DBUJRN command that's part of Prodata's DBU utility.

There's also at least one open source tool, Export Journal Entries that would be where I'd start if I wanted to roll my own.