Chapter 10 Using Temporary Tables 第十章使用临时表

Chapter 10 Using Temporary Tables

第十章使用临时表

Understanding Temporary Tables

理解临时表

Because Application Engine programs run in batch mode, multiple instances of the same program often execute in parallel. When this happens, the risk of data contention and deadlocks on tables is significant. To avoid this risk, 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 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 a batch window. In most cases, parallel processing is more efficient in environments containing multiple CPUs and partitioned data.

当必须在有限的时间内或批处理窗口内更新或处理大量数据时，使用并行处理。在大多数情况下，并行处理在包含多个CPU和分区数据的自环境中更有效。

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 are inserted into PS_MYAPPLTMP1; employees with last names beginning with N through Z are inserted into PS_MYAPPLTMP2.

要使用并行处理，在程序的多个并发运行之间划分数据，每个运行都有自己的临时表的专用版本（例如，PS_MYAPPLTMP）。如果您有一个工资单批处理过程，您可以按姓氏划分员工数据。例如，姓氏以A到M开头的员工将被插入到PS_MYAPPLTMP1中；姓氏以N到Z开头的员工将被插入到PS_MYAPPLTMP2中。

To use two instances of a temporary table, you would define your program (for example, MYAPPL) to access one of two dedicated temporary tables. One run would use A through M and the other N through Z.

要使用一个临时表的两个实例，需要定义程序（例如MYAPPL）来访问两个专用临时表中的一个。一次运行将使用A到M，另一次运行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:

应用程序引擎程序调用逻辑来选择一个可用实例。在每个程序实例与一个可用的临时表实例匹配之后，%Table meta-SQL构造将使用相应的临时表实例。传递给MYAPPL程序的每个实例的运行控制参数使它能够识别哪些输入行属于它，并且每个程序实例使用%Table将源表中的行插入到分配给它的临时表实例中。下图说明了这个过程：

This is a diagram of 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 task. With each step, 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.

1. 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.

1. Set the number of total and online temporary table instances on the PeopleTools Options page.
2. Build temporary table records in Application Designer by running the SQL Build process.

Understanding Global Temporary Tables

理解全局临时表

You can define Global Temporary Tables (GTTs) in the PeopleSoft Application Designer. The performance of Application Engine programs are enhanced on using GTTs. GTTs are temporary tables that hold data that exists only for the duration of a transaction or a session.

GTT应用于同时以批处理模式和在线模式运行的应用引擎程序。GTT中的数据对于以批处理模式运行的Application Engine程序是会话特定的，对于以联机模式运行的Application Engine程序是事务特定的。

• GTTs are applied to Application Engine programs running in both batch mode and online mode. The data in GTT is session specific for Application Engine programs running in batch mode and transaction specific for Application Engine programs running in online mode.
• GTT应用于同时以批处理模式和在线模式运行的应用引擎程序。GTT中的数据对于以批处理模式运行的Application Engine程序是会话特定的，对于以联机模式运行的Application Engine程序是事务特定的。
• GTTs in online mode cannot be shared between Application Engine programs. Each Application Engine program has its own set of GTTs. Therefore, it is recommended that GTTs should not be used as Share Tables in online mode.
• You can also define GTTs to restart-enabled batch Application Engine programs considering that the data in a GTT is not retained when the program exists.
• You can set the number of instances to a maximum of 9999 when the Application Engine program uses only GTTs in temporary tables.

Note: If the Application Engine program uses GTTs and temporary tables then a warning is displayed on the number of instances exceeding 99.

Note: %UPDATESTATS meta-SQL does not support Application Engine programs that use Oracle GTTs as temporary tables. On using this meta-SQL with GTT, no update statistics is generated and no updates to the system catalog tables happen.

Specifying Global Temporary Tables

指定全局临时表

You can define a GTT in Application Designer for a new or an existing record whose Record Type is selected as Temporary Table.

可以在应用程序设计器中为新记录或其记录类型选择为“临时表”的现有记录定义GTT。

This example illustrates the Global Temporary Table (GTT) check box.

