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?

No alt text provided for this image

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:

No alt text provided for this image

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:

No alt text provided for this image

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.

Tahir Mahmood

System Administrator/Analyst at Ministry in Kuwait.

1 年

excellent !

回复

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 '*')

要查看或添加评论,请登录

Bob Cozzi的更多文章

  • Using my READSPLF SQL Function

    Using my READSPLF SQL Function

    A SQL Table Function to Directly Read IBM i Spooled Files Managing spooled files on IBM i has always been a critical…

    7 条评论
  • Example SQL iQuery Script for IBM i

    Example SQL iQuery Script for IBM i

    Since releasing SQL iQuery for the IBM i operating system, my customers have primarily been using a very cool feature…

    3 条评论
  • Reading Source File Members Using SQL

    Reading Source File Members Using SQL

    With the introduction of my SQL Tools product several years ago, I created a number of "READ" SQL functions that…

    1 条评论
  • IBM i SQL Function Adoption Rate

    IBM i SQL Function Adoption Rate

    IBM i Developers have long relied on various interfaces and tools to navigate system functions, but many remain unaware…

    3 条评论
  • SQL iQuery for Web Config Directives

    SQL iQuery for Web Config Directives

    Last time I showed how to use the no-charge SQL iQuery for Web product to create a simple File Inquiry web app for the…

    1 条评论
  • HTML/Browser Apps for IBM i

    HTML/Browser Apps for IBM i

    There have been myriad methods for creating HTML browser enabled applications that use IBM i database files. For the…

    12 条评论
  • SQL iQuery is Free (tell your friends)

    SQL iQuery is Free (tell your friends)

    Challenges of Pricing Software in the IBM i Ecosystem In the dynamic arena of technology services and software support…

    9 条评论
  • IBM i SQL UDTF: SYSINFO

    IBM i SQL UDTF: SYSINFO

    I had a post about a simple SQL Function I created that gives me everything I need to know about the physical Power…

  • Reading Stuff using SQL for IBM i

    Reading Stuff using SQL for IBM i

    My SQL Tools licensed program (product) has 4 so called read functions. These functions allow users to retrieve data…

    1 条评论
  • Add it Up in RPG

    Add it Up in RPG

    One of the features that has been re-introduced to RPG over the decades is the myriad methods to perform an ADD…

    17 条评论

社区洞察

其他会员也浏览了