Temporary Table Creation

For each temporary table you define, a base table structure and a number of its instances are created in the database as ordinary tables with ordinary table structures. The number of temporary table instances is determined by the value of the Temp Table Instances setting in PeopleTools options Utilities > Administration > PeopleTools Options added to the number of PeopleSoft Application Engine temporary tables. These temporary tables are used as work tables that hold transient data, and because they are real tables, they are permanent structures in the database and remain until an explicit drop table command is executed against them.

The nature of a temporary table means that the amount of data that each temporary work table holds varies significantly after each use. Therefore, when RUNSTATS are executed against them, there is a good chance that the statistics captured may not apply and will negatively influence the DB2 optimizer access path selection the next time you use the temporary work table.

Each record of the type Temporary Table is defined as a VOLATILE table in DB2 (beginning with version 8). This definition takes advantage of the DB2 optimizer’s enhanced capability to formulate efficient index access paths for those tables that hold volatile data, without relying on current table statistics. Additionally, because each temporary work table can only be assigned to a single process, the temporary work table is defined with the LOCKSIZE TABLE attribute to reduce the number of lock resources to be managed by DB2. Both the VOLATILE andLOCKSIZE TABLE attributes are be implemented using ALTER table statements.

Example: ALTER Statement Using VOLATILE and LOCKSIZE TABLE

This example shows the additional DDL for ALTER statements generated for temporary tables that implements the VOLATILE andLOCKSIZE TABLE attributes.

CREATE TABLE PS_AC_CSTSEQ_TAO1 (
PROCESS_INSTANCE DECIMAL(10) NOT NULL,
   	DEPOSIT_BU CHAR(5) NOT NULL,
   	DEPOSIT_ID CHAR(15) NOT NULL,
   	PAYMENT_SEQ_NUM INTEGER NOT NULL,
   	BUSINESS_UNIT CHAR(5) NOT NULL,
   	CUST_ID CHAR(15) NOT NULL,
   	ID_SEQ_NUM INTEGER NOT NULL)
IN ARWORK INDEX IN ARWORKIDX
NOT LOGGED INITIALLY;

ALTER TABLE PS_AC_CSTSEQ_TAO1 VOLATILE;
ALTER TABLE PS_AC_CSTSEQ_TAO1 LOCKSIZE TABLE;