DECLARE GLOBAL TEMPORARY TABLE

The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other application processes. Each application process that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the application process ends, the temporary table is dropped.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

If the LIKE or AS select-statement clause is specified, the privileges held by the authorization ID of the statement must include at least one of the following on any table or view specified in the LIKE clause or as-subquuery clause:

The SELECT privilege for the table or view
Ownership of the table or view
Administrative authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
Administrative authority

For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Syntax

>>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->
 
        .-,------------------------------------------.
        V                                            |
>--+-(----+-column-definition----------------------+-+--)-+----->
   |      '-LIKE--+-table-name-+--+--------------+-'      |
   |              '-view-name--'  '-copy-options-'        |
   +-LIKE--+-table-name-+--+--------------+---------------+
   |       '-view-name--'  '-copy-options-'               |
   '-as-subquery-clause-----------------------------------'
 
   .-----------------------------------------------.
   V                                               | (1)
>----+-WITH REPLACE------------------------------+-+-----------><
     | .-ON COMMIT DELETE ROWS---.               |
     +-+-------------------------+---------------+
     | '-ON COMMIT PRESERVE ROWS-'               |
     |             .-ON ROLLBACK DELETE ROWS---. |
     '-NOT LOGGED--+---------------------------+-'
                   '-ON ROLLBACK PRESERVE ROWS-'
 
column-definition:
 
|--column-name--+-------------------------------------+--------->
                |      .-COLUMN-.                     |
                '-FOR--+--------+--system-column-name-'
 
>--data-type---------------------------------------------------->
 
   .-----------------------------------------------------.
   V                                                     | (1)
>----+-------------------------------------------------+-+-------|
     +-+-default-clause------------------------------+-+
     | +-GENERATED--+-ALWAYS-----+--identity-options-+ |
     | |            '-BY DEFAULT-'                   | |
     | '-NOT NULL------------------------------------' |
     |                  (2)                            |
     '-datalink-options--------------------------------'
 
data-type:
 
|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'

Notes:

The same clause must not be specified more than once.
The datalink-options can only be specified for DATALINKs and distinct-types sourced on DATALINKs.

built-in-type:
 
|--+-+---SMALLINT---+-----------------------------------------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                                               |
   | | '-INT-----'  |                                                                                               |
   | '---BIGINT-----'                                                                                               |
   |                  .-(5,0)------------------------.                                                              |
   +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                                              |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                                              |
   |                                '-, integer-'                                                                   |
   |          .-(--52--)------.                                                                                     |
   +-+-FLOAT--+---------------+-+-----------------------------------------------------------------------------------+
   | |        '-(--integer--)-' |                                                                                   |
   | +-REAL---------------------+                                                                                   |
   | |         .-PRECISION-.    |                                                                                   |
   | '-DOUBLE--+-----------+----'                                                                                   |
   |                    .-(--1--)-------.                                                                           |
   +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+---------------------+-+
   | | | '-CHAR------'  '-(--integer--)-'                               |  +-FOR BIT DATA---+                     | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-'  +-FOR SBCS DATA--+                     | |
   | |   | '-CHAR------'          |                 '-allocate-clause-'    +-FOR MIXED DATA-+                     | |
   | |   '-VARCHAR----------------'                                        '-ccsid-clause---'                     | |
   | |                                          .-(--1M--)-------------.                                          | |
   | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+--+----------------+-' |
   |       | '-CHAR------'               |      '-(--integer--+---+--)-'  '-allocate-clause-'  +-FOR SBCS DATA--+   |
   |       '-CLOB------------------------'                    +-K-+                            +-FOR MIXED DATA-+   |
   |                                                          +-M-+                            '-ccsid-clause---'   |
   |                                                          '-G-'                                                 |
   |                .-(--1--)-------.                                                                               |
   +-+---GRAPHIC----+---------------+----------------------------+--+--------------+--------------------------------+
   | |              '-(--integer--)-'                            |  '-ccsid-clause-'                                |
   | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+                                                  |
   | | '-VARGRAPHIC------'                 '-allocate-clause-'   |                                                  |
   | |             .-(--1M--)-------------.                      |                                                  |
   | '---DBCLOB----+----------------------+--+-----------------+-'                                                  |
   |               '-(--integer--+---+--)-'  '-allocate-clause-'                                                    |
   |                             +-K-+                                                                              |
   |                             +-M-+                                                                              |
   |                             '-G-'                                                                              |
   |             .-(--1--)-------.                                                                                  |
   +-+-+-BINARY--+---------------+------------------------------+-----------------+---------------------------------+
   | | |         '-(--integer--)-'                              |                 |                                 |
   | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-'                 |                                 |
   | |   '-VARBINARY------'                 '-allocate-clause-'                   |                                 |
   | |                              .-(--1M--)-------------.                      |                                 |
   | '---+-BLOB----------------+----+----------------------+--+-----------------+-'                                 |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'  '-allocate-clause-'                                   |
   |                                              +-K-+                                                             |
   |                                              +-M-+                                                             |
   |                                              '-G-'                                                             |
   +-+-DATE------+--------------------------------------------------------------------------------------------------+
   | +-TIME------+                                                                                                  |
   | '-TIMESTAMP-'                                                                                                  |
   |             .-(--200--)-----.                                                                                  |
   '---DATALINK--+---------------+--+-----------------+--+--------------+-------------------------------------------'
                 '-(--integer--)-'  '-allocate-clause-'  '-ccsid-clause-'
 
