Understanding Temporary Table Instances

To run processes in parallel, you need to enable multiple instances of the same temporary table. You use the PeopleTools Options page to set the number of temporary table instances for Application Engine processes started online from the PeopleCode CallAppEngine function.

This global setting is separate from the instance count setting for a particular program. To use a temporary table with a specific program, you assign the table to the program and set the number of instances created when a particular program is run.

Key Fields for Temporary Tables

To take advantage of multiple instances of a temporary table, use the Temporary Table record type.

Insert the PROCESS_INSTANCE field as a key on any temporary tables that you intend to use with Application Engine. Application Engine expects Temporary Table records to contain the PROCESS_INSTANCE field.

Note:

When all instances of a temporary table are in use and the Continue runtime option on the Program Properties dialog box Temp Table tab is selected, PeopleTools inserts rows into the base table using PROCESS_INSTANCE as a key. If you do not include PROCESS_INSTANCE as a key field in a temporary table, select theAbort Temp Table tab runtime option.

Temporary Table Performance Considerations

When you run batch processes in parallel, there is a risk of data contention and deadlocks on temporary tables. To avoid this, Application Engine has a feature that enables you to dedicate specific instances of temporary tables for each process. When Application Engine manages a dedicated temporary table instance, it controls the locking of the table before use and the unlocking of the table after use.

When you decide on the number of instances for temporary tables for a process, you must consider the number of temporary tables that the process uses. The more instances you have, the more copies of the temporary tables you will have in your system. For example, if a process uses 25 temporary tables and you have 10 instances for a process, then you will have 250 temporary tables in your system.

On the other hand, if you are running a process in parallel and all of the dedicated temporary table instances are in use, processing performance will be slower. Therefore, you will need to find a balance that works for your organization.

If you need more temporary table instances after you have entered production, you must rebuild all of your temporary tables so that the database reflects the proper inventory of instances. While the build process runs, users cannot access the database. Because of this, spend time deriving adequate estimates as to the number of temporary tables required.

A physical table within the database, named PS_AEONLINEINST, stores online temporary table instance usage. If you notice performance issues related to online Application Engine program runs, enable the Application Engine SQL and Timings trace.

If the following SQL command requires more time than normal to complete, then the number of online temporary instances defined on the PeopleTools Options page is probably insufficient.

UPDATE PS_AEONLINEINST ...