Global Temporary Tables
| Sometimes, 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. Temporary tables are either created or declared. Created Temporary TablesCreated temporary tables (CTTs) can help improve performance in many ways. Whenever repetitive SQL is 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, as no recovery is possible. However, no indexing is done either, so a table space scan will always be used as the access path. Also, an SQL UPDATE or DELETE can not make 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 tablefor example, extracted 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 simply execute it. A table function may be even better, as it would allow the retrieval of this data using standard SQL statements. The benefit is that the DB2 join algorithms, instead of a homegrown program, are used to perform the outer join. CCTs have another major benefit. They can be used 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, owing to the 15-table limits on an SQL statement. CTTs can be used to hold the results of some of the tables prior to a later statement, which would join the global temporary table with the remaining tables. The only access path available against a CTT is a table space 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 DDL, except that it is not physically created in a table space. These tables cannot be created with default values and 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, such as sorting using DSNDB07 work files. 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 whether an instance of a temporary table was created in a work file. You can also see whether a cursor was opened or closed. This will give you an idea of the amount of work occurring against the work file table space for temporary tables. If you find that a lot of activity is occurring and feel that 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) enable you 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 only for a UOW. The table description does not appear in the DB2 catalog, 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. The statement is executable and 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 authority will be required to use the new user temporary table space where the table will be materialized. DTTs can be useful for applications that need to extract data from various sources and use it in SQL joins or for data that needs to be used repetitively or kept separate from other online transaction processing processes. DTTs 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 ConsiderationsDTTs 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 a staging area for IMS or 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 DMLINSERT, UPDATE, SELECT, DELETEcan be performed on them. DTTs are supported by rollback to savepoint or last commit point. The table exists until thread termination; 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, locks are taken on table space and DBD in share mode. DDTs 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 you are planning to use DTTs. NOTE Any dynamic SQL statements that reference DTTs will not be able to use the dynamic statement cache. Some restrictions apply when using DTTs. They do not support the following:
TEMP DatabaseDTTs are not materialized in DSNDB07 but rather in a segmented TEMP table space. A TEMP database must be created prior to the creation of the DTTs. This database is not sharable across data sharing members; each member must have its own. Several segmented table spaces will need to be created. DB2 will decide where tables are created. PUBLIC will automatically have authority to create DTTs in these table spaces. The DTTs cannot span table spaces. This fact enables you to have small table spaces and to be able to control the size of any DTT that is created. Different table spaces for 4K, 8K, 16K, and 32K pages may be needed. DB2 will choose the appropriate one; if one does not exist, the DECLARE fails. NOTE It is recommended that you use the same-sized pages for each table space and spread the data sets across several volumes. NOTE It is important to size this database accordingly to accommodate growth and concurrent transactionsthose transactions that are all using DTTs at a given point in time Commit Options for Declared DTTsPrior to version 8, declared temporary tables persisted until the end of the application. This made for a requirement to discard prior to this point. Version 8 has some options on the DECLARE GLOBAL TEMPORARY TABLE statement to provide alternatives. Those options are as follows.
These options are useful for self-contained stored procedures with several DTTs and the cursors defined on them. The invokers, or creators, of the DTTs can access results, then commit and drop the DTT. The user will not need to specify the name of the DDT in this process. These features will also improve DDF threads (CMSTAT=INACTIVE) because DTTs will no longer stop a thread from becoming inactive. |
EAN: 2147483647
Pages: 175