Discovering remote journals with SQL

I have an ongoing "battle" to keep the journals and their journal receivers under control in the IBM i partitions I am responsible for. Part of this has been to find and report on various features and functions of the journals. A recent addition to SQL, as part of IBM i 7.5 TR1 and 7.4 TR7, has been a View that returns results about the remote journals in a partition.

What is a remote journalling?

Remote journalling, in very simple terms, will send journal transactions to other partitions. I could use it to replicate changes made to file on the master partition to other partitions, and use those to update the remote files and keep all of the copies in synch.

If you have never created a remote journal the steps are simple. If I am creating this all from scratch I would need to:

1. Create a journal receiver on the local partition
2. Create a journal on the local partition, that uses the journal receiver
3. Create the remote journal

Step 1:  To create the journal receiver on the local partition I would use the Create Journal Receiver command, CRTJRNRCV. Below I am creating the simplest of receivers, that has many of the commands defaults:

CRTJRNRCV JRNRCV(MYLIB/TESTRCVR) Journal receiver TESTRCVR created in library MYLIB.

Step 2:  With the journal receiver created I can then create a journal with the Create Journal command, CRTJRN. Again I have used most of commands defaults to make the simplest of journals:

CRTJRN JRN(MYLIB/TESTJRN) JRNRCV(MYLIB/TESTRCVR) MNGRCV(*USER) Journal TESTJRN created in library MYLIB.

Step 3:  And now I can create the remote journal. Before I do I need to be sure that there is a remote database connection between the systems, for this I use the Work with Remote Database Directory Entry command, WRKRDBDIRE. In this example the remote partition is called OTHERSYS, and has a remote database directory entry.

To create the remote journal I use the Add Remote Journal command, ADDRMTJRN. It will create the journal receiver and the journal on the remote partition for me:

ADDRMTJRN RDB(OTHERSYS) SRCJRN(MYLIB/TESTJRN) Remote journal TESTJRN in MYLIB was added.

How can I check if the remote journal was created?

I can use the JOURNAL_INFO View to return information about any journal. Just to be fancy here I wanted to check from the local system for the journal I just created on OTHERSYS. To do this I would use the SQL three part name in my SQL statement:

01 SELECT JOURNAL_LIBRARY,JOURNAL_NAME,JOURNAL_TYPE,JOURNAL_STATE 02 FROM OTHERSYS.QSYS2.JOURNAL_INFO 03 WHERE JOURNAL_LIBRARY = 'MYLIB'

Line 2: This is where I use the three part name. As the remote partition is called OTHERSYS it comes before the library name. I must use the dot as the separator between the partition, library, and View name, rather than a slash ( / ).

The results show that the journal was created as I wanted.

JOURNAL_ JOURNAL JOURNAL JOURNAL LIBRARY _NAME _TYPE _STATE -------- ------- ------- --------- MYLIB TESTJRN *REMOTE *INACTIVE

I could run the same statement on OTHERSYS, just without the partition name in the three-part name, for the same results.

The new View has the easy to remember name REMOTE_JOURNAL_INFO, and is found in the QSYS2 library. Let me just confirm that as we are looking for remote journals the results from this View show the results for the remote journals that were created in this partition using the ADDRMTJRN command.

I always recommend that you see all of the columns in the View to decide which ones are interesting to you, rather than rely on what I find interesting and useful.

-- Return all columns SELECT * FROM QSYS2.REMOTE_JOURNAL_INFO

For this example I found eight columns that were interesting for this story:

-- Return just the ones I am interested in 01 SELECT SOURCE_JOURNAL_LIBRARY,SOURCE_JOURNAL, 02 REMOTE_DATABASE_NAME,REMOTE_JOURNAL_LIBRARY, 03 REMOTE_JOURNAL_RECEIVER_LIBRARY, 04 REMOTE_JOURNAL_STATE,REMOTE_JOURNAL_TYPE 05 FROM QSYS2.REMOTE_JOURNAL_INFO

The results are:

REMOTE_ SOURCE_ REMOTE_ REMOTE_ JOURNAL_ REMOTE_ REMOTE_ JOURNAL SOURCE_ DATABASE JOURNAL_ REMOTE_ RECEIVER JOURNAL JOURNAL _LIBRARY JOURNAL _NAME LIBRARY JOURNAL _LIBRARY _STATE _TYPE -------- ------- -------- -------- ------- --------- -------- -------- MYLIB TESTJRN OTHERSYS MYLIB TESTJRN MYLIB INACTIVE *TYPE1

The columns I chose are:

• SOURCE_JOURNAL_LIBRARY and SOURCE_JOURNAL:  Name of the local journal and the library it is in
• REMOTE_DTATBASE_NAME:  Name of the remote database directory entry for the remote partition
• REMOTE_JOURNAL_LIBRARY and REMOTE_JOURNAL:  Name of the journal and the library it is in in the remote partition
• REMOTE_JOURNAL_STATE:  Journalling has not been started, therefore, the state of the remote journal is inactive
• REMOTE_JOURNAL_TYPE:  This is a *TYPE1 journal. What this means is that a remote journal receiver can be saved and restored into another partition

This View is a wonderful addition to my "toolbox" of SQL tools I use to understand the state of journals and their receivers in the partitions I am responsible for.

 

You can learn more about this from the IBM website:

• REMOTE_JOURNAL_INFO View
• Remote journal setup

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.