ccsid-clause:
 
                   .-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
                   '-NORMALIZED-----'
 
allocate-clause:
 
|--ALLOCATE--(integer)------------------------------------------|

default-clause:
 
   .-WITH-.
|--+------+--DEFAULT--+-------------------------------------------------+--|
                      +-constant----------------------------------------+
                      +-USER--------------------------------------------+
                      +-NULL--------------------------------------------+
                      +-CURRENT_DATE------------------------------------+
                      +-CURRENT_TIME------------------------------------+
                      +-CURRENT_TIMESTAMP-------------------------------+
                      '-cast-function-name--(--+-constant----------+--)-'
                                               +-USER--------------+
                                               +-CURRENT_DATE------+
                                               +-CURRENT_TIME------+
                                               '-CURRENT_TIMESTAMP-'
 
identity-options:
 
|--AS IDENTITY--+-------------------------------------------------------+--|
                |    .-,-------------------------------------------.    |
                |    V               .-1----------------.     (1)  |    |
                '-(----+-START WITH--+-numeric-constant-+---+------+--)-'
                       |               .-1----------------. |
                       +-INCREMENT BY--+-numeric-constant-+-+
                       | .-NO MINVALUE----------------.     |
                       +-+-MINVALUE--numeric-constant-+-----+
                       | .-NO MAXVALUE----------------.     |
                       +-+-MAXVALUE--numeric-constant-+-----+
                       | .-NO CYCLE-.                       |
                       +-+-CYCLE----+-----------------------+
                       | .-CACHE--20------.                 |
                       +-+-NO CACHE-------+-----------------+
                       | '-CACHE--integer-'                 |
                       | .-NO ORDER-.                       |
                       '-+-ORDER----+-----------------------'

Notes:

The same clause must not be specified more than once.

datalink-options:
 
   .-LINKTYPE URL-.  .-NO LINK CONTROL-.
|--+--------------+--+-----------------+------------------------|
 
copy-options:
 
                           .-COLUMN ATTRIBUTES-.                 .-COLUMN-.               (1)
|----+-EXCLUDING IDENTITY--+-------------------+-+--+-EXCLUDING--+--------+--DEFAULTS-+---------|
     |                     .-COLUMN ATTRIBUTES-. |  |            .-COLUMN-.           |
     '-INCLUDING IDENTITY--+-------------------+-'  +-INCLUDING--+--------+--DEFAULTS-+
                                                    '-USING TYPE DEFAULTS-------------'
 
