Using Temporary Tables

This chapter provides an overview of temporary tables and discusses how to:

Click to jump to parent topicUnderstanding Temporary Tables

Because Application Engine programs run in batch mode, multiple instances of the same program often execute in parallel. When this happens, there is a significant risk of data contention and deadlocks on tables. To avoid this, you can dedicate specific instances of temporary tables for each program run.

You can also use temporary tables to improve performance. For example, if you find that, multiple times during a run, the program accesses a small subset of rows from a much larger table, you can insert the necessary rows into a temporary table as an initialization task. Then the program accesses the data residing in the smaller temporary table rather than the large application table. This technique is similar to reading the data into an array in memory, except that the data never leaves the database, which is an important consideration when the program employs a set-based processing algorithm.

Any number of programs, not just Application Engine programs, can use the temporary table definitions. When you specify a temporary table on the Temp Tables tab in the Application Engine program properties, Application Engine automatically manages the assignment of temporary table instances. 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.

Parallel Processing

Parallel processing is used when considerable amounts of data must be updated or processed within a limited amount of time, or batch window. In most cases, parallel processing is more efficient in environments containing multiple CPUs and partitioned data.

To use parallel processing, partition the data between multiple concurrent runs of a program, each with its own dedicated version of a temporary table (for example, PS_MYAPPLTMP). If you have a payroll batch process, you could divide the employee data by last name. For example, employees with last names beginning with A through M get inserted into PS_MYAPPLTMP1; employees with last names beginning with N-Z get inserted into PS_MYAPPLTMP2.

To use two instances of the temporary table, you would define your program (say, MYAPPL) to access to one of two dedicated temporary tables. One execution would use A-M and the other N-Z.

The Application Engine program invokes logic to pick one of the available instances. After each program instance is matched with an available temporary table instance, the %Table meta-SQL construct uses the corresponding temporary table instance. Run control parameters passed to each instance of the MYAPPL program enable it to identify which input rows belong to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table. The following diagram illustrates this process:

Multiple program instances running against multiple temporary table instances

No simple switch or check box enables you to turn parallel processing on and off. To implement parallel processing, you must complete the following set of tasks. With each task, you must consider details about your specific implementation.

  1. Define and save temporary table records in Application Designer.

    You do not need to run the SQL Build process at this point.

  2. In Application Engine, assign temporary tables to Application Engine programs, and set the instance counts dedicated for each program.

    Employ the %Table meta-SQL construct so that Application Engine can resolve table references to the assigned temporary table instance dynamically at runtime.

  3. Set the number of total and online temporary table instances on the PeopleTools Options page.

  4. Build temporary table records in Application Designer by running the SQL Build process.

Click to jump to parent topicCreating Temporary Table Instances

This section provides an overview of temporary table instances and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding 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 the Abort 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 take into consideration the number of temporary tables that the process uses. The more instances you have the more copies of the temporary tables you will have on your system. For example, if a process uses 25 temporary tables and you have 10 instances for a process, you will have 250 temporary tables on 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, this slows down the performance of the process. So, 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, this is a good indication that not enough online temporary instances are defined on the PeopleTools Options page.

UPDATE PS_AEONLINEINST ...

Click to jump to top of pageClick to jump to parent topicDefining Temporary Tables

To define a temporary table:

  1. In Application Designer, select File, New.

  2. Select Record from the New Definition dialog box.

  3. Select Insert, Field, and insert the PROCESS_INSTANCE field.

  4. Select the Record Type tab and select the Temporary Table option.

Click to jump to top of pageClick to jump to parent topicSetting the Number of Temporary Table Instances

Select PeopleTools, Utilities, Administration, PeopleTools Options to access the PeopleTools Options page.

The system determines the total available number of temporary table instances for a base table according to the settings for total and online instances that you make on this page.

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 online program execution.

Click to jump to top of pageClick to jump to parent topicBuilding Table Instances

The system builds temporary table instances at the same time it builds the 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 that you made on the PeopleTools Options page.

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 the temporary table instances is as follows: 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.

Click to jump to parent topicManaging Temporary Table Instances

This section provides an overview of temporary table instance numbers and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding Temporary Table Instance Numbers

You use the Temp Tables tab in the Program Properties dialog box to manage the number of different batch or dedicated temporary tables required for each program definition and the number of instances of each. You select all the necessary temporary table records to meet the needs of your program‘s logic.

Note. You must set the instance count on the Temp Tables tab before building the tables in Application Designer.

