English
  • PowerCenter 10.2 HotFix 2
  • All Products

Table of Contents

Search

  1. Preface
  2. Workflow Manager
  3. Workflows and Worklets
  4. Sessions
  5. Session Configuration Object
  6. Tasks
  7. Sources
  8. Targets
  9. Connection Objects
  10. Validation
  11. Scheduling and Running Workflows
  12. Sending Email
  13. Workflow Monitor
  14. Workflow Monitor Details
  15. Session and Workflow Logs
  16. Session Properties Reference
  17. Workflow Properties Reference

Workflow Basics Guide

Workflow Basics Guide

Back Next

Target Table Truncation

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