Hi
I am getting SQL out as - 284, for the following declared cursor, I have declared a cursor and feteched the same and inserted teh results in a temp table.

further did proccessing form retriving from temp tables like manipulation on temp table data.

but I am not getting any output form cursor
CREATE PROCEDURE SP_Clean_tab
(IN CLEAN_PERIOD INTEGER,
OUT SQLSTATE_OUT CHAR(5),
OUT SQLCODE_OUT INTEGER )
DYNAMIC RESULT SETS 2
LANGUAGE SQL
P1: BEGIN
-- Declare variables
DECLARE CLEAN_PERIOD_VAR INTEGER;
DECLARE CLEAN_POID BIGINT;
DECLARE CLEAN_PACKID BIGINT;
DECLARE VAR_INVOICE_ID VARCHAR(20);
DECLARE END_TABLE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
-- Declare cursors
DECLARE C1 CURSOR WITH HOLD WITH RETURN FOR
SELECT PO_UNI_ID, PACK_UNI_ID
FROM INS.INS_PO
WHERE (TIMESTAMPDIFF(64,CHAR((CURRENT_TIMESTAMP) - (CHECKED_DTTM)))) > (MAX(12, CLEAN_PERIOD_VAR))
AND CHECKED_DTTM is NOT NULL
AND STATUS_CODE = 'CHECKED ‘;
-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT FROM SYSIBM.SYSDUMMY1;

DECLARE Continue HANDLER FOR NOT FOUND
Begin
SET END_TABLE = 1;
End;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
(DEL_POID BIGINT, DEL_PACKID INTEGER, DEL_INVOICE_ID VARCHAR(20), DEL_PERIOD INTEGER)IN TPACE1 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;
-- INITIALIZE OUTPUT SQL PAAMAETERS
SET CLEAN_PERIOD_VAR = CLEAN_PERIOD;
OPEN C1;
L1: REPEAT
FETCH C1 INTO CLEAN_POID, CLEAN_PACKID;
IF SQLCODE = 0 THEN
-- POPULATING TEMP TABLE FOR FURTHER PROCESSING.
SELECT INVOICE_ID INTO VAR_INVOICE_ID FROM INS.INS_PO_CLASS_ELEMENTS WHERE PO_UNI_ID = CLEAN_POID;
INSERT INTO SESSION.TEMP_CLEAN VALUES (CLEAN_POID, CLEAN_PACKID, VAR_MRN, VAR_INVOICE_ID, VAR_UPLOAD_ID, VAR_UPLOAD_SN, CLEAN_PERIOD_VAR);
COMMIT;
END IF;
UNTIL END_TABLE = 1
END REPEAT L1;
CLOSE C1;

--CLEANS THE FETCHED POS FROM INS_PO TABLE.
DELETE FROM INS.INS_PO WHERE PO_UNI_ID IN (SELECT DEL_POID FROM SESSION.TEMP_CLEAN);
-- CLEAN CORRESPONDING PACK_UNI_ID FROM INS_PACK_PO TABLE
DELETE FROM INS.INS_PACK_PO A
WHERE (0 = (SELECT COUNT(*) FROM INS.INS_PO C WHERE C.PACK_UNI_ID = A.pack_UNI_ID))
AND A.PACK_UNI_ID IN (SELECT DEL_PACKID FROM SESSION.TEMP_CLEAN);


SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
END P1

Read the error message:

You are specifying the wrong type of tablespace for the temp table.

Andy

I'm wondering: why don't you do a simple INSERT INTO ... SELECT ... ?

Thanks for your effots and time.

I checked and re ran the procedure with table space as Tempspace2 which is created, but still I am getting same sql code -284 and sql state as 42838.

And I am not using Insert Select combined because , I have to select few columns from one table and few from another table for inserting into teh table.

Is there a some way I can debug my procedure or we canuse Signal.

Thanks in advance.
Regards
Nee

Nee, Unless I am missing something, you are defining TEMP_CLEAN with 4 columns. But your Insert into TEMP_CLEAN has 7 values.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
(DEL_POID BIGINT
,DEL_PACKID INTEGER
,DEL_INVOICE_ID VARCHAR(20)
,DEL_PERIOD INTEGER)
IN TPACE1 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;

INSERT INTO SESSION.TEMP_CLEAN
VALUES
(CLEAN_POID
,CLEAN_PACKID
,VAR_MRN
,VAR_INVOICE_ID
,VAR_UPLOAD_ID
,VAR_UPLOAD_SN
,CLEAN_PERIOD_VAR);


I agree with stolze, you should be able to use INSERT INTO table-name Select...

