Guru: Logging SQL Errors And Warnings

October 30, 2017 Birgitta Hauser

If something goes wrong with an embedded SQL statement or an SQL routine (a trigger, stored procedure or user-defined function), the system does not crash. Instead, DB2 returns a negative SQL code and an SQL state that starts with something other than 00, 01, or 02. If the routine does not handle the error, the program continues to run.

It is good practice to log errors to a table, especially unexpected ones. Before you can log any errors, you need to know how an SQL error can be trapped and handled. You need to know how to write a condition handler.

Continue, Exit, and Undo Handlers

In a SQL routine, you can code condition handlers to process warnings and errors. Condition handlers are activated as soon as a specific SQLSTATE is signaled. You may define handlers for any unhandled SQL error or warning.

There are three types of handlers.

1. Continue Handler: After the handler is executed, execution of the routine continues after the statement that caused the handler to be activated.
2. Exit Handler: After the handler is executed, the routine is ended.
3. Undo Handler: After the handler is executed, the routine is also ended, but if the routine is run under commitment control, the database manager performs a ROLLBACK.

Define condition handlers in the declaration section of the routine body, after the variable, condition, and cursor definitions and before the executable statements.

Within a handler, you may code either a single SQL statement or, if multiple statements need to be performed, a compound statement. A compound statement starts with BEGIN and ends with END. Between BEGIN and END, you may include a declaration section followed by multiple executable statements that should be performed as soon as the specified SQL state occurs.

The syntax of a condition handler looks like this:

DECLARE Continue/Exit/Undo HANDLER FOR SQLSTATE 'XXXXX' 
        Single Statement or Compound Statement;

You may define multiple condition handlers to manage different SQL states.

Sometimes unexpected errors or warnings occur. SQL states for unexpected errors are normally not known and cannot be anticipated. But handling or at least logging those errors might be even more important than managing foreseen SQL states.

General Conditions

SQL programming defines three general conditions for all unexpected errors or warnings. You can use these conditions instead of an SQL state.

• SQLEXCEPTION: Can be used for any SQLSTATE that does not start with 00 (runs correctly) or 01 (warning) or 02 (not found) that is not specifically handled elsewhere.
• SQLWARNING: Represents any SQLSTATE starting with 01 (warning) that is not handled elsewhere.
• NOT FOUND: Stands for any SQLSTATEs starting with 02 (not found) that is not handled elsewhere.

The following example displays the syntax of an exit handler that is activated for any error:

DECLARE Exit HANDLER FOR SQLEXCEPTION 
        Single Statement or Compound Statement;

You can always retrieve the SQL state of a statement, even for unexpected errors. The SQL state is a cryptic value consisting of five digits and/or letters. You can use the GET DIAGNOSTICS statement to retrieve more detailed information about a statement that failed.

GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement provides information about the previous SQL statement. It can only be used in embedded SQL programs and SQL PL routines.

GET DIAGNOSTICS provides detailed statement, connection, and condition information. You can specify multiple items or keywords to describe the information to be returned. The syntax for retrieving information differs slightly depending on the information type.

Statement information includes, among other information, the following keywords

• ROW_COUNT: Returns the number of rows associated with the previous SQL statement, i.e. returns the number of rows deleted, updated or inserted by the previous INSERT, UPDATE or DELETE statement
• MORE: Indicates whether more errors were raised than could be handled
• NUMBER: Returns the number of errors and warnings detected by the execution of the previous SQL statement

The following GET DIAGNOSTICS Statement returns the number of inserted, updated or deleted rows of the previous statement.

GET DIAGNOSTICS Set LocNbrOfRows = ROW_COUNT; 

You can retrieve information based on multiple keywords with one GET DIAGNOSTICS statement. The different Variable = Keyword pairs are listed one after the other and separated by commas.

To retrieve condition information, you must specify an additional condition as an integer value. For information about the previous statement, specify CONDITION 1.

Condition information includes, among others, the following keywords

• MESSAGE_TEXT: Identifies the message text of the error, warning, or successful completion returned from the last SQL statement that was executed.
• DB2_MESSAGE_ID: Returns the message ID corresponding to the MESSAGE_TEXT
• COLUMN_NAME: If the error was caused by an inaccessible column, the column name is returned
• CONSTRAINT_NAME: If the error was caused by a constraint violation, the constraint name is returned
• TRIGGER_NAME: If a trigger caused an exception, the trigger name is returned.

The following statement shows the GET DIAGNOSTICS statement for retrieving the error text and message ID for the previous statement.

GET DIAGNOSTICS Condition 1
    Set LocMsgText = MESSAGE_TEXT,
        LocMsgId   = DB2_MESSAGE_ID;

You can find detailed information about the GET DIAGNOSTICS statement in the IBM i Knowledge Center

Information such as the message text or the name of the constraint that causes the error is very important when logging the error information.

For logging SQL errors additional information that is not returned by the GET DIAGNOSTICS statement might be essential. For example, you might need to know which user ran the SQL routine, the routine name itself, or the job in which the routine ran.

