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

Source Qualifier Transformation with an SQL Override

Source Qualifier Transformation with an SQL Override

Use the following rules and guidelines when you configure pushdown optimization for a session containing a Source Qualifier transformation with an SQL override:
  • The SELECT statement in a custom SQL query must list the port names in the order in which they appear in the transformation. If the ports are not in the correct order, the session can fail or output unexpected results.
  • Configure the session for pushdown optimization with a view.
  • The session fails if the SELECT statement in the SQL override refers to a database sequence.
  • The session fails if the SQL override contains an ORDER BY clause and you push the Source Qualifier transformation logic to an IBM DB2, a Microsoft SQL Server, a Sybase ASE, or a Teradata database.
  • If a Source Qualifier transformation is configured to select distinct values and contains an SQL override, the Integration Service ignores the distinct configuration.
  • If the session contains multiple partitions, specify the SQL override for all partitions.
  • You must disable the creation of temporary views for pushdown optimization to Teradata when the Source Qualifier transformation contains an SQL override. The PowerCenter Integration Service creates derived tables instead of views.
  • Test the SQL override query on the source database before you push it to the database because PowerCenter does not validate the override SQL syntax. The session fails if the SQL syntax is not compatible with the source database.


Updated June 25, 2020