Journal information using SQL

It is Monday morning and I receive an email from my superior asking about the journals on the production IBM i. How can there be so many? How many are active? How much space do they take up? How many are remote journals? I need a quick and easy way to get to information about the journals. There is a Retrieve Journal Information API I could use. Fortunately there is a SQL View, added in IBM i 7.2, that does the basically the same thing as the API, but it is a heck of a lot easier to get the information from.

Those of you who are regular readers of this blog will not be surprised to learn that the JOURNAL_INFO View is located in the library QSYS2. It contains a lot more information that I have been asked to provide. Rather than list all of the columns I am going to refer you to the relevant page in the IBM's KnowledgeCenter here.

To get the information for my superior I am going to use the following columns:

Long name	Short name	Description
JOURNAL_NAME	JRNNAME	Journal name
JOURNAL_LIBRARY	SYS_DNAME	Library that contains the journal
JOURNAL_STATE	STATE	Are journal entries being sent to the journal
JOURNAL_TYPE	TYPE	Is the journal local or remote
ATTACHED_JOURNAL_RECEIVER_NAME	ATTRCVNAME	Name of the attached journal receiver
ATTACHED_JOURNAL_RECEIVER_LIBRARY	ATTRCVLIB	Name of the library the journal receiver is in
NUMBER_JOURNAL_RECEIVERS	NUMJRNRCV	Total number of journal receivers associated with this journal
TOTAL_SIZE_JOURNAL_RECEIVERS	SIZJRNRCV	Total size of the journal receivers associated with this journal
SOURCE_RECEIVER_SYSTEM	SRCRCVSYS	Name of remote journal

And now to answer her questions:

How can there be so many?

I cannot answer can answer the question as it is asked as I have no idea who or why most of these journals were created. But what I can do is to give a count of the number of journals in each library:

SELECT JOURNAL_LIBRARY,COUNT(*) FROM QSYS2.JOURNAL_INFO GROUP BY JOURNAL_LIBRARY ORDER BY JOURNAL_LIBRARY

This gives me the list:

JOURNAL_LIBRARY COUNT ( * ) LIB1 1 LIB2 2 LIB3 1 LIB4 3 LIB6 13

As there are no journals in the library LIB5 it does not appear in the results.

How many are active?

It would not surprise me if a large number of the journals are not active. This summary select looks complicated, but it is really not:

01 SELECT JOURNAL_LIBRARY, 02 COUNT(*) AS TOTAL, 03 SUM(CASE WHEN STATE = '*ACTIVE' THEN 1 ELSE 0 END) AS ACTIVE, 04 SUM(CASE WHEN STATE <> '*ACTIVE' THEN 1 ELSE 0 END) AS INACTIVE 05 FROM QSYS2.JOURNAL_INFO 06 GROUP BY JOURNAL_LIBRARY 07 ORDER BY JOURNAL_LIBRARY

Line 2: The second column is to be a count of all the rows, that I am giving the column heading "TOTAL".

Line 3: This looks complicated, but it is not. Whenever a row is found where the value of STATE is '*ACTIVE' the column value is 1. If it is not then the column value is zero. As I am using the SUM function this adds one to the value whenever STATE = '*ACTIVE'. I am giving this column the heading "ACTIVE".

Line 4: The same logic as line 3, except I am comparing STATE to not equal to '*ACTIVE'. I am naming this column heading "INACTIVE", as that is the opposite of active.

The information selected looks like:

JOURNAL_LIBRARY TOTAL ACTIVE INACTIVE LIB1 1 0 1 LIB2 2 1 1 LIB3 1 0 1 LIB4 3 0 3 LIB6 13 10 3

If I wanted to see the detail for, let's say, LIB2 I would just use:

SELECT JOURNAL_LIBRARY,JOURNAL_NAME,STATE FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY = 'LIB2'

Which gives me a detailed list of the journals in LIB2:

JOURNAL_LIBRARY JRNNAME STATE LIB2 JRN10 *ACTIVE LIB2 JRN11 *INACTIVE

How much space do they take up?

I know this is my superior's main concern. Here is my select to get the top 10 biggest journals:

SELECT JOURNAL_LIBRARY AS LIBRARY, JOURNAL_NAME, TOTAL_SIZE_JOURNAL_RECEIVERS, ATTACHED_JOURNAL_RECEIVER_NAME, ATTACHED_JOURNAL_RECEIVER_LIBRARY, NUMBER_JOURNAL_RECEIVERS, STATE FROM QSYS2.JOURNAL_INFO ORDER BY TOTAL_SIZE_JOURNAL_RECEIVERS DESC FETCH FIRST 10 ROWS ONLY

Which gives me:

LIBRARY JRNNAME SIZJRNRCV ATTRCVNAME ATTRCVLIB NUMJRNRCV STATE LIB7 JRN70 22,074,544 JRN700219 LIB7 136 *ACTIVE LIB7 JRN71 4,763,544 JRN711095 LIB7 186 *ACTIVE LIB8 JRN80 3,728,232 JRN806778 LIB8 1,638 *ACTIVE LIB8 JRN81 3,401,376 JRN816893 LIB8 1,628 *ACTIVE LIB7 JRN72 3,306,284 JRN727037 LIB7 1,575 *ACTIVE LIB9 JRN90 2,920,260 JRN900065 LIB9 66 *ACTIVE LIB8 JRN82 2,758,900 JRN822790 LIB8 1,359 *ACTIVE LIB7 JRN73 2,653,664 JRN737983 LIB7 1,164 *ACTIVE LIB7 JRN74 2,433,984 JRN741082 LIB7 1,345 *ACTIVE LIB7 JRN75 2,278,428 JRN750190 LIB7 1,007 *ACTIVE

How many are remote journals?

This time I am going to include the column SRCRCVSYS, which gives me the name of the server the remote journal is on. In this select I am only interested in the active journals:

SELECT JOURNAL_LIBRARY, JOURNAL_NAME, TYPE,STATE, SOURCE_RECEIVER_SYSTEM FROM QSYS2.JOURNAL_INFO WHERE TYPE = '*REMOTE' AND STATE = '*ACTIVE' ORDER BY JOURNAL_LIBRARY,JOURNAL_NAME

Which gives me:

JOURNAL_LIBRARY JRNNAME TYPE STATE SRCRCVSYS LIB10 XSXJRN1 *REMOTE *ACTIVE OTHERSYS LIB10 XSXJRN2 *REMOTE *ACTIVE OTHERSYS

 

There is whole lot more you can do with the information from this View than my examples. I just wanted to show some simple sample code to illustrate how you can use it.

 

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

 

This article was written for IBM i 7.2.