CREATE GLOBAL TEMPORARY TABLE

The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server.

Invocation for CREATE GLOBAL TEMPORARY TABLE

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

Authorization for CREATE GLOBAL TEMPORARY TABLE

The privilege set that is defined below must include at least one of the following:

The CREATETMTAB system privilege
The CREATETAB database privilege for any database
DBADM, DBCTRL, or DBMAINT authority for any database
SYSADM or SYSCTRL authority
System DBADM
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

However, DBADM, DBCTRL, or DBMAINT authority is not sufficient authority if you are creating a temporary table for someone else and the table qualifier is not your authorization ID.

Additional privileges might be required when the data type of a column is a distinct type or the LIKE clause is specified. See the description of distinct-type and LIKE for the details.

Privilege set: The privilege set is the same as the privilege set for the CREATE TABLE statement. See information about CREATE TABLE Authorization for details.

Syntax for CREATE GLOBAL TEMPORARY TABLE

column-definition:

data-type:

built-in-type:

Notes:

¹ The CCSID clause must only be specified for a character string or a graphic string column in an EBCDIC created global temporary table.

Description for CREATE GLOBAL TEMPORARY TABLE

table-name

Names the temporary table. The name, including the implicit or explicit qualifier, must not identify a table, view, alias, synonym, or temporary table that exists at the database server, or a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table.

The qualification rules for a temporary table are the same as for other tables.

The owner acquires ALL PRIVILEGES on the table WITH GRANT OPTION and the authority to drop the table.

For more information, see Guidelines for table names.

column-definition

Defines the attributes of a column for each instance of the table. The number of columns defined must not exceed 750. The maximum record size must not exceed 32714 bytes. The maximum row size must not exceed 32706 bytes (8 bytes less than the maximum record size).

column-name

Names the column. The name must not be qualified and must not be the same as the name of another column in the table.

data-type

Specifies the data type of the column. The data type can be a built-in data type or a distinct type.

built-in-type: The data type of the column is a built-in data type.
For more information on and the rules that apply to the data types, see built-in-type.
distinct-type: Any distinct type except one that is based on a LOB or ROWID data type. The privilege set must implicitly or explicitly include the USAGE privilege on the distinct type.

NOT NULL

Specifies that the column cannot contain nulls. Omission of NOT NULL indicates that the column can contain nulls.

LIKE table-name or view-name

Specifies that the columns of the table have exactly the same name and description as the columns of the identified table or view. The name specified after LIKE must identify a table, view, or temporary table that exists at the current server. The identified table must not be an accelerator-only table. A view cannot contain columns of length 0.

The privilege set must implicitly or explicitly include the SELECT privilege on the identified table or view.

This clause is similar to the LIKE clause on CREATE TABLE, but it has the following differences:

If any column of the identified table or view has an attribute value that is not allowed for a column in a temporary table, that attribute value is ignored. The corresponding column in the new temporary table has the default value for that attribute unless otherwise indicated.
If any column of the identified table or view allows a default value other than null, that default value is ignored and the corresponding column in the new temporary table has no default value. A default value other than null is not allowed for any column in a temporary table.

CCSID encoding-scheme

Specifies the encoding scheme for string data stored in the table.

ASCII: Specifies that the data must be encoded by using the ASCII CCSIDs of the server.
An error occurs if a valid ASCII CCSID has not been specified for the installation.
EBCDIC: Specifies that data must be encoded by using the EBCDIC CCSIDs of the server.
An error occurs if a valid EBCDIC CCSID has not been specified for the installation.
UNICODE: Specifies that data must be encoded by using the CCSIDs of the server for Unicode.
An error occurs if a valid CCSID for Unicode has not been specified for the installation.

Usually, each encoding scheme requires only a single CCSID. Additional CCSIDs are needed when mixed, graphic, or Unicode data is used. An error occurs if CCSIDs have not been defined.

The CCSID clause can be specified for the created temporary table, or for individual columns in the created temporary table. If a CCSID clause is specified for the table, that CCSID specifies the encoding scheme for the table, regardless of whether the LIKE clause is also specified. If a CCSID clause is not specified for the table, the encoding scheme for the table is EBCDIC.

If a CCSID clause is specified for a column, the encoding scheme for the created temporary table must be EBCDIC. If a CCSID clause is not specified for a column, and the LIKE clause is not specified for the table, the CCSID of the column is the same as the CCSID of the table. If the LIKE clause is specified, and the source table that is specified in the LIKE clause is an EBCDIC table with Unicode columns, the columns in the created temporary table that correspond to the Unicode columns in the source table are also Unicode.

Notes for CREATE GLOBAL TEMPORARY TABLE

Owner privileges

The owner of the table has all table privileges (see GRANT (table or view privileges)) with the ability to grant these privileges to others. For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.

Instantiation and termination

Let T be a temporary table defined at the current server and let P denote an application process:

