DB2 Stored Procedures and Defaults

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:

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@

The above code worked just fine. As expected, the result looked like this:

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

But then I moved the prepare and execute code in the anonymous block above into a scalar function named EQ_FN:

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@

This did not work. The error message was:

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.

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.

If you enjoyed this post, make sure you subscribe to my RSS feed!
  • Search