SQL TOKEN -438 : Nightmare Error

We are not expected to remember the fix/resolution for each and every SQL error code we come across. Most of the error codes like -904, -805, -204 are common and we get to see them once in a week ? Most probably, yes ! But what about those error codes which you seldom encounter ? Manuals - SQL REFERENCE is an excellent manual to refer for SQL error codes. What if your error code resolution says - " Investigate on the application text " ? OK - You go investigate. What if your investigation shakes the very foundation of your knowledge on DB2 ?

Here is the error message from SQLCA :

Piece of cake, Column COL1 is not a part of the parent table TAB1. Now here is the scary part - COL1 was indeed a part of the TAB1 when I checked the catalog. Then why is the error text misleading me ? When I checked with the application development team I got some useful information as well. They pointed out the statement number which ended up with an -438.

Here is the SQL statement :

EXEC SQL INSERT
INTO TAB2
( COL1
)
VALUES
( : HOST-VARIABLE )

An insert query, anyways I had lost it as there was no problem with the table, column, statistics what so ever. I ended up shooting an email to my team asking for help. I was amazed when my team pointed out the problem. It was a TRIGGER that was defined on TAB2 that was causing this error.

CREATE TRIGGER TRIG1
NO CASCADE BEFORE INSERT ON APP1.TAB2
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN ( EXISTS ( SELECT COL1 FROM APP1.TAB1 WHERE COL2 = NEW.COL2 ) )
BEGIN ATOMIC
SIGNAL SQLSTATE 'APPOUT' ( ' COL1 DOES NOT EXIST IN THE PARENT TABLE TAB1 ')

The development team was contacted to correct the row being inserted into the child table ( TAB2 ).

The SIGNAL being passed from the trigger was misleading, if the SIGNAL SQLSTATE passed for a wrong insert was ' VALUE BEING PASSED INTO COL1 DOES NOT EXIST IN THE PARENT TABLE TAB1 ' , then it would have been better ? At least, it would not have made me upset for a while.