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 !!

Solution 1

There is a good article from Craig S. Mullins that covers the major differences between the two. For most purposes, they work the same.

Created Temp Tables are created in DSNDB07, which is the working file database (the same storage area used during SQL statements that need working storage). Declared Temp Tables are stored in temporary tablespace that you have to create.

There are some cons for CTT's:

• Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.

• Indexes can not be created on created temporary tables so all access is by a complete table scan.

• Constraints can not be created on created temporary tables.

• A null is the only default value permitted for columns of a created temporary table.

• Created temporary tables can not be referenced by DB2 utilities.

• Created temporary tables can not be specified as the object of an UPDATE statement.

• When deleting from a created temporary table, all rows must be deleted.

• Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.

DTT are usually much more flexible:

• Declared temporary tables can have indexes and CHECK constraints defined on them.

• You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.

• You can implicitly define the columns of a declared temporary table and use the result table from a SELECT.

Now for your numbered questions:

1. & 2. There isn't one. I believe (and I'm not 100% sure if this is accurate, our shop pretty much uses DTTs in all cases) that a CTT is declared once (by a DBA), and then the application programmers can use it in any session. Each connection will have their own copy, and once the application disconnects, the data stored in that CTT in that session will go away.

3. SESSION is just the schema identifier for DTTs. It shows that it is a temporary table that does not persist.

4. I think both will have about the same performance. They will be faster than normal tables, because locking, logging, recovery, etc will not apply.

5. In general, I would say that DTTs are the way to go, but CTTs are useful (as Craig says in his article):

(CTTs) should be considered primarily when no updating of temporary data is needed and access to the temporary data is purely sequential.

Solution 2

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