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):
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:
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.
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:
? 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.
System Administrator/Analyst at Ministry in Kuwait.
1 年excellent !
--
2 年Tried running the code above in RSS, got the following error: SQL State: 42601 Vendor Code: -199 Message: [SQL0199] Keyword RETURNS not expected. Valid tokens: ) ,. Cause . . . . . :?The keyword RETURNS was not expected here.?A syntax error was detected at keyword RETURNS.?The partial list of valid tokens is ) ,. This list assumes that the statement is correct up to the unexpected keyword.?The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery?. . . :?Examine the SQL statement in the area of the specified keyword.?A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again. On the first line of the statement the closing bracket is missing in the example above: create or replace function QGPL.QJOBLOG( job varchar(28) default '*')