此示例演示了全局临时表（GTT）复选框。

To define a new GTT record:

要定义新的GTT记录，请执行以下操作:

1. Select File, New to open the New Definition dialog box in Application Designer.

选择“文件，新建”以打开“应用程序设计器”中的“新建定义”对话框。

1. Select Record and click OK.

选择Record并单击确定。

1. Insert some fields in the Record Fields tab.

在“记录字段”选项卡中插入一些字段。

1. Select the Record Type tab.

选择“记录类型”选项卡。

1. Select the Temporary Table that displays the Global Temporary Table (GTT) check box.

选中显示全局临时表（GTT）的临时表复选框。

1. Select the Global Temporary Table (GTT) check box.

选中“全局临时表（GTT）”复选框。

Likewise, if you want to set GTTs for an existing Temporary Table record, open the record in Application Designer and select the Global Temporary Table (GTT) check box.

同样，如果要为现有的临时表记录设置GTT，请在应用程序设计器中打开该记录并选中“全局临时表（GTT）”复选框。

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.
  • 构建表实例。

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.

要并行运行进程，需要启用同一临时表的多个实例。使用“PeopleTools选项”页可以设置从PeopleCode CallAppEngine函数联机启动的应用程序引擎进程的临时表实例数。

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.

将PROCESS_INSTANCE字段作为键插入到要与Application Engine一起使用的任何临时表中。应用程序引擎要求临时表记录包含PROCESS_INSTANCE字段。

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.

注意：当一个临时表的所有实例都在使用并且选择了Program Properties对话框中的Continue Run time选项卡时，PeopleTools使用-PROCESS_INSTANCE作为键将行插入到基表中。如果您不将PROCESS_INSTANCE作为关键字段包含在临时表中，请选择中止临时表选项卡运行时选项。

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.

并行运行批处理过程时，存在临时表上的数据争用和死锁的风险。为了避免这种情况，Application Engine提供了一个功能，使您能够为每个进程专用特定的临时表实例。当应用程序引擎管理专用临时表实例时，它控制表在使用前的锁定和表在使用后的解锁。

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.

在决定某个进程的临时表的实例数量时，必须考虑该进程使用的临时表的数量。实例越多，系统中临时表的副本就越多。例如，如果一个进程使用25个临时表，而您的一个进程有10个实例，那么您的系统中将有250个临时表。

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.

数据库中名为PS_AEONLINEINST的物理表存储联机临时表实例使用情况。如果发现与联机应用程序引擎程序运行有关的性能问题，请启用应用程序引擎SQL和计时跟踪。

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 ...

如果下面的SQL命令需要比正常情况下更多的时间才能完成，则“PeopleTools Options”页上定义的联机临时实例的数量可能不足。

Defining Temporary Tables

定义临时表

To define a temporary table:

定义一个临时表：

1. In Application Designer, select File, New.

在应用程序设计器中，选择“文件”、“新建”。

1. Select Record from the New Definition dialog box.

从“新建定义”对话框中选择“记录”。

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

选择Insert,Field，并插入PROCESS_INSTANCE字段。

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

选择“记录类型”选项卡，选择“临时表”选项。

Setting the Number of Temporary Table Instances

设置临时表实例数

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

选择PeopleTools>实用程序>管理>PeopleTools选项来访问PeopleTools选项页面。

This example illustrates the fields and controls on the PeopleTools Options page.

此示例说明了PeopleTools选项页上的字段和控件。

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	Description
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.
Field or Control	Description
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.

Building Table Instances

生成表实例

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.

系统在为记录定义构建基表的同时构建临时表实例。当系统构建一个表（如Build，Current Object）并且记录类型为Temporary Table时，它根据在“PeopleTools选项”页上所做的设置以及应用程序设计器的“应用程序引擎程序属性”中的“临时表”选项卡中的“即时计数”值确定临时表的实例总数。

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.

当实例计数降低并重建临时表时，将删除并重建临时表实例。例如，如果PeopleTools选项设置为3且实例计数为10那么系统目录中将有14个表。如果实例计数降低到5，并且重新生成表，则系统目录中仍将保留14个表。最后5个表TAO10、TAO11、TAO12、TAO13、TAO14需要手动删除，以匹配应用程序引擎使用的表。

