%TruncateTable meta-SQL element

Syntax

%TruncateTable(table name)

Description

Use the %TruncateTable construct to invoke a bulk delete command on a table. This construct is functionally identical to a Delete SQL statement with no Where clause, but it is faster on databases that support bulk deletes. If you are familiar with COBOL, this construct is an enhanced version of the COBOL meta-SQL construct with the same name.

Some database vendors have implemented bulk delete commands that decrease the time required to delete all the rows in a table by not logging rollback data in the transaction log. For the databases that support these commands, Application Engine replaces %TruncateTable with Truncate Table SQL. For the other database types, %TruncateTable is replaced with Delete From SQL.

You should commit after the step that immediately precedes the step containing the %TruncateTable statement. In general, you should use this construct early in your Application Engine program as an initialization task. In addition, avoid using this meta-SQL when your Application Engine program is started from the PeopleCode CallAppEngine function.

Unlike the COBOL version, Application Engine determines if a commit is possible before making the substitution. If a commit is possible, Application Engine makes the substitution and then forces a checkpoint and commit after the delete runs successfully.

If a commit is not possible, Application Engine replaces the meta-SQL with a Delete From string. This string ensures restart integrity when your program runs against a database for which an implicit commit is associated with Truncate Table or for which rollback data is not logged.

For databases that either run an implicit commit for %TruncateTable or require a commit before or after this meta-SQL, replace %TruncateTable with an unconditional delete in the following circumstances:

  • A commit is not allowed, as in an Application Engine program called from PeopleCode.

  • The program issues a non-select SQL statement since the last commit occurred. In such a situation, data is likely to have changed.

  • You are deferring commits in a Select/Fetch loop within a restartable program.

Note:

To use a record name as the argument for %TruncateTable (instead of an explicit table name), you must include a %Table meta-SQL function to resolve the unspecified table name. For example, to specify the record PO_WEEK as the argument, use the following statement: %TruncateTable(%Table(PO_WEEK)).

Related Topics