as-subquery-clause:
 
|--+----------------------------------------------------------------+-->
   |    .------------------------------------------------------.    |
   |    V                                                      |    |
   '-(----column-name--+-------------------------------------+-+--)-'
                       |      .-COLUMN-.                     |
                       '-FOR--+--------+--system-column-name-'
 
>--AS--(--select-statement--)--+-WITH NO DATA-+--+--------------+--|
                               '-WITH DATA----'  '-copy-options-'

Notes:

The clauses can be specified in any order.

Description

table-name

Names the temporary table. The qualifier, if specified explicitly, must be SESSION, otherwise an error is returned. If the qualifier is not specified, it is implicitly defined to be SESSION. If a declared temporary table, or an index or view that is dependent on a declared temporary table already exists with the same name, an error is returned.

If a persistent table, view, index, or alias already exists with the same name and the schema name SESSION:

The declared temporary table is still defined with SESSION.table-name. An error is not issued because the resolution of a declared temporary table name does not include a permanent library.
Any references to SESSION.table-name will resolve to the declared temporary table rather than to a permanent table, view, index, or alias with a name of SESSION.table-name.

The table will be created in library QTEMP.

column-definition

Defines the attributes of a column. There must be at least one column definition and no more than 8000 column definitions.

The sum of the row buffer byte counts of the columns must not be greater than 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740. Additionally, if a LOB is specified, the sum of the row data byte counts of the columns must not be greater than 3.5 gigabytes. For information on the byte counts of columns according to data type, see Notes.

column-name

Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.

FOR COLUMN system-column-name

Provides an i5/OS name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.

data-type

Specifies the data type of the column.

built-in-type: Specifies a built-in data type. See CREATE TABLE for a description of built-in-type.
A ROWID column or a DATALINK column with FILE LINK CONTROL cannot be specified for a global temporary table.
distinct-type-name: Specifies that the data type of the column is a distinct type (a user-defined data type). The length, precision, and scale of the column are respectively the length, precision, and scale of the source type of the distinct type. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path.

NOT NULL

Prevents the column from containing null values. Omission of NOT NULL implies that the column can be null.

DEFAULT

Specifies a default value for the column. This clause cannot be specified more than once in a column-definition. DEFAULT cannot be specified an identity column (a column that is defined AS IDENTITY). The database manager generates default values for identity columns. If a value is not specified following the DEFAULT keyword, then:

if the column is nullable, the default value is the null value.

if the column is not nullable, the default depends on the data type of the column:

Data type	Default value
Numeric	0
Fixed-length character or graphic string	Blanks
Fixed-length binary string	Hexadecimal zeros
Varying-length string	A string length of 0
Date	The current date at the time of INSERT
Time	The current time at the time of INSERT
Timestamp	The current timestamp at the time of INSERT
Datalink	A value corresponding to DLVALUE('','URL','')
`distinct-type`	The default value of the corresponding source type of the distinct type.

Omission of NOT NULL and DEFAULT from a column-definition is an implicit specification of DEFAULT NULL.

constant

Specifies the constant as the default for the column. The specified constant must represent a value that could be assigned to the column in accordance with the rules of assignment as described in Assignments and comparisons. A floating-point constant must not be used for a SMALLINT, INTEGER, DECIMAL, or NUMERIC column. A decimal constant must not contain more digits to the right of the decimal point than the specified scale of the column.

USER

Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value of the column. The data type of the column must be CHAR or VARCHAR with a length attribute greater than or equal to the length attribute of the USER special register.

NULL

Specifies null as the default for the column. If NOT NULL is specified, DEFAULT NULL must not be specified within the same column-definition.

CURRENT_DATE

Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the column must be DATE or a distinct type based on a DATE.

