|
Declared global temporary tables are like regular database tables but offer performance benefits:
In the following sections, you will learn how to employ temporary tables in your applications. Introduction to Temporary TablesDeclared 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 TablesOn 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
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 TablesLet'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 ProceduresYou 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 ProceduresThis 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:
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:
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:
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:
ConsiderationsOn 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 TablesOn zSeries, there are two types of temporary tables:
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. |
|