0

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?

Jorge Ubeda
  • 193
  • 5
  • 18

1 Answers1

3

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.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • thanks so much, Charles! I will try your reccomendation on such open source tool. – Jorge Ubeda May 10 '19 at 06:57
  • You can also use the DISPLAY_JOURNAL table function to get the journal entries. See https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/rzajqudfdisplayjournal.htm for details. Once you have this, you can run all types of queries on the journal entries. – jweberhard May 10 '19 at 11:22
  • @jweberhard DISPLAY_JOURNAL doesn't make the variable length data any easier to handle. Arguably it's harder, since SQL doesn't offer a built in way to interpret a set of bytes in the middle of a buffer as a packed,zoned, or integer value. – Charles May 10 '19 at 14:31