Managing Temporary Table Instances
This section provides an overview of temporary table instance numbers and discusses how to:
Assign temporary tables to programs.
Adjust meta-SQL.
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 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 you defined APPLTMPA as a temporary record type, 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 for 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 theTemp Table Instances (Online) field is less than the value for theTemp 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 in PeopleSoft EPM).
Open an Application Engine program in Application Designer. Select File, Definition Properties and then select the Temp Tables tab.
Image: Program Properties dialog box:Temp Tables tab
This screenshot illustrates the fields and controls on the Program Properties dialog box: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. Any time you change the instance count, you should rebuild the temporary tables to ensure that the correct number of instances is created and available for your programs.
Share Tables in Online Mode has to be selected if the online Application Engine program needs to share the temporary table instances.
If an Application Engine program is currently sharing the temporary table instances , deselect the Share Tables in Online Mode to make the program use dedicated temporary tables. With Share Tables in Online Mode disabled, any temporary table instance can be accessed by only one online process at a time. In case no temporary table instances are available for an online Application Engine process, the process has to wait until one of the temporary table instance is released by other processes.
Use Delete for Truncate Tableclears the temporary table by performing delete during Application Engine process.
Note: The concept of dedicated temporary tables is isolated to the Application Engine program run. The locking, truncate/delete from, and unlocking actions 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 no temporary tables are available and you selected Continue for theIf 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 theRelease button on the Temporary Tables tab of the Application Engine program properties. |
Note: After you manually release 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.
A critical step in implementing parallel processing is to ensure that you have included appropriate meta-SQL within the code that your Application Engine program runs.
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 are 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 a Temporary Table Process Instance with a 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 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 a 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 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 because they preclude the possibility of concurrent processing.
Use Delete for Truncate Table to delete the temporary tables during Application Engine allocation process. This feature will not delete the temporary tables by default during the allocation. If Use Delete for Truncate Table option is selected delete is performed in all the cases.
Open an Application Engine program in Application Designer. Select File, Definition Properties and then select the Temp Tables tab. Select the Use Delete for Truncate Table to improve the Application Engine process performance.