DB2 临时表

 今天用到DB2临时表， 顺便看了下语法逻辑。

 因为总是用到LIKE 创建临时表，所以重点看了下这点。

like：

1.可以是视图或表（包含临时表）

2.like包含视图或表的字段的相关属性（字段名，数据类型，是否为空等）复制

3. like不包含表的相关属性（约束（主键，外键），索引，触发器等）

其中第3点是重点，如果数据量比较大的话，记得要加索引。。。

以下为临时表语法图。。。。

DECLARE GLOBAL TEMPORARY TABLE statement

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

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• USE privilege on the USER TEMPORARY table space
• SYSADM or DBADM authority

When defining a table using LIKE or a fullselect, the privileges held by the authorization ID of the statement must also include at least one of the following on each identified table or view:

• SELECT privilege on the table or view
• CONTROL privilege on the table or view
• SYSADM or DBADM authority

Syntax

>>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->

        .-,---------------------.
        V                       |
>--+-(----| column-definition |-+--)-----------------------------+-->
   +-LIKE--+-table-name1-+--+------------------+-----------------+
   |       '-view-name---'  '-| copy-options |-'                 |
   '-AS--(--fullselect--)--DEFINITION ONLY--+------------------+-'
                                            '-| copy-options |-'

      .-ON COMMIT DELETE ROWS---.
>--*--+-------------------------+--*---------------------------->
      '-ON COMMIT PRESERVE ROWS-'

>--+-------------------------------------------+---------------->
   |             .-ON ROLLBACK DELETE ROWS---. |
   '-NOT LOGGED--+---------------------------+-'
                 '-7 ON ROLLBACK PRESERVE ROWS-'

>--*--+--------------+--*--+---------------------+-------------->
      '-WITH REPLACE-'     '-IN--tablespace-name-'

>--*--+------------------------------------------------------------+--*-><
      |                      .-,-----------.                       |
      |                      V             |     .-USING HASHING-. |
      '-PARTITIONING KEY--(----column-name-+--)--+---------------+-'

column-definition:

|--column-name--| data-type |--+--------------------+-----------|
                               '-| column-options |-'

column-options:

|--*--+----------+--*--+----------------------------------------------------------------------+--*--|
      '-NOT NULL-'     +-| default-clause |---------------------------------------------------+
                       '-GENERATED--+-ALWAYS-----+--AS--IDENTITY--+-------------------------+-'
                                    '-BY DEFAULT-'                '-| identity-attributes |-'

copy-options:

                                                                        .-COLUMN ATTRIBUTES-.
                                                  .-EXCLUDING IDENTITY--+-------------------+-.
|--*--+-------------------------------------+--*--+-------------------------------------------+--*--|
      |                .-COLUMN-.           |     |                     .-COLUMN ATTRIBUTES-. |
      '-+-INCLUDING-+--+--------+--DEFAULTS-'     '-INCLUDING IDENTITY--+-------------------+-'
        '-EXCLUDING-'

Description

table-name

Names the temporary table. The qualifier, if specified explicitly, must be SESSION, otherwise an error is returned (SQLSTATE 428EK). If the qualifier is not specified, SESSION is implicitly assigned.

Each session that defines a declared global temporary table with the same table-name has its own unique description of that declared global temporary table. The WITH REPLACE clause must be specified if table-name identifies a declared temporary table that already exists in the session (SQLSTATE 42710).

It is possible that a table, view, alias, or nickname already exists in the catalog, with the same name and the schema name SESSION. In this case:

• A declared global temporary table table-name may still be defined without any error or warning
• Any references to SESSION.table-name will resolve to the declared global temporary table rather than the SESSION.table-name already defined in the catalog.

column-definition

Defines the attributes of a column of the temporary table.

column-name

Names a column of the table. The name cannot be qualified, and the same name cannot be used for more than one column of the table (SQLSTATE 42711).

A table may have the following:

• A 4K page size with a maximum of 500 columns, where the byte counts of the columns must not be greater than 4 005.
• An 8K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 8 101.
• A 16K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 16 293.
• A 32K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 32 677.

For more details, see "Row Size" in "CREATE TABLE".

data-type

For allowable types, see  data-type in "CREATE TABLE". Note that BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC, DATALINK, reference, and structured types cannot be used with declared global temporary tables (SQLSTATE 42962). This exception includes distinct types sourced on these restricted types.

FOR BIT DATA can be specified as part of character string data types.

column-options

Defines additional options related to the columns of the table.

NOT NULL

Prevents the column from containing null values. For specification of null values, see NOT NULL in "CREATE TABLE".

default-clause

For specification of defaults, see  default-clause in "CREATE TABLE".

IDENTITY and  identity-attributes

For specification of identity columns, see IDENTITY and  identity-attributes in "CREATE TABLE".

