List all of objects that are being journaled by a specific journal

While this post is not going to be one of my longer ones, it is going to demonstrate another useful addition to Db2 for i within the latest round of Technology Refreshes, IBM i 7.2 TR3 and 7.3 TR9.

I often want to know which objects are being journaled by a particular journal. In the past I gave an example of how to do this using the Work Journal Attribute command, WRKJRNA. But this addition makes it so much easier.

The new SQL View JOURNALED_OBJECTS give a list of all objects that are related to the journal. This is not just the files, it also returns names of the journal receivers. This is something that my prior example did not do.

As this is a View I can list all of the objects that are involved with all journals:

SELECT * FROM QSYS2.JOURNALED_OBJECTS

The number of results returned from that statement is, in my opinion, information overload. What I can do is to list all of the objects involved with one of the more popular journals QSQJRN in the library QSYS2:

SELECT * FROM QSYS2.JOURNALED_OBJECTS WHERE JOURNAL_LIBRARY = 'QSYS2' AND JOURNAL_NAME = 'QSQJRN'

I am not going to show you the results from that statement as it is too much data to fit here. But I do recommend you run it for yourself, in ACS's "Run SQL Scripts", and look at the information it returns.

For my example I am going to use a journal that was created when I created a new schema I am calling MYLIB2:

CREATE SCHEMA MY_SQL_SCHEMA FOR "MYLIB2"

A SQL Schema is just a library to the rest of the IBM i operating system. I can either look if the Schema/library has been created using the Work Library command, WRKLIB:

WRKLIB MYLIB2

But the Schema name is not displayed.

I can get both the library and Schema names with SQL with the following statement:

SELECT SYSTEM_SCHEMA_NAME,SCHEMA_NAME FROM QSYS2.SYSSCHEMAS WHERE SYSTEM_SCHEMA_NAME = 'MYLIB2' SYSTEM_SCHEMA_NAME SCHEMA_NAME ------------------ ------------- MYLIB2 MY_SQL_SCHEMA

The system library name is in the SYSTEM_SCHEMA_NAME column and the Schema name is in SCHEMA_NAME.

When the Schema is created it also creates a whole lot of SQL Views, a journal, and several journal receivers. I need to create a post describing what these Views are, but for now I am just going to ignore them. Whenever I create a Table in this Schema/library the new Table will be automatically journaled by the journal in the schema/library.

This Schema is where my example Temporal Table "lives". For a temporal table to work it, and its history table, have to be journaled. Therefore, I know if I use the JOURNALED_OBJECTS View I will see the name of the journal and the journal receivers used:

SELECT OBJECT_TYPE, OBJECT_LIBRARY, OBJECT_NAME, FILE_TYPE, JOURNAL_IMAGES, OMIT_JOURNAL_ENTRY, INHERIT, REMOTE_JOURNAL_FILTER FROM QSYS2.JOURNALED_OBJECTS WHERE JOURNAL_LIBRARY = 'MYLIB2' AND JOURNAL_NAME = 'QSQJRN'

I have only included some of the available columns in my results as most of them I am not interested in for this example. I have excluded the columns for the journal name and library as those are used in the statement's WHERE clause. For a full list of the available columns click on the link to IBM's documentation at the bottom of this post.

I have had to split the results into two rows so that it will fit in the width of this page.

OBJECT_TYPE OBJECT_LIBRARY OBJECT_NAME FILE_TYPE ----------- -------------- ----------- --------- *JRNRCV MYLIB2 QSQJRN0001 - *LIB QSYS MYLIB2 - *FILE MYLIB2 TMPRLTAB_H PHYSICAL *FILE MYLIB2 TMPRLTAB PHYSICAL *JRNRCV MYLIB2 QSQJRN0003 - *JRNRCV MYLIB2 QSQJRN0002 - *JRNRCV MYLIB2 QSQJRN0004 - JOURNAL_IMAGES OMIT_JOURNAL_ENTRY INHERIT REMOTE_JOURNAL_FILTER -------------- ------------------ ------- --------------------- - - - - *AFTER *NONE *YES *NO *BOTH *OPNCLO - *NO *BOTH *OPNCLO - *NO - - - - - - - - - - - -

The columns I considered to be the most useful are:

• OBJECT_TYPE
• OBJECT_LIBRARY
• OBJECT_NAME
• FILE_TYPE:  PHYSICAL, LOGICAL, or null if it is not a file
• JOURNAL_IMAGES:  When is the image written to the journal
• OMIT_JOURNAL_ENTRY:
  • *NONE No entries are omitted
  • *OPNCLO Open and close entries are omitted
  • *OPNCLOSYN Open, close, and force entries omitted
  • If the OBJECT_TYPE column is *JRNRCV then this is null
• INHERIT:  Will new objects created, copied, or restored to this library inherit the journal state of the library
• REMOTE_JOURNAL_FILTER:  Is the object that inherit the journal state from the library eligible for remote journaling

I don't think there is much more I can say about this View, except for it is another useful addition to our SQL toolset.

If you want to learn more information abour journals you can use the JOURNAL_INFO View.

 

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

 

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