Unanswered: Temporary table not dropped after terminate
I am working on DB2 9.7 and below is the DDL for temporary table
CREATE GLOBAL TEMPORARY TABLE SESSION.TEST_1 (TGT_EMP_SKEY BIGINT,TGT_WRKD_SHFT_SKEY BIGINT,TGT_TSITEM_SKEY BIGINT,MEAL_ORDER_FCT_PAYCD SMALLINT,MEAL_ORDER_TMISC SMALLINT,SHORT_MEAL_IND CHAR(1), LATE_MEAL_IND CHAR(1),MISSED_MEAL_IND CHAR(1),EXCP_DESC VARCHAR(11), CURRENT_BRK_CNT INTEGER,MEAL_ORDER INTEGER, LATEMEALEXCPHIST VARCHAR(200)) ON COMMIT PRESERVE ROWS NOT LOGGED
If I open a different session on another putty window and hit describe SESSION.TEST_1 the above definition shows up (but always has 0 records when queried from other session), Confused here as I was having an understanding that these tables are local only to the session.
More over the above with couple more are used in a stored procedure and each time before invoking the procedure even from another DB2 session I am forced to drop them.
Below is the error when I try creating the temporary table after "terminate"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0601N The name of the object to be created is identical to the existing
name "SESSION.TEST_1" of type "CREATE TEMPORARY TABLE". SQLSTATE=42710
Use "WITH REPLACE" as part of the syntax of the declare global temporary table.
DB2 will drop the session table when the connection closes.
You will get SQL0601N on the 'declare global...' if you omitted 'with replace' and the same connection re-issues the declare global temporary...
In version 9.7 and later DB2 introduced a new temporary table CGTT. These are temporary tables that have their definition persisted in the database. When you use CREATE instead of DECLARE, you are creating a CGTT. Read the manual for further information on CGTT.