Tuesday, December 29, 2020

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

view objectts being journaled by a 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_TYPEPHYSICAL, 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.

4 comments:

  1. Thank you, I use the system files, SYSCOLUMNS etc. when I am looking for something, this is helpful to be able to see journaled objects.

    ReplyDelete
  2. Simon, thanks for sharing. A very useful tool and examples.

    ReplyDelete
  3. Harold Adolfo Mendoza AvendañoSeptember 11, 2021 at 1:18 PM

    Simón que bueno gracias

    ReplyDelete
  4. Very much useful

    Thanks

    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.