Allowing TRUNCATE on DB2 Tables

How to:

There is a new Prior to Load option called Truncate Table, which allows you to remove all rows from the target table without logging.
Top of page
x
Procedure: How to Set Options for Relational Targets
  1. In the data flow workspace, right-click the data target and select Properties.

    The Target Properties pane opens.

  2. For existing targets, select whether to remove data prior to loading the data target in the Prior to Load Option section. The options are:

    No changes does not delete the records already in a data target.

    Delete all rows from table deletes all rows and creates a database log.

    Truncate table deletes all rows from the table, but does not generate a database log. This is a faster option than Delete all rows from table.

    Note: Truncate table is not supported by DB2 on IBM i.

  3. Select a Load Type. The options are:

    Insert/Update allows you to set a behavior when loading records.

    Insert records from memory speeds the loading of the data target by inserting a block of rows at once. You can set the row interval to commit or write transactions and the number of records to load in a block. This option is only available for relational databases that support it, including DB2 on i V6R1 for CLI, DB2 on z, Informix, MS SQL Server, MySQL, ORACLE, Sybase ASE, Teradata 13, and UDB. This option:

    • Requires clean input data. If any row in the block causes a data source constraint violation, such as not null or unique index, the entire block is rejected.
    • Does not provide row counts (the number of records inserted or rejected) in the detail log or statistics. NA (not available) will appear instead.
    • Does not provide record logging, for example, rejected records cannot be written to a flat file for review.

    Bulk load utility via a disk file and Extend Bulk Load Utility uses database bulk loaders instead of iWay to insert data into a target. DataMigrator automates bulk loading for Hyperstage, Ingres, Informix, Microsoft SQL Server, IBM DB2, Teradata, Nucleus, Oracle, Sybase Adaptive Server Enterprise, and Sybase Adaptive Server IQ. You can set format version, maximum number of errors, first and last row to copy, packet size, and row interval to commit or write transactions.

    Slowly Changing Dimensions enables you to load a data target with column values that change over time. There are two column types. Type I simply overwrites the value. Type II allows you track activity before and after the change. Type II changes are handled either using an activation flag or end date/begin date. When you update a Type II column, you do not actually change its value. Instead, you update the activation flag to inactive, or the end date to today's date by default. You then add a new record with the new value and the activation flag on or the begin date set.

    IUD Processing enables you to load a data target with only the records that have changed. This feature is an optional, add on component to DataMigrator.

    Note: The IUD Processing option is only available for existing relational targets.

    IUD/Slowly Changing Dimension is a combination of the previous two load types. The Insert/Update/Delete indications in the CDC logs are not applied directly to the target. Instead, the SCD Types of the target are used.

WebFOCUS