The system creates a maximum of 99 temporary table instances, even if the sum exceeds 99 for a particular temporary table.

系统最多创建99个临时表实例，即使某个特定临时表的实例总数超过99。

The naming convention for temporary table instances is: BaseTableName_Number, where Number is a number between 1 and 99, as in PS_TEST_TMP23.

临时表实例的命名约定是：BaseTableName_Number，其中Number是1到99之间的数字，如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.

注意：您可以利用特定于数据库的特性，如表空间和分段。例如，您可能希望使用Build流程生成一个数据定义语言（DDL）脚本，然后在脚本执行之前对其进行微调，或者您可以根据实例编号在不同的表空间上放置不同的临时表集。

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.
• 调整元 SQL。

Understanding 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 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.

无论“应用程序引擎”程序属性或“PeopleTools选项”页中的实例计数值如何，请确保已将相应的记录分配给相应的程序。您还需要确保Application Engine程序中的SQL包含%Table构造的正确用法。

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.

在SQL生成过程中为特定临时表记录生成的临时表实例数是“PeopleTools选项”页中的临时表实例总数的值加上在“临时表”选项卡上为使用该临时表的应用程序引擎程序指定的所有实例计数值的总和。

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.

例如，假设您将APPLTMPA定义为一个临时记录类型，总临时表实例的数量设置为10，APPLTMPA出现在两个应用程序引擎程序的Program-Properties对话框的Temp Tables选项卡中。在一个程序中，实例计数被设置为3，并且在另一个实例计数设置为2。运行SQL Build流程时，PeopleTools为APPLTMPA总共构建了15个临时表实例。

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).

总实例数和联机实例数应该相等，除非PeopleSoft应用程序文档提供了不同设置这些值的具体说明。当两个值相等时，“Temp Table Instances（Total）”字段控制由应用程序设计器为临时表记录定义创建的联机程序使用的物理临时表实例数。如果Temp表实例（联机）字段的值小于Temp表实例的值（合计）字段这两个数字之间的差异为利用%Table的开发人员提供了向后兼容性的表池（record_name，instance_number）手动管理临时表锁定的方法（例如在PeopleSoft EPM中）。

Assigning Temporary Tables to Programs

将临时表分配给程序

Open an Application Engine program in Application Designer. Select File, Definition Properties and then select the 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 Table clears the temporary table by performing delete during Application Engine process.

使用Delete for Truncate Table在应用程序引擎过程中通过执行删除来清除临时表。

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.

在线流程有自己的一组专用临时表，在PeopleTools Options页面上全局定义。当您在线调用一个进程时，PeopleTools会随机地为程序分配一个临时表实例号，以满足其所有专用的临时表需求。程序运行时，没有其他程序可以使用该实例编号。碰巧获得相同实例值的任何其他联机进程都将等待第一个程序完成，以便解锁实例号。

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.

相比之下，批处理进程是在逐个记录的基础上分配临时表实例的。系统从每个临时表可用的最小实例号开始，直到所有临时表实例都在使用中。如果没有可用的临时表，并且您为If non-shared Tables cannot be assigned group框选择了Continue，那么将使用基表，并将流程实例号作为键。

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

当程序正常结束或使用Process Monitor取消时，系统会自动释放分配的实例。

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.
Condition	Online	Batch
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.

专用临时表不会跨流程实例保持锁定。如果连续的应用程序引擎程序需要通过临时表共享数据，则父应用程序引擎程序应该调用共享数据的程序。

Adjusting Meta-SQL

调整元SQL

A critical step in implementing parallel processing is to ensure that you have included appropriate metaSQL within the code that your Application Engine program runs.

实现并行处理的一个关键步骤是确保在应用程序引擎程序运行的代码中包含适当的元SQL。

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.

