IBM Support

DataStage ODBC Common Connector uses DELETE statement to truncate target table

Troubleshooting


Problem

When loading data to Oracle using ODBC Connector stage with Truncate Table action, DELETE statement is generated for the truncation.

Symptom

ODBC Connector generates "DELETE FROM TargetTable" statement for Truncate Table action.

A DELETE statement is inefficient when compared to a genuine TRUNCATE statement such as:

  • "TRUNCATE TABLE <table_name>" for Oracle
  • "IMPORT FROM /dev/null OF del REPLACE INTO <table_name>" for DB2.

Cause

This behaviour is by design.

The ODBC Connector is a generic component which is expected to work with various DBMS, so it uses a DELETE statement which works for all DBMS.

Resolving The Problem

Use user-defined Truncate SQL in ODBC Common Connector instead of generated SQL by setting


    Table action = Truncate
    Generate truncate statement at runtime = No
    Truncate statement = <Custom Truncate SQL optimal for your DB>

Or use native DB Connectors like Oracle Common Connector, DB2 Common Connector, etc.

[{"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.1;11.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21445833

Page Feedback