Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Unanswered: What's the problem with Stored Proc from IBM App. Dev. Guide

    Hi There,

    We copy a stored proc from IBM DB2 "Application Development Guide - Version 7" Page 258 to 260 as the following. But when we try to run it with the following DB2 command:

    db2 => call LOTADMIN.temp_table_create(10,20,?,?)
    PARM3: 0
    PARM4: 0

    "TEMP_TABLE_CREATE" RETURN_STATUS: "-30850"

    db2 =>

    What's the problem? How to read 'RETURN_STATUS: "-30850"'?

    Thanks,

    The Stored Proc:

    CREATE PROCEDURE LOTADMIN.temp_table_create(IN parm1 INTEGER, IN parm2 INTEGER,
    OUT parm3 INTEGER, OUT parm4 INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE loc1 RESULT_SET_LOCATOR VARYING;
    DECLARE total3,total4 INTEGER DEFAULT 0;
    DECLARE rcolumn1, rcolumn2 INTEGER DEFAULT 0;
    DECLARE result_set_end INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
    BEGIN
    SET result_set_end = 1;
    END;
    --Create the temporary table that is used in both this SQL procedure
    --and in the SQL procedure called by this SQL procedure.
    DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
    NOT LOGGED;
    --Insert rows into the temporary table.
    --The result set includes these rows.
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+1, parm2+1);
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+2, parm2+2);
    --Make a nested call to the 'temp_table_insert' SQL procedure.
    CALL temp_table_insert(parm1, parm2);
    --Issue the ASSOCIATE RESULT SET LOCATOR statement to
    --accept a single result set from 'temp_table_insert'.
    --If 'temp_table_insert' returns multiple result sets,
    --you must declare one locator variable (for example,
    --ASSOCIATE RESULT SET LOCATOR(loc1, loc2, loc3) for each result set.
    ASSOCIATE RESULT SET LOCATOR(loc1) WITH PROCEDURE temp_table_insert;
    --The ALLOCATE statement is similar to the OPEN statement.
    --It makes the result set available in this SQL procedure.
    ALLOCATE cursor1 CURSOR FOR RESULT SET loc1;
    --Insert rows into the temporary table.
    --The result set does not include these rows.
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+5, parm2+5);
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+6, parm2+6);
    SET result_set_end = 0;
    --Fetch the columns from the first row of the result set.
    FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
    WHILE (result_set_end = 0) DO
    SET total3 = total3 + rcolumn1;
    SET total4 = total4 + rcolumn2;
    --Fetch columns from the result set for the
    --next iteration of the WHILE loop.
    FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
    END WHILE;
    CLOSE cursor1;
    SET parm3 = total3;
    SET parm4 = total4;
    END

    ;

    DROP SPECIFIC PROCEDURE LOTADMIN.SQL040601103717620
    ;
    CREATE PROCEDURE LOTADMIN.temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER )
    LANGUAGE SQL
    BEGIN
    DECLARE result_set_end INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN
    SET result_set_end = 1;
    END;
    --To use a temporary table that is created by a different stored
    --procedure, include a DECLARE GLOBAL TEMPORARY TABLE statement
    --inside a condition statement that always evaluates to false.
    IF (1 = 0) THEN
    DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
    NOT LOGGED;
    END IF;
    --Insert rows into the temporary table.
    --The result set includes these rows.
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3);
    INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4);
    --To return a result set from the temporary table, issue
    --the DECLARE CURSOR statement inside a new scope, such as
    --a compound SQL statement (BEGIN...END block).
    --Issue the DECLARE CURSOR statement after the DECLARE
    --GLOBAL TEMPORARY TABLE statement.
    BEGIN
    --The WITH RETURN TO CALLER clause causes the SQL procedure
    --to return its result set to the calling procedure.
    DECLARE cur1 CURSOR WITH RETURN TO CALLER
    FOR SELECT * FROM session.ttt;
    --To return a result set, open a cursor without closing the cursor.
    OPEN cur1 ;
    END;
    END


    ;

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you created a User temperory tablespace for the GTTs ...

    Example :
    db2 "create user temporary tablespace usertemp1 managed by system using ('usertemp1')"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    61
    Yes, the command used is:

    CREATE USER TEMPORARY TABLESPACE TS1 IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
    USING ('/db2data1/.../NODE0000/SQL00001/ts1file')
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL IBMDEFAULTBP
    OVERHEAD 24.100000
    TRANSFERRATE 0.900000;

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    hmmm .. any errors in db2diag.log file ?

    Set your diaglevel to 4, execute the proc and then review what db2diag.log file has

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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