Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
This is what I have got, I am not sure as never tried it but worth taking a look,
| Code: |
A created temporary table exists only as long as the process that uses it. Temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. When created, the schema for the table is stored in the DB2 system catalog (SYSIBM.SYSTABLES) just like any other table, but the TYPE column is set to 'G' to indicate a global temporary table. Created temporary tables are sometimes referred to as global temporary tables – but this is confusing since declared temporary tables are also referred to as global declared tables.
It is important to remember that a created global temporary table must be created using a DDL CREATE statement before it can be used in any program.
A created temporary table is instantiated when it is referenced in an OPEN, SELECT INTO, INSERT, or DELETE statement, not when it is created. Each application process that uses the temporary table creates a new instance of the table for its use. When using a created temporary table, keep the following in mind:
• Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.
• Indexes can not be created on created temporary tables so all access is by a complete table scan.
• Constraints can not be created on created temporary tables.
• A null is the only default value permitted for columns of a created temporary table.
• Created temporary tables can not be referenced by DB2 utilities.
• Created temporary tables can not be specified as the object of an UPDATE statement.
• When deleting from a created temporary table, all rows must be deleted.
• Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.
Work file data sets are used to manage the data of created temporary tables. The work database (DSNDB07) is used as storage for processing SQL statements that require working storage – not just for created temporary tables. So if you are using created temporary tables be sure to examine the DB2 Installation Guide for tactics to estimate the disk storage required for temporary work files.
When a temporary work file result table is populated using an INSERT statement, it uses work file space. No other process can use the same work file space as that temporary work file table until the table goes away. The space is reclaimed when the application process commits or rolls back, or when it is deallocated, depending which RELEASE option was used when the plan or package was bound. It is a good idea to keep the work files in a separate buffer pool to make it easier to monitor. IFCID 0311 in performance trace class 8 can be used to distinguish these tables from other uses of the work file. |
|
|
