Pages

Wednesday, December 30, 2020

List journal inheritance rules with SQL

sql view to view journal inheritance

Having brought up journal inheritance in my previous post I thought it would be wise to follow that up with this post. This post describes how to view the journal inheritance rules in your IBM i partition.

Like the JOURNALLED_OBJECTS SQL View this View JOURNAL_INHERIT_RULES was introduced as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9. It gives a list of all the libraries on your partition with the journal inheritance rules. The only library on the IBM i partition I use for writing these posts that has any inheritance rules is the library I wrote about in my previous post, MYLIB2.

Being a SQL View the statement to retrieve results from it is very simple. In its simplest form it is just:

SELECT * FROM QSYS2.JOURNAL_INHERIT_RULES

If I do a count of the number of libraries in the JOURNAL_INHERIT_RULES View:

SELECT COUNT(*) AS "Count"
  FROM QSYS2.JOURNAL_INHERIT_RULES

Count
-----
  138

This is less than the total number of libraries in the partition. I can get a count of number of libraries from the SYSSCHEMAS View:

SELECT COUNT(*) AS "Count"
  FROM QSYS2.SYSSCHEMAS

Count
-----
  242

I can see which libraries are not in the JOURNAL_INHERIT_RULES View with the following statement:

SELECT SYSTEM_SCHEMA_NAME FROM QSYS2.SYSSCHEMAS
 WHERE SYSTEM_SCHEMA_NAME NOT IN 
         (SELECT LIBRARY_NAME FROM QSYS2.JOURNAL_INHERIT_RULES)
 ORDER BY SYSTEM_SCHEMA_NAME

I am not going to list these libraries as they are probably different on your IBM i partition. But I would check which ones are "missing" from the JOURNAL_INHERIT_RULES View in your partition.

What does the JOURNAL_INHERIT_RULES View show? Let me list my libraries:

SELECT * FROM QSYS2.JOURNAL_INHERIT_RULES
WHERE LIBRARY_NAME LIKE 'MYLIB%'

By using the WHERE clause with the LIKE allows me to return all the library that start with "MYLIB".

The results look like:

LIBRARY NAME  JOURNALED  IASP_NUMBER  IASP_NAME  ORDINAL_POSITION
------------  ---------  -----------  ---------  ----------------
MYLIB         NO                   1  *SYSBAS                   1
MYLIB2        YES                  1  *SYSBAS                   1


OBJECT_TYPE  OPERATION  RULE_ACTION  NAME_FILTER  JOURNAL_IMAGES
-----------  ---------  -----------  -----------  --------------
*ALL         *ALLOPR    *INCLUDE     *ALL         *OBJDFT
*FILE        *CREATE    *INCLUDE     *ALL         *BOTH


OMIT_JOURNAL_ENTRY  REMOTE_JOURNAL_FILTER
------------------  ---------------------
*OBJDFT             *OBJDFT
*OPNCLO             *OBJDFT

It is obvious what the LIBRARY column contains, so I am only going to list the columns that I think need further explanation. For a more detailed explanation go to IBM's documentation, that is linked to at the bottom of this post.

  • JOURNALED:  Journaling status of the library
  • ORDINAL_POSITION:  A unique assigned number for each journaling rule added to this library
  • OPERATION:  The operations performed on the object type for journaling to be started
    • *ALLOPR All of the above
    • *CREATE Journaling started for objects created in the library
    • For the other options see the IBM documentation
  • RULE_ACTION:  Whether objects that match the object type and operation in this rules will inherit the journal's rules
  • NAME_FILTER:  Object names that are identified by this rule. *ALL is that the rule applies to all objects that match the criteria

While I can see the value of this information. I am sure how often I would use this.

 

You can learn more about the SQL View JOURNAL_INHERIT_RULES from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

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.