Regardless of the instance counts value in the Application Engine program properties or on the PeopleTools Options page, make sure that you have the appropriate records assigned to the appropriate programs. You also need to ensure that the SQL inside your Application Engine program contains the correct usage of the %Table construct.

The number of temporary table instances built for a specific temporary table record during the SQL Build process is the value of the total temporary table instances from the PeopleTools Options page added to the sum of all the instance count values specified on the Temp Table tab for the Application Engine programs that use that temporary table.

For example, assume that we have defined APPLTMPA as a temporary record type. If the number of total temporary table instances is set to 10, and APPLTMPA appears in the Temp Tables tab in the Program Properties dialog box for two Application Engine programs. In one program, the instance count is set to 3, and in the other, the instance count is set to 2. When you run the SQL Build process, PeopleTools builds a total of 15 temporary table instances for APPLTMPA.

The total and online instance counts should be equal, unless your PeopleSoft application documentation provides specific instructions on setting these values differently. When the values are equal, the Temp Table Instances (Total) field controls the number of physical temporary table instances to be used by online programs that Application Designer creates for a temporary table record definition. If the value for the Temp Table Instances (Online) field is less than the value for the Temp Table Instances (Total) field, the difference between the two numbers provides a pool of tables for backward compatibility for developers who took advantage of the %Table(record_name, instance_number) approach for manually managing temporary table locking, (such as PeopleSoft EPM).

Click to jump to top of pageClick to jump to parent topicAssigning Temporary Tables to Programs

Open an Application Engine program in Application Designer. Select File, Definition Properties. Select the Temp Tables tab.

In the Record List box, include all the necessary temporary table records for this program.

In the Instance Count field, specify the number of copies of temporary tables for a program. Anytime you change the instance counts, you need to rebuild the temporary tables to ensure that the right number of instances get created and are available for your programs.

Note. The concept of dedicated temporary tables is isolated to the Application Engine program run. The locking, truncate/delete from, and unlocking are designed to occur within the bounds of an Application Engine program run. Therefore, the system does not keep a temporary table instance available after the Application Engine program run is over.

Runtime Allocation of Temporary Tables

Online processes have their own set of dedicated temporary tables, defined globally on the PeopleTools Options page. When you invoke a process online, PeopleTools randomly allocates a single temporary table instance number to a program for all its dedicated temporary table needs. While the program runs, no other program can use that instance number. Any other online process that happens to get the same instance value waits for the first program to finish, so that the instance number is unlocked.

In contrast, batch processes are allocated temporary table instances on a record-by-record basis. The system begins with the lowest instance number available for each temporary table until all of the temporary table instances are in use. If there are not any temporary tables, available and you selected Continue for the If non-shared Tables cannot be assigned group box, then the base table is used, with the process instance number as a key.

When a program ends normally or is cancelled with Process Monitor, the system automatically releases the assigned instances.

Condition

Online

Batch

Temporary tables are allocated using meta-SQL.

%Table(temp-tbl)

%Table(temp-tbl)

Temporary tables are allocated at runtime.

Psae.exe randomly assigns an instance number from the number range on your online temporary table setting on the PeopleTools Options page. Psae.exe uses that number for all tables for that program run.

Individually allocates an instance number based on availability on a record-by-record basis. Psae.exe begins with the lowest instance number available for each temporary table, until all of the instances are in use.

No temporary tables are free.

For a particular record, if the instance is currently in use and the program is set to Continue, then the psae.exe queues the program until the assigned instance number becomes free.

If the program is set to Continue, the system uses a shared base table.

If the program is set to Abort, then the system terminates the program.

Never queues for a table.

A temporary table is initially clear.

Yes, when program instance becomes available.

Yes, when assigned.

An instance number is locked.

The lock is on when the program is loading into memory.

The lock is on when the program is loading into memory. For restartable programs, the temporary tables remain locked across restarts until the program has completed successfully or until the temporary tables are manually released using Process Monitor or the Manage Abends page.

An instance number is unlocked.

Temp tables unlocked on completion of program.

In the event of a kill or a crash, the tables remain locked, and the tables must be freed using Process Monitor or the Manage Abends page.

If restart is disabled, the temporary tables are unassigned automatically in the event of a controlled abnormal termination.

If you cancel a process using Process Monitor, PeopleTools frees the temporary tables automatically.

When you use the Manage Abends page, you must click the Temp Tables button corresponding to the correct process instance, and then click the Release button on the Temporary Tables tab of the Application Engine program properties.

