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 drop table command is executed against them.
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 explicitThe 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;