LIKE  table-name1  or  view-name

Specifies that the columns of the table have exactly the same name and description as the columns of the identified table ( table-name1) or view ( view-name), or nickname ( nickname). The name specified after LIKE must identify a table, view or nickname that exists in the catalog or a declared temporary table. A typed table or typed view cannot be specified (SQLSTATE 428EC).

The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table or view.

• If a table is identified, then the implicit definition includes the column name, data type and nullability characteristic of each of the columns of table-name1. If EXCLUDING COLUMN DEFAULTS is not specified, then the column default is also included.
• If a view is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of the fullselect defined in view-name.

Column default and identity column attributes may be included or excluded, based on the copy-attributes clauses.

The implicit definition does not include any other attributes of the identified table or view. Thus, the new table does not have any unique constraints, foreign key constraints, triggers, or indexes. The table is created in the table space either implicitly or explicitly, as specified by the IN clause.

The names used for table-name1 and view-name cannot be the same as the name of the global temporary table that is being created (SQLSTATE 428EC).

AS ( fullselect) DEFINITION ONLY

Specifies that the columns of the table have the same name and description as the columns that would appear in the derived result table of the fullselect if the fullselect were to be executed. The use of AS ( fullselect) is an implicit definition of  n columns for the declared global temporary table, where  n is the number of columns that would result from the fullselect.

The implicit definition includes the following attributes of the n columns (if applicable to the data type):

• Column name
• Data type, length, precision, and scale
• Nullability

