DECLARE GLOBAL TEMPORARY TABLE Vs CREATE GLOBAL TEMPORARY TABLE in DB2

Question

am creating a GLOBAL TEMPORARY TABLE in DB2. and when i surfed i got a two way to create 1. Declare 2. Create.

1. DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
      (EMPNO  CHAR(6) NOT NULL,
       SALARY DECIMAL(9, 2),
       BONUS  DECIMAL(9, 2),
       COMM   DECIMAL(9, 2)) WITH REPLACE ON COMMIT PRESERVE ROWS ;

2. CREATE GLOBAL TEMPORARY TABLE TMPDEPT
      (TMPDEPTNO   CHAR(3)      NOT NULL,
       TMPDEPTNAME VARCHAR(36)  NOT NULL,
       TMPMGRNO    CHAR(6),
       TMPLOCATION CHAR(16) ) ON COMMIT PRESERVE ROWS ;

and from IBM site i got a info that create is the best since its being persistent , allowing all user sessions to access the same table definition without having to declare it at startup and many more advantages.

Link : http://www.ibm.com/developerworks/data/library/techarticle/dm-0912globaltemptable/

and i had few queries in using create over declare:

1. I couldn't find the Replace keyword while using CREATE GLOBAL TEMPORARY TABLE .

2. consider one scenario, am opening a connection and executing a Stored Procedure,
   within that Stored Procedure am creating Global temp table and with in that Stored Procedure am calling Another Stored Procedure which again have same Create Temp table statement .. what will happen in this case.. does it throw any error since both table naes are same and within the single connection?

3. Declare have session and create doesn't have?? does this related to persistant??

4. in performace wise which is better? Declare temp or create temp?

5. Suggest some scenarioes for the best usage of declare / create !!

Answer 1

Craig S. Mullins article applies to DB2 for OS/390 platform. In DB2 9.7 you actually can create indexes for both DGTT and CGTT. Also you can enable logging in CGTT. For 9.7 read here: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054491.html