您可以使用%Table引用任何表，但只有那些定义为临时表的记录才会被Application Engine替换为专用实例表。在开发利用%Table的程序时，请小心选择临时表索引。根据使用的临时表和数据配置文件中，系统索引可能就足够了。另一方面，可能需要一个自定义索引，或者根本不需要索引。在设计应用程序时考虑这些问题。你想要定义在大多数情况下执行良好的索引和SQL，但是个别程序或环境在实现过程中可能需要额外的性能调优。

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

附注：默认的表名引用PS_recname，其中PS_recname 1,2，...表示专用的临时表。

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.

当应用程序引擎解析任何%Table时，它会检查内部数组，以查看是否已为当前记录选择了临时表实例。如果是，则应用程序引擎替换所选的表名。如果没有，例如当一条记录没有出现在程序的临时表列表中时，则Application Engine默认使用基表实例（PS_recname）。无论%Table是在PeopleCode SQL中还是在应用程序引擎SQL操作中，程序都使用相同的物理SQL表。

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) metaSQL to return the numeric (unquoted) process instance.

所有临时表都应该由流程实例键入。如果在无法分配批处理或专用表时使用“继续”选项，则需要将流程实例作为关键字段。当前流程实例会自动放入状态记录中，但是在向临时表中插入行时，必须提供该流程实例。使用%ProcessInstance或%Bind（PROCESS_INSTANCE）Meta-SQL返回数字（无引号）进程实例。

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.

对于使用CallAppEngine函数启动的程序，流程实例值始终为零因为用CallAppEngine调用的程序在进程中运行。也就是说，它运行在与它相关联的组件相同的工作单元中。

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.

如果您正在使用专用表，并且在无法分配专用表时选择继续使用，那么对专用临时表的SQL引用必须在Where子句中包含PROCESS_INSTANCE。

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.

不需要手动删除临时表中的数据。当临时表分配给程序时，它们会被自动截断。如果由于没有专用实例而分配了共享基表，则应用程序引擎将按流程实例执行删除操作，而不是执行截断操作。在这种情况下，需要PROCESS_INSTANCE作为高级键。

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.

您可以在运行期间执行临时表结果的其他删除操作，但是必须包含您自己的使用%TruncateTable函数的SQL操作。如果由于没有可用的专用实例而分配了共享基表，那么%TruncateTable将被替换为delete by process实例，而不是truncate。

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

注意：您应该始终使用%TruncateTable对专用临时表执行批量删除，特别是在Continue选项生效的情况下。

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.

即使您选择在无法分配专用表时终止程序，您仍然可以使用%TruncateTable meta-SQL和专用临时表。%TruncateTable根据需要解析为Truncate或Delete（按流程实例）。

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:

Truncat Table的参数是表名而不是记录名。因此，必须编写SQL代码，如本例所示：

%TruncateTable(%Table(recname))

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

注意：应该避免在%TruncateTable中硬编码表名，因为它们排除了并发处理的可能性。

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.

使用Delete for Truncate Table在应用程序引擎分配过程中删除临时表。默认情况下，此功能不会在分配期间删除临时表。如果选中“使用删除截断表”选项，则在所有情况下都将执行删除操作。

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.

在应用程序设计器中打开应用程序引擎程序。选择文件，定义属性，然后选择临时表选项卡。选择Use Delete for Truncate Table以提高应用程序引擎进程性能。

Related Links

“Specifying Trace Settings” (System and Server Administration)

相关链接"指定跟踪设置"（系统和服务器管理）

Making 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, you should issue a commit just before the Call Section action.

PeopleTools使您能够立即提交，以便应用程序引擎可以提交它为锁定临时表实例而执行的更新。否则，没有其他并行进程可以执行任何分配。通常，您应该在Call Section操作之前发出一个提交。

While making external program calls, note that:

在进行外部程序调用时，请注意：

