Table of Contents

Search

  1. Preface
  2. Understanding Pipeline Partitioning
  3. Partition Points
  4. Partition Types
  5. Pushdown Optimization
  6. Pushdown Optimization and Transformations
  7. Real-time Processing
  8. Commit Points
  9. Row Error Logging
  10. Workflow Recovery
  11. Stopping and Aborting
  12. Concurrent Workflows
  13. Grid Processing
  14. Load Balancer
  15. Workflow Variables
  16. Parameters and Variables in Sessions
  17. Parameter Files
  18. FastExport
  19. External Loading
  20. FTP
  21. Session Caches
  22. Incremental Aggregation
  23. Session Log Interface
  24. Understanding Buffer Memory
  25. High Precision Data

Advanced Workflow Guide

Advanced Workflow Guide

Update Strategy Transformation

Update Strategy Transformation

The following table shows the pushdown types for each database to which you can push the Update Strategy transformation:
Database
Pushdown Type
Amazon Redshift
Full
Greenplum
Target-side
IBM DB2
Full
Microsoft SQL Server
Full
Netezza
Full
Oracle
Full
PostgreSQL
Source-side, Full
SAP HANA
Source-side, Target-side, Full
Snowflake
Source-side, Full
Sybase ASE
Full
Teradata
Full
Vertica
Full
Microsoft Azure SQL Data Warehouse
Full
Use the following rules and guidelines when you configure the Integration Service to push Update Strategy transformation logic to a database:
  • The generated SQL for an Update Strategy transformation with an update operation can be complex. Run the session with and without pushdown optimization to determine which configuration is faster.
  • If there are multiple operations to the same row, the Integration Service and database can process the operations differently. To ensure that new rows are not deleted or updated when pushed to a database, source rows are processed in the following order: delete transactions, update transactions, and then insert transactions.
  • If the transformation contains more than one insert, update, or delete operation, the Integration Service generates and runs the insert, update, and delete SQL statements serially. The Integration Service runs the three statements even if they are not required. This might decrease performance.
  • The Integration Service ignores rejected rows when using full pushdown optimization. It does not write reject rows to a reject file.
The Integration Service processes the Update Strategy transformation if any of the following conditions are true:
  • If the Integration Service cannot push the update strategy expression to the database. For example, if the expression contains an operator that cannot be pushed to the database, the Integration Service does not push the expression to the database.
  • The transformation uses operations other than the insert operation and the Integration Service cannot push all transformation logic to the database.
  • The update strategy expression returns a value that is not numeric and not Boolean.


Updated August 27, 2020