Note. When you have manually released the temporary tables from their locked state, you lose any option to restart the program run.

Sharing Temporary Table Data

Dedicated temporary tables do not remain locked across process instances. If sequential Application Engine programs need to share data by way of temporary tables, a parent Application Engine program should call the programs that share data.

Click to jump to top of pageClick to jump to parent topicAdjusting Meta-SQL

A critical step in implementing parallel processing is to make sure that you have included appropriate meta-SQL within the code that your Application Engine program executes.

Referencing Temporary Tables

To reference a dedicated temporary table, you must use: 

%Table(record)

You can reference any table with %Table, but only those records defined as temporary tables get replaced with a dedicated instance table by Application Engine. When you are developing programs that take advantage of %Table, choose temporary table indexes carefully. Depending on the use of the temporary table in your program and your data profile, the system indexes may be sufficient. On the other hand, a custom index may be needed instead, or perhaps no indexes are necessary at all. Consider these issues when designing your application. You want to define indexes and SQL that perform well in most situations, but individual programs or environments may require additional performance tuning during implementation.

Note. The default table name refers to PS_recname, where PS_recname1,2,… represents the dedicated temporary tables.

As Application Engine resolves any %Table, it checks an internal array to see if a temporary table instance has already been chosen for the current record. If so, then Application Engine substitutes the chosen table name. If not, as in when a record does not appear in the temp table list for the program, then Application Engine uses the base table instance (PS_recname) by default. Regardless of whether %Table is in PeopleCode SQL or in an Application Engine SQL Action the program uses the same physical SQL table.

Populating the Temporary Table Process Instance with the Process Instance

All temporary tables should be keyed by process instance. If you use the Continue option when batch or dedicated tables cannot be assigned, Process Instance is required as a key field. The current process instance is automatically put into the state record, but when you insert rows into your temporary tables, you must supply that process instance. Use %ProcessInstance or %Bind(PROCESS_INSTANCE) meta-SQL to return the numeric (unquoted) process instance.

The process instance value is always zero for programs initiated with the CallAppEngine function. his is because the program called with CallAppEngine runs in process; that is, it runs within the same unit of work as the component with which it is associated.

If you are using dedicated tables and have elected to continue if dedicated tables cannot be assigned, then SQL references to dedicated temporary tables must include PROCESS_INSTANCE in the Where clause.

Clearing Temporary Tables

You do not need to delete data from a temporary table manually. The temporary tables are truncated automatically when they are assigned to your program. If the shared base table has been allocated, because no dedicated instances were available, then Application Engine performs a delete by process instance instead of performing a truncate. In such a case, PROCESS_INSTANCE is required as a high-level key.

You can perform additional deletes of temporary table results during the run, but you must include your own SQL action that uses the %TruncateTable function. If the shared base table has been allocated because no dedicated instances were available, then %TruncateTable is replaced with a delete by process instance instead of a truncate.

Note. You should always use %TruncateTable to perform a mass delete on dedicated temporary tables, especially if the Continue option is in effect.

Even if you have elected to terminate the program if a dedicated table cannot be allocated, you may still use %TruncateTable meta-SQL with dedicated temporary tables. %TruncateTable resolves to either a Truncate or a Delete by process instance, as needed.

The argument of %TruncateTable is a table name instead of a record name. As a result, you must code your SQL as shown in this example:

%TruncateTable(%Table(recname))

Note. You should avoid hard-coded table names inside %TruncateTable, since they preclude the possibility of concurrent processing.

Click to jump to parent topicMaking External Calls

When you call one Application Engine program from another, the assignment of dedicated tables for the called, or child, program, occurs only if the calling, or parent, program is in a state where a commit can occur immediately.

PeopleTools enables you to commit immediately, so that Application Engine can commit the update it performs to lock the temporary table instance. Otherwise, no other parallel process could perform any assignments. In general, this means that you should issue a commit just prior to the Call Section action.

While making external program calls, note the following:

External Calls in Batch Mode

For batch runs, list in the program properties of the root program all of the temporary tables that any called programs or sections use. This ensures that the tables get locked sooner and as a single unit. This approach can improve performance, and it ensures that all the tables required by the program are ready before execution starts.

External Calls in Online Mode

If the online program run is designed to use any temporary tables at any point during the CallAppEngine unit of work, then the root program must have at least one temporary table specified in the Application Engine program properties. This is true even if the root program does not use temporary tables. This is required so that the system locks the instance number early on to avoid an instance assignment failure after the process has already started processing.

