Craig S. Mullins 

Return to Home Page

August 2002

 


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.