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


;

Have you created a User temperory tablespace for the GTTs ...

Example :
db2 "create user temporary tablespace usertemp1 managed by system using ('usertemp1')"

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;

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