CURRENT_TIME

Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the column must be TIME or a distinct type based on a TIME.

CURRENT_TIMESTAMP

Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the column must be TIMESTAMP or a distinct type based on a TIMESTAMP.

cast-function-name

This form of a default value can only be used with columns defined as a distinct type, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME or TIMESTAMP data types. The following table describes the allowed uses of these cast-functions.

Data Type	Cast Function Name
Distinct type N based on a BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
Distinct type N based on a DATE, TIME, or TIMESTAMP	N (the user-defined cast function that was generated when N was created) ** or DATE, TIME, or TIMESTAMP *
Distinct type N based on other data types	N (the user-defined cast function that was generated when N was created) **
BINARY, VARBINARY, BLOB, CLOB, or DBCLOB	BINARY, VARBINARY, BLOB, CLOB, or DBCLOB *
DATE, TIME, or TIMESTAMP	DATE, TIME, or TIMESTAMP *
Notes: * The name of the function must match the name of the data type (or the source type of the distinct type) with an implicit or explicit schema name of QSYS2. ** The name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type.

constant: Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. For BINARY, VARBINARY, BLOB, CLOB, DBCLOB, DATE, TIME, and TIMESTAMP functions, the constant must be a string constant.
USER: Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value for the column. The data type of the source type of the distinct type of the column must be CHAR or VARCHAR with a length attribute greater than or equal to the length attribute of the USER special register.
CURRENT_DATE: Specifies the current date as the default for the column. If CURRENT_DATE is specified, the data type of the source type of the distinct type of the column must be DATE.
CURRENT_TIME: Specifies the current time as the default for the column. If CURRENT_TIME is specified, the data type of the source type of the distinct type of the column must be TIME.
CURRENT_TIMESTAMP: Specifies the current timestamp as the default for the column. If CURRENT_TIMESTAMP is specified, the data type of the source type of the distinct type of the column must be TIMESTAMP.

If the value specified is not valid, an error is returned.

GENERATED

Specifies that the database manager generates values for the column. GENERATED may be specified if the column is to be considered an identity column (defined with the AS IDENTITY clause).

ALWAYS: Specifies that the database manager will always generate a value for the column when a row is inserted into the table. ALWAYS is the recommended value.
BY DEFAULT: Specifies that the database manager will generate a value for the column when a row is inserted only if a value is not specified for the column. If a value is specified, the database manager uses that value.
For an identity column, the database manager inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique constraint or a unique index that solely specifies the identity column.

AS IDENTITY

Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT, DECIMAL or NUMERIC with a scale of zero, or a distinct type based on one of these data types). If a DECIMAL or NUMERIC data type is specified, the precision must not be greater than 31.

An identity column is implicitly NOT NULL. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

datalink-options

Specifies the options associated with a DATALINK data type.

LINKTYPE URL: Defines the type of link as a Uniform Resource Locator (URL).
NO LINK CONTROL: Specifies that there will not be any check made to determine that the linked files exist. Only the syntax of the URL will be checked. There is no database manager control over the linked files.

LIKE

table-name or view-name

Specifies that the columns defined in the specified table or view are included in this table. The table-name or view-name specified in a LIKE clause must identify the table or view that already exists at the application server.

The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view. The implicit definition includes the following attributes of the n columns (if applicable to the data type):

Column name (and system column name)
Data type, length, precision, and scale
CCSID

If the LIKE clause is specified immediately following the table-name and not enclosed in parenthesis, the following column attributes are also included, otherwise they are not included (the default value and identity attributes can also be controlled by using the copy-options):

Default value, if a table-name is specified (view-name is not specified)
Identity attributes
Nullability
Column heading and text (see LABEL)

If the specified table or view is a non-SQL created physical file or logical file, any non-SQL attributes are removed. For example, the date and time format will be changed to ISO.

The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.

as-subquery-clause

column-name

Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.

FOR COLUMN system-column-name

