CREATE GLOBAL TEMPORARY TABLE

Invocation

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

Authorization

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

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.

Description

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.

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.

For the creation of temporary tables, the CCSID clause can be specified whether or not the LIKE clause is specified. If the CCSID clause is specified, the encoding scheme of the new table is the scheme that is specified in the CCSID clause. If the CCSID clause is not specified, the encoding scheme of the new table is the same as the scheme for the table specified in the LIKE clause.

Notes

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.

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:

  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.