Hi,

I am stuck at an issue; not able to resolve it. Need your help.

There is a procedure say PROC1 which recursively calls itself. This procedure is called from a function lets say FUNC1 for a list of values from with in a FOR Loop passing a value fetched from a table.

PROC1 header looks somewhat as mentioned below:

CREATE PROCEDURE schema1.PROC1
(INOUT param1 INTEGER, INOUT param2 INTEGER, INOUT param3 INTEGER)
SPECIFIC PROC1
LANGUAGE SQL
MODIFIES SQL DATA

CREATE FUNCTION schema1.FUNC1 ( param1 VARCHAR(16) )
RETURNS INTEGER
SPECIFIC FUNC1
LANGUAGE SQL
MODIFIES SQL DATA

F1:BEGIN ATOMIC
DECLARE var1 INTEGER;
DECLARE var2 INTEGER;
DECLARE var3 INTEGER;

FOR LOOP (<<SELECT statement>> for a particular param1)
DO
CALL schema1.PROC1 (var1, var2, var3);
END FOR;
--------------------
---Some Logic------

RETURN var3;
END F1

Procedure is compiling without any errors.
But when I try to compile the function, I am getting the below errors:

DB2 SQL Error: SQLCODE=-628, SQLSTATE=42613, SQLERRMC=MODIFIES SQL DATA, DRIVER=3.53.71

Can you please help me in resolving this problem?

Function (FUNC1) is compiling without any errors when the code is compiled commenting the statement which calls the procedure (PROC1).

Thanks,
Satish

You can't specify MODIFIES SQL DATA for a function(SQL scalar).

You can see the following note for MODIFIES SQL DATA in manual "DB2 9.7 for LUW SQL Reference, Volume 2"

Thanks Tonkuma.

I tried compiling FUNC1 without using MODIFIES SQL DATA clause. But, I am now getting an error different from previous one. Below is the error I am getting:

DB2 SQL Error: SQLCODE=-374, SQLSTATE=428C2, SQLERRMC=MODIFIES SQL DATA;schema1.FUNC1, DRIVER=3.53.71

I have pasted function and procedure body again for your reference:

FUNC1 function is somewhat as mentioned below:
CREATE FUNCTION schema1.FUNC1 ( param1 VARCHAR(16) )
RETURNS INTEGER
SPECIFIC FUNC1
LANGUAGE SQL

F1:BEGIN ATOMIC
DECLARE var1 INTEGER;
DECLARE var2 INTEGER;
DECLARE var3 INTEGER;

FOR LOOP (SELECT col1 from tab1 WHERE col2 = param1)
DO
SET var1 = LOOP.col1
CALL schema1.PROC1 (var1, var2, var3);
END FOR;
--------------------
---Some Logic------

RETURN var3;
END F1

PROC1 header looks like:

CREATE PROCEDURE schema1.PROC1
(INOUT param1 INTEGER, INOUT param2 INTEGER, INOUT param3 INTEGER)
SPECIFIC PROC1
LANGUAGE SQL
P1:BEGIN
DECLARE v_procedure_call VARCHAR(100);
------------------------
------------------------
SET v_procedure_call = 'CALL schema1.PROC1 (?, ?, ?)';
PREPARE proc_exec FROM v_procedure_call;
EXECUTE proc_exec
INTO param1, param2, param3
USING param1, param2, param3;
END P1

Can you please let me know where I am going wrong?

Thanks,
Satish

Sorry ... I forgot to mention the DB2 Version.

DB2 version that I'm using is v9.5

Thanks,
Satish

Specify READS SQL DATA for "CREATE PROCEDURE schema1.PROC1 ...".

MODIFIES SQL DATA is default for SQL Procedure.

I'm getting a different error when I am compiling the procedure with READS SQL DATA. The error is:
SQLCODE=-577, SQLSTATE=42985
And the line where it is showing error in the code is where I have used READS SQL DATA clause.

I checked PROC1 for any DML statements updating any table. No DML statements have been used in PROC1.

The only typical thing happening is:

SET v_procedure_call = 'CALL schema1.PROC1 (?, ?, ?)';
PREPARE proc_exec FROM v_procedure_call;
EXECUTE proc_exec
INTO param1, param2, param3
USING param1, param2, param3;

We are passing param1, param2 & param3; as all these are INOUT parameters to PROC1, I am storing the value again back in the same parameters.

For ex: If we are passing 1,2,3 for param1, param2 & param3 respectively; and param1, param2, param3 are reset with 4,5,6 in RECURSIVE PROC1 call then after returning from RECURSIVE call I am assigning 4,5,6 into param1, param2 & param3 respectively.

Thanks,
Satish

-577 is SQL0577
Somewhere you do have a SQL statement that tries to modify some data.

You can create recursive procedure without using dynamic SQL
by using MODULE.

Here is an example creating a recursive function:
comp.databases.ibm-db2 | Google Groups

By using this way, you can create a recursive SQL procedure with READS SQL DATA, if you didn't modify any data.

I'm sorry. You can't use MODULE on DB2 9.5.
It's supported from DB2 9.7.

Thanks tonkuma. Is there any other alternative to implement recursive Stored Procedure in DB2 V9.5?

My recursive stored procedure is getting compiled without any errors when the procedure is compiled commenting PREPARE and EXECUTE statements used to call the procedure recursively.