Table of Contents

Search

  1. Preface
  2. Introduction to PowerExchange
  3. DBMOVER Configuration File
  4. Netport Jobs
  5. PowerExchange Message Logs and Destination Overrides
  6. SMF Statistics Logging
  7. PowerExchange Security
  8. Secure Sockets Layer Support
  9. PowerExchange Alternative Network Security
  10. PowerExchange Nonrelational SQL
  11. PowerExchange Globalization
  12. Using the PowerExchange ODBC Drivers
  13. PowerExchange Datatypes and Conversion Matrix
  14. DTL__CAPXTIMESTAMP Time Stamps
  15. PowerExchange Glossary

Truncating a Target Table

Truncating a Target Table

For nonrelational target types, PowerExchange and PowerCenter support clearing target tables before writing to them in a PowerCenter workflow.
You can clear a target table in the following ways:
  • Include SQL to truncate the table in the
    Pre SQL
    session attribute for the target table.
  • Select the
    Truncate target option
    session property for a VSAM target that uses a PWXPC connection.
  • Select the
    Truncate Target Table
    property for a nonrelational target that uses an PowerExchange ODBC connection.
The first two tokens in the SQL can be either TRUNCATE TABLE or DELETE FROM. The final tokens in the SQL define the fully qualified table from which the data map can be determined. For example, you could use one fo the following statements:
TRUNCATE TABLE schema.
map
_
table
DELETE FROM schema.
map
_
table
If you select the
Truncate target option
session property or the
Truncate Target Table
ODBC property, PowerExchange generates a statement, which you cannot modify, in one of these forms.
The system loads the data map for the table and determines the underlying map type.
For map types of ESDS, KSDS, RRDS, SEQ, or TAPE, the file defined in the data map (or overridden at run time) is opened in write mode and closed so that existing contents of the file are cleared. This method of clearing files is useful because otherwise, if no data is processed in a workflow, the output file might contain records from an earlier run that are retained. PowerExchange defers opening of output files until actual data is being written.
The entire file is always cleared. In the case of multiple-record files, any of the table names can be chosen and data for all tables is cleared. Although it is physically possible for an individual table to be cleared from KSDS and RRDS files, you cannot achieve this result by defining TRUNCATE TABLE or DELETE FROM in the
Pre SQL
session property. It might be possible to clear part of a KSDS file using DELETE SQL with a WHERE clause that is run as part of a workflow, but not by defining the statement in the
Pre SQL
session property.
Clearing a target table by using one of these methods is not supported for other data map types.
VSAM data sets must be defined with the REUSE option for table truncation to function correctly.