• If the situation is suitable for a commit, then the temporary table assignment and the appropriate truncates occur.
• 如果这种情况适合于提交，则会出现临时表分配和适当的截断。
• If the situation is not suitable for a commit and the called program is set to continue if dedicated tables cannot be allocated, then the base tables are used instead and a delete by process instance is performed.
• ·如果情况不适合提交，并且如果不能分配专用表，则被调用的程序被设置为继续，则使用基表，并执行按流程实例删除。
• If the situation is not suitable for a commit and the called program is set to terminate if dedicated tables cannot be allocated, then the program run terminates.
• 如果情况不适合提交，并且被调用的程序被设置为如果不能分配专用表则终止，那么程序运行终止。

This situation reflects an implementation flaw that you need to correct.

这种情况反映了您需要纠正的实现缺陷。

• The called Application Engine program is allowed to share temporary tables with the calling program.
• ·被调用的应用程序引擎程序允许与调用程序共享临时表。

Common temporary tables are the way you share data between the calling and called programs. Application Engine locks only instances of temporary tables that have not already been used during the current program run. Temporary tables that already have an assigned instance continue to use that instance.

常用的临时表是在调用程序和被调用程序之间共享数据的方式。应用程序引擎仅锁定当前程序运行期间尚未使用的临时表的实例。已经分配了实例的临时表将继续使用该实例。

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 to ensure that the tables are 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 a run 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 statement is true even if the root program does not use temporary tables. Having at least one temporary table specified is required so that the system locks the instance number early so as to avoid an instance assignment failure after the process has already started processing.

如果将联机程序运行设计为在CallAppEngine工作单元期间的任何时刻使用任何临时表，则根程序必须至少具有一个在应用程序引擎程序属性中指定的临时表。即使根程序不使用临时表，这个语句也是正确的。必须至少指定一个临时表，这样系统才能尽早锁定实例号，从而避免在进程已经开始处理之后实例分配失败。

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 used.

特定程序、程序库或外部节使用的所有临时表都必须在该程序中指定，以确保系统对正在使用的表发出截断（删除）操作。

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

如果根程序属性中没有出现任何临时表，并且应用程序引擎遇到对临时表记录的%Table引用，则会出现错误。

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:

假设您有程序A和程序B以及三个临时表定义：PS_TMPA、PS_TMPB和PS_TMPC。每个节目的“节目属性”对话框中的“临时表”选项卡上的值为：

• Program A: PS_TMPA and PS_TMPB are specified as the dedicated temporary tables, and the instance count is 4.
• ·程序A：PS_TMPA和PS_TMPB被指定为专用的临时表，实例计数为4。
• Program B: PS_TMPB and PS_TMPC are specified as the dedicated temporary tables, and the instance count is 3.
• ·程序B：PS_TMPB和PS_TMPC被指定为专用临时表，实例计数为3。

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

在应用程序设计器中运行SQL生成进程后，数据库中将出现以下临时表清单。

For PS_TMPA:

• PS_TMPA1
• PS_TMPA2
• PS_TMPA3
• PS_TMPA4

For PS_TMPB:

• PS_TMPB1
• PS_TMPB2
• PS_TMPB3
• PS_TMPB4
• PS_TMPB5
• PS_TMPB6
• PS_TMPB7

For PS_TMPC:

• PS_TMPC1
• PS_TMPC2
• PS_TMPC3

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.

因为程序A的实例计数是4，所以系统构建PS_TMPA和PS_TMPB的四个实例供程序A使用。因为程序B的实例计数为3，所以系统会生成另外三个PS_TMPB实例和三个PS_TMPC实例供程序B使用。

Notice that because Program A and Program B are sharing PS_TMPB, seven instances were built. 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.

注意，因为程序A和程序B共享PS TMPB，所以生成了7个实例。系统通过将共享特定临时表实例的所有程序的实例计数值相加来得出这个总数。在本例中，来自程序A的四个实例和来自程序B的三个实例组合在一起，总共需要构建七个PS TMPB实例。

Given that this collection of temporary tables exists in your database, assume 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.

在运行时，应用程序引擎检查专用于程序A的临时表列表，并将第一个可用实例分配给程序A。然后，假设没有其他程序正在运行，应用程序引擎将PS_TMP 1和PS_TMP 1分配给程序A。

Suppose that shortly after you started Program A, another user starts 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.

