This tip helps you to easier analyze journal entries. It will automatically create a physical file with journal fields (date, time, user, program, etc.) and file fields. After file generation you will be able to use SQL commands to analyze data. This tip is especially useful to analyze Data File changes (insert/update/delete).
The components of this tip are:
1. JRNCAB, it's a DDS of journal output
2. CL program JRN
3. SQL command into a source file member
1. CRTLIB JRNANZ
2. CRTSRCPF FILE(JRNANZ/SOURCES)
Put all sources in this file.
3. JRNCAB DDS. Create a member of Type PF and write the following lines:
0002.00 A R RJRN
0003.00 A JOENTL 5S 0 TEXT('Length of entry')
0004.00 A JOSEQN 10S 0 TEXT('Sequence number')
0005.00 A JOCODE 1A TEXT('Journal Code')
0006.00 A JOENTT 2A TEXT('Entry Type')
0007.00 A JODATE 6A TEXT('Data of entry')
0008.00 A JOTIME 6S 0 TEXT('Time of entry')
0009.00 A JOJOB 10A TEXT('Name of job')
0010.00 A JOUSER 10A TEXT('Name of user')
0011.00 A JONBR 6S 0 TEXT('Number of job')
0012.00 A JOPGM 10A TEXT('Name of Program')
0013.00 A JOOBJ 10A TEXT('Name of Objet')
0014.00 A JOLIB 10A TEXT('Objets library')
0015.00 A JOMBR 10A TEXT('Name of Member')
0016.00 A JOCTRR 10S 0 TEXT('Count or relative ')
0017.00 A JOFLAG 1A TEXT('Flag: 1 or 0')
0018.00 A JOCCID 10S 0 TEXT('Commit cycle identifier')
0019.00 A JOUSPF 10A TEXT('User Profile')
0020.00 A JOSYNM 8A TEXT('System Name')
0021.00 A JORES 20A TEXT('Not used')
4. Source of JRN CL program:
PGM PARM(&FILE &LIB &JRN &JRNLIB)
DCL &FILE *CHAR 10
DCL &LIB *CHAR 10
DCL &JRN *CHAR 10
DCL &JRNLIB *CHAR 10
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
DSPFFD FILE(&LIB/&FILE) OUTPUT(*OUTFILE) +
OUTFILE(JRNANZ/FIELDS)
CPYF FROMFILE(JRNANZ/SOURCES) +
TOFILE(JRNANZ/SOURCES) FROMMBR(JRNCAB) +
TOMBR(&FILE) MBROPT(*REPLACE)
OVRDBF FILE(SOURCES1) TOFILE(JRNANZ/SOURCES) MBR(&FILE)
RUNSQLSTM SRCFILE(JRNANZ/SOURCES) SRCMBR(SQL) +
COMMIT(*NONE)
CHGPFM FILE(JRNANZ/SOURCES) MBR(&FILE) SRCTYPE(PF)
DLTF FILE(JRNANZ/&FILE)
MONMSG MSGID(CPF0000)
CRTPF FILE(JRNANZ/&FILE) SRCFILE(JRNANZ/SOURCES) +
SRCMBR(&FILE) SIZE(*NOMAX)
DSPJRN JRN(&JRNLIB/&JRN) FILE((&LIB/&FILE)) +
ENTTYP(*RCD) OUTPUT(*OUTFILE) +
OUTFILFMT(*TYPE2) OUTFILE(JRNANZ/TMP)
CPYF FROMFILE(JRNANZ/TMP) TOFILE(JRNANZ/&FILE) +
MBROPT(*REPLACE) FMTOPT(*NOCHK)
GOTO CMDLBL(QUIT)
ERROR: CALL PGM(QMHRSNEM) PARM(' ' X'00000000')
MONMSG MSGID(CPF0000)
QUIT:
ENDPGM
5. Source of SQL command. Create a member called SQL, Type TXT and put the following lines:
insert into jrnanz/SOURCES1
SELECT 1 , 1 ,' A '|| WHFLDI||' '||
case when whfldd=0 then substr(digits(whfldb) , 3 , 3)
else ' '||digits(whfldd) end
||whfldt||
case when whfldp=0 then ' '
else substr(digits(WHFLDp) , 1 , 2) end
||' TEXT('||''''|| trim(left(WHFTXT , 28))||''''||')'
from jrnanz/fields
6. After the program creation you can run it with the following parameter:
-File Name
-File Library
-Journal Name
-Journal Library
Ex: CALL PGM(JRNANZ/JRN) PARM(FI054B MAXDD ADIJRN ADIJRN)
Now you can use the command STRSQL to analyze results.
Ex: SELECT * FROM JRNANZ/FI054B
You can select changes in your files by date, program, type of change (update/delete/insert), user, data and much more. You can select all programs or users that have changed one specific field to a specific value.
This was first published in November 2001