Provides an i5/OS name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

select-statement

Specifies that the columns of the table are to have the same name and description as the columns that would appear in the derived result table of the select-statement if the select-statement were to be executed. The use of AS select-statement is an implicit definition of n columns for the table, where n is the number of columns that would result from the select-statement. The implicit definition includes the following attributes of the n columns (if applicable to the data type):

Column name (and system column name)
Data type, length, precision, and scale
CCSID
Nullability
Column heading and text (see LABEL)

The following attributes are not included (the default value and identity attributes may be included by using the copy-options):

Default value
Identity attributes

The implicit definition does not include any other optional attributes of the tables or views referenced in the select-statement.

The implicitly defined columns of the table inherit the names of the columns from the result table of the select-statement. Therefore, a column name must be specified in the select-statement or in the column name list for all result columns. For result columns that are derived from expressions, constants, and functions, the select-statement must include the AS column-name clause immediately after the result column or a name must be specified in the column list preceding the select-statement.

The select-statement must not refer to variables or include parameter markers (question marks). The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression.

WITH DATA

Specifies that the select-statement is executed. After the table is created, the result table rows of the select-statement are automatically inserted into the table.

WITH NO DATA

Specifies that the select-statement is not executed. Therefore, there is no result table with a set of rows with which to automatically populate the table.

copy-options

INCLUDING IDENTITY COLUMN ATTRIBUTES or EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies whether identity column attributes are inherited.

INCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that the table inherits the identity attribute, if any, of the columns resulting from select-statement, table-name, or view-name. In general, the identity attribute is copied if the element of the corresponding column in the table, view, or select-statement is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with the identity attribute. If the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified with the AS select-statement clause, the columns of the new table do not inherit the identity attribute in the following cases:

The select list of the select-statement includes multiple instances of an identity column name (that is, selecting the same column more than once).
The select list of the select-statement includes multiple identity columns (that is, it involves a join).
The identity column is included in an expression in the select list.
The select-statement includes a set operation (UNION or INTERSECT).

If INCLUDING IDENTITY is not specified, the table will not have an identity column.

EXCLUDING IDENTITY COLUMN ATTRIBUTES

Specifies that the table does not inherit the identity attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.

EXCLUDING COLUMN DEFAULTS or INCLUDING COLUMN DEFAULTS or USING TYPE DEFAULTS

Specifies whether column defaults are inherited.

EXCLUDING COLUMN DEFAULTS

Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new table are either null or there are no default values. If the column can be null, the default is the null value. If the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on INSERT for the new table.

INCLUDING COLUMN DEFAULTS

Specifies that the table inherits the default values of the columns resulting from the select-statement, table-name, or view-name. A default value is the value assigned to a column when a value is not specified on an INSERT.

Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.

If INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.

USING TYPE DEFAULTS

Specifies that the default values for the table depend on the data type of the columns that result from the select-statement, table-name, or view-name. If the column is nullable, then the default value is the null value. Otherwise, the default value is as follows:

Data type	Default value
Numeric	0
Fixed-length character or graphic string	Blanks
Fixed-length binary string	Hexadecimal zeros
Varying-length string	A string length of 0
Date	The current date at the time of INSERT
Time	The current time at the time of INSERT
Timestamp	The current timestamp at the time of INSERT
Datalink	A value corresponding to DLVALUE('','URL','')
`distinct-type`	The default value of the corresponding source type of the distinct type.

Do not specify USING TYPE DEFAULTS if INCLUDING COLUMN DEFAULTS is specified.

WITH REPLACE

Specifies that, in the case that a declared global temporary table already exists with the specified name, the existing table is replaced with the temporary table defined by this statement (and all rows of the existing table are deleted).

When WITH REPLACE is not specified, then the name specified must not identify a declared global temporary table that already exists in the current session.

ON COMMIT

Specifies the action taken on the global temporary table when a COMMIT operation is performed.