Special Registers

Special Registers provide current user and current time information. The following list shows a subset of the currently available special registers

• USER / SESSION_USER: Returns the current user at run-time
• SYSTEM_USER: Returns the user who connected to the current server. May be different from the SESSION_USER.
• CURRENT USER: Returns the user profile that runs the program or routine. May be different from the session user. If the program or routine is compiled with the user profile option set to *OWNER, the owner user profile is returned.
• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP: Returns the current date or the current time or the current timestamp
• CURRENT_TIMEZONE: Returns the difference between UTC (Coordinated Universal Time) and local time at the current server.

To get the UTC, the system subtracts the CURRENT_TIMEZONE special register from the CURRENT_TIMESTAMP or CURRENT_TIME special registers.

You can find detailed information about special registers in the IBM i Knowledge Center.

Built-In Global Variables

You can retrieve additional information from built-in global variables that are provided with the database manager and are located in either the QSYS2 or SYSIBM schema. The scope of a global variable is the session (or connection), which means that the same variable used in different jobs at the same time can return different values.

The following list shows a subset of the built-in global variables that are currently available:

• JOB_NAME: Contains the name of the current job in the format JOB/USER/JOBNO
• ROUTINE_SPECIFIC_NAME: Contains the name of the currently executing routine.
• CLIENT_HOST: Contains the host name of the current client, as returned by the system.

You can find detailed information about built-in global variables in the IBM i Knowledge Center.

Auditing Columns

Beginning with Release 7.3, you can define auditing columns in SQL tables with the GENERATED ALWAYS attribute and a special register or a built-in global variable.

The database manager automatically sets the content of the auditing columns as soon as the row is inserted or updated.

The following snippet shows the definition of two auditing columns to hold the session user and the qualified job.

Create or Replace Table YourSchema.YourTable                                                        
      (...               , 
       SessUser       VarChar(128)  Generated Always as (Session_User),                                  
       Job            VarChar(28)   Generated Always as (QSYS2.Job_Name),
       ...) 

You can find detailed information about auditing columns can be found in the IBM i Knowledge Center.

Logging SQL Errors and Warnings

Now let’s pull it all together for logging SQL errors or warnings.

The first step is to create an SQL table where the error information is written. The following example shows the CREATE TABLE Statement for the ERRORLOG table that will be updated in the subsequent examples. The ErrSQLCODE, ErrSQLSTATE and MSGTEXT columns will be populated with information returned by the GET DIAGNOSTICS statement. The ROUTINE, ROUTSCHEMA, SESSUSER, JOB and CHGDATTIM columns will be automatically populated with special register values or built-in global variable values.

Create or Replace Table YourSchema.ErrorLog                                                        
      (Routine        VarChar(128)  Generated Always 
                                    as (SysIBM.Routine_Specific_Name),                                  
       RoutSchema     VarChar(128)  Generated Always 
                                    as (SysIBM.Routine_Schema),                                  
       ErrSQLCODE     Integer       Not NULL Default 0,                                   
       ErrSQLSTATE    For Column    ESQLSTATE                                             
                      Char(5)       Not NULL Default '00000',                             
       Type           Char(1)       Not NULL Default '',                                  
       MsgText        VarChar(1024) Not Null Default '',                                  
       SessUser       VarChar(128)  Generated Always as (Session_User),                                  
       Job            VarChar(28)   Generated Always as (QSYS2.Job_Name),                                  
       ChgDatTim      Timestamp     Not NULL                                              
                                    Generated Always For Each Row on Update                 
                                                  as Row Change TimeStamp); 

A Stored Procedure with Error Logging

After you create the table, write condition handlers to handle specific SQL states and/or any error or warning conditions. The following example shows the syntax for a stored procedure that includes several condition handlers: a condition handler for SQLSTATE 24501, a condition handler for all SQLWARNINGs and an exit handler for all SQL exceptions except SQLSTATE 24501.

Within the handler source code, first define the LocMsgText, LocSQLCODE and LocSQLSTATE local variables. The procedure runs GET DIAGNOSTICS and populates these variables with the MESSAGE_TEXT (Error Message Text), DB2_RETURNED_SQLCODE (i.e. the SQLCODE of the previous statement in error) and RETURNED_SQLSTATE (the SQLSTATE of the previous statement in error). Then the procedure writes these values to the ERRORLOG table.

Other information, such as the session user or the qualified job, are updated automatically because the appropriate columns in the ERRORLOG log table are defined with GENERATE ALWAYS as auditing columns.

Since the source code for logging a specific SQLSTATE or all SQLWARNINGS or all SQLEXCEPTONS is identical, I have commented out that code in this example.

Create or Replace Procedure YourSchema.YourProcedure 
                            (Define Input/Output Parameters)  
        Dynamic Result Sets 1                                       
        Language SQL                                                
