Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: Error calling a Stored Procedure from a Function in DB2

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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"
    CREATE FUNCTION (SQL scalar, table, or row)

    ...
    MODIFIES SQL DATA (2)
    ...

    Notes:
    ...
    2 Valid if RETURNS specifies a table (that is, TABLE column-list). ...
    ...

  3. #3
    Join Date
    Aug 2010
    Posts
    7
    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

  4. #4
    Join Date
    Aug 2010
    Posts
    7
    Sorry ... I forgot to mention the DB2 Version.

    DB2 version that I'm using is v9.5

    Thanks,
    Satish

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Specify READS SQL DATA for "CREATE PROCEDURE schema1.PROC1 ...".

    MODIFIES SQL DATA is default for SQL Procedure.

  6. #6
    Join Date
    Aug 2010
    Posts
    7
    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

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    -577 is SQL0577
    SQL0577N User defined routine "<routine-name>" (specific name
    "<specific-name>") attempted to modify data but was not defined as
    MODIFIES SQL DATA.

    Explanation:

    The program used to implement the body of a routine is not allowed to
    modify SQL data.

    User response:

    Remove any SQL statements that modify data then recompile the program.
    Investigate the level of SQL allowed as specified when defining the
    routine.
    Somewhere you do have a SQL statement that tries to modify some data.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 version that I'm using is v9.5
    I'm sorry. You can't use MODULE on DB2 9.5.
    It's supported from DB2 9.7.

  10. #10
    Join Date
    Aug 2010
    Posts
    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.

Posting Permissions

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