Creating declared temporary tables
Use declared temporary tables when you need to store data for only the life of an application process and do not need to share the table definition. The definition of this table exists only while the application process runs. DB2® performs limited logging and locking operations for declared temporary tables.
About this task
You create an instance of a declared temporary table by using the SQL DECLARE GLOBAL TEMPORARY TABLE statement. That instance is known only to the application process in which the table is declared, so you can declare temporary tables with the same name in different applications. The qualifier for a declared temporary table is SESSION.
Before you can define declared temporary tables, you must have a WORKFILE database that has at least one table space with a 32-KB page size.
- Specify all the columns in the table.
Unlike columns of created temporary tables, columns of declared temporary tables can include the WITH DEFAULT clause.
- Use a LIKE clause to copy the definition of a base table, created
temporary table, or view.
If the base table, created temporary table, or view from which you select columns has identity columns, you can specify that the corresponding columns in the declared temporary table are also identity columns. To include these identity columns, specify the INCLUDING IDENTITY COLUMN ATTRIBUTES clause when you define the declared temporary table.
If the source table has a row change timestamp column, you can specify that those column attributes are inherited in the declared temporary table by specifying INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES.
- Use a fullselect to choose specific
columns from a base table, created temporary table, or view.
If you want the declared temporary table columns to inherit the defaults for columns of the table or view that is named in the fullselect, specify the INCLUDING COLUMN DEFAULTS clause. If you want the declared temporary table columns to have default values that correspond to their data types, specify the USING TYPE DEFAULTS clause.
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
(SERIAL CHAR(8) NOT NULL WITH DEFAULT '99999999',
DESCRIPTION VARCHAR(60) NOT NULL,
PRODCOUNT INTEGER GENERATED ALWAYS AS IDENTITY,
MFGCOST DECIMAL(8,2),
MFGDEPT CHAR(3),
MARKUP SMALLINT,
SALESDEPT CHAR(3),
CURDATE DATE NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD LIKE BASEPROD
INCLUDING IDENTITY COLUMN ATTRIBUTES;
DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
AS (SELECT * FROM PRODVIEW)
DEFINITION ONLY
INCLUDING IDENTITY COLUMN ATTRIBUTES
INCLUDING COLUMN DEFAULTS;
After you run a DECLARE GLOBAL TEMPORARY TABLE statement, the definition of the declared temporary table exists as long as the application process runs.
DROP TABLE SESSION.TEMPPROD;
- Populate the declared temporary table by using INSERT statements
- Modify the table using searched or positioned UPDATE or DELETE statements
- Query the table using SELECT statements
- Create indexes on the declared temporary table
The ON COMMIT clause that you specify in the DECLARE GLOBAL TEMPORARY TABLE statement determines whether DB2 keeps or deletes all the rows from the table when you run a COMMIT statement in an application with a declared temporary table. ON COMMIT DELETE ROWS, which is the default, causes all rows to be deleted from the table at a commit point, unless a held cursor is open on the table at the commit point. ON COMMIT PRESERVE ROWS causes the rows to remain past the commit point.
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
AS (SELECT * FROM BASEPROD)
DEFINITION ONLY
INCLUDING IDENTITY COLUMN ATTRIBUTES
INCLUDING COLUMN DEFAULTS
ON COMMIT PRESERVE ROWS;
EXEC SQL INSERT INTO SESSION.TEMPPROD SELECT * FROM BASEPROD;
⋮
EXEC SQL COMMIT;
⋮
When DB2 runs the preceding DECLARE GLOBAL TEMPORARY TABLE statement, DB2 creates an empty instance of TEMPPROD. The INSERT statement populates that instance with rows from table BASEPROD. The qualifier, SESSION, must be specified in any statement that references TEMPPROD. When DB2 executes the COMMIT statement, DB2 keeps all rows in TEMPPROD because TEMPPROD is defined with ON COMMIT PRESERVE ROWS. When the program ends, DB2 drops TEMPPROD.