Wednesday, June 3, 2015

Using SQL to get information from Job Logs

getting job log data using sql using joblog_info instead of rcvmsg

I know I have written a lot recently about SQL, but IBM has added a lot of great views that allow you to access information easily. A good example is today's post: how to get information from a job log using SQL. Prior to this if you need to get information from the job log you had to use the RCVMSG command or the "List Job Log Messages" API, QMHLJOBL. Now I can get the information I want just using a SQL Select statement.

The JOBLOG_INFO table function was added to IBM i 7.2 and 7.1 TR9. It returns one row for each job log message, and what I really like about it is I can choose what information to return by using a WHERE clause in the Select statement.

I can simply go into the Interactive SQL screen, STRSQL, and type to list the job log for my current job, and press Enter:

  select * from table(qsys2.joblog_info('*')) a

As I want the information for my current job I can use the asterisk (*) as I would do using the "Display Job" command, DSPJOB. I also have to include the "A" following the table part of the statement. If I omit it the statement will not execute.

The output looks like:

ORDIN00001   MESSAGE_ID  MESSA00001     MESSA00002              SEVERITY
         1    CPF1124    INFORMATIONAL                                0
         2    -          REQUEST                                      0
         3    -          REQUEST                                      0
         4    CPD0030    DIAGNOSTIC                                  30
         5    CPF0001    DIAGNOSTIC                                  30

There are a lot more other columns to the left of these. I am not going to list them all as they can be found in IBM's documentation that I will give a link to at the bottom of this post.

Below are what I consider the most useful columns to select:

What is the kind of information I would want to know about a view?

Column Description
ORDINAL_POSITION Unique number assigned to each row (job log entry).
MESSAGE_ID Id of the message received.
MESSAGE_TYPE Type of message. I will give more details in a separate table below.
MESSAGE_SUBTYPE If message type is NOTIFY or ESCAPE:
  • EXCEPTION HANDLED
  • EXCEPTION NOT HANDLED
If message type is REPLY:
  • FROM EXIT PROGRAM
  • FROM SYSTEM REPLY LIST
  • MESSAGE DEFAULT USED
  • NOT VALIDITY CHECKED
  • SYSTEM DEFAULY USED
  • VALIDITY CHECKED
For all other message types this will be null.
SEVERITY Severity of the message.
MESSAGE_TIMESTAMP Timestamp when the message occurred.
TO_LIBRARY Library containing the program the received the message.
TO_PROGRAM Program or service program that received the message.
TO_MODULE Module that received the message.
TO_PROCEDURE Procedure that received the message.
TO_INSTRUCTION Instruction that received the message.
MESSAGE_TEXT First level message text.

Some of the above fields are large VARCHAR fields that I use the CAST to make them smaller to fit on a display.

I know many people are unsure what the various message types are, so I will list them below with an explanation taken from IBM's documentation:

Message type Description
Completion (*COMP) Reports the successful completion of a task.
Diagnostic (*DIAG) Describes errors in processes or input data. When an error occurs, a program usually sends an escape message, which causes the task to end abnormally. One or more diagnostic messages can be sent before the escape message to describe the error.
Escape (*ESCAPE) Indicates a condition causing a program to end abnormally, without completing its work.
Exception (*EXCP) Indicates a condition causing a program to end abnormally, without completing its work. An exception message can be either an escape or a notify message. The exception message type and the special value *EXCP are used only with the Receive Program Message (QMHRCVPM) API.
Informational (*INFO) Conveys information without asking for a reply.
Inquiry (*INQ) Conveys information and asks for a reply.
Notify (*NOTIFY) Describes a condition in the sending program requiring corrective action or a reply.
Reply (*RPY) Responds to an inquiry or notify message.
Request (*RQS) Requests a function from the receiving program.
Sender's copy (*COPY) Is a copy of an inquiry or notify message. This copy is kept by the sender of the inquiry or notify message.
Scope (*SCOPE) Specifies a program to run when the program this message is sent to completes. If the message is sent to *EXT the program is to run when the job completes.
Status (*STATUS) Describes the status of work being done by a program.

Let me give a very simple example of how this can be used. Here is a CL program that I am going to run. The file DUMMY does not exist in QTEMP. The first attempt to delete it, line 2, will error and the "Monitor Message" command, MONMSG on line 3, will prevent the program from erroring. The second delete attempt, line 4, is not monitor and the program will error .

01  PGM                         
02  DLTF       FILE(QTEMP/DUMMY)
03  MONMSG     MSGID(CPF0000)
04  DLTF       FILE(QTEMP/DUMMY) 
05 ENDPGM 

I run the program, and answer the CPF2105 message with a "C".

