A Better SQL Joblog Function for #IBMi

QJOBLOG() SQL Table Function for #IBMi V7R2 and Later

[UPDATE: The code at the end of this article was missing a closing paren on the first line. We have updated it to include that missing symbol. The code is updated.]

IBM shipped the JOBLOG_INFO table function back in IBM i v7r2 as a way to view the joblog from PC-based client access apps, such as RUN SQL Scripts. I use it from time to time to get the joblog for QHTTPSVR jobs and while helping out Clients debug their batch and interactive jobs, remotely. It works well, but it isn't perfect.

I have two problems with JOBLOG_INFO and most IBM i Services ("IBM Services" is the name IBM has given their SQL Functions and Store Procedures objects):

1. I can never intuitively predict the function name; I always have to go look up the proper name except for a few often used functions.
2. The parameter names feel like they were standardized by a COBOL programmer with a short memory. (i.e. too unnecessarily long).

The first issue gets easier with time and experience. However compare this issue to using CL commands. If I wanted to look at the joblog, I could easily guess that the name of the CL command to do so would be DSPJOBLOG; and I'd be right. Then to view the joblog, you'd enter it and bam! the joblog appears with the important information right there. With IBM Services, the path to determining which function you and how to spell it takes a bit longer.

Here's my journey to find the function that returns the JOBLOG as a resultSet:

1. Go to the IBM website. If you're like me, you have it book marked.
2. Sit there for a minute and try to guess under which of the curiously named subtopics your function is documented.
3. Since I've been using this machine since I was 22 and it was called System/38, I know that Work Management services is likely where a joblog function would be classified. So I clicked on it.
4. The closest match is JOB_INFO, it doesn't feel right, but I select it anyway.
5. I realize it isn't right. So I go back to the subTopic index.
6. Now since the job log is a technically a message queue (*MSGQ) I my next guess is that it's under Message Handling, and I select that subtopic.
7. Under Message Handling (on V7R2) I see: HISTORY_LOG_INFO, JOBLOG_INFO, MESSAGE_QUEUE_INFO and REPLY_LIST_INFO.
8. I select JOBLOG_INFO. That's it! Success!

Running JOBLOG_INFO

