Wednesday, March 14, 2018

Creating a list of journaled files

make list of files being journaled

"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 PGMSDHTST3.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:

 

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

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.