Meta-SQL %TruncateTable()

This section discusses DB2 LUW and %TruncateTable().

Note: Beginning with PeopleTools 8.52 when installed on DB2 LUW 9.7, whenever it is operationally applicable, PeopleTools translates %TruncateTable() metaSQL to the native TRUNCATE TABLE command introduced in DB2 LUW 9.7. However, if an earlier version of DB2 LUW (less than 9.7) is installed, PeopleTools reverts to the previous method of using a DB2 utility as documented in this section.

Prior to DB2 LUW 9.7, there's no SQL implementation of a Truncate Table command, such as the one found in Oracle. PeopleTools has implemented a DB2 UDB utility to achieve the same effect as the Truncate Table command. This utility is available through the PeopleCode function %TruncateTable().

You might wish to disable this meta-SQL function because of the performance overhead incurred by bufferpool flushing. The negative effect of bufferpool flushing comes when you truncate large tables using the DB2 LUW API. The process can run much longer than a SQL DELETE FROM clause. If you're experiencing this problem, you can convert %TruncateTable into a SQL DELETE FROM clause.

To enable this conversion there is a setting (DbFlags) in PSPRCS.CFG and PSAPPSRV.CFG (or PSADMIN). DbFlags is a bitmap value and if it contains the value of 2, then SQL is used rather than the DB2 UDB API to set the table to zero rows. The default value for DbFlags is zero.

The following is an example:

DbFlags=2 will enable the workaround.

DbFlags=1 doesn't enable the workaround and the Truncate is done similar to the Oracle's Truncate command.

During the execution of the %TruncateTable() meta-SQL, error information is written to a disk file. The location of this disk file varies depending on which platform type is used.

Windows

If you are running %TruncateTable on Windows, then the directory name format is "%TEMP%\PS\DB2Truncate\PS_TruncateLogFile_pid_id.txt” , where pid_id is a variable depending on the process ID.

UNIX

If you are running %TruncateTable on UNIX, then the directory name format is $PS_HOME/log/DB2Truncate/PS_TruncateLogFile_pid_id.txt, where pid_id is a variable depending on the process ID.

In most cases, error files might be created under the following circumstances:

  • %TruncateTable(Table_name), where Table_name doesn't exist.

  • Internal errors in DB2 UDB.