Results 1 to 2 of 2

Thread: db2 UDF problem

  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: db2 UDF problem

    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

  2. #2
    Join Date
    Aug 2009
    Posts
    12
    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.
    Last edited by sunil.tcs; 11-11-10 at 07:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •