Declared Global Temporary Tables


Declared global temporary tables are like regular database tables but offer performance benefits:

  • They can store data with minimal infrastructure and resource requirements.

  • They can be tuned to be memory bound for better performance.

  • Because they are accessible only from the connection that creates it, there is no need for locking mechanisms

  • They can be manipulated without transaction logging on some platforms.

In the following sections, you will learn how to employ temporary tables in your applications.

Introduction to Temporary Tables

Declared global temporary tables are often referred to as DGTTs or simply, temporary tables. As the name implies, temporary tables are not persistent database objects. Temporary tables are much like normal tables except that they persist only for the duration of a connection and can only be accessed by the connection that declares it. If two connections each create a temporary table with the same name, each instance of the temporary table is unique. When a database connection is closed, all temporary tables declared through that connection are dropped automatically.

Temporary tables are not owned by a particular user; they belong to the session in which they are created. All temporary tables belong to schema SESSION. You have to fully qualify the temporary tables with SESSION for all temporary table references; otherwise, DB2 will look for a persistent table in the current schema instead (for example, SESSION.temptable1).

Creating the Environment for Temporary Tables

On DB2 LUW and zSeries, some setup is required before temporary tables can be used. On iSeries, no setup is required (temporary tables will be created in the library QTEMP).

On DB2 LUW, a user temporary table space must exist (which is different from, and often confused with, a SYSTEM temporary table space). Like all other table space types, the user temporary table space requires that a buffer pool be assigned to it. When a temporary table is declared and populated, the data will use the buffer pool assigned to the table space to keep the data in memory. If the memory requirements for the temporary table exceed the size of the buffer pool assigned to the table space, DB2 will automatically page some of the data to disk.

Here is a simple example of creating a simple user temporary table space on LUW:

 CREATE USER TEMPORARY TABLESPACE usertempspace     MANAGED BY SYSTEM USING ('usertempspace')     BUFFERPOOL ibmdefaultbp 

Temporary tables that use usertempspace table space will be held in the IBMDEFAULTBP buffer pool. If you are concerned that your temporary tables may consume too much of your primary buffer pool, you may want to create a dedicated buffer pool for the user temporary table space.

On zSeries you need to

  • Create a database and use the AS TEMP clause to ensure this database is only used for temporary tables.

  • Create a user temporary table space in the TEMP database.

For example, this statement will create the database tempdb that will only be used by temporary tables:

 CREATE DATABASE tempdb AS TEMP 

Only one TEMP database can be created for each DB2 subsystem or data sharing member. A TEMP database cannot be shared between DB2 subsystems or data sharing members.

Once a TEMP database has been created, you must create a table space in this database. For example, the following statement will create table space temptbl in database tempdb using 8K page buffer pool BP8K0:

 CREATE TABLESPACE temptbl in tempdb bufferpool BP8K0 

Declaring Global Temporary Tables

Let's now look at the many ways in which you can declare and use a temporary table. The syntax for declaring a temporary table looks a lot like creating standard tables. The syntax and options available for declared global temporary tables varies slightly by platform. Therefore, a basic syntax diagram is provided for each platform in Figures 10.8, 10.9, and 10.10. For a full description of the DECLARE GLOBAL TEMPORARY TABLE, consult the SQL Reference for your platform. The following examples will also call out some of the slight differences in options and syntax you should be aware of.

Figure 10.8. DECLARE GLOBAL TEMPORARY TABLE syntax for DB2 LUW.
 >>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->         .-,---------------------.         V                       | >--+-(----| column-definition|-+--)------------------------------+-->    +-LIKE--+-table-name1-+--+------------------+-------------------+    | '-view-name---' '-| copy-options |-' |    '-AS--(--fullselect--)-+-DEFINITION ONLY-+---+------------------+-                                                 '-| copy-options |-'       .-ON COMMIT DELETE ROWS---. >--*--+-------------------------+--*---------------------------->       '-ON COMMIT PRESERVE ROWS-' >--+-------------------------------------------+---------------->    |  .-ON ROLLBACK DELETE ROWS---. |       '-NOT LOGGED--+---------------------------+-'       '-ON ROLLBACK PRESERVE ROWS-' >--*--+--------------+--*--+---------------------+-------------->    '-WITH REPLACE-'  '-IN--tablespace-name-' 

Figure 10.9. DECLARE GLOBAL TEMPORARY TABLE syntax for iSeries.
 >>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->        .-,------------------------------------------.        V                                            | >--+-(----+-column-definition----------------------+-+--)-+----->    |  '-LIKE--+-table-name-+--+--------------+-' |    |          '-view-name--' '-copy-options-'            |    +-LIKE--+-table-name-+--+--------------+---------------+    |        '-view-name--' '-copy-options-' |    '-as-subquery-clause-----------------------------------' .-----------------------------------------------. V                                               | >----+-WITH REPLACE------------------------------+-+-----------><      | .-ON COMMIT DELETE ROWS---. |      +-+-------------------------+---------------+      | '-ON COMMIT PRESERVE ROWS-' |      |             .-ON ROLLBACK DELETE ROWS---. |      '-NOT LOGGED--+---------------------------+-'                    '-ON ROLLBACK PRESERVE ROWS-' 

Figure 10.10. DECLARE GLOBAL TEMPORARY TABLE syntax for zSeries.
 >>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->        .-,---------------------.        V                       | >--+-(----+-column-definition---+-----------------------------------------+---->    |                                   --COLUMN ATTRIBUTES--              |    |                        --EXCLUDING IDENTITY--|--------------------|  |    |--LIKE-- --table-name-- ----|------------------------------------- |  |    |     |--view-name----|                      |--COLUMN ATTRIBUTES-- |  |    |                       --INCLUDING IDENTITY-|--------------------- |  | |    |--AS--(fullselect)--WITH NO DATA---------------------------------- |                                     |--copy-options--|   .-----------------------------------------------.   V                                               | >----+-------------------------------------------+-+-----------><      | .-ON COMMIT DELETE ROWS---.               |      +-+-------------------------+---------------'      | |-ON COMMIT PRESERVE ROWS-|      | '-ON COMMIT DROP TABLE----|      '-----CCSID--+-ASCII---+----'                   |-EBCIDIC-|                   '-UNICODE-' 

An example of declaring a temporary table is shown in Figure 10.11.

Figure 10.11. An example of declaring a temporary table.
 DECLARE GLOBAL TEMPORARY Table session. tempnewproj, (projname VARCHAR(24)                                                     , projsdate DATE                                                     , projedate DATE                                                     , category VARCHAR(10)                                                     , desc VARCHAR(100) )       ON COMMIT PRESERVE ROWS       NOT LOGGED                    -- applies to LUW and iSeries       ON ROLLBACK PRESERVE ROWS     -- applies to LUW and iSeries       WITH REPLACE                  -- applies to LUW and iSeries      IN usertempspace              -- applies to LUW ; 

The example demonstrates creating a temporary table called tempnewproj. The columns and data types are defined explicitly the same way you would for standard tables.

On LUW, BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC, DATALINK, reference, and structured types are not supported in temporary tables. On zSeries, all built-in data types are supported in temporary tables except for BLOB, CLOB, DBCLOB, and ROWID data types. On iSeries, BLOB, CLOB, DBCLOB, VARCHAR, VARGRAPHIC, and DATALINK are supported, but with minor restrictions. Refer to the iSeries SQL Reference for these restrictions.

The IN clause is used to specify a temporary table space for the temporary table and applies to DB2 LUW only. If you do not specify it, DB2 will automatically select one by default. DB2 UDB for iSeries does not have the concept of table space, and on zSeries temporary tables are created in a table space with the appropriate page size that belongs to the TEMP database. For portability, leave this option out of the temporary table definition.

Before you can declare another temporary table with the same name in the same session, the existing temporary table has to be dropped. You can do this by explicitly using the DROP TABLE statement.

 DROP TABLE session.tempnewproj 

On DB2 LUW and iSeries, a WITH REPLACE option is available, as shown in Figure 10.8. When using this option, should you declare a table with the same name (within the same session), DB2 will automatically drop the existing temporary table and re-create it with the new definition. The WITH REPLACE option is particularly useful in connection pooled environments (where connections are returned to a pool rather than released by applications). With connection pooling, it is possible that previously used temporary tables have not been dropped when the connection is returned to the pool. Using the WITH REPLACE option ensures that next application using a pooled connection will not pick up data left over from its previous use.

The ON COMMIT option allows you to specify whether you want to preserve or delete data in the temporary table on commit. The default behavior is to flush the temporary table's contents on commit.

The NOT LOGGED clause is used to indicate that data changes in this table are not logged (for better performance). On zSeries, minimal logging is required and the NOT LOGGED clause is currently not available.

There are other methods for declaring temporary tables as well.

 DECLARE GLOBAL TEMPORARY TABLE TEMP_EMPLOYEE LIKE EMPLOYEE 

Often, temporary tables take the same form of a real table because the temporary table will be used to stage a subset of data from another table for processing. Figure 10.11 demonstrates the use of the LIKE clause to easily accomplish this. The main benefit of this is simplicity because there is no need to provide the column definitions for the temporary table. The source table definition can be a real table, another temporary table, a view, or a nickname (for a remote table). Only column names, their types, and nullable attribute are copied. Indexes and constraints are not copied.

Figure 10.12 illustrates yet another way to declare temporary tables. The column names and types of the full select determine the structure of the temporary table. The temporary table does not get populated by the SELECT statement.

Figure 10.12. Declaring a temporary table using a full-select.
 DECLARE GLOBAL TEMPORARY TABLE TEMP_EMPDEPT AS     ( SELECT E.FIRSTNME, E.LASTNAME, D.DEPTNAME           FROM EMPLOYEE E, DEPARTMENT D WHERE E.WORKDEPT=D.DEPTNO)     DEFINITION ONLY     -- on zSeries and iSeries, this is a synonym for WITH NO DATA 

Using Temporary Tables in SQL Procedures

You can declare, manipulate, and drop temporary tables in an SQL procedure. Figure 10.13 demonstrates the basic elements of declaring and using temporary tables in the context of a stored procedure. It also highlights a potential problem that is not immediately evident.

Figure 10.13. A simple example of using a temporary table.
 CREATE PROCEDURE temp_table_demo()     LANGUAGE SQL     SPECIFIC temp_table_demo      -- applies to LUW and iSeries   --WLM ENVIRONMENT <env>         -- applies to zSeries DYNAMIC RESULT SETS 1 BEGIN     DECLARE v_sql VARCHAR(100) DEFAULT 'SELECT * FROM session.temp_table';     DECLARE c_cur CURSOR WITH RETURN TO CALLER FOR stmt;                 -- (1)     DECLARE GLOBAL TEMPORARY TABLE         session.temp_table ( id INT, value VARCHAR(10))         ON COMMIT PRESERVE ROWS;     INSERT INTO session.temp_table VALUES (1,'a');     INSERT INTO session.temp_table VALUES (2,'b');     INSERT INTO session.temp_table VALUES (3,'c');     PREPARE stmt FROM v_sql;     OPEN c_cur; END 

Looking carefully at the sample code, you will notice that the cursor c_cur on Line (1) is declared to return the contents of the temporary table to the calling application. However, rather than declaring the cursor to directly select from the temporary table, dynamic SQL is used to do this. Use of dynamic SQL (discussed in Chapter 7, "Working with Dynamic SQL") is required on LUW because object dependencies are resolved at procedure build time.

Use of dynamic SQL to open a cursor on a temporary table is not required on zSeries and iSeries. On zSeries, procedures are created with the VALIDATE (RUN) bind option by default. The example is applicable, however, if the procedure is created with the VALIDATE (BIND) option. On iSeries, object dependencies are always deferred until execution time. Using dynamic SQL for cursors is recommended for compatibility on all DB2 platforms.

Sharing Temporary Tables Between Multiple Procedures

This section demonstrates how to share temporary tables between procedures. The examples are designed such that they work on all platforms.

Before you can reference a temporary table, it must first be declared so that DB2 knows its structure. Complications can arise when the location where a temporary table is declared differs from the location(s) where it may be referenced. Consider the following scenarios:

  • An application wants to declare temporary table T, populate it, and then call procedure P to process the data. How can both the application and procedure P make reference to T without procedure P re-declaring T?

  • A stored procedure P1 may declare a temporary table T, populate it, and wish to call procedures P2 and P3 to process the data. How can P2 and P3 reference a temporary table declared in P1 without re-declaring it themselves?

In both cases, attempts to re-declare a temporary table within the same session will result in either SQLSTATE 42710 (the object already exists) or the first table being replaced by subsequent declarations if WITH REPLACE is specified.

The solution to this problem is to recognize that the declaration and reference(s) do not have to be contained in the same procedure body. The only requirement is that the temporary table must be declared in the current session at procedure build time. In the following example, two procedures that share a temporary table called SESSION.temp will be demonstrated.

Figure 10.14 shows a procedure called init_temp that simply encapsulates the declaration of the temporary table SESSION.temp.

Figure 10.14. A procedure for encapsulating the declaration of a temporary table.
 CREATE PROCEDURE init_temp()     LANGUAGE SQL     SPECIFIC init_temp              -- applies to LUW and iSeries   --WLM ENVIRONMENT <env>           -- applies to zSeries it: BEGIN     DECLARE GLOBAL TEMPORARY TABLE       session.temp (id INT, data VARCHAR(10))       WITH REPLACE                    -- applies to LUW and iSeries       ON COMMIT PRESERVE ROWS; END it 

Using a procedure to initialize the temporary table yields three key benefits:

  • It allows temporary tables to be easily declared for the database connection used by the DB2 Development Center.

  • The procedure developer does not have to hunt through application code (that may be maintained by another person) to find the DDL of the temporary table.

  • If the same temporary table may be instantiated from multiple locations in application code, the definition of the table is centralized at one place.

With the procedure init_temp, it is relatively easy to manage an environment for building procedures which share temporary tables. You can build a procedure which references the temporary table as long as init_temp is executed first in the current database connection.

To complement init_temp, define a procedure to drop the temporary table as illustrated in Figure 10.15. This is necessary for zSeries because the WITH REPLACE option is not currently available on that platform. On LUW or iSeries, if the temporary table SESSION.temp exists in the current session, calling init_temp automatically drops any existing table.

Figure 10.15. A procedure to encapsulate the dropping of a temporary table.
 CREATE PROCEDURE close_temp()     LANGUAGE SQL     SPECIFIC CLOSE_TEMP              -- applies to LUW and iSeries     -- WLM ENVIRONMENT <env>         -- applies to zSeries BEGIN     DROP TABLE SESSION.TEMP; END 

Using a procedure to drop the temporary table has these two benefits:

  • It allows one to drop temporary tables for the session when working in DB2 Development Center.

  • It completes the encapsulation of the temporary table declaration through stored procedures.

To build the procedure insert_to_temp in Figure 10.16, execute the procedure init_temp. Notice that insert_to_temp statically refers to SESSION.temp but does not contain DECLARE TEMPORARY TABLE in its body.

Figure 10.16. A procedure that references a temporary table without a declaration.
 CREATE PROCEDURE insert_to_temp()     LANGUAGE SQL     SPECIFIC insert_to_temp           -- applies to LUW and iSeries   --WLM ENVIRONMENT <env>             -- applies to zSeries BEGIN     INSERT INTO SESSION.TEMP VALUES (1, 'one');     INSERT INTO SESSION.TEMP VALUES (2, 'two');     INSERT INTO SESSION.TEMP VALUES (3, 'three'); END 

Another procedure, show_rows in Figure 10.17, shares the temporary table data. It returns the contents of the temporary table to the application.

Figure 10.17. A procedure that returns the contents of a temporary table.
 CREATE PROCEDURE show_rows()     LANGUAGE SQL     SPECIFIC show_rows             -- applies to LUW and iSeries   --WLM ENVIRONMENT <env>          -- applies to zSeries DYNAMIC RESULT SETS 1 BEGIN     DECLARE cur CURSOR WITH HOLD WITH RETURN TO CALLER FOR         SELECT * FROM session.temp;     OPEN cur; END 

To wrap up this example, Table 10.1 lists the calling sequence:

Table 10.1. Calling Sequence for Sharing Temporary Tables

Step 1:

CALL init_temp

This will re-initialize the SESSION.temp table.

Step 2:

CALL insert_to_temp

This will insert three rows into SESSION.temp.

Step 3:

CALL show_rows

The result set returned will be the three rows contained in the temporary table populated by insert_to_temp.

Step 4:

CALL close_temp

Drops the temporary table.


Considerations

On iSeries temporary tables are supported in both user-defined functions and triggers. On zSeries, temporary tables are supported in user-defined functions but not in triggers. On LUW, temporary tables are not supported in user-defined functions or triggers. However, functions and triggers can call stored procedures that use temporary tables.

Created Global Temporary Tables

On zSeries, there are two types of temporary tables:

  • Temporary tables created with the DECLARE GLOBAL TEMPORARY TABLE statement (declared).

  • Temporary tables created with the CREATE GLOBAL TEMPORARY TABLE statement (created).

This chapter has discussed the first type of temporary tables. For portability with the other platforms, this is the type of temporary tables that should be used. If you are developing applications exclusively on zSeries, however, you may want to consider using the second type of temporary tables.

Created temporary tables put a description of the table in the catalog. This description is persistent and can therefore be shared across application processes. Though the description is shared, an instance of this table is distinct for each application process, and it will not persist beyond the life of the application process.

The name for this table follows the rules of any standard table, and the schema name does not need to be SESSION.

Indexes, UPDATE operations, and DELETE (positioned only) operations are not supported with the created temporary tables. Locking, logging, recovery, table space, and database operations do not apply to this type of table as well.

The created global temporary table is stored in table spaces in the work file database. It is not stored in a database created as TEMP as with declared global temporary tables.

For more detail about created global temporary tables, refer to the DB2 for zSeries SQL Reference.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net