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. Chunking transformation
  9. Cleanse transformation
  10. Data Masking transformation
  11. Data Services transformation
  12. Deduplicate transformation
  13. Expression transformation
  14. Filter transformation
  15. Hierarchy Builder transformation
  16. Hierarchy Parser transformation
  17. Hierarchy Processor transformation
  18. Input transformation
  19. Java transformation
  20. Java transformation API reference
  21. Joiner transformation
  22. Labeler transformation
  23. Lookup transformation
  24. Machine Learning transformation
  25. Mapplet transformation
  26. Normalizer transformation
  27. Output transformation
  28. Parse transformation
  29. Python transformation
  30. Rank transformation
  31. Router transformation
  32. Rule Specification transformation
  33. Sequence transformation
  34. Sorter transformation
  35. SQL transformation
  36. Structure Parser transformation
  37. Transaction Control transformation
  38. Union transformation
  39. Vector Embedding transformation
  40. Velocity transformation
  41. Verifier transformation
  42. 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!