假设在您启动程序A后不久，另一个用户启动了程序B。同样，应用程序引擎检查专用于程序B的临时表列表，并分配第一个可用的实例。在此场景中，应用程序引擎将PS_TMP 2和PS_TMP 1分配给程序B。因为程序A已经在使用PS_TMPB1，所以系统将PS_TMPB2分配给程序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, then the system uses the base table instead of the dedicated instance.

系统将记录（如TMPA）分配给程序。默认情况下，除了专用临时实例之外，还构建了基本表，如PS_TMPA。如果在没有实例可用时，将Temp Tables选项卡的Program Properties对话框设置设置为Continue，那么系统将使用基表而不是专用实例。

Viewing Temporary Table Usage

查看临时表使用情况

This section discusses how to:

本节讨论如何：

• View temporary table usage by record.
• 按记录查看临时表的使用情况。
• View temporary table settings by program.
• 按程序查看临时表设置。
• View online instance usage.
• 查看在线实例使用情况。
• Resolve the temporary table usage warning message.
• 解决临时表使用警告消息。

Viewing Temporary Table Usage by Record

按记录查看临时表使用情况

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

选择PeopleTools>应用程序引擎>检查AE临时表使用情况以访问“按记录使用临时表”页面。

This example illustrates the fields and controls on the Temp Table Usage by Record page. You can find definitions for the fields and controls later on this 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.

Field or Control	Description
Program Use Count	Shows the instance count of the listed program.
Total Instances	Shows the total number of existing instances of a temporary table.
Locked Instances	Shows the current number of instances that the system has locked for program runs.
Unused Instances	Shows the current number of instances that are available for use.

Viewing 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.

选择PeopleTools>应用程序引擎>查看临时表用法>按程序设置的临时表以访问按程序设置的临时表页面。

This screenshot shows the fields and controls on the Temp Table Settings by Program page.

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

如果应用程序引擎进程是在进程监视器中启动的，则可以选择PeopleTools、ApplicationEngine、Manage AE Abends以访问Manage Abends页面，然后访问进程监视器。

Viewing Online Instance Usage

查看联机实例使用情况

Select PeopleTools > Application Engine > Online AE Instance Locks to access the Online Instance Usage page.

选择PeopleTools>应用程序引擎>联机AE实例锁以访问联机实例使用页面。

This example illustrates the fields and controls on the Online Instance Usage page.

此示例说明“联机实例使用情况”页上的字段和控件。

Resolving 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 is 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:

您可以通过以下两种方式查看警告消息:

• A warning message appears in the standard output of the process.
• 流程的标准输出中会出现一条警告消息。

When running from the command prompt, the message appears in that window. When the program is running on a server through PeopleSoft Process Scheduler, the output is sent to the standard status file, which you can access using Process Monitor.

从命令提示符运行时，该消息将显示在该窗口中。当程序通过PeopleSoft Process Scheduler在服务器上运行时，输出被发送到标准状态文件，您可以使用Process Monitor访问该文件。

• A warning message appears in the AET trace file if a dedicated temporary table instance cannot be locked because none is available.
• 如果因为没有可用的临时表实例而无法锁定专用的临时表实例，则AET跟踪文件中会出现一条警告消息

This message appears in the trace file regardless of the trace settings you selected.

无论您选择的跟踪设置如何，此消息都将显示在跟踪文件中。

If you see the warning about base temporary table usage, then an insufficient number of temporary table instances are defined or some locked instances 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 you must release the locked temporary tables. 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 and you must use the Manage Abends page to release temporary tables used by processes invoked outside of PeopleSoft Process Scheduler.

当一个可重新启动的进程异常终止时，临时表将保持锁定状态，以便能够顺利重新启动。但是，如果不想重新启动进程，则必须释放锁定的临时表。使用Process Monitor取消该进程时，将自动释放锁定的临时表。如果进程不是通过PeopleSoft Process Scheduler启动的，则Process Monitor不会跟踪该进程，您必须使用“管理异常终止”页来释放在PeopleSoft Process Scheduler外部调用的进程所使用的临时表。