Global Temporary Tables
Global Temporary Tables There are occasions when a table does not need to exist for a long period of time. For example, a table may be needed to act as a staging area for data for use by a program. Temporary tables can be used to accommodate this. There are two types of temporary tables: created or declared. Created Temporary TablesCreated temporary tables (CTTs) can help improve performance in many different ways. Any time there is repetitive SQL being used to return a result set, producing exactly the same result each time, a CTT might provide benefit. A subquery that is used more than once is a prime example. It would be better to issue the subquery once, storing the result set rows in the CTT, and use the CTT in subsequent subqueries. The biggest advantage of CTTs is that no logging is done, since no recovery is possible. However, no indexing is done either, so a tablespace scan will always be used as the access path . Also, either an SQL UPDATE or DELETE can make no modifications to the data in the CTT. Only INSERTs are allowed. The CTT will exist for the duration of the unit of work, and will be automatically deleted when a commit is issued unless the table is used in a cursor definition using CURSOR WITH HOLD. CTTs are very useful for stored procedures. For example, global temporary tables can be used as a holding area for nonrelational or non-DB2 data such as data extracted from VSAM files. The data will be held for the duration of the unit of work and can be referenced in SQL statements. This is particularly valuable when a left or full outer join is required using one DB2 table and one non-DB2 table (for example, using VSAM data). An INSERT statement can load the temporary table with the VSAM data, and then the following SQL statement can perform the outer join: EXEC SQL SELECT * FROM T1 LEFT JOIN global-temp-name ON join predicates END-EXEC. This technique logically fits in a stored procedure so that any other process that needs the result can just execute it. The benefit is that the DB2 join algorithms, instead of a homegrown program, are used to perform the outer join. Another major benefit of CTTs is to use them when a materialized set is present for a result set, a view, or a table expression, and the materialized set needs to be used more than once. Sometimes it is necessary to use a workaround in SQL due to the 15-table limit for an SQL statement. CTTs can be used to hold the results of some of the tables prior to a later statement, which would combine the global temporary table to the remaining tables. The only access path available against a CTT is a tablespace scan, so keep the size of the scan in mind when doing performance analysis. When a CTT is used in a join, the access path will generally be a merge scan join that might require sorting the CTT. A CTT can be held longer than a unit of work when it is used inside a cursor definition that is defined WITH HOLD. Creating a Temporary TableA created temporary table is created in the same manner as a normal table, through Data Definition Language (DDL), except that it is not physically created in a tablespace. These tables cannot be created with default values, and they cannot have unique, referential, or check constraints defined for them. The following example shows the creation of a created temporary table that will hold rows containing an amount and a date. CREATE GLOBAL TEMPORARY TABLE SUMMARY AMOUNT_SOLD DECIMAL(5,2) NOT NULL, SOLD_DATE DATE NOT NULL) An empty instance of the table is created when the first implicit or explicit reference is made to it in an SQL statement. In normal use, an insert would be the first statement issued. The temporary table exists only until the originating application commits, does a rollback, or terminates, unless the table is used in a cursor using the WITH HOLD option. Determining How Often CTTs Are MaterializedCTTs are materialized in DSNDB07. If multiple global temporary tables are being continually materialized, you could run into a problem with the performance of all processes using DSNDB07 work files (e.g., sorting). In order to keep control over DSNDB07, you can monitor this materialization through DB2 traces. Performance trace, class 8, IFCID 311 contains information about CTT materialization and cursor processing. Field QW0311CI shows if an instance of a temporary table was created in a work file. You can also see if a cursor was opened or closed. This will give you an idea of the amount of work occurring against the work file tablespace for temporary tables. If you find that there is a lot of activity occurring and feel it may be causing problems, you can also use the trace fields to determine what queries or programs are causing the materialization. 0017 QW0017TT 'TT'=TEMPORARY TABLE SCAN. Declared Temporary TablesDeclared temporary tables (DTTs) give us the ability to declare a temporary table for use in a program. The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session, not just for a UOW. The table description does not appear in the DB2 catalog. It is not persistent and cannot be shared (unlike a CTT). This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can also be dynamically prepared. Each session that defines a declared global temporary table of the same name has its own unique instantiation of the temporary table. When the session terminates, the temporary table is dropped. With DTTs, some of the locking, DB2 catalog updates, and DB2 restart forward and backward log recoveries that are associated with persistent tables are avoided. No authority is required to issue the DECLARE GLOBAL TEMPORARY TABLE statement, but there will be an authority required to use the new user temporary tablespace where the table will be materialized. DTTs can be useful for applications that need to extract data from various sources and use them in SQL joins, or for data that needs to be used repetitively or kept separate from other online transaction processing (OLTP) processes. They can also be used as staging areas for data that comes from various sources so that the data can be manipulated before it is stored permanently in regular tables. Following are a couple of examples of the syntax for DTTs. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.CERTTEST LIKE DB2USER1.TEST INSERT INTO SESSION.CERTEST SELECT * FROM DB2USER1.TEST END-EXEC. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.CERTEST AS (SELECT * FROM DB2USER1.TEST) DEFINITION ONLY END-EXEC. Usage ConsiderationsThese DTTs could be used as a way to temporarily hold or sort data within a program. DTTs are useful for relational online analytical processing (ROLAP) and multidimensional online analytical processing (MOLAP) queries for warehouse tools and as staging area for IMS of VSAM data so it is SQL- and ODBC-accessible. The word SESSION must be the qualifier for a DTT and can be named explicitly in the table name or can be in the QUALIFIER BIND option on the plan or package. Only Undo records are logged and the full range of Data Manipulation Language (DML) (INSERT, UPDATE, SELECT, DELETE) can be performed on them. DTTs are supported by rollback to savepoint or last commit point. The table exists until thread termination, or if thread reuse is being used, it will exist until it is implicitly dropped (which may or may not be desirable, depending on the application). No locks are taken (PAGE, ROW, or TABLE) on DTTs; however, there are locks taken on tablespace and DBD in share mode. They also do not require a declared cursor to hold rows across commits. Static SQL referencing a DTT will be incrementally bound at runtime. The cost associated with a DTT is equivalent to the cost of executing a dynamic SQL statement. High-volume transaction applications need careful evaluation when planning to use DTTs. NOTE
There are some restrictions when using DTTs. They do not support the following:
TEMP DatabaseDTTs are not materialized in DSNDB07, but rather in a segmented TEMP tablespace. There must be a TEMP database created prior to the creation of the DTTs. This database is not sharable across data sharing members , each member must have own. There will need to be several segmented tablespaces created. DB2 will decide where tables are created. PUBLIC will automatically have authority to create DTTs in these tablespaces. The DTTs cannot span tablespaces. This fact does give you the ability to have small tablespaces and be able to control the size of any DTT that is created. There may be a need to have different tablespaces for 4K, 8K, 16K, and 32K pages. DB2 will choose the appropriate one, and if one does not exist, the DECLARE fails. NOTE
NOTE
 |
| | |
Team-Fly  |
| Top |
EAN: 2147483647
Pages: 163