SELF improvement

Having asked at several conferences I seem to be the only person using SELF to capture SQL errors. SELF, or SQL Error Logging Facility, was introduced in IBM i 7.5 TR1 and 7.4 TR7, as a way to capture SQL errors and insert them into a table that I could use to diagnose and remedy those errors. The major drawback of this tool was that I would have to list all of the SQL codes I was interested in and insert them into a SQL Global Variable every time I wanted to use SELF.

This was remedied as part of the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, now I could use the special values of '*ERROR', '*WARN', or '*ALL'. Rather than list all of the SQL codes I could just use one of these to capture all errors, all warnings, or all errors and warnings. Which makes my life a whole lot easier.

I am not going to go into too many details about the basics of SELF. If you are not familiar with it read my previous post describing SELF when it was introduced.

I can use the VALIDATE_SELF to validate what that a value is valid for SELF. For example I can use it with '*ERROR':

VALUES SYSIBMADM.VALIDATE_SELF('*ERROR')

If it returns the value then it is a valid value for use with SELF:

00001 ------- *ERROR

I can validate the other new values. Let me start with '*WARN':

VALUES SYSIBMADM.VALIDATE_SELF('*WARN') ; 00001 ------- *WARN

That verifies that it is a valid value.

The other value is '*ALL':

VALUES SYSIBMADM.VALIDATE_SELF('*ALL') ; 00001 ------- *ALL

Which is valid too:

I can update the SELF codes I am interested in are contained within the Global Variable SELFCODES. The value contain within a Global Variable is only for that SQL "session". Any other job or after a SQL program ends the Global Variable will be set to default. Here that is null.

I can use the SET statement to change the contents of SELFCODES for this SQL "job" to be what I desire:

SET SYSIBMADM.SELFCODES = '*ERROR'

I can use the following statement to verify that I have updated the Global Variable correctly:

VALUES SYSIBMADM.SELFCODES ; 00001 ------- *ERROR

I use SELF within RPG programs to capture errors, and warnings, when SQL code within them fails execute in the way I expect. Below is the same example program I used in my previous post about SELF:

01 **free 02 dcl-s KeyIn char(10) ; 03 exec sql SET OPTION CLOSQLCSR = *ENDMOD ; 04 exec sql SET SYSIBMADM.SELFCODES = '*ALL' ; 05 exec sql DECLARE C0 CURSOR FOR SELECT KEY FROM TESTFILE FOR READ ONLY ; 06 exec sql OPEN C0 ; 07 exec sql CLOSE C0 ; 08 exec sql FETCH NEXT FROM C0 INTO :KeyIn ;

Line 4: Rather than a long list of SQL codes I can just have '*ALL'.

Line 5: SQL cursor defined.

Line 6: Cursor opened.

Line 7: Cursor closed.

Line 8: Attempted Fetch, which errors as you cannot fetch from a closed cursor.

After the program completes I look in the job log, and I see a message:

Additional Message Information Message ID . . . . . . : SQL0501 Date sent . . . . . . : DD/DD/DD Message . . . . : Cursor C0 not open. Cause . . . . . : The cursor C0 was specified in a FETCH or CLOSE statement, but the cursor is not open...

I can look in the SELF output table, SQL_ERROR_LOG:

01 SELECT LOGGED_SQLCODE,LOGGED_SQLSTATE,STATEMENT_TEXT, 02 STATEMENT_OPERATION_DETAIL, 03 PROGRAM_LIBRARY,PROGRAM_NAME,MODULE_NAME,PROGRAM_TYPE 04 FROM QSYS2.SQL_ERROR_LOG

Lines 1 - 3: The columns I am interested in.

Line 4: From the SEFL output table.

The results give me a better idea what happened, and in what program:

STATEMENT_ LOGGED_ LOGGED_ OPERATION_ SQLCODE SQLSTATE STATEMENT_TEXT DETAIL ------- -------- --------------------------- ---------- -501 24501 FETCH NEXT FROM C0 INTO : H FETCH PROGRAM_ PROGRAM MODULE PROGRAM LIBRARY _NAME _NAME _TYPE -------- ------- ------- ------- MYLIB TESTPGM TESTPGM *PGM

I am so happy that now I can just use '*ALL', rather than trying to determine all of the appropriate SQL codes that be caused by an error in the program.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.