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 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-result-table clause:

  • The SELECT privilege for the table or view
  • Ownership of the table or view
  • Start of changeDatabase administrator authorityEnd of change

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
  • Start of changeDatabase administrator authorityEnd of change

For information about 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

Read syntax diagramSkip visual syntax diagramDECLARE GLOBAL TEMPORARY TABLEtable-name FOR SYSTEM NAMEsystem-object-identifier (,column-definitionLIKEtable-nameview-namecopy-options)LIKEtable-nameview-namecopy-optionsas-result-table WITH REPLACEON COMMIT DELETE ROWSON COMMIT PRESERVE ROWSNOT LOGGEDON ROLLBACK DELETE ROWSON ROLLBACK PRESERVE ROWS1RCDFMTformat-name2media-preferencememory-preference
media-preference
Read syntax diagramSkip visual syntax diagramUNIT ANYUNIT SSD
memory-preference
Read syntax diagramSkip visual syntax diagramKEEP IN MEMORY NOYES
column-definition
Read syntax diagramSkip visual syntax diagramcolumn-nameFORCOLUMNsystem-column-namedata-type3 default-clauseGENERATEDALWAYSBY DEFAULTidentity-optionsas-row-change-timestamp-clauseFIELDPROCexternal-program-name(,constant)NOT NULLNOT HIDDENIMPLICITLY HIDDENdatalink-options41
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name
Notes:
  • 1 The same clause must not be specified more than once.
  • 2 The optional clauses can be specified in any order.
  • 3 data-type is optional for row change timestamp columns
  • 4 The datalink-options can only be specified for DATALINKs and distinct-types sourced on DATALINKs.
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)allocate-clauseFOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)allocate-clauseFOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)allocate-clauseDBCLOB(1M)(integerKMG)allocate-clauseccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)allocate-clauseNATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)allocate-clausenormalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)allocate-clauseBLOBBINARY LARGE OBJECT(1M)(integerKMG)allocate-clauseDATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)allocate-clauseccsid-clauseXMLccsid-clause
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED
allocate-clause
Read syntax diagramSkip visual syntax diagramALLOCATE(integer)
default-clause
Read syntax diagramSkip visual syntax diagramWITHDEFAULT constantUSERNULLCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMP(6)(integer)cast-function-name(constantUSERCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMP(6)(integer))
identity-options
Read syntax diagramSkip visual syntax diagramAS IDENTITY (START WITH1numeric-constantINCREMENT BY1numeric-constantNO MINVALUEMINVALUEnumeric-constantNO MAXVALUEMAXVALUEnumeric-constantNO CYCLECYCLECACHE20NO CACHECACHEintegerNO ORDERORDER1)
Notes:
  • 1 The same clause must not be specified more than once.
as-row-change-timestamp-clause
Read syntax diagramSkip visual syntax diagramFOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
datalink-options
Read syntax diagramSkip visual syntax diagramLINKTYPE URLNO LINK CONTROL
copy-options
Read syntax diagramSkip visual syntax diagramEXCLUDING IDENTITYCOLUMN ATTRIBUTESINCLUDING IDENTITYCOLUMN ATTRIBUTESEXCLUDINGCOLUMNDEFAULTSINCLUDINGCOLUMNDEFAULTSUSING TYPE DEFAULTSEXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTESINCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTESEXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTESINCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES1
as-result-table
Read syntax diagramSkip visual syntax diagram(column-nameFORCOLUMNsystem-column-name)AS(select-statement ) WITH NO DATAWITH DATA copy-options
Notes:
  • 1 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.

Start of changeFOR SYSTEM NAME system-object-identifierEnd of change
Start of changeIdentifies the system-object-identifier of the table. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

When system-object-identifier is specified, table-name must not be a valid system object name.

End of change

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, VARGRAPHIC, or VARBINARY column is specified, 32740. Additionally, if a LOB or XML column is specified, the sum of the row data byte counts of the columns must not be greater than 3.5 gigabytes. For information about the byte counts of columns according to data type, see Maximum row sizes.

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 IBM® i 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 declared 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.
DEFAULT
Specifies a default value for the column. This clause cannot be specified more than once in a column-definition. DEFAULT cannot be specified for an XML column, an identity column (a column that is defined AS IDENTITY) or a row change timestamp column. The database manager generates default values for identity columns and row change timestamp columns. For an XML column, the default is NULL unless NOT NULL is specified; in that case there is no default. 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 or decimal 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 or CURRENT_TIMESTAMP(integer)
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. Start of changeThe timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register.End of change
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 or CURRENT_TIMESTAMP(integer)
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. Start of changeThe timestamp precision of the CURRENT_TIMESTAMP special register used as the default will always match the timestamp precision of the column, regardless of the precision specified for the special register.End of change

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 or a row change timestamp column.
ALWAYS
Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a default value must be generated. ALWAYS is the recommended value.
BY DEFAULT
Specifies that the database manager will generate a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified.

