Who’s the Scoundrel That Corrupted My Database?
July 14, 2010 Dear Guru
Yes, I’m talking to you. You who read this newsletter are gurus. Devoted and profoundly resourceful reader Sarah showed me a tip that I’ve got to share with you. Her technique makes it easy to find out who’s doing what with the database.
Sometimes a user asks Sarah to determine who changed something in a file. The journal tells all. The problem is that journals are not easy to read. The data is stored in one big long field, called entry-specific data. However, Sarah has a way to break down the entry-specific data as it is defined in the database. Here’s how it’s done.
Assume that physical file CUSTOMERS is journaled into journal MYJRN. Create a file–let’s call it JRNCUST–into which the journaled data will be copied. Here’s the DDS for JRNCUST.
A R QJORDJE A JOENTL 5S 0 A JOSEQN 10S 0 A JOCODE 1A A JOENTT 2A A JODATE 6A A JOTIME 6S 0 A JOJOB 10A A JOUSER 10A A JONBR 6S 0 A JOPGM 10A A JOOBJ 10A A JOLIB 10A A JOMBR 10A A JOCTRR 10S 0 A JOFLAG 1A A JOCCID 10S 0 A JOINCDAT 1A A JOMINESD 1A A JORES 6A A COMPANYNO 3P 0 A CUSTOMERNO 5P 0 A CUSNAM 20A A BILLSTREET 16A A BILLCITY 12A A BILLSTATE 2A A BILLZIP 10A A SHIPSTREET 16A A SHIPCITY 12A A SHIPSTATE 2A A SHIPZIP 10A A TYPE 1A
The first part of the file–everything from JOENTL to JORES–has the journaling information. The entry-specific data is replaced with the fields from the database file: COMPANYNO thru TYPE.
To load the file, use the Display Journal (DSPJRN) command.
DSPJRN JRN(MYLB/MYJRN) FILE((MYLB/CUSTOMERS)) ENTTYP(PT DL UB UP) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE1) OUTFILE(MYLB/JRNCUST)
Voilà! Use your favorite query tools to view the data in human-readable format.