DB2/iSeries stored procedure error trapping SQL State

Question

I have a stored procedure that is called from an unmonitored system and I need to trap all errors that occur.

Currently, I can trap any errors that occur when the data is well formed, but not when there is a data type mismatch.

Does anyone have experience in trapping errors that occur from bad formed data?

EDIT: Any solution that allows me to trap this error would be appreciated, it doesn't have to be in the stored procedure. If anyone knows if there is a way to see where an error like this is logged, I can work with that also. Just any information, actually, would be helpful.

Here are my test files and procedure:

CREATE OR REPLACE PROCEDURE test_error_trap (
  IN_TEST_DATA_01 DECIMAL(10,0) DEFAULT 0,
  IN_TEST_DATA_02 CHAR(50) DEFAULT ' '
)

SPECIFIC SPROC001

LANGUAGE SQL
MODIFIES SQL DATA

BEGIN

DECLARE Exit HANDLER FOR SQLEXCEPTION
  BEGIN

    DECLARE sqlstate$ CHAR(5) DEFAULT '00000';
    DECLARE sqlcode#  integer;
    DECLARE sendMSG   varchar(100);

    SET sqlstate$ = SQLSTATE;
    SET sqlcode# = SQLCODE;
    SET sendMSG = 'SQL Error Code from trap = ' || sqlcode#;

    INSERT INTO LOGERROR
    (
      OUT_SQLSTATE,
      OUT_SQLCODE
    )

    VALUES (
      sqlstate$,
      sqlcode#
    );

    SIGNAL SQLSTATE sqlstate$ SET MESSAGE_TEXT = sendMSG;

  END;

INSERT INTO LOGPASS
(
TEST_DATA_01,
TEST_DATA_02
)

VALUES (
IN_TEST_DATA_01,
IN_TEST_DATA_02
);

END;

LABEL ON SPECIFIC PROCEDURE SPROC001 IS 'SQL Error Trap Test';   

CREATE OR REPLACE TABLE LOGPASS (

-- Test Data 01
TEST_DATA_01
  FOR COLUMN TST01
  DECIMAL(10 , 0)
  DEFAULT 0
  NOT NULL,

-- Test Data 02
TEST_DATA_02
  FOR COLUMN TST02
  CHAR(50)
  DEFAULT ' '
  NOT NULL,

CONSTRAINT LOGPASS_PK PRIMARY KEY
(
 TEST_DATA_01,
 TEST_DATA_02
)
)
RCDFMT PASSREC
;

LABEL ON TABLE LOGPASS IS
'Stored Procedure Pass Log'; 

CREATE OR REPLACE TABLE LOGERROR (

-- SQL State
OUT_SQLSTATE
  FOR COLUMN ERRSQLS
  CHAR(5)
  DEFAULT ' '
  NOT NULL,

-- SQL Code
OUT_SQLCODE
  FOR COLUMN ERRSQLC
  INTEGER
  DEFAULT 0
  NOT NULL,

-- RECORD TIMESTAMP
OUT_RECORD_TIMESTAMP
  FOR COLUMN DLRECTS
  TIMESTAMP
  DEFAULT CURRENT_TIMESTAMP
  NOT NULL,

CONSTRAINT LOGERROR_PK PRIMARY KEY
(
 OUT_RECORD_TIMESTAMP
)
)
RCDFMT ERRORREC
;

LABEL ON TABLE LOGERROR IS
'Stored Procedure Error Log';  

And here are three runs of the procedure. Run 1 is a normal run. Run 2 is a correctly trapped error. Run 3 is an intentional bad data run that does not seem to get caught in my error trapping.

Three test runs

Answer 1

You are not going to be able to trap the error in your 3rd run.

Basically, the error is occurring in the DB before it gets to your stored proc.

Assuming your external system is calling the stored procedure properly using parameters, as opposed to building a dynamic string. Then the error would be thrown in the external system and not even get to Db2.