For an identity column or row change timestamp column, the database manager inserts or updates a specified value but does not verify that it is a unique value for the column unless the identity column or row change timestamp column has a unique constraint or a unique index that solely specifies the identity column or row change timestamp 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. An identity column cannot have a DEFAULT clause. See the AS IDENTITY clause in CREATE TABLE for the descriptions of the identity attributes.

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
Specifies that the column is a timestamp and the values will be generated by the database manager. The database manager generates a value for the column for each row as a row is inserted, and for every row in which any column is updated. The value generated for a row change timestamp column is a timestamp corresponding to the time of the insert or update of the row. If multiple rows are inserted with a single SQL statement, the value for the row change timestamp column may be different for each row to reflect when each row was inserted. The generated value is not guaranteed to be unique.
A table can have only one row change timestamp column. If data-type is specified, it must be a TIMESTAMP Start of changewith a precision of 6End of change or a distinct type based on a TIMESTAMP Start of changewith a precision of 6End of change. A row change timestamp column cannot have a DEFAULT clause and must be NOT NULL.
FIELDPROC
Designates an external-program-name as the field procedure exit routine for the column. It must be an ILE program that does not contain SQL. It cannot be a service program.
The field procedure encodes and decodes column values. Before a value is inserted in the column, it is passed to the field procedure for encoding. Before a value from the column is used, it is passed to the field procedure for decoding.
The field procedure is also invoked during the processing of the CREATE TABLE statement. When so invoked, the procedure provides DB2® with the column's field description. The field description defines the data characteristics of the encoded values. By contrast, the information supplied for the column in the CREATE TABLE statement defines the data characteristics of the decoded values.
constant
Specifies a parameter that is passed to the field procedure when it is invoked. A parameter list is optional.
A field procedure cannot be defined for a column that is a ROWID or DATALINK or a distinct type based on a ROWID or DATALINK. The column must not be an identity column or a row change timestamp column. The column must not have a default value of CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, or USER. The column cannot be referenced in a check condition. If it is part of a foreign key, the corresponding parent key column must use the same field procedure. See Embedded SQL programming topic collection for an example of a field procedure.
NOT NULL
Prevents the column from containing null values. Omission of NOT NULL implies that the column can be null. NOT NULL is required for a row change timestamp column.
NOT HIDDEN
Indicates the column is included in implicit references to the table in SQL statements. This is the default.
IMPLICITLY HIDDEN
Indicates the column is not visible in SQL statements unless it is referred to explicitly by name. For example, SELECT * does not include any hidden columns in the result. A table must contain at least one column that is not IMPLICITLY HIDDEN.
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, identity, row change timestamp, and hidden 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
  • Hidden attributes
  • Row change timestamp attribute
  • 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-result-table

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 IBM i 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.

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, identity, row change timestamp, and hidden attributes may be included by using the copy-options):

  • Default value
  • Hidden attribute
  • Identity attributes
  • Row change timestamp attribute

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. The UPDATE, SKIP LOCKED DATA, and USE AND KEEP EXCLUSIVE LOCKS clauses may not be specified.

Start of changeIf the select-statement contains an isolation-clause the isolation level specified in the isolation-clause applies to the entire SQL statement.End of change

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 Start of change(that is, a join returned more than one identity column)End of change.
  • 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.

INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES or EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
Specifies whether implicitly hidden columns are inherited.
INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
Specifies that the table inherits implicitly hidden columns from select-statement, table-name, or view-name and those columns will be defined with the implicitly hidden attribute in the new table.

If INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES is not specified, the table will not have any implicitly hidden columns.

EXCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES
Specifies that the table does not inherit implicitly hidden columns from the fullselect, table-name, or view-name.
INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES or EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
Specifies whether the row change timestamp attribute is inherited.
INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
Specifies that the table inherits the row change timestamp attribute, if any, of the columns resulting from select-statement, table-name, or view-name. In general, the row change timestamp 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 row change timestamp attribute. If the INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES clause is specified with the AS select-statement clause, the columns of the new table do not inherit the row change timestamp in the following cases:
  • The select list of the select-statement includes multiple instances of a row change timestamp column name (that is, selecting the same column more than once).
  • The select list of the select-statement includes multiple row change timestamp columns Start of change(that is, a join returned more than one row change timestamp column)End of change.
  • The row change timestamp column is included in an expression in the select list.
  • The select-statement includes a set operation (UNION or INTERSECT).

If INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES is not specified, the table will not have a row change timestamp column.

EXCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES
Specifies that the table does not inherit the row change timestamp attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.
WITH REPLACE
Specifies that, in the case that a declared 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 temporary table that already exists in the current session.

ON COMMIT
Specifies the action taken on the declared temporary table when a COMMIT operation is performed. The default is DELETE ROWS.

The ON COMMIT clause does not apply if the declared 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.
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 declared temporary table when a ROLLBACK operation is performed.

The ON ROLLBACK clause does not apply if the declared 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.
RCDFMT format-name
An unqualified name that designates the IBM i record format name of the table. A format-name is a system identifier.

If a record format name is not specified, the format-name is the same as the system-object-name of the table.

Start of changemedia-preferenceEnd of change
Start of changeSpecifies the preferred storage media for the table.
UNIT ANY
No storage media is preferred. Storage for the table will be allocated from any available storage media.
UNIT SSD
Solid state disk storage media is preferred. Storage for the table may be allocated from solid state disk storage media, if available.
End of change
Start of changeKEEP IN MEMORYEnd of change
Start of changeSpecifies whether the data for the table should be brought into a main storage pool when the data is used in a query.
NO
The data will not be brought into a main storage pool.
YES
The data will be brought into a main storage pool.
End of change

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 Start of changethreadEnd of change 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, CREATE ALIAS, 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).
Start of change

Creating a table using a remote select-statement: The select-statement for an as-result-table can refer to tables on a different server than where the table is being created. This can be done by using a three-part object name or an alias that is defined to reference a three-part name of a table or view. The result of the select-statement cannot contain a column that has a field procedure defined. If the remote server is DB2 for LUW or DB2 for z/OS®, copy-options are not allowed. If the remote server is DB2 for LUW, a column list should be explicitly specified before the AS keyword.

End of change

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

  • Start of changeINLINE LENGTH is a synonym for ALLOCATE.End of change
  • 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.