Truncate Target Table

It is very interesting to know what happens when the integration service issues a Truncate command on the target. We assume that it truncates the target table. Well but that's not the actual case.  There are some scenarios where truncate option is not considered by the IS and issues a Delete instead of Truncate command.

Read below article to know more .

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:

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 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 thesession properties, the Work flow 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.

Hope you enjoyed it.

Please share your valuable comments.