PeopleSoft Dedicated Temporary Tables

One of the ways to improve scalability and reduce the time taken for processing batch programs is to run multiple instances of the same program on subsets of the data in parallel. For example, instead of processing orders 1 to 1,000,000 in a single instance, run five concurrent instances of the same batch program for 200,000 orders each. When running more than one instance of the program concurrently, the most serious potential problem is data contention, which can result in lock waits and deadlocks.

To reduce contention, PeopleSoft Application Engine provides dedicated temporary tables. These temporary tables are permanent with respect to the Application Engine program definition. Only the data residing in these tables is temporary.

Temporary tables also improve performance when a subset of data from a huge table is referenced multiple times within the program. Temporary tables can:

• Store intermediate data during the process.
• Minimize contention when running in parallel.
• Ensure that each process uses its own temporary table instance.

It is important to allocate the correct number of dedicated temporary tables for your environment. If the temporary tables are under-allocated, the total available instances of a particular temporary table are less than the total number of programs running at one time that use the temporary table. When this condition occurs, the program either uses the base temporary table and continues to run, or it aborts with an error, depending on whether the program property setting If non-shared tables cannot be assigned is set to Continue or Abort, respectively. These options may both be undesirable.

Following are the drawbacks if the program uses the base (non-shared) temporary table:
• Multiple instances may read or write into the same table, causing contention.
• Selecting becomes dependent on ProcessInstance as a leading qualifier.
DELETE is performed instead of TRUNCATE TABLE. DELETE is far slower than TRUNCATE TABLE.

Important reasons for PeopleSoft to choose regular tables as temporary tables instead of using SQL Server’s temporary tables (#tables) include the following:

• A number of Application Engine programs are restartable. This means that if a program abends in the middle of the run, data stored in the temporary tables is preserved. This enables the program to restart from the last commit point. This is not possible if it uses #tables instead, because they will not be available after the session terminates.
• In SQL Server, this regular form of temporary table offers the same performance as the #tables when they are allocated correctly.

Statistics at Runtime for Temporary Tables
PeopleSoft uses shared temporary tables or dedicated temporary tables in the batch processes. These temporary tables will have few or no rows in the beginning of the process and again few or no rows at the end of the process. Temporary tables are populated during the process, and are deleted or truncated at the beginning or end of the process. Because data in these tables changes so radically, accurate statistics on these tables may help the SQL statements significantly.

Beginning with PeopleSoft 8, if the process is written in PeopleSoft Application Engine, then %UpdateStats meta-SQL can be used in the program after the rows are populated. This ensures that the statistics are updated before the selection happens from that table.

Note. COMMIT is required prior to executing this statement. Make sure to use the COMMIT statement immediately following the previous step. If you do not do so, then this statement will be skipped by Application Engine and will not be executed.

For example, suppose you have the following meta-SQL command in an SQL step of an Application Engine program:


This meta-SQL issues the following command to the database at runtime:


Make sure the temporary table statistics have been handled as shown above. If you find that statistics created by AUTO UPDATESTATS is sufficient, you can disable %UpdateStats in the program.

Disabling Update Statistics
Update Statistics (%UpdateStats) can be disabled in two ways.

• Program level: Identify the steps that issue %UpdateStats and deactivate them. These steps can be identified by the Application Engine trace (for example, set the trace flag TraceAE=3). This is a program-specific setting.
• Installation level: If there is a compelling reason to disable update statistics for all batch programs, then the installation level setting can be applied to disable %UpdateStats. The following parameter should be set in the Process Scheduler configuration file psprcs.cfg to achieve this:

; DbFlags Bitfield
; Bit Flag
; --- ----
; 1 - Ignore metaSQL to update database statistics (shared with COBOL)

Note. Carefully consider all the positive and negative effects before setting this flag because it can adversely affect performance.