DB2oC (DB2 on Cloud) : Facing "attempted to modify data but was not defined as MODIFIES SQL DATA" error

user_14202768 0
Viewed 299

I have created very simple function in DB2oC as below, which has one UPDATE sql statement and one SELECT sql statement along with MODIFIES SQL DATA. But still I get the below error, though I have specified MODIFIES SQL DATA. I did GRANT ALL on that TEST table to my user id and also did GRANT EXECUTE ON FUNCTION to my user id on safe side. Can you please help to explain on what could be the issue?

I have simply invoked the function using SELECT statement like below:

SELECT TESTSCHEMA.MAIN_FUNCTION() FROM TABLE(VALUES(1));

SQL Error [38002]: User defined routine "TESTSCHEMA.MAIN_FUNCTION" (specific name "SQL201211013006981") attempted to modify data but was not defined as MODIFIES SQL DATA.. SQLCODE=-577, SQLSTATE=38002, DRIVER=4.27.25

CREATE OR REPLACE FUNCTION MAIN_FUNCTION()
RETURNS VARCHAR(20)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
  DECLARE val VARCHAR(20);
  UPDATE TEST t SET t.CONTENT_TEXT = 'test value' WHERE t.ID = 1;
  select CONTENT_TEXT into val from TEST where ID = 1;
  return val;
end;

Appreciate your help.

db2-luw db2
2 Answers

For the modifies SQL data clause , the usage of the function is restricted on Db2-LUW.

These restrictions do not apply for user defined functions that do not modify data.

For your specific example, that UDF will operate when used as the sole expression on the right hand side of an assignment statement in a compound-SQL compiled statemnent.

For example:

create or replace variable my_result varchar(20) default null;

begin
    set my_result = main_function();
end@

Consider using stored procedures to modify table contents, instead of user defined functions.

user_82906340

You could avoid using a function, and just use a single "change data statement"

SELECT CONTENT_TEXT
FROM NEW TABLE(
    UPDATE TEST t
    SET t.CONTENT_TEXT = 'test value'
    WHERE t.ID = 1
)
user_95253440
Related