Now I build a SQL Select statement only for the fields I want to display and where the severity of the error is greater or equal to 20. I have used the AS to give each column heading a meaningful name. I have also used the CAST to convert Program name, Procedure, and Message text from VARCHAR to fixed length character fields, and converted the CCSID for the Message text too.

select ordinal_position as seq, 
       message_id as msg_id,
       message_type as msg_type,
       message_subtype as msg_subtype,
       severity,
       message_timestamp as timestamp,
       to_library as library,
       cast(to_program as char(10)) as program,
       to_module as moldule,
       cast(to_procedure as char(20)) as procedure
       to_instruction as instruction,
       cast(message_text as char(200) ccsid 37) as message_text
from table(qsys2.joblog_info('*')) a
where severity >= 20

When I execute the select this is what is displayed (I have removed all other log rows apart from those to do with this program for clarity). The first part shows the Ordinal position, Message id, Message type, Message subtype and the Timestamp:

SEQ   MSG_ID   MSG_TYPE       MSG_SUBTYPE             SEVERITY  TIMESTAMP                 
 21   CPF2105  ESCAPE         EXCEPTION HANDLED            40   2015-04-05-20.13.19.994456
 23   CPF2105  ESCAPE         EXCEPTION HANDLED            40   2015-04-05-20.13.19.994569
 24   CPA0702  INQUIRY                                     99   2015-04-05-20.13.19.994816
 25   -        REPLY          VALIDITY CHECKED             99   2015-04-05-20.13.22.508821
 26   CEE9901  ESCAPE         EXCEPTION HANDLED            30   2015-04-05-20.13.22.509147

The second part contains the columns to do with the program that caused the error with the Program's library, Program, Module, Procedure, and Instruction number.

LIBRARY     PROGRAM     MOLDULE     PROCEDURE             INSTRUCTION
RPGPGM1     TESTCLLE    TESTCLLE    TESTCLLE              300
RPGPGM1     TESTCLLE    TESTCLLE    TESTCLLE              600
-           *EXT                    -                     *N
-           *EXT                    -                     *N
QSYS        QUOCMD                  -                     03B3

The last part displays the Message text for the messages:

MESSAGE_TEXT
Object DUMMY in QTEMP type *FILE not found.
Object DUMMY in QTEMP type *FILE not found.
CPF2105 received by procedure TESTCLLE. (C D I R)
C
Application error.  CPF2105 unmonitored by TESTCLLE at statement 0000000600, instruction X'0000'.

My example has just been for my current job. If I wanted to use the log of another job I would simply use the following:

  select * from table(qsys2.joblog_info('149289/jsmith/qpadev0099')) a

The next time someone tells me that John Smith had an error an hour ago and answered it without telling anyone, I can use this approach to easily and quickly find what it was and see if it was something I need to worry about.

 

You can learn more about this on the IBM website:

 

This article was written for IBM i 7.2

12 comments:

  1. Lots and lots of possibilities here. Like!!!

    ReplyDelete
  2. You can always "print" the joblog into an *OUTFILE and than you can use SQL to access it.

    ReplyDelete
    Replies
    1. You could do. But why do those extra steps when you can use the method I describe above the get the data in one step.

      Delete
  3. While running the sql "select * from table(qsys2/joblog_info('*')) a " I am getting error "JOBLOG_INFO in QSYS2 type *N not found". I am in V6R1M000. please advise.

    ReplyDelete
    Replies
    1. As I mentioned at the top of this post this table was added as part of IBM i 7.1 TR9 and 7.2

      As you are at 6.1 the table, as you have found, is not present on your server.

      Delete
    2. Is there a process we can do to get similar results if we are running on 6.1?

      Delete
    3. If your IBM i is only at 6.1 you will have to use the DSPJOBLOG command.

      Delete
  4. This looks useful, but what if I wanted to use it in a program or command to show a list of possible errors. We would need to be able to change the select statement on the fly, is this possible?

    ReplyDelete
    Replies
    1. I think that you can do what you want if you create your SQL statement in a variable, and then use the EXECUTE IMMEDIATE to execute the statement.

      For example:

      String = 'select * from table(qsys2.joblog_info(''*'')) a' ;
      exec sql EXECUTE IMMEDIATE :String ;

      Delete
  5. Hi,

    On your second example, why can't I find the jobs triggered by other users?

    The only job logs I can see when I use the "*" parameter are joblogs under my user id.

    Am I missing anything?

    ReplyDelete
    Replies
    1. This is probably due to the settings of your user profile.

      Do you have *JOBCTL authority?

      Delete
  6. Missing ending coma:

    cast(to_procedure as char(20)) as procedure

    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.