List journal inheritance rules with SQL

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.