Adjusting Meta-SQL

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.