Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Advanced properties

Advanced properties

Configure advanced properties for the SQL transformation on the
Advanced
tab. The advanced properties vary based on whether the transformation processes a stored procedure or function or a query.

Advanced properties for stored procedures or functions

The following table describes the advanced properties when the transformation processes a stored procedure or function:
Property
Description
Tracing Level
Detail level of error and status messages that
Data Integration
writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Subsecond Precision
Subsecond precision for datetime fields. You can change the precision for databases that have an editable scale for datetime data.
If you enable pushdown optimization, the database returns the complete datetime value, regardless of the subsecond precision setting.
Enter a positive integer value from 0 to 9. Default is 6 microseconds.
Stored Procedure Type
For unconnected transformations, determines when the stored procedure runs.
Select one of the following options:
  • Target Pre Load. Runs before the target is loaded.
  • Target Post Load. Runs after the target is loaded.
  • Normal. Runs on a row-by-row basis.
  • Source Pre Load. Runs before the mapping receives data from the source.
  • Source Post Load. Runs after the mapping receives data from the source.
Call Text
For unconnected transformations with stored procedure type Target Pre/Post Load or Source Pre/Post Load, enter the call text for the stored procedure.
The call text is the stored procedure name followed by the input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses. Do not include the SQL statement EXEC or use the :SP keyword.
Does not apply to Normal stored procedure types.

Advanced properties for queries

The following table describes the advanced properties when the transformation processes a saved or user-entered query:
Property
Description
Behavior
Transformation behavior, either active or passive.
If active, the transformation can generate more than one output row for each input row. If passive, the transformation generates one output row for each input row.
Default is Active.
Continue on SQL Error within Row
Continues processing the remaining SQL statements in a query after an SQL error occurs.
Enable this option to ignore SQL errors in a statement.
Data Integration
continues to run the rest of the SQL statements for the row. The SQL transformation does not generate a row error, but the SQLError field contains the failed SQL statement and error messages.
Disable this option to debug database errors. Otherwise, you might not be able to associate errors with the query statements that caused them.
Default is disabled.
Auto Commit
Enables auto-commit for each database connection.
Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first.
Default is disabled.
Max Output Row Count
The maximum number of rows that the SQL transformation can output from a SELECT query.
To configure unlimited rows, set this property to zero. Default is 600.
Tracing Level
Detail level of error and status messages that
Data Integration
writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Transformation Scope
The method in which
Data Integration
applies the transformation logic to incoming data.
Select one of the following options:
  • Row. Applies the transformation logic to one row of data at a time. Choose Row when the results of the transformation depend on a single row of data.
  • Transaction. Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.
  • All Input. Applies the transformation logic on all incoming data. When you choose All Input,
    Data Integration
    drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.
Default is Row.