Monday, March 19, 2018

Creating a list of journaled files, improved

finding which files are journaled using sql object-statistics

In my previous post about making a list of files in a library that are journaled I stated that I could not find this information in any Db2 for i view or table function.

Krister Karlsson brought to my attention that the information is available in the OBJECT_STATISTICS table function. Looking at the documentation from IBM it would appear to have been added in the previous round of Technology Refreshes.

Rather than using the Display Object Description command, DSPOBJD, I can get the information I desire from a simple Select statement.

SELECT OBJLIB,OBJNAME,JOURNALED,JOURNAL_LIBRARY,
       JOURNAL_NAME,JOURNAL_IMAGES,OMIT_JOURNAL_ENTRY
  FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','*FILE')) AS A
 WHERE JOURNALED = 'YES'

This gives me:

OBJLIB  OBJNAME     OBJTYPE  JOURNALED  JOURN00002  JOURN00001
MYLIB   AVAILSPACE  *FILE       YES     QRECOVERY   QDBJRNCOLM

JOURN00003  OMIT_00001
  *AFTER     *NONE

This means I do not have to have a CL program, as I did before, I can just run the following statement in my favorite SQL interface to generate a table/file.

CREATE TABLE MYLIB.JRNFILE AS
(SELECT OBJLIB,OBJNAME,OBJTYPE,
        JOURNAL_LIBRARY AS JRNLIB,
        JOURNAL_NAME AS JRNNAME,
        JOURNAL_IMAGES AS JRNIMG,
        OMIT_JOURNAL_ENTRY AS OMITJRNENT
   FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*FILE')) AS A
  WHERE JOURNALED = 'YES')
WITH DATA

And the created table/file looks like:

OBJLIB  OBJNAME     OBJTYPE  JRNLIB     JRNNAME     JRNIMG  OMITJRNENT
MYLIB   AVAILSPACE  *FILE    QRECOVERY  QDBJRNCOLM  *AFTER   *NONE

Which is a lot faster that using the DSPOBJD command, and then creating my table of journaled files from it.

 

You can learn more about the Db2 for i OBJECT_STATISTICS table function from the IBM website here.

 

This article was written for IBM i 7.3 TR3 and 7.2 TR7.

2 comments:

  1. Hi Simon, the earlier article was good work around too. I do understand that the OBJECT_STATISTICS is the best way, but the work around can also hang along. Hope the old link is still available in some archived form.

    ReplyDelete
  2. The link to the original post is the first link in this post.

    ReplyDelete

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.