Wednesday, February 3, 2016

Journal information using SQL

joblog_info sql view

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.

5 comments:

  1. I have this view in 7.1 also, just an fyi

    ReplyDelete
    Replies
    1. Thank you for the FYI. I am always grateful for information like this.

      Delete
    2. We don't have this view in 7.1

      Delete
  2. I used the new SQL views recently to solve a business issue. A PTF was applied which changed the command defaults on compiles. A quick SQL was able to go thru the entire set of programs to identify programs installed without the correct compile options. There is a treasure chest of information in the new views.

    Some under 7.1 have it and others don't. PTF level is the issue. IBM introduces new (hidden) features with PTF's. Not all of them are supported in future releases.

    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.