Tip

Analyzing a journal with SQL

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.