The following attributes are not included (the default value and identity attributes can 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 fullselect.

Every select list element must have a unique name (SQLSTATE 42711). The AS clause can be used in the select clause to provide unique names. The fullselect must not result in a column having a LOB data type. The fullselect must not refer to host variables or include parameter markers.

copy-options

These options specify whether or not to copy additional attributes of the source result table definition (table, view, or fullselect).

INCLUDING COLUMN DEFAULTS

Column defaults for each updatable column of the source result table definition are copied. Columns that are not updatable will not have a default defined in the corresponding column of the created table.

If LIKE table-name1 is specified, and table-name1 identifies a base table or declared temporary table, then INCLUDING COLUMN DEFAULTS is the default.

EXCLUDING COLUMN DEFAULTS

Column defaults are not copied from the source result table definition.

This clause is the default, except when LIKE table-name is specified and table-name identifies a base table or declared temporary table.

INCLUDING IDENTITY COLUMN ATTRIBUTES

If available, identity column attributes (START WITH, INCREMENT BY, and CACHE values) are copied from the source's result table definition. It is possible to copy these attributes if the element of the corresponding column in the table, view, or fullselect is the name of a column of a table, or the name of a column of a view, which directly or indirectly maps to the column name of a base table with the identity property. In all other cases, the columns of the new temporary table will not get the identity property. For example:

• the select list of the fullselect includes multiple instances of the name of an identity column (that is, selecting the same column more than once)
• the select list of the fullselect includes multiple identity columns (that is, it involves a join)
• the identity column is included in an expression in the select list
• the fullselect includes a set operation (union, except, or intersect).

EXCLUDING IDENTITY COLUMN ATTRIBUTES

Identity column attributes are not copied from the source result table definition.

ON COMMIT

Specifies the action taken on the global temporary table when a COMMIT 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

Specifies that insert, update, or delete operations against the table are not to be logged, but that the creation or dropping of the table is to be logged. During a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation:

• If the table had been created within a unit of work (or savepoint), the table is dropped
• If the table had been dropped within a unit of work (or savepoint), the table is recreated, but without any data

ON ROLLBACK

Specifies the action that is to be taken on the not logged global temporary table when a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed.

DELETE ROWS

If the table data has been changed, all the rows will be deleted. This is the default.7 7

PRESERVE ROWS7

Rows of the table will be preserved. 7

WITH REPLACE

Indicates 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 (SQLSTATE 42710).

IN  tablespace-name

Identifies the table space in which the global temporary table will be instantiated. The table space must exist and be a USER TEMPORARY table space (SQLSTATE 42838), over which the authorization ID of the statement has USE privilege (SQLSTATE 42501). If this clause is not specified, a table space for the table is determined by choosing the USER TEMPORARY table space with the smallest sufficient page size over which the authorization ID of the statement has USE privilege. When more than one table space qualifies, preference is given according to who was granted the USE privilege:

1. the authorization ID
2. a group to which the authorization ID belongs
3. PUBLIC

If more than one table space still qualifies, the final choice is made by the database manager. When no USER TEMPORARY table space qualifies, an error is raised (SQLSTATE 42727).

Determination of the table space may change when:

• table spaces are dropped or created
• USE privileges are granted or revoked.

The sufficient page size of a table is determined by either the byte count of the row or the number of columns. For more details, see "Row Size" in "CREATE TABLE".

PARTITIONING KEY ( column-name,...)

Specifies the partitioning key used when data in the table is partitioned. Each  column-name must identify a column of the table and the same column must not be identified more than once.

If this clause is not specified, and this table resides in a multiple partition database partition group, then the partitioning key is defined as the first column of declared temporary table.

For declared temporary tables, in table spaces defined on single-partition database partition groups, any collection of columns can be used to define the partitioning key. If you do not specify this parameter, no partitioning key is created.

USING HASHING

Specifies the use of the hashing function as the partitioning method for data distribution. This is the only partitioning method supported.

Notes

• |A user temporary table space must exist before |a user-defined temporary table can be declared (SQLSTATE 42727).
• Referencing a declared global temporary table: The description of a declared global temporary table does not appear in the DB2 catalog (SYSCAT.TABLES); therefore, it is not persistent and is not shareable across database connections. This means that each session that defines a declared global temporary table called table-name has its own possibly unique description of that declared global temporary table.

  In order to reference the declared global temporary table in an SQL statement (other than the DECLARE GLOBAL TEMPORARY TABLE statement), the table must be explicitly or implicitly qualified by the schema name SESSION. If table-name is not qualified by SESSION, declared global temporary tables are not considered when resolving the reference.

  A reference to SESSION.table-name in a connection that has not declared a global temporary table by that name will attempt to resolve from persistent objects in the catalog. If no such object exists, an error occurs (SQLSTATE 42704).

• When binding a package that has static SQL statements that refer to tables implicitly or explicitly qualified by SESSION, those statements will not be bound statically. When these statements are invoked, they will be incrementally bound, regardless of the VALIDATE option chosen while binding the package. At runtime, each table reference will be resolved to a declared temporary table, if it exists, or a permanent table. If neither exists, an error will be raised (SQLSTATE 42704).
• Privileges: When a declared global temporary table is defined, the definer of the table is granted all table privileges on the table, including the ability to drop the table. Additionally, these privileges are granted to PUBLIC. (None of the privileges are granted with the GRANT option, and none of the privileges appear in the catalog table.) This enables any SQL statement in the session to reference a declared global temporary table that has already been defined in that session.
• Instantiation and Termination: For the explanations below, P denotes a session and T is a declared global temporary table in the session P:
  • An empty instance of T is created as a result of the DECLARE GLOBAL TEMPORARY TABLE statement that is executed in P.
  • Any SQL statement in P can make reference to T; and any reference to T in P is a reference to that same instance of T.
  • If a DECLARE GLOBAL TEMPORARY TABLE statement is specified within the SQL procedure compound statement (defined by BEGIN and END), the scope of the declared global temporary table is the connection, not just the compound statement, and the table is known outside of the compound statement. The table is not implicitly dropped at the END of the compound statement. A declared global temporary table cannot be defined multiple times by the same name in other compound statements in that session, unless the table has been explicitly dropped.
  • 2 Assuming that the ON COMMIT DELETE ROWS clause was 2 specified implicitly or explicitly, then when a commit operation 2 terminates a unit of work in P, and there is no open WITH HOLD 2 cursor in P that is dependent on T, the commit includes the 2 operation DELETE FROM SESSION.T.
  • 2 When a rollback operation terminates a unit of work 2 or a savepoint in P, and that unit of work or savepoint includes a 2 modification to SESSION.T, then if NOT LOGGED was specified, the 2 rollback includes the operation DELETE from SESSION.T, else the 2 changes to T are undone. 2 2

    When a rollback operation terminates a unit of work or a 2 savepoint in P, and that unit of work or savepoint includes the 2 declaration of SESSION.T, then the rollback includes the operation 2 DROP SESSION.T.

    2 2

    If a rollback operation terminates a unit of work or a savepoint 2 in P, and that unit of work or savepoint includes the drop of a 2 declared temporary table SESSION.T, then the rollback will undo the 2 drop of the table. 2 If NOT LOGGED was specified, then the table will also have been 2 emptied.

  • When the application process that declared T terminates or disconnects from the database, T is dropped and its instantiated rows are destroyed.
  • When the connection to the server at which T was declared terminates, T is dropped and its instantiated rows are destroyed.
• Restrictions on the Use of Declared Global Temporary Tables: Declared global temporary tables cannot:
  • Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or REVOKE statement (SQLSTATE 42995).
  • 2 Be referenced in a CREATE ALIAS, CREATE FUNCTION 2 (SQL Scalar, Table, or Row), CREATE TRIGGER, or CREATE VIEW 2 statement (SQLSTATE 42995).
  • Be specified in referential constraints (SQLSTATE 42995).
• Compatibilities
  • For compatibility with DB2 for OS/390 and z/OS:
    • The following syntax is accepted as the default behavior:
      • CCSID ASCII
      • CCSID UNICODE