Creating a list of journaled files

"How do we know what files are journaled in library x?" one of the programmers asked me. A straight forward question, but where to find the information?

I could not find reference to journals in either the Db2 for i Views SYSTABLES or SYSTABLESTAT.

I could find a fields for journal information using the Display File Description command, DSPFD, but only in the types of information that I can display or print, not in those that create an outfile.

I might have used an API, QUSLOBJ with format type OBJD0500 or QUSROBJD format OBJD0400, if I only wanted to know about one file, but I want a "list" of files.

This leaves the Display Object Description command, DSPOBJ. In its basic or full detail outfile there are several fields to do with journals:

• ODJRNM Journal name
• ODJRLB Journal library
• ODJRIM Journal images: 0=*AFTER, 1=*BOTH
• ODJREN Journal entries omitted: 0=*NONE, 1=*OPNCLO
• ODJRCN Journal century: 0=19 or 1=20
• ODJRDT Journal date: on my system the date is in *MDY format
• ODJRTI Journal time

I can put the DSPOBJD command in a program so I can do other things while it runs. This example will only produce the "list" for one library, I am sure you can all modify it to do the same for multiple libraries.

01 PGM 02 DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1) 03 RTVJOBA TYPE(&JOBTYPE) /* Job is interactive */ 04 IF COND(&JOBTYPE = '1') THEN(DO) 05 SBMJOB CMD(CALL PGM(TESTPGM1)) JOB(JRNFILE) 06 RETURN 07 ENDDO 08 DSPOBJD OBJ(PRODLIB/*ALL) OBJTYPE(*FILE) + OUTPUT(*OUTFILE) OUTFILE(QTEMP/WDSPOBJD) 09 RUNSQL SQL('CREATE TABLE MYLIB.JRNFILE AS + 10 (SELECT ODLBNM,ODOBNM,ODOBAT,+ 11 ODJRLB,ODJRNM + 12 FROM QTEMP.WDSPOBJD + 13 WHERE ODJRNM <> '' '' + 14 ORDER BY ODJRLB,ODJRNM,ODLBNM,ODOBNM) + 15 WITH DATA') + 16 COMMIT(*NC) 17 ENDPGM

This is an example of a program that submits itself to batch.

Line 3: By using the Retrieve Job Attributes command, RTVJOBA, I retrieve job type to determine if it running interactively or in batch.

Lines 4 - 7: If the retrieved job type is '1' then the job is running interactively. I submit the job to batch, line 5, and then quit the program using the RETURN command, line 6.

The rest of this program will only be performed if it is running in batch.

Line 8: I am running the DSPOBJD command for all the files in the library PRODLIB, and directing the output to an outfile in QTEMP. Alas, I cannot any more specific that file, so the output file could contain display files, printer files, DDM files, etc.

Lines 9 - 16: Regular readers will recognize one of my favorite commands, Run SQL Statement, RUNSQL. Here I am creating a file on the fly, creating and filling the file in one statement.

Lines 10 and 11: The table/file will contain the following columns/fields:

• ODLBNM File library
• ODOBNM File
• ODOBAT Object attribute: should be PF or LF
• ODJRLB Journal library
• ODJRNM Journal

Line 13: I only want the rows/records that have a journal name. As this is a CL command I need to two apostrophes so they will be translated to single one when the command in run.

Line 14: I want to have the "list" in journal order.

Line 15: I need the WITH DATA so the table will be created with the data in it.

The results of this program will look like:

Library Object Object Journal Journal Attribute Library Name PRODLIB AAACDE PF JRNLIB JOURNAL1 PRODLIB AAACDE0 LF JRNLIB JOURNAL1 PRODLIB ABCREF PF JRNLIB JOURNAL1 PRODLIB ABCREF0 LF JRNLIB JOURNAL1 PRODLIB ABEHWR PF JRNLIB JOURNAL1

I was mistaken, I can get information about which files are journaled using Db2 for i.

Having made a list of all the files in a library that are journaled, what about the other way around? A list of the files that are being journaled by journal.

Fortunately I can get this information from the Work with Journal Attributes command, WRKJRNA, when I direct its output to an outfile. The fields I am interested in are:

• QJOJOURNAL Journal name
• QJOJRNLIB Journal library
• QJOLIBRARY File library
• QJOOBJECT File
• QJOFILETYP File type: PF or LF

I want a file that contains all of the files that are journaled by journals in JRNLIB. I am going to put this in a program that submits itself to batch.

01 PGM 02 DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1) 03 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 04 DCLF FILE(QTEMP/WDSPOBJD) 05 RTVJOBA TYPE(&JOBTYPE) /* Job is interactive */ 06 IF COND(&JOBTYPE = '1') THEN(DO) 07 SBMJOB CMD(CALL PGM(TESTPGM2)) JOB(JRNFILE) 08 RETURN 09 ENDDO 10 DSPOBJD OBJ(JRNLIB/*ALL) OBJTYPE(*JRN) + OUTPUT(*OUTFILE) OUTFILE(QTEMP/WDSPOBJD) 11 DOWHILE COND(&LOOP) 12 RCVF 13 MONMSG MSGID(CPF0864) EXEC(LEAVE) 14 WRKJRNA JRN(&ODLBNM/&ODOBNM) + OUTPUT(*OUTFILE) + DETAIL(*JRNFILE) + OUTFILE(QTEMP/WWRKJRNA) + OUTMBR(*FIRST *ADD) 15 ENDDO 16 RUNSQL SQL('CREATE TABLE MYLIB.FILEJRN AS + 17 (SELECT QJOJOURNAL,QJOJRNLIB,QJOLIBRARY,+ 18 QJOOBJECT,QJOFILETYP + 19 FROM QTEMP.WWRKJRNA + 20 ORDER BY QJOJRNLIB,QJOJOURNAL) + 21 WITH DATA') + 22 COMMIT(*NC) 23 ENDPGM

This program is similar to the previous one, except...

Line 4: I am declaring a file as I will be reading it later in the program.

Line 10: I am using the DSPOBJD to list all of the journals, object type *JRN, into the output file WDSPOBJD.

Lines 11 – 15: I have this Do loop so I will read all of the records from the outfile produced by the DSPOBJD command.

Line 12: As I have defined only one file in this program I can just use the Receive file command, RCVF, without the file id.

Line 13: When the end of the file is encounter this line will leave the Do loop.

Line 14: The reason I needed to make a list of all the journals is that the WRKJRNA command does not allow *ALL as the name of the journal. I have to run this command once for each journal.

Line 16: When all the journals have been processed I create my own table/file using the RUNSQL command.

The end result is a file that looks like:

Journal Library Library Object Type of name name name name file JOURNAL1 JRNLIB AALIB YA0JCPP PF JOURNAL1 JRNLIB AALIB YA0JCPP0 LF JOURNAL1 JRNLIB AALIB YA0JCPP1 LF

Using these two methods my colleague can answer his own question about which files are journaled.

 

You can learn more about this from the IBM website:

• DSPOBJD command
• WRKJRNA command

 

This article was written for IBM i 7.3, and should work for earlier releases too.