DB2 9.5 SQL Procedure Developer Certification Examination 735 preparation, 5th part

Global declarative Temporary tables

Temporary tables are often used to store temporary data and intermediate results. Because they do not require logging and are not present in the system catalog, they can improve performance. In addition, because a temporary table allows only a single connection, no locks are required.

Only a declaration (create) declarative global temporary table (DGTT) connection can access it. When this database connection ends, the temporary table is deleted.

To create a DGTT, you need to execute the DECLARE GLOBAL temporary TABLE statement. Here is the syntax diagram for this statement:

Listing 1. Syntax for global temporary table declarations

.-,---------------------.
V |
>--+-(----| column-definition |-+--)-----------------------------+-->
+-like--+-table-name1-+--+------------------+-----------------+
| '-view-name---'-| copy-options | |
'-as--(--fullselect--)--definition only--+------------------+-'
'-| copy-options | '
.-on COMMIT DELETE ROWS---.
>--?--+-------------------------+--?---------------------------->
'-on COMMIT PRESERVE rows-'
>--+-------------------------------------------+---------------->
| .-on ROLLBACK DELETE ROWS---. |
'-not logged--+---------------------------+-'
'-on ROLLBACK PRESERVE rows-'
>--?--+--------------+--?--+---------------------+-------------->
'-with replace-'-in--tablespace-name-'
>--?--+------------------------------------------------------------+--?-><
|　　　　　　　　　　　 .-,-----------. |
|　　 V | .-using hashing-. |
'-partitioning key--(----column-name-+--)--+---------------+-'
Column-definition
|--column-name--| Data-type |--+--------------------+-----------|
'-| column-options | '

Note that when you specify the WITH REPLACE clause, an existing DGTT with the same name is deleted and replaced with the new table definition.

Each session that defines a declarative global temporary table with the same name has its own unique temporary table description. When the session terminates, the table row and temporary table descriptions are deleted.

Some options are explained below:

On commit delete rows: Deletes all rows in the table if the with HOLD cursor is not open on the table when the COMMIT operation is performed. This is the default setting.

On commit PRESERVE rows: All rows in the table are preserved when a COMMIT is performed.

On ROLLBACK Delete rows: When you perform a ROLLBACK (or ROLLBACK to savepoint) operation, all rows in the table are deleted if the table data has been modified. This is the default setting.

On ROLLBACK PRESERVE rows: Preserves all rows in the table when the ROLLBACK (or ROLLBACK to SavePoint) operation is performed.

Note that blobs, CLOB, Dbclob, long VARCHAR, long vargraphic, XML, references, and structured types cannot be used as data types for columns of declared global temporary tables.

Follow these steps to use the DB2 GDTT:

Step 1. Make sure that there is a user temp table space. If there is no user temporary tablespace, execute the CREATE user temporary tablespace statement using the following syntax:

CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY SYSTEM USING
　　　　　　　　 ('c:\temp\usertempspace') ;

Step 2. Executes the DECLARE GLOBAL temporary TABLE statement in the application using the syntax provided earlier. For example:

Listing 2. Examples of DGTT declarations

DECLARE GLOBAL TEMPORARY TABLE temp_proj
　　　　 (projno CHAR(6), projname VARCHAR(24), projsdate DATE, projedate DATE,)
　　WITH REPLACE
　　ON COMMIT PRESERVE ROWS
　　NOT LOGGED
　　IN usr_tbsp ;

The database schema for a declarative temporary table is always session.