I am using DB2 v7 for OS/390. I'm writing several stored procedures that will be called from an ASP web site. A few of these require the use of a temp table to create a recordset that I ultimately want to return from the stored procedure. I'm using DECLARE GLOBAL TEMPORARY TABLE in the stored procedure, then I'm using DECLARE CURSOR / OPEN to return the results to ASP.

At first, I had the statement "DROP TABLE SESSION.RESULTS" at the end of the stored procedure (after opening the cursor), however, this seems to remove all the rows from the open cursor so that I don't get any records. However, once I took this statement out, the next time I run the stored procedure I get a message indicating that the table "SESSION.RESULTS" already exists.

I need to be able to create the temp table, return the results to the calling ASP page, then effectively drop the SESSION.RESULTS table. Additionally, I need to ensure that multiple users hitting the ASP site at the same time will each have their own SESSION.RESULTS table. I want each call to the stored procedure to use it's own temp table.

Any ideas on how I can make this happen? TIA!

Here is one of the stored procedures where this is a problem:


CREATE PROCEDURE SHXT.WSFSP003 (
IN StyleType VARCHAR(50),
IN SubCategory VARCHAR(50),
IN SellingColorNumber VARCHAR(5),
IN RoomUse VARCHAR(50),
IN RecordsPerPage SMALLINT,
IN PageNumber SMALLINT
)
DYNAMIC RESULT SETS 1
PARAMETER CCSID EBCDIC
LANGUAGE SQL
EXTERNAL NAME WSFSP003
PARAMETER STYLE GENERAL WITH NULLS
NOT DETERMINISTIC
FENCED
CALLED ON NULL INPUT
MODIFIES SQL DATA
NO DBINFO
COLLID SHXTPROC
WLM ENVIRONMENT DBT1SPTM
ASUTIME LIMIT 100
STAY RESIDENT NO
PROGRAM TYPE SUB
SECURITY USER
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS

P1: BEGIN

DECLARE RS1 CURSOR WITH RETURN FOR
SELECT MfgStyleNumber,
MfgColorNumber,
SellingStyleNumber,
SellingColorNumber,
SellingColorName,
TotalRecordCount
FROM SESSION.RESULTS;

DECLARE GLOBAL TEMPORARY TABLE SESSION.RESULTS (
TID INTEGER GENERATED ALWAYS AS IDENTITY,
MfgStyleNumber VARCHAR(5),
MfgColorNumber VARCHAR(5),
SellingStyleNumber VARCHAR(5),
SellingStyleName VARCHAR(50),
SellingColorNumber VARCHAR(5),
SellingColorName VARCHAR(50),
Priority SMALLINT,
TotalRecordCount SMALLINT
) ON COMMIT PRESERVE ROWS;


INSERT INTO SESSION.RESULTS (MfgStyleNumber,
MfgColorNumber,
SellingStyleNumber,
SellingStyleName,
SellingColorNumber,
SellingColorName,
Priority)
SELECT DISTINCT A.PRODUCT_ID,
B.INV_COLOR_NBR,
A.SELLING_STYLE_NBR,
A.SELLING_STYLE_NAME,
B.SELLING_COLOR_NBR,
B.SELLING_COLOR_NAME,
0)
FROM SHXT.SELL_STYLE A INNER JOIN SHXT.SELLING_COLORS B
ON A.SELLING_STYLE_NBR = B.SELLING_STYLE_NBR
WHERE A.CUST_REFERENCE_NBR = '102'


OPEN RS1;

--DROP TABLE SESSION.RESULTS;

END P1