Declared global temporary tables can be useful
when implementing solutions that require the creation of non-persistent
tables. For example, an application can create a declared temporary
table to process intermediate results. When the application session
ends, the temporary table is dropped.
A declared global temporary tables exists only for the session
during which it was declared. The table cannot be shared with other
sessions. When the session ends, the rows of the table are deleted,
and the description of the temporary table is dropped. Declared temporary
tables do not have catalog contention issues because there is no catalog
entry for declared temporary tables.
Declared global temporary table can contain XML columns and XML
data can be queried and updated. Declared global temporary tables
can also be used in a partitioned database environment. When the temporary
table contains a column specified as a partitioning key, work performed
on the XML data in the temporary table can be distributed among the
database partitions.
Examples
The
following examples assume that a table with company employees contains
XML data. The employee data is like the following employee information:
<company name="MyFirstComany">
<emp id="31201" salary="60000" gender="Female">
<name>
<first>Laura</first>
<last>Brown</last>
</name>
<dept id="M25">Finance</dept>
</emp>
</company>
The following CREATE statement creates
an employee table and with an XML column.
CREATE TABLE COMPANYINFO (ID INT, DOC XML)
The following two statements create a global temporary
table that can be used with the employee table. Both statements create
a temporary table with the same column names and descriptions as the
COMPANYINFO table. The first statement creates a temporary table using
column definitions. The second statement creates a temporary table
using the LIKE clause. The columns of the table have the same name
and description as the columns of the specified table. The ON COMMIT
DELETE ROWS clause specifies all rows of the table are deleted if
no WITH HOLD cursor is open on the table when a COMMIT operation is
performed.
DECLARE GLOBAL TEMPORARY TABLE INSTMPTB (ID INT, DOC XML)
ON COMMIT DELETE ROWS in USR_TBSP
DECLARE GLOBAL TEMPORARY TABLE INSTMPTB LIKE COMPANYINFO
ON COMMIT DELETE ROWS in USR_TBSP
The following
DECLARE statement creates a temporary table with base table row storage
of XML documents. If the XML document is smaller than the inline length
specified, it is stored in the base table.
DECLARE GLOBAL TEMPORARY TABLE TEMPTB (ID INT, DOC XML INLINE LENGTH 3000)
ON COMMIT PRESERVE ROWS NOT LOGGED
If you are
inserting large amounts of data into a global temporary table and
performing queries over the data, you can create indexes over the
XML data to improve performance. For example, the following CREATE
INDEX statements create two indexes over XML data. The first index
is over the employee ID in the XML documents. The second index is
over the employee's family name.
CREATE INDEX SESSION.TEMP_IDX ON SESSION.INSTMPTB (DOC)
GENERATE KEY USING XMLPATTERN '/company/emp/@id'
AS SQL DOUBLE
CREATE UNIQUE INDEX SESSION.TEMP_IDX2 ON SESSION.INSTMPTB (DOC)
GENERATE KEY USING XMLPATTERN '/company/name/last'
AS SQL VARCHAR(100)
You can create a declared
global temporary table in a partitioned database environment to take
advantage of database partitioning. The following DECLARE statement
creates a temporary table using DOCID as the distribution key. After
you add XML data to the temporary table, queries and other operations
on the XML data can take advantage of the partitioned database environment.
DECLARE GLOBAL TEMPORARY TABLE INSTMPTB (ID INT, DOC XML)
ON COMMIT DELETE ROWS
IN USR_TBSP
DISTRIBUTE BY HASH (ID)
The following XQuery
expression uses the global temporary table and the COMPANYINFO table.
The temporary table contains a subset of documents from the COMPANYINFO
table. The XQuery expression returns the employee information from
the COMANYINFO table of employees in the temporary table who are in
the finance department.
XQUERY
for $i in db2-fn:xmlcolumn("SESSION.INSTMPTB.DOC")/company/emp
for $j in db2-fn:xmlcolumn("COMPANYINFO.DOC ")[/company/emp/@id = $i/@id ]
where $i/dept = "Finance"
return $j ;
The following INSERT statement inserts
data from the temporary table into the COMPANYINFO table.
INSERT INTO COMPANYINFO FROM
(SELECT ID, DOC FROM SESSION.INSTMPTB)
Usage notes
The following items apply when
using a declared global temporary table:
- Data row compression is enabled for a declared temporary table.
When the database manager determines that there is a performance gain,
table row data including XML documents stored inline in the base table
object is compressed. However, data compression of the XML storage
object of a declared temporary table is not supported.
- In a partitioned database environment, use the DISTRIBUTE BY clause
to create a declared temporary table with partitioning key. If you
create a declared temporary table using the LIKE clause and the source
table has a partitioning key, the temporary table does not have a
partitioning key.
- Index compression is enabled for indexes that are created on declared
temporary tables including user-created indexes over XML data.
- User-created indexes over XML data have the same restrictions
as indexes created for non-temporary tables. For example, in a partitioned
database environment, unique XML indexes over XML data are not supported.