Creating Temporary Table Instances
This section provides an overview of temporary table instances and discusses how to:
Define temporary tables.
Set the number of temporary table instances.
Build 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 ...To define a temporary table:
In Application Designer, select File, New.
Select Record from the New Definition dialog box.
Select Insert, Field, and insert the PROCESS_INSTANCE field.
Select the Record Type tab and select the Temporary Table option.
Select to access the PeopleTools Options page.
Image: PeopleTools Options page
This example illustrates the fields and controls on the PeopleTools Options page.
The system determines the total available number of temporary table instances for a base table according to your settings for total and online instances on this page.
|
Field or Control |
Definition |
|---|---|
| Temp Table Instances (Total) (temporary table instances [total]) |
The difference between the total and online numbers is your EPM-managed tables. If you are not using PeopleSoft EPM, the total and online numbers should be the same. |
| Temp Table Instances (Online) (temporary table instances [online]) |
Enter the number of temporary table instances for Application Engine processes started online from the PeopleCode CallAppEngine function. In general, the number you enter should be relatively small (less than 10) so that extra instances do not affect performance. Application Engine uses this value to identify a range of temporary tables devoted to programs called by the CallAppEngine function. A randomizing algorithm balances the load for the online process that is assigned to a temporary table devoted to running the program online. |
The system builds temporary table instances at the same time it builds a base table for the record definition. When the system builds a table (as in Build, Current Object) and the record type is Temporary Table, it determines the total number of instances of the temporary table based on the settings made on PeopleTools Options page, and on the value of Instant Count, in Application Engine Program Properties, Temp Tables tab in Application Designer.
When Instance Count is lowered and temporary tables are rebuilt, temporary table instances are dropped and rebuilt. For instance, If the PeopleTools Option is set to 3 and Instance Count is 10 then there will be 14 tables in the System Catalog. If the Instance Count were lowered to 5 and tables are rebuilt ,14 tables would still remain in the System Catalog. The last 5 tables TAO10, TAO11, TAO12, TAO13, TAO14,need to be manually dropped to match what the Application Engine uses.
The system creates a maximum of 99 temporary table instances, even if the sum exceeds 99 for a particular temporary table.
The naming convention for temporary table instances is: BaseTableName_Number, where Number is a number between 1 and 99, as in PS_TEST_TMP23.
Note: You can take advantage of database-specific features such as table spaces and segmentation. For instance, you may want to use the Build process to generate a data definition language (DDL) script and then fine-tune the script before its execution, or you could place different sets of temporary tables on different table spaces according to instance number.