Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Posts
    15

    Unanswered: Temporary table not dropped after terminate

    Hi

    I am working on DB2 9.7 and below is the DDL for temporary table

    Code:
    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.

  2. #2
    Join Date
    Dec 2012
    Posts
    15
    Below is the error when I try creating the temporary table after "terminate"

    Code:
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    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...

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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.

    Andy

  6. #6
    Join Date
    Dec 2012
    Posts
    15
    Thanks all

Posting Permissions

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