Something like this should work:

INSERT INTO SESSION.TEMP_CLEAN
SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID, CLEAN_PERIOD
FROM INS.INS_PO AS A
, INS.INS_PO_CLASS_ELEMENTS AS B
WHERE A.PO_UNI_ID = B.PO_UNI_ID
AND TIMESTAMPDIFF(64,CHAR(CURRENT_TIMESTAMP - A.CHECKED_DTTM)) > MAX(12, CLEAN_PERIOD)
AND A.CHECKED_DTTM is NOT NULL
AND A.STATUS_CODE = 'CHECKED'

PS Personally, instead of TIMESTAMPDIFF, I would use MONTH(CURRENT_TIMESTAMP - A.CHECKED_DTTM) > MAX(12, CLEAN_PERIOD). To me it is easier to see what is going on (but it is a personal preference.

Thanks folks,

Defintely this way of insert is good and I can avoid using cursor, it will work.
but basically again i am getting sql code as -284 and sql state out put as 42838.

Basically I am having stored proc as

create procedure sp_clean_pos
(IN clean_PERIOD INTEGER,
OUT SQLSTATE_OUT CHAR(5),
OUT SQLCODE_OUT INTEGER )
DYNAMIC RESULT SETS 2
LANGUAGE SQL



DECLARE clean_PERIOD_VAR INTEGER;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;


DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
(DEL_POID BIGINT
,DEL_PACKID INTEGER
,DEL_INVOICE_ID VARCHAR(20)
)
IN Tempspace2 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED

-- INITIALIZE OUTPUT SQL PAAMAETERS
SET CLEAN_PERIOD_VAR = CLEAN_PERIOD;

INSERT INTO SESSION.TEMP_CLEAN
SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID,
FROM INS.INS_PO AS A
, INS.INS_PO_CLASS_ELEMENTS AS B
WHERE A.PO_UNI_ID = B.PO_UNI_ID
AND TIMESTAMPDIFF(64,CHAR(CURRENT_TIMESTAMP - A.CHECKED_DTTM)) MAX(12, CLEAN_PERIOD)
AND A.CHECKED_DTTM is NOT NULL
AND A.STATUS_CODE = 'CHECKED'
COMMIT;
SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;

Can you please suggest that I have created a separate tempspace2 for using to declare GTTs, but still it gives sql out as -284.

Nee, a -284 indicates a problem with the Table space you specified (as ARWinner pointed out).

From the SQL Reference manual under Declare Global Temporary Table:

IN tablespace-name

Identifies the table space in which the global temporary table will be instantiated.The table space must exist and be a USER TEMPORARY table space (SQLSTATE 42838), over which the authorization ID of the statement has USE privilege (SQLSTATE 42501).

Check the type of Table space Tempspace2 to ensure it is a User Temporary table space and/or who has the Use privilege.

PS This could just be a typo but you have an extra comma in the Select list:

SELECT A.PO_UNI_ID, A.PACK_UNI_ID, B.INVOICE_ID, <==
FROM INS.INS_PO AS A

Yes thats true the sql code -284 is problem with tablespace used in GTT declaration.
I have used the GTT decalaration as follows
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_CLEAN
(DEL_POID BIGINT
,DEL_PACKID INTEGER
,DEL_INVOICE_ID VARCHAR(20)
)
IN Tempspace2 ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED
and
the tempsace2 i have created as

CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
IN DATABASE PARTITION GROUP IBMTEMPGROUP
PAGESIZE 4 K
MANAGED BY DATABASE
USING ( FILE '/db2/db2inst4/db2inst4/.../...TMP' 5000)
EXTENTSIZE 32
BUFFERPOOL IBMDEFAULTBP
TRANSFERRATE 0.06;
still same error and we cannot grant privileges to system temporary tablespace.
then i tried creating normal tablespace as
CREATE TABLESPACE tempsPACE2 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE '/db2/db2inst4/db2inst4/../../....TMP' 5000) EXTENTSIZE 32 BUFFERPOOL IBMDEFAULT
BP TRANSFERRATE 0.06.
and granted privilleges to Publlic for this tablespace, still same error as sql code -284.

Thanks for all your efforts and support.
Regards

If you read the manual you will see that for DECLARE GLOBAL TEMPORARY TABLE, the tablespace must be a USER TEMPORARY tablespace, not a SYSTEM TEMPORARY one.

Andy

Thanks Folks for all my learnings.

My proc is working with Insert.. Select statment and w/o cursor.
Also I have reacreated teh tempspace2 as user temporary tablespace.

Regards

Neet