All temporary tables used by a specific program, library, or external section must be specified in that program to ensure that the system issues truncates (deletes) for the tables being utilized.

If no temporary tables appear in the root program properties, and Application Engine encounters a %Table reference for a temporary table record, an error appears.

Sample Implementation

The following scenario describes the runtime behavior of Application Engine and temporary tables.

Assume you have Program A and Program B, and three temporary table definitions: PS_TMPA, PS_TMPB, and PS_TMPC. Values on the Temporary Tables tab in the Program Properties dialog box for each program are as follows:

After you run the SQL Build process in Application Designer, the following inventory of temporary tables appears in the database.

For PS_TMPA:

For PS_TMPB:

For PS_TMPC:

Because the instance count for Program A is 4, the system builds four instances of PS_TMPA and PS_TMPB for Program A to use. Because the instance count for Program B is 3, the system builds an additional three instances of PS_TMPB and three instances of PS_TMPC for Program B to use.

Notice that because Program A and Program B are sharing PS_TMPB, there are seven instances. The system derives this total by adding the instance count value from all the programs that share a particular temporary table instance. In this case, the four from Program A and the three from Program B combine to require a total of seven instances of PS_TMPB to be built.

Given that this collection of temporary tables exists in your database, let's say that you start Program A. At runtime, Application Engine examines the list of temporary tables dedicated to Program A, and assigns the first available instances to Program A. Then, assuming that no other programs are running, Application Engine assigns PS_TMPA1 and PS_TMPB1 to Program A.

Suppose that shortly after you started Program A, another user started Program B. Again, Application Engine examines the list of temporary tables dedicated to Program B and assigns the first available instances. In this scenario, Application Engine assigns PS_TMPB2 and PS_TMPC1 to Program B. Because Program A is already using PS_TMPB1, the system assigns PS_TMPB2 to Program B.

The system assigns records, such as TMPA, to programs. The base tables, such as PS_TMPA, are also built, by default, in addition to the dedicated temporary instances. If the Program Properties dialog box setting for the Temp Tables tab is set to Continue when no instances are available, the system uses the base table instead of the dedicated instance.

Click to jump to parent topicViewing Temporary Table Usage

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicViewing Temporary Table Usage by Record

Select PeopleTools, Application Engine, Review Temp Table Usage to access the Temp Table Usage by Record page.

If you implemented temporary tables for parallel Application Engine program runs, use this page and the Temp Table Usage by Program page to find out how the system allocates temporary tables to your programs.

Parallel processing is designed to be a performance enhancing option. However, if the demand for temporary table instances consistently exceeds the current supply, performance suffers. Also, in other situations, your inventory of temporary table instances may far outnumber demand. Here, you may consider reducing the number of instances provided to conserve system resources.

This page shows you the following metrics for evaluating inventory and allocation of temporary tables.

Program Use Count

Shows the instance count of listed program.

Total Instances

Shows the total number of existing instances of a temporary table.

Locked Instances

Shows the current number of instances that they system has locked for program runs.

Unused Instances

Shows the current number of instances that are available for use.

Click to jump to top of pageClick to jump to parent topicViewing Temporary Table Settings by Program

Select PeopleTools, Application Engine, Review Temporary Table Usage, Temp Table Settings by Program to access the Temp Table Settings by Program page.

If the Application Engine process was started in Process Monitor, you can select PeopleTools, Application Engine, Manage Abends to access the Manage Abends page and then the Process Monitor.

Click to jump to top of pageClick to jump to parent topicViewing Online Instance Usage

Select PeopleTools, Application Engine, Review Online Instance Usage to access the Online Instance Usage page.

Click to jump to top of pageClick to jump to parent topicResolving the Temporary Table Usage Warning Message

If an Application Engine batch program is unable to get a dedicated temporary table because all instances are locked, but it can use the base table, the system issues a warning. However, if the program has been set to terminate when a dedicated instance is not available, then the program terminates even if the base table can be used.

You could see the warning message in two ways:

If you see the warning regarding base temporary table usage, this means either there aren’t enough temporary table instances defined or some locked instances that must be released.

When a restartable process terminates abnormally, the temporary tables stay locked to enable a smooth restart. However, if you do not want to restart the process, then the locked temporary tables must be released. When you cancel the process using Process Monitor, the release of locked temporary tables occurs automatically. If the process was not launched through PeopleSoft Process Scheduler, Process Monitor does not track the process. Because of this, you must use the Manage Abends page to release temporary tables used by processes invoked outside of PeopleSoft Process Scheduler.