Calling a stored procedure in a user defined function

[mohitgyl]

Hello,

I am trying to call a stored procedure in a user defined function.

CREATE FUNCTION test () 
RETURNS DECIMAL (15,4) 
LANGUAGE SQL 

READ SQL DATA 
EXTERNAL ACTION 



F1: BEGIN 
DECLARE VAR1 DECIMAL(15,4); 
DECLARE VAR2 VARCHAR(256); 

call test1('C','F',545,VAR1,VAR2); 

RETURN var1; 


END

The Stored procedure 'test1' does a lot of SQL work inside it and finally gives two output parameters and does not any DML operation inside it.

Inside UDF 'test' when this stored procedure is called, following error is seen.

SQL0577N User defined routine <> (specific name
"") attempted to modify data but was not defined as MODIFIES SQL DATA or was
used in a context that does not allow MODIFIES SQL DATA. LINE NUMBER=29.
SQLSTATE=38002

I understand that Maximum allowed SQL access level in scaler UDF is READS SQL DATA and this is what my code is doing.

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/inde x.jsp?topic=%2F com.ibm.db2.luw .apdv.routines. doc%2F doc%2Fr0020478. html

Problem here is I could solve this in my Development Env and codes works really fine as expected, but when I was trying to deploy this call in other Enviroment, I got the above error. It proves conceptually and logically there is no problem in the code.

More or less looks to be an Environment Issue. Have done a rebind to the package got created for Stored Procedure.

Am I missing something.

OS: RHEL 5.5
DB2 - 9.7 FP4