Workflow Basics Guide

10.4.0
- 10.5.7
- 10.5.6
- 10.5

Back Next

Target Table Truncation

The Integration Service can truncate target tables before running a session. You can choose to truncate tables on a target-by-target basis. If you have more than one target instance, select the truncate target table option for one target instance.

The Integration Service issues a delete or truncate command based on the target database and primary key-foreign key relationships in the session target. To optimize performance, use the truncate table command. The delete from command may impact performance.

The following table describes the commands that the Integration Service issues for each database:

Target Database	Table contains a primary key referenced by a foreign key	Table does not contain a primary key referenced by a foreign key
DB2¹	delete from <table_name>	truncate table <table_name> immediate
Informix	delete from <table_name>	delete from <table_name>
ODBC	delete from <table_name>	delete from <table_name>
Oracle	delete from <table_name> unrecoverable	truncate table <table_name>
Microsoft SQL Server	delete from <table_name>	truncate table <table_name>²
Sybase 11.x	truncate table <table_name>	truncate table <table_name>

If the Integration Service issues a truncate target table command and the target table instance specifies a table name prefix, the Integration Service verifies the database user privileges for the target table by issuing a truncate command. If the database user is not specified as the target owner name or does not have the database privilege to truncate the target table, the Integration Service issues a delete command instead.

If the Integration Service issues a delete command and the database has logging enabled, the database saves all deleted records to the log for rollback. If you do not want to save deleted records for rollback, you can disable logging to improve the speed of the delete.

For all databases, if the Integration Service fails to truncate or delete any selected table because the user lacks the necessary privileges, the session fails.

If you enable truncate target tables with the following sessions, the Integration Service does not truncate target tables:

Incremental aggregation.
When you enable both truncate target tables and incremental aggregation in the session properties, the Workflow Manager issues a warning that you cannot enable truncate target tables and incremental aggregation in the same session.

Test load.
When you enable both truncate target tables and test load, the Integration Service disables the truncate table function, runs a test load session, and writes a message to the session log indicating that the truncate target tables option is turned off for the test load session.

Real-time.
The Integration Service does not truncate target tables when you restart a JMS or WebSphere MQ real-time session that has recovery data.

Rename Saved Search

Table of Contents

Workflow Basics Guide

Workflow Basics Guide

Target Table Truncation

Target Table Truncation