The ON COMMIT clause does not apply if the declared global temporary table is opened under isolation level No Commit (NC) or if a COMMIT HOLD operation is performed.

DELETE ROWS: All rows of the table will be deleted if no WITH HOLD cursor is open on the table. This is the default.
PRESERVE ROWS: Rows of the table will be preserved.

NOT LOGGED

Changes to the table are not logged, including creation of the table. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed and the table was changed in the unit of work (or savepoint), the changes are not rolled back. If the table was created in the unit of work (or savepoint), then that table will be dropped. If the table was dropped in the unit of work (or savepoint) then the table will be restored, but with no rows.

ON ROLLBACK

Specifies the action taken on the global temporary table when a ROLLBACK operation is performed.

The ON ROLLBACK clause does not apply if the declared global temporary table was opened under isolation level No Commit (NC) or if a ROLLBACK HOLD operation is performed.

DELETE ROWS: All rows of the table will be deleted. This is the default.
PRESERVE ROWS: Rows of the table will be preserved.

Notes

Instantiation, scope, and termination: Let P denote an application process and let T be a declared temporary table in an application program in P:

When a program in P issues a DECLARE GLOBAL TEMPORARY TABLE statement, an empty instance of T is created.
Any program in P can reference T, and any of those references is a reference to that same instance of T. (If a DECLARE GLOBAL TEMPORARY statement is specified within a compound statement of an SQL function, SQL procedure, or trigger; the scope of the declared temporary table is the application process and not the compound statement.)
If T was declared at a remote server, the reference to T must use the same connection that was used to declare T and that connection must not have been terminated after T was declared. When the connection to the database server at which T was declared terminates, T is dropped.
If T is defined with the ON COMMIT DELETE ROWS clause, when a commit operation terminates a unit of work in P and no program in P has a WITH HOLD cursor open that is dependent on T, all rows are deleted.
If T is defined with the ON ROLLBACK DELETE ROWS clause, when a rollback operation terminates a unit of work in P, all rows are deleted.
When the application process that declared T terminates, T is dropped.

Temporary table ownership: The owner of the table is the user profile of the job executing the statement.

Temporary table authority: When a declared temporary table is defined, PUBLIC implicitly is granted all table privileges on the table and authority to drop the table.

Referring to a declared temporary table in other SQL statements: Many SQL statements support declared temporary tables. To refer to a declared temporary table in an SQL statement other than DECLARE GLOBAL TEMPORARY TABLE, the table must be implicitly or explicitly qualified with SESSION.

If you use SESSION as the qualifier for a table name but the application process does not include a DECLARE GLOBAL TEMPORARY TABLE statement for the table name, the database manager assumes that you are not referring to a declared temporary table. The database manager resolves such table references to a permanent table.

Restrictions on the use of declared temporary tables:

Declared temporary tables cannot be specified in an ALTER TABLE, COMMENT, GRANT, LABEL, LOCK, RENAME, or REVOKE statement.
Declared temporary tables cannot be specified as the parent table in referential constraints
If a declared temporary table is referenced in a CREATE INDEX or CREATE VIEW statement, the index or view must be created in SESSION (or library QTEMP).

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

DEFINITION ONLY is a synonym for WITH NO DATA

Examples

Example 1: Define a declared temporary table with column definitions for an employee number, salary, commission, and bonus.

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

Example 2: Assume that base table USER1.EMPTAB exists and that it contains three columns, one of which is an identity column. Declare a temporary table that has the same column names and attributes (including identity attributes) as the base table.

   DECLARE GLOBAL TEMPORARY TABLE TEMPTAB1
     LIKE USER1.EMPTAB
     INCLUDING IDENTITY
     ON COMMIT PRESERVE ROWS

In the above example, the database manager uses SESSION as the implicit qualifier for TEMPTAB1.

[ Top of Page | Previous Page | Next Page | Contents | Index ]