is it possible to do an insert and then select from same table within a UDF in DB2. I have a UDF AAA.GET_DEAL_COLL_test which I need to use as below.
this UDF calls an SP AAA.TEST_TEST which inserts a record in a table and then I have to select from the same table withinn UDF.
but I get SQL0746N error.

db2 "SELECT * FROM TABLE(AAA.GET_DEAL_COLL_test('test_record')) "

drop PROCEDURE AAA.TEST_TEST @

CREATE PROCEDURE AAA.TEST_TEST(IN IN_DEAL_COLL VARCHAR(100))
SPECIFIC AAA.TEST_TEST
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE V_SQL VARCHAR(500);
DECLARE V_SQL_1 VARCHAR(500);

DECLARE c1 CURSOR WITH HOLD FOR SELECT SQL FROM SPROD.DEAL_COLLECTION WHERE COLLECTION_NAME = IN_DEAL_COLL;
open c1;
fetch c1 into V_SQL ;
close c1;

SET V_SQL_1 = 'INSERT INTO SPROD.DEAL_ID ' || V_SQL;

EXECUTE IMMEDIATE V_SQL_1;

COMMIT;
END
@


drop FUNCTION AAA.GET_DEAL_COLL_test @

CREATE FUNCTION AAA.GET_DEAL_COLL_test(IN_DEAL_COLL VARCHAR(100))
RETURNS TABLE(DEAL_ID INTEGER)
SPECIFIC AAA.GET_DEAL_COLL_test
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
NULL CALL
INHERIT SPECIAL REGISTERS
MODIFIES SQL DATA
BEGIN ATOMIC

CALL AAA.TEST_test(IN_DEAL_COLL);


RETURN SELECT * FROM SPROD.DEAL_ID ;
END
@







db2 "SELECT * FROM TABLE(AAA.GET_DEAL_COLL_test('test_record')) "

SQL0746N Routine "AAA.TEST_TEST" (specific name "TEST_TEST") violated
nested SQL statement rules when attempting to perform operation "MODIFY" on
table "SPROD.DEAL_ID". SQLSTATE=57053

I am using DB2 v9.5 fixpack 4 .. I dont get any issue in UDF and SP creation. problem occurs when I select from the UDF.

Is the issue because of the insert is not commited due to "BEGIN ATOMIC". tried selecting records using "with UR" but doesnt work, I think its not supported within UDF. Any help would be highly appreciated.