Using SQL to write to the job log

Another useful addition to IBM i with the latest round of Technical Refreshes, 7.4 TR1 and 7.3 TR7, is a SQL function that allows you to write directly to the job's job log.

Why would I want to write to the job's job log?

I would use this to write to the job log when something I would expect to happen did not. Or as a record that some part of the program ran, and completed successfully or failed.

The syntax for this function could not be simpler:

CALL SYSTOOLS.LPRINTF(string or variable)

Notice that this function is in the library SYSTOOLS, therefore, if you do not qualify the schema/library name for this function you will need to add SYSTOOLS to your library list.

Let me start with the simplest example. I just STRSQL and then entered:

CALL SYSTOOLS.LPRINTF('Hello ' || CURRENT_USER)

CURRENT_USER is a Db2 "special register" that returns the name of the job's current user.

Rather than use the Display Job Log command, DSPJOBLOG, I prefer to use SQL's JOBLOG_INFO table function.

SELECT MESSAGE_ID,MESSAGE_TEXT FROM TABLE(QSYS2.JOBLOG_INFO('704892/SIMON/QPADEV0001')) ORDER BY ORDINAL_POSITION DESC

By using the ORDER BY ORDINAL_POSITION DESC I get the most recent job log entry first, therefore I see:

MESSAGE_ID MESSAGE_TEXT ---------- ------------ - Hello SIMON - strsql

I can make this a bit more complicated and use a RPG program to execute LPRINTF:

01 exec sql CALL SYSTOOLS.LPRINTF('Welcome ' || 02 CURRENT_USER || 03 '. Today is ' || 04 DAYNAME(CURRENT_DATE) || ' ' || 05 MONTHNAME(CURRENT_DATE) || ' ' || 06 DAYOFMONTH(CURRENT_DATE) || ', ' || 07 YEAR(CURRENT_DATE) ) ;

Lines 4 – 7: I am using these functions to convert the date to words. When I look in this job's job log, using the same JOBLOG_INFO as before I see:

MESSAGE_ID MESSAGE_TEXT ---------- ------------------------------------------------- - Welcome SIMON. Today is Tuesday December 10, 2019

Now to make this a bit more like "real life". In this example I want to write out to the job log when this program fetches data from a file. This way I can know from the job log if the fetching process was successful, and if it was not what caused it to fail.

This is the snippet of the procedure I used to do this:

01 exec sql CALL SYSTOOLS.LPRINTF('Get data...') ; 02 exec sql DECLARE C0 CURSOR FOR SELECT PID, RTRIM(LNAME) || ', ' || FNAME FROM PERSON ORDER BY LNAME FOR READ ONLY ; 03 exec sql CALL SYSTOOLS.LPRINTF('Open cursor C0') ; 04 exec sql OPEN C0 ; 05 SuccessOrNot() ; 06 exec sql CALL SYSTOOLS.LPRINTF('Fetching data from cursor C0') ; 07 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 08 SuccessOrNot() ; 09 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 10 exec sql CALL SYSTOOLS.LPRINTF('Close cursor C0') ; 11 exec sql CLOSE C0 ; 12 exec sql CALL SYSTOOLS.LPRINTF('... end of getting data') ;

Line 1: I am writing a message to the job's job log to state that the process to get data is starting.

Line 2: Definition of the cursor I will be using to fetch the data from the file PERSON.

Line 3: Sending the message to the job log that the cursor is being opened.

Line 5: Having opened the cursor I want to send a message to the job log about whether this was successful or not. To do this I call the procedure SuccessOrNot, which will be explained later.

Line 6: Message to the job log to say that the Fetch is happening.

Line 7: The multiple row fetch, fetching data from the cursor into the data structure array Data.

Line 8: Use SuccessOrNot to report the status of the fetch.

Line 9: Retrieve the number of rows fetched.

Line 10: Message that the cursor is about to be closed.

Line 12: Send the message that the getting of the data has finished.

And what does the SuccessOrNot procedure look like:

20 dcl-proc SuccessOrNot ; 21 if (SQLCOD = 0) ; 22 exec sql CALL SYSTOOLS.LPRINTF('Successful') ; 23 else ; 24 exec sql GET DIAGNOSTICS CONDITION 1 :ReturnedSqlCode = DB2_RETURNED_SQLCODE, :MessageText = MESSAGE_TEXT ; 25 exec sql CALL SYSTOOLS.LPRINTF('Failed') ; 26 exec sql CALL SYSTOOLS.LPRINTF('SQLCOD = ' || :ReturnedSqlCode) ; 27 exec sql CALL SYSTOOLS.LPRINTF('Message = ' || :MessageText) ; 28 endif ; 29 end-proc ;

Line 21: The SQLCOD is part of the SQLCA data structure, that is returned by every executed SQL statement in a RPG program. The value of zero means that no error or warning happened.

Line 22: Message of "Successful" is written to the job log.

Line 24: When the SQL code is not zero I use the GET DIAGNOSTICS to retrieve the returned SQL code and the related message text.

Lines 25 – 27: Here I write messages to the job log of what the code and the message text was for the error or warning.

When I run this program and there are no errors I see the following in the job's job log:

MESSAGE_ID MESSAGE_TEXT ---------- ----------------------------- - Get data... - Open cursor C0 - Successful - Fetching data from cursor C0 - Successful - Close cursor C0 - ... end of getting data

Now what would happen if the file PERSON was not in my library list?

MESSAGE_ID MESSAGE_TEXT ---------- ----------------------------- - Get data... - Open cursor C0 SQL0204 PERSON in *LIBL type *FILE not found. - Failed - SQLCOD = -204 - Message = PERSON in *LIBL type *FILE not found. - Fetching data from cursor C0 SQL0501 Cursor C0 not open. - Failed - SQLCOD = -501 - Message = Cursor C0 not open. - Close cursor C0 SQL0501 Cursor C0 not open. - ... end of getting data

By all means the messages I have generated are a bit redundant as the system generated messages say the same thing. But this does give you an example of what you could do, even if you were not going to use SQL to get data, etc.

 

I cannot find a page for this function in IBM's KnowledgeCenter, therefore, this link is to the developerWorks site that is being closed on January 1, 2020.

 

This article was written for IBM i 7.4 TR1 and 7.3 TR7.