...
 Begin 
    -- Define Variables
    -- Define Cursors
    
    -- Continue Handler for Cursor Not Opened (Close not successful)               
    Declare Continue Handler for SQLSTATE '24501' 
             Begin 
                Declare LocMsgText  VarChar(1024) Default '';
                Declare LocSQLCODE  Integer       Default 0;
                Declare LocSQLSTATE Char(5)       Default '00000';
                
                Get Diagnostics Condition 1 
                        LocMsgText  = MESSAGE_TEXT,
                        LocSQLCODE  = DB2_RETURNED_SQLCODE,
                        LocSQLSTATE = RETURNED_SQLSTATE;
                        
                Insert into ErrorLog 
                        (ErrSQLCode, ErrSQLState, Type, MsgText)
                   Values(LocSQLCODE, LocSQLSTATE, 
                          Case When     LocSQLCODE  < 0   Then 'E'
                               When     LocSQLCODE  > 0 
                                    and LocSQLCode <> 100 Then 'W'
                                    Else '' End,
                          LocMsgText)
                   With NC; 
             End;  
             
     -- Global Continue Handler for all Warnings             
     Declare Continue Handler for SQLWARNING 
             Begin 
              -- Same source code as for the continue handler
              -- For SQLSTATE 24501
             End;         
             
     -- Global Exit Handler for all not explicitly handled Errors                                                                                            
     Declare Exit Handler for SQLEXCEPTION 
             Begin 
              -- Same source code as for the continue handler
              -- For SQLSTATE 24501                
             End;                                                                
                                          
   -- SQL Source Code
                           
End  ; 

Note: If you are not yet on release 7.3 and cannot define columns with the GENERATE ALWAYS attribute for auditing, you must define the appropriate columns as regular columns and populate them within the handler.

The following example shows the content of the log file after having run the stored procedure multiple times from different jobs and with different errors.

SQL Include Statement – SQL Copy Member

Repeating the same source code over and over again is not only bad design but also error-prone. With the first technology refresh in Release 7.3, IBM introduced the INCLUDE statement. The INCLUDE statement inserts application code, including declarations, into an SQL routine body. The source code must be specified at a point in the source code where it is syntactically correct. For example, DECLARE statements are not allowed among the executable statements. The source code to be inserted can be stored either in a source physical member or in a stream file. The source code can be either pure SQL code or C code. Visit the IBM Knowledge Center for detailed information about the enhanced INCLUDE statement.

To simplify procedure source code and make the error logging available for all other SQL sources, I created a copybook called CPYERRLOG. I put it in source physical file QSQLCPY. I did not include the BEGIN and END tokens, which indicate a compound statement so that I would be able to add variables before the INCLUDE statement and additional executable statements after the copy member within a handler compound statement.

Here’s the source code of the CPYERRLOG copy member:

        Declare LocMsgText  VarChar(1024) Default '';       
        Declare LocSQLCODE  Integer       Default 0;        
        Declare LocSQLSTATE Char(5)       Default '00000';  
                                                            
        Get Diagnostics Condition 1                         
                LocMsgText  = MESSAGE_TEXT,                 
                LocSQLCODE  = DB2_RETURNED_SQLCODE,         
                LocSQLSTATE = RETURNED_SQLState;            
                                                            
        Insert into ErrorLog73                              
                (ErrSQLCode, ErrSQLState, Type, MsgText)    
           Values(LocSQLCODE, LocSQLSTATE,                  
                  Case When     LocSQLCODE  < 0   Then 'E'  
                       When     LocSQLCODE  > 0             
                            and LocSQLCode <> 100 Then 'W'  
                            Else '' End,                    
                  LocMsgText)                               
           With NC;   

Include this copy member in any source code wherever you need to write an error log. Here’s an example.

Create or Replace Procedure YourSchema.YourProcedure 
                            (Define Input/Output Parameters)  
        Dynamic Result Sets 1                                       
        Language SQL                                                
...
 Begin 
    -- Define Variables
    -- Define Cursors
    
    -- Continue Handler for Cursor Not Opened (Close not successful)               
    Declare Continue Handler for SQLSTATE '24501' 
             Begin 
                Include SQL QSQLCPY(CPYERRLOG);                
             End;  
             
     -- Global Continue Handler for all Warnings             
     Declare Continue Handler for SQLWARNING 
             Begin 
                Include SQL QSQLCPY(CPYERRLOG);                
             End;         
             
     -- Global Exit Handler for all not explicitly handled Errors                                                                                            
     Declare Exit Handler for SQLEXCEPTION 
             Begin 
                Include SQL QSQLCPY(CPYERRLOG); 
             End;                                                                
                                          
   -- SQL Source Code
                           
End  ; 

Logging errors — where they first occurred, how they were handled, and so forth — is very important for analysis, especially when job logs and program dumps are not available. Using log tables with GENERATED ALWAYS columns and the SQL INCLUDE statement makes error-logging easy.

RELATED STORIES

Error Handling in SQL PL, Part 1

Error Handling in SQL PL, Part 2