Temporary
Tables: Declared and Created
by Craig S. Mullins
DB2
for OS/390 has provided the capability to create temporary tables since Version
5. But the initial functionality was practical only in certain circumstances due
to some inherent limitations. This first type of temporary table is known as a
created temporary table. IBM’s support of temporary tables has expanded as of
Version 7 – now DB2 offers two different types of temporary tables: created
and declared.
Why
use Temporary Tables?
But
before we investigate these two types of temporary tables, let’s first address
why anyone would want or need to use a temporary table in the first place.
One
potential usage of temporary tables is to store intermediate SQL results.
Consider, for example, if the results of a first query need to be used in a
subsequent query. Instead of rerunning the first query (or combining it with the
subsequent query), the results of the first query can be stored in a temporary
table. Then the temporary table can be joined into the second query without
incurring the overhead of rerunning the first query. This is particularly useful
if the first query is particularly complex or inefficient.
Or
what about result sets that need to be returned more than once by the same
program? Consider this scenario: a complex multi-table join is coded that
consumes a lot of resources to run. Furthermore, that join needs to be run three
times during the course of the program. Instead of running the join three times
you can run it once and populate a temporary table with the results. The next
two times you can simply read the temporary table which might be more efficient
than re-executing the complex, resource-consuming multi-table join.
Temporary
tables also are useful for enabling non-relational data to be processed using
SQL. For example, you can create a global temporary table that is populated with
IMS (or any other non-relational data source) data by a program. Then during the
course of that program, the temporary table containing the IMS data can be
accessed by SQL statements and even joined to other DB2 tables. The same could
be done for data from a flat file, VSAM, IDMS, or any other non-relational data.
Another
reason for IBM’s inclusion of temporary table support in DB2 is to make
conversion from other relational products easier. Microsoft SQL Server and
Oracle both have supported temporary tables for quite some time now. Without
such support in DB2 it was very difficult for developers to convert or port
their Oracle or SQL Server applications to DB2. IBM alleviated this problem by
enabling temporary table support in DB2.
Now
let’s examine the two types of temporary tables supported by DB2.
Created
Temporary Tables
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.
Declared
Temporary Tables
With
Version 7 of DB2, IBM introduced declared temporary tables. Actually, to be more
accurate, declared temporary tables were made available in the intermediate DB2
Version 6 refresh.
This
new type of temporary table is different than a created temporary table and
overcomes many of their limitations. The first significant difference between
declared and created temporary tables is that declared temporary tables are
specified using a DECLARE statement in an application program – and not using
a DDL CREATE statement. Because they are not persistent they do not have
descriptions in the DB2 Catalog.
Additionally, declared temporary
tables offer significant features and functionality not provided by created
temporary tables. Consider:
·
Declared
temporary tables can have indexes and CHECK
constraints defined on them.
·
You
can issue UPDATE statements and positioned DELETE statements against a declared
temporary table.
·
You
can implicitly define the columns of a declared temporary table and use the
result table from a SELECT.
So declared temporary tables are much
more functional than created temporary tables. To “create” an instance of a
declared temporary table you must issue the DECLARE GLOBAL TEMPORARY TABLE
statement inside of an application program. That instance of the declared
temporary table is known only to the process that issues the DECLARE statement.
Multiple concurrent programs can be executing using the same declared temporary
table name because each program will have its own copy of the temporary table.
But there is more work required to use
a declared temporary table than there is to use a created temporary table.
Before you can declare temporary tables you must create a temporary database and
table spaces for them to use. This is accomplished by specifying the AS TEMP
clause on a CREATE DATABASE statement. Then, you must create segmented table
spaces in the temporary database. Only one temporary database for declared
temporary tables is permitted per DB2 subsystem.
When a DECLARE GLOBAL TEMPORARY TABLE
statement is issued, DB2 will create an empty instance of the temporary table in
the temporary table space. INSERT statements are used to populate the temporary
table. Once inserted, the data can be accessed, modified, or deleted. When the
program completes, DB2 will drop the instance of the temporary table. Also, be
aware that users of temporary tables must have been granted USE authority on the
temporary table space.
The following example shows a DECLARE
statement that can be issued from an application program (assuming the temporary
database and table spaces already have been defined):
DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP
(EMPNO
CHAR(6) NOT
NULL,
FIRSTNME
VARCHAR(12) NOT NULL,
MIDINIT
CHAR(1) NOT
NULL,
LASTNAME
VARCHAR(15) NOT NULL,
WORKDEPT
CHAR(3),
PHONENO
CHAR(4)
);
This creates a declared temporary table named TEMP_EMP. Additionally, you can
use the LIKE clause to DECLARE a temporary table that uses the same schema
definition as another currently existing table. You can use the INCLUDING
IDENTITY COLUMN ATTRIBUTES clause to copy the IDENTITY columns as well. For
example:
DECLARE GLOBAL TEMPORARY TABLE TEMP_PROJ
LIKE
DSN8710.PROJ
INCLUDING IDENTITY
ON COMMIT PRESERVE ROWS;
This example shows how to use the INCLUDING IDENTITY clause. However, the sample
table DSN8710.PROJ does not use an IDENTITY column, so this statement would not
work – it is shown as an example only.
Notice
also the ON COMMIT PRESERVE ROWS clause in the previous example. The ON COMMIT
clause specifies what action DB2 is to take with the data in the declared
temporary table when the program issues a COMMIT statement. There are two
options: PRESERVE or DELETE rows. Specifying PRESERVE ROWS indicates that the
rows of the table are to be kept. Beware, though, that the PRESERVE ROWS option
impacts thread reuse. You will not be able to reuse threads for any application
process that contains, at its most recent COMMIT, an active declared temporary
table defined using the PRESERVE ROWS option of the ON COMMIT clause. The other
option, which is the default, is ON COMMIT DELETE ROWS. In that case all of the
rows of the table are deleted as long as there are no cursors defined using WITH
HOLD.
Scrollable Cursors
Scrollable
cursors, another new feature of DB2 Version 7, require declared temporary
tables. A scrollable cursor provides the ability to scroll forward and backward
through the data once the cursor is open. This can be achieved using nothing but
SQL - no host language code (e.g., COBOL, C) is required to facilitate a
scrollable cursor in DB2 V7. A scrollable cursor makes navigating through SQL
result sets much easier.
The
data from a scrollable cursor is maintained in a declared temporary table. DB2
uses this mechanism to facilitate scrolling through data in multiple ways –
forward, backward, or to a specific position.
So,
keep in mind, even if you do not choose to use temporary tables in your
application programs, you may need to implement them to support scrollable
cursors.
Declared Temporary Table Storage
Before
using declared temporary tables, the temporary database and temporary
table spaces must be defined to store the temporary data. For example:
CREATE DATABASE TEMPDB AS TEMP;
CREATE
TABLESPACE TEMPTS
IN
TEMPDB
SEGSIZE 4
BUFFERPOOL BP7;
The
table space is created as a temporary table space by virtue of it being in the
temporary database.
The
page size of the temporary table space must be large enough to hold the longest
row in the declared temporary table. The size of a row in the declared temporary
table might be considerably larger then the size of the row in the table for
which the scrollable cursor is used. As with a regular table, the size of the
row depends on the number of columns that are stored in the declared temporary
table and the size of each column.
An
in-depth discussion of calculating the storage requirements for declared
temporary table table spaces is provided in the DB2 Installation Guide. Be sure
to refer to that manual before implementing declared temporary tables or any
features that rely on declared temporary tables (e.g. scrollable cursors).
Keep
in mind, too, that when there is more than one temporary table space defined to
the DB2 subsystem, DB2 will select which temporary table spaces it will use for
scrollable cursor processing.
Declare or Create?
With
all of the limitations of created temporary tables why would anyone still want
to use them instead of declared temporary tables?
Well,
there are a few potential problems with declared temporary tables, too. First of
all, the SYSPACKDEP catalog table will not show dependencies for declared
temporary tables, but it will for created temporary tables. Secondly, some DBAs
are leery of allowing database structures to be created by application
programmers inside of an application programmer. With limited DDL and database
design knowledge it may not be wise to trust programmers to get the table
structure correct. Furthermore, the additional management of the temporary
database and table spaces can become an administrative burden.
So,
created temporary tables are still useful – in the right situations. They
should be considered primarily when no updating of temporary data is needed and
access to the temporary data is purely sequential.
Synopsis
DB2 provides two options for handling
temporary data in tables: created and declared temporary tables. The wise DB2
professional will understand the capabilities and shortcomings of each type of
temporary table – and deploy the correct type for each specific situation.
From DB2 Update (Xephon) August 2002.
© 2002 Craig S. Mullins, All rights reserved.
Home.
| 
Craig S. Mullins