Using SQL to get information from Job Logs

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 21 CPF2105 ESCAPE EXCEPTION HANDLED 40 23 CPF2105 ESCAPE EXCEPTION HANDLED 40 24 CPA0702 INQUIRY 99 25 - REPLY VALIDITY CHECKED 99 26 CEE9901 ESCAPE EXCEPTION HANDLED 30 TIMESTAMP 2015-04-05-20.13.19.994456 2015-04-05-20.13.19.994569 2015-04-05-20.13.19.994816 2015-04-05-20.13.22.508821 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:

• SQL: JOBLOG_INFO

 

This article was written for IBM i 7.2