Db2 for z/OS has provided the ability to create temporary tables since way back in Version 5. But the initial functionality was practical only in certain circumstances due to some inherent limitations. The first type of temporary table supported by Db2 is now known as a created temporary table.
But IBM’s support of temporary tables expanded since (in Version 7) and Db2 offers two different types of temporary tables: created and declared.
Why use Temporary Tables?
Before we delve into these two types of temporary tables, let’s first consider why anybody 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 one 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.
An additional use case is when a query result set needs to be returned more than once during the execution of 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 can be 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 data (or any other non-relational data source) by a program. Then during the course of that program, the temporary table (that contains the heretofore non-relational 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 temporary tables is to make conversion from other relational products easier.
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.
Declared Temporary Tables
The second type of Db2 temporary table is the temporary tables. It 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.
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.
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 DSN8C10.PROJ
ON COMMIT PRESERVE ROWS;
Notice 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
A scrollable enables cursor provides the ability to scroll forward and backward through the data once the cursor is open. Using only SQL, the programmer can navigate up and down the cursor results. Although this blog post is not about scrollable cursors, it is important to know that there are two types of scrollable cursors: static and dynamic. Static scrollable cursors use declared temporary tables.
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;
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. static 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 program. 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.
