Have you ever spent a long time trying to figure out what was causing DB2 to give you a bizarre error message only to find that the root cause was a default you didn’t know was a default? I got just such a message the other day when writing an SQL stored procedure. In this post, I will show you the confusing message I encountered and give you the opportunity to see if you can figure out the solution.
I was writing a procedure to do a simple comparison between two variables of ROW type, since there are no built-in comparison functions for variables of ROW type in DB2 9.7. It looked something like the following:
<!--start_raw-->SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@
CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
BEGIN
IF (LEFT.ONE = RIGHT.ONE) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
END@
BEGIN
DECLARE T1 TEST_T;
DECLARE T2 TEST_T;
DECLARE RESULT INTEGER;
SET T1.ONE = 1;
SET T2.ONE = 1;
PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
EXECUTE S1 INTO RESULT USING T1, T2;
CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@<!--end_raw-->
The above code worked just fine. As expected, the result looked like this:
<!--start_raw-->DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. Result = 1<!--end_raw-->
But then I moved the prepare and execute code in the anonymous block above into a scalar function named EQ_FN:
<!--start_raw-->CREATE OR REPLACE FUNCTION EQ_FN(LEFT TEST_T, RIGHT TEST_T)
RETURNS INTEGER
BEGIN
DECLARE RESULT INTEGER;
PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
EXECUTE S1 INTO RESULT USING LEFT, RIGHT;
RETURN RESULT;
END@
BEGIN
DECLARE T1 TEST_T;
DECLARE T2 TEST_T;
DECLARE RESULT INTEGER;
SET T1.ONE = 1;
SET T2.ONE = 1;
SET RESULT = EQ_FN(T1, T2);
CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@<!--end_raw-->
This did not work. The error message was:
<!--start_raw-->DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0577N User defined routine "KMCDONAL.EQ" (specific name "SQL100516224914100") attempted to modify data but was not defined as MODIFIES SQL DATA.<!--end_raw-->
My EQ procedure does not actually modify SQL data, so why does it say it attempted to? Do you know what I did wrong? I’ll give the answer in DB2 Stored Procedures and Defaults (Conclusion). Coverage of db2top continues next week.