So I return to IBM ACS Run SQL Scripts (from here out I'll call it RSS) and type in the SELECT statement to show me the joblog.

select * from table(job_log_info());        

Of course it doesn't run! I had the HISTORY_LOG_INFO (the first one listed) in my brain and was foolishly assuming the function had consistent names (silly me). So I look up the correct name and try again:

select * from table(joblog_info());        

And this too doesn't work. I get this lovely and not very accurate message.

X SQL State: 4270
Vendor Code: -204
Message: [SQL0204] JOBLOG_INFO in *LIBL type *N not found.4        

So I return to the documentation center and read it again. Apparently it has no default for the JOB_NAME parameter; you have to explicitly enter an asterisk for the Current Job. So I try again:

select * from table(joblog_info('*'));        

And we have lift off! I have the joblog for the current job.

<sarcasm>Using SQL is so much more productive than CL commands!</sarcasm>

After running this function in RSS I see a listing that could be better organized. Sure it's up to the end-user to select the columns you want, but come on, where is the message text?

So I scroll to the right and see the message text and 2nd level text are close to the far right-end of the listing (1st world problems, I know). But if I had just used DSPJOBLOG I would be presented with something like the following right away:

I can see the messages, I can react to those messages and then go back to what I need to be doing.

The solution is to type in the column names you want every time you use JOBLOG_INFO in RSS.

1. Type in SELECT * FROM JOBLOG_INFO
2. Position the cursor in front of the * and press F4
3. Select the fields/column you want, then press Enter.
4. Run the statement.

Somehow I don't think this is more productive that just typing in DSPJOBLOG on the Command Entry display.

SQL Tools QJOBLOG Table Function

Rather that do one of those things, I decided to do what I always do and fix the problem. Since JOBLOG_INFO is on V7R2 and later, I decided to make a better version of it so that you and I can (A) remember the function name and (B) get the information you really need right away without much if any column selection.

The QJOBLOG table function returns the joblog for any job, including the one running the function. You pass in an optional fully qualified 3-part job ID to get a specific job's joblog. If you don't pass in a job ID it defaults to the job running the function and returns the joblog for the current job. You know... just like DSPJOBLOG.

Here's the version of the SQL statement from earlier in this article, rewritten to use the new QJOBLOG table function:

select * from table(qjoblog());        

Not a lot of difference, right? But subtle enough so that an RPG developer who uses it occasionally will (a) remember its name and (b) not get pissed off by a goofy "JOBLOG_INFO in *LIBL not found" message.

With the QJOBLOG function, instead of a weird error message, you see this:

You see the qualified Job name in this version of the function, which wasn't part of JOBLOG_INFO until the latest TR (and only on V7R3 and later). You immediately see the 1st level message text and the other less-important information is off to the right if needed. You get the info you need at first glance and can then go back to doing your job... just like DSPJOBLOG.

While I have written my own JOBLOG Table Function for internal use, I do not ship it with SQL Tools [Edit: The JOBLOG_LIST UDTF is included as part of SQL Tools 2023 and later]. Instead, I created the QJOBLOG function that leverages the IBM JOBLOG_INFO function itself with a reformatted resultSet so it is more usable and has IMHO better default behavior.

You can download and install the free trial of SQL Tools from our webiste: https://www.SQLiQuery.com/SQLTools you'll also see the directory of over 100 SQL functions and views we've created for you.

Or you can cut/paste the code listed below and compile it using RSS or the RUNSQLSTM CL command. It is created in library QGPL so if you want to create it elsewhere, change the source code before compiling it.

You may note that the Specific name is Z_JOBLOG. There are a handful of SQL Tools functions that have the Z_ prefix for their specific name. These are simply functions that are so simple that we ship the source code for them in SQL Tools. Some of the other functions in that realm included:

• JOBLOG - The subject of this article.
• MSGQ - A reformatted MESSAGE_QUEUE_INFO Function.
• PGMREFS - An SQL function version of the DSPPGMREF CL command.
• SBS_JOBLOG - A consolidated joblog listing for all jobs in a specified subsystem.
• USER_STG - User Profile Storage Usage Percentages. A Function version of the PRTUSRINT CL command.
• ENCODEXML - Encode data for use in XML tags.

? create or replace function QGPL.QJOBLOG( 
                      job varchar(28) default '*'
                                         )
???????? RETURNS TABLE (
??????????? JOB????????? VARCHAR(28),
??????????? MSGID??????? VARCHAR(7),
??????????? MSGTYPE????? VARCHAR(13),
??????????? SEVERITY???? SMALLINT,
??????????? POSTED?????? TIMESTAMP,
??????????? MSGTEXT????? VARGRAPHIC(1024) CCSID 1200,
??????????? FROM_USER??? VARCHAR(10),
??????????? SENDER?????? VARCHAR(48),
??????????? TO_PGM?????? VARCHAR(48),
??????????? SECLVL?????? VARGRAPHIC(4096) CCSID 1200,
??????????? ordinal_position int
?????????????????????? )
???? LANGUAGE SQL
???? READS SQL DATA
???? DISALLOW PARALLEL
???? NO EXTERNAL ACTION
???? NOT DETERMINISTIC
???? NOT FENCED
???? SPECIFIC? QGPL.Z_JOBLOG

??? set option datfmt = *ISO, commit=*NONE

R: BEGIN
???? DECLARE JOB_ID varchar(28) not null default '';

???? if (JOB is NULL or JOB = '' or JOB = '*' or
???????? upper(rTrim(JOB)) in ('*CURRENT','CURRENT')) THEN
????? set JOB_ID = qsys2.job_name;
???? else
????? set JOB_ID = upper(JOB);
???? end if;

?? RETURN? SELECT
?????? R.JOB_ID,
?????? MESSAGE_ID,
?????? CASE message_type
???????? WHEN 'COMMAND'?????? THEN '*CMD'
???????? WHEN 'INFORMATIONAL' THEN '*INFO'
???????? WHEN 'INQUIRY'?????? THEN '*INQ'
???????? WHEN 'REQUEST'?????? THEN '*RQS'
???????? WHEN 'COMPLETION'??? THEN '*COMP'
???????? WHEN 'DIAGNOSTIC'??? THEN '*DIAG'
???????? WHEN 'NOTIFY'??????? THEN '*NOTIFY'
???????? WHEN 'ESCAPE'??????? THEN '*ESCAPE'
???????? ELSE '*' CONCAT TRIM(MESSAGE_TYPE)
?????? END,
?????? severity,
?????? message_timestamp AS POSTED,
?????? TRIM(message_text),

?????? FROM_USER,
?????? TRIM(from_library) CONCAT '/' CONCAT
?????? TRIM(left(from_program,10)) CONCAT '.' CONCAT
?????? TRIM(from_instruction) AS SENDER,

?????? TRIM(to_library) CONCAT '/' CONCAT
?????? TRIM(to_program) CONCAT '.' CONCAT
?????? TRIM(to_instruction) AS TO_PGM,
?????? message_second_level_text,
?????? ORDINAL_POSITION
??? FROM TABLE ( qsys2.joblog_info( upper(JOB) ) ) JL;

end;

LABEL ON Specific routine QGPL.Z_JOBLOG? IS
?'Cleaner Joblog message queue listing';

COMMENT ON Specific function QGPL.Z_JOBLOG? IS
? 'List messages in the joblog for the given job in a cleaner format
? than other interfaces';

comment on parameter specific function QGPL.Z_JOBLOG
( JOB is 'The fully qualified (3-part) JOB name or ''*'' for the
job running this function. The default is ''*'' '
);
?        

That's all there is to it.