DB2 LUW version 10.5.0.10 - Get Warning (Not found) Line in Stored Procedure

Question

I'm trying to determine the line in a stored procedure or the last SQL-statement which is causing a warning / not found. As a workaround I'm using temporary variables which I manually set to determine in which part of my stored procedure a warning occurs.

-- Create an ErrorLog table
Create Table SCHEMA.ErrorLog_lrc_test                                                        
  (                                 
   ErrSQLCODE     Integer ,                                   
   Codepart    Char(1),                             
   Type           Char(1) ,                                  
   MsgText        VarChar(1024));

CREATE OR REPLACE PROCEDURE SCHEMA.test_warning(IN divisor INT)
LANGUAGE SQL
BEGIN
-- Define variables
DECLARE codepart_var Char(1);

DECLARE test_INT INT;

-- Define sqlcode
DECLARE SQLCODE INTEGER;
       
--Define Warning-Handler
DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND 
    BEGIN 
    INSERT INTO SCHEMA.ErrorLog_lrc_test(ErrSQLCODE, Codepart, TYPE, MsgText) 
        VALUES(SQLCODE, codepart_var, 'W', SYSPROC.SQLERRM(SQLCODE));
    END;
 
-- Set temporary variable to 'a' to get part of code where warning occured
SET codepart_var = 'a';

-- Create Not Found (Sqlcode 100)
INSERT INTO SCHEMA.ErrorLog_lrc_test
SELECT NULL, NULL, NULL, NULL FROM "SYSIBM".SYSDUMMY1
WHERE 1 = 0 ;

END 

call SCHEMA.test_warning(0);

SELECT *
FROM SCHEMA.ErrorLog_lrc_test;

I get the following:

ERRSQLCODE	CODEPART	TYPE	MSGTEXT
100	a	W	SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.

I know that for errors there is a function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE that you can use for tracing errors. Unfortunately that function doesn't trace warnings. Also there is a function DBMS_UTILITY.FORMAT_CALL_STACK, but this doesn't work neither.

Is there another/better way to log the specific line or SQL-statement in a stored procedure which is causing a warning / not found?

Answer 1

No, there is no such ability for Warnings built-in at the present time, as far as I'm aware. Consider carefully your motivation here.

A programmer can catch warnings by using exception handlers (or multiple exception handlers) inside the routine(s), and could decide to log such events (or in extremis, to convert them into errors by raising new exceptions, which would then be visible via DBMS_UTILITY.FORMAT* methods). But that would be unwise, except in a development environment perhaps.

Your routines could have a continue handler for warnings, in which you check the SQLSTATE, and conditionally decide to log specific SQLSTATES. It may be foolish to record SQLSTATE '02000' (sqlcode 100 - no rows found) as this is benign like most warnings. But some warnings might suggest other issues, e.g. with data quality/conversion/truncation or optimization issues that may be interesting to record.

I'm unaware of any tooling to help with this, or provide line-numbers for warnings, or integrate such work for client side enquiry.

A DBA can (for Db2-LUW) use other tools at the Db2-server to more closely observe executing statements and packages. For example monitoring, db2pd, db2cos, auditing etc. But that route seems like using a sledgehammer to crack a possibly non-existent nut, hence the need to double check your motivation.