An empty instance of T is created as a result of the first implicit or explicit reference to T in an OPEN, SELECT INTO or SQL data change operation that is executed by any program in P.
Any program in P can reference T and any reference to T by a program in P is a reference to that instance of T.
When a commit operation terminates a unit of work in P and no program in P has an open WITH HOLD cursor that is dependent on T, the commit includes the operation DELETE FROM T.
When a rollback operation terminates a unit of work in P, the rollback includes the operation DELETE FROM T.
When the connection to the database server at which an instance of T was created terminates, the instance of T is destroyed. However, the definition of T remains. A DROP TABLE statement must be executed to drop the definition of T.

Considerations for column names longer than 30 bytes

If a length of a new column name is greater than 30 Unicode bytes, truncation occurs in the SQLNAME field of the SQLDA when the column is described in an application. A column name in UTF8, and its equivalent in the system EBCDIC CCSID, must be 128 bytes or less. For more information about long column names, see Column names longer than 30 bytes. End of change

Restrictions and extensions

Let T denote a temporary table:

Columns of T cannot have default values other than null.
A column of T cannot have a LOB or ROWID data type (or a distinct type based on one).
T cannot have unique constraints, referential constraints, or check constraints.
T cannot be defined as the parent in a referential constraint.
T cannot be referenced in:
- A CREATE INDEX statement.
- A LOCK TABLE statement.
- As the object of an UPDATE statement in which the object is T or a view of T. However, you can reference T in the WHERE clause of an UPDATE statement (including the update operation of the MERGE statement).
- Db2 utility commands.
If T is referenced in the fullselect of a CREATE VIEW statement, you cannot specify a WITH CHECK OPTION clause in the CREATE VIEW statement.
ALTER TABLE T is valid only if the statement is used to add a column to T. Any column that you add to T must have a default value of null.
When you alter T, any packages that refer to the table are invalidated, and Db2 automatically rebinds the packages the next time they are run.
DELETE FROM T or a view of T is valid only if the statement does not include a WHERE or WHERE CURRENT OF clause. In addition, DELETE FROM view of T is valid only if the view was created (CREATE VIEW) without the WHERE clause. A DELETE FROM statement deletes all the rows from the table or view.
You can refer to T in the FROM clause of any subselect. If you refer to T in the first FROM clause of a select-statement, you cannot specify a FOR UPDATE clause.
You cannot use a DROP DATABASE statement to implicitly drop T. To drop T, reference T in a DROP TABLE statement.
A temporary table instantiated by an SQL statement using a three-part table name can be accessed by another SQL statement using the same name in the same application process for as long as the Db2 connection which established the instantiation is not terminated.
GRANT ALL PRIVILEGES ON T is valid, but you cannot grant specific privileges on T.
Of the ALL privileges, only the ALTER, INSERT, DELETE, and SELECT privileges can actually be used on T.
REVOKE ALL PRIVILEGES ON T is valid, but you cannot revoke specific privileges from T.
A COMMIT operation deletes all rows of every temporary table of the application process, but the rows of T are not deleted if any program in the application process has an open WITH HOLD cursor that is dependent on T. In addition, if RELEASE(COMMIT) is in effect and no open WITH HOLD cursors are dependent on T, all logical work files for T are also deleted.
A ROLLBACK operation deletes all rows and all logical work files of every temporary table of the application process.
You can reuse threads when using a temporary table, and a logical work file for a temporary table name remains available until deallocation. A new logical work file is not allocated for that temporary table name when the thread is reused.

You can refer to T in the following statements:

Statement	Statement	Statement
ALTER FUNCTION ALTER PROCEDURE COMMENT CREATE ALIAS CREATE FUNCTION	CREATE PROCEDURE CREATE SYNONYM CREATE TABLE LIKE CREATE VIEW DESCRIBE TABLE	DECLARE TABLE DELETE (if it does not include a WHERE clause) DROP TABLE INSERT LABEL SELECT INTO

Alternative syntax and synonyms: For compatibility with previous releases of Db2, you can specify LONG VARCHAR as a synonym for VARCHAR(integer) and LONG VARGRAPHIC as a synonym for VARGRAPHIC(integer) when defining the data type of a column. However, the use of these synonyms is not encouraged because after the statement is processed, Db2 considers a LONG VARCHAR column to be VARCHAR and a LONG VARGRAPHIC column to be VARGRAPHIC.

Examples for CREATE GLOBAL TEMPORARY TABLE

Example 1: Create a temporary table, CURRENTMAP. Name two columns, CODE and MEANING, both of which cannot contain nulls. CODE contains numeric data and MEANING has character data. Assuming a value of NO for the field MIXED DATA on installation panel DSNTIPF, column MEANING has a subtype of SBCS:

CREATE GLOBAL TEMPORARY TABLE CURRENTMAP
     (CODE INTEGER NOT NULL, MEANING VARCHAR(254) NOT NULL);

Example 2: Create a temporary table, EMP:

CREATE GLOBAL TEMPORARY TABLE EMP
     (TMPDEPTNO   CHAR(3)     NOT NULL,
      TMPDEPTNAME VARCHAR(36) NOT NULL,
      TMPMGRNO    CHAR(6)             ,
      TMPLOCATION CHAR(16)            );