Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Access Policy transformation
  6. Aggregator transformation
  7. B2B transformation
  8. Cleanse transformation
  9. Data Masking transformation
  10. Data Services transformation
  11. Deduplicate transformation
  12. Expression transformation
  13. Filter transformation
  14. Hierarchy Builder transformation
  15. Hierarchy Parser transformation
  16. Hierarchy Processor transformation
  17. Input transformation
  18. Java transformation
  19. Java transformation API reference
  20. Joiner transformation
  21. Labeler transformation
  22. Lookup transformation
  23. Machine Learning transformation
  24. Mapplet transformation
  25. Normalizer transformation
  26. Output transformation
  27. Parse transformation
  28. Python transformation
  29. Rank transformation
  30. Router transformation
  31. Rule Specification transformation
  32. Sequence transformation
  33. Sorter transformation
  34. SQL transformation
  35. Structure Parser transformation
  36. Transaction Control transformation
  37. Union transformation
  38. Velocity transformation
  39. Verifier transformation
  40. 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 SQL ELT 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.
When the SQL transformation is part of a mapplet, the stored procedure type is always Normal.
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.
The call text 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.

0 COMMENTS

We’d like to hear from you!