Pages

Wednesday, January 24, 2024

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.

5 comments:

  1. Simon, once the values are set in SYSIBMADM.SELFCODES, this is set for the whole system correct? It's not per job is it? So any job on the system that gets that error message would be written correct?

    ReplyDelete
    Replies
    1. When you create a global variable you can give it a default.
      When any job uses the global variable they get the default.
      Now if I use the Set statement to change it the I change value is only for my job. Everyone else still gets the default.

      Delete
    2. I've always found the term "global variable" confusing - it's not global as in system-wide, but instead global for the SQL session (job).

      I think the SELF description from 7.5 TR1 is the simplest explanation of how to set the SELF codes system-wide:
      https://www.ibm.com/support/pages/node/6828091

      Delete
    3. One should be careful to do system wide if there are MANY sql calls that will write to this object, we had a case with high throughput batch jobs running slower and causing locks to this specific file. When we disabled it problem went away magically.

      I would suggest its best to "toggle" this as diagnostic calls off example in environment variables if you need to know whats happening on the job, instead of running across a multitude of calls in a normal production systems jobs.

      Delete
  2. Great post Simon!

    I didn't know about this.... and... I'm gonna make a video about this, because it's very cool.

    Thanks!

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.