3

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.

Krishna
  • 29
  • 2
  • Most likely incorrect usage. Please edit your question to show how you __invoke__ the function. Please note that the function can only appear on the *right hand side* of an assignment statement in a compound-SQL compiled statement. – mao Dec 11 '20 at 13:06
  • Thank you, @mao for response. I have edited the question on how I have invoked using SELECT statement, which usually works while I tested other functions. – Krishna Dec 11 '20 at 13:24

2 Answers2

3

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.

mao
  • 11,321
  • 2
  • 13
  • 29
0

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
)
Paul Vernon
  • 3,818
  • 1
  • 10
  • 23