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

Views

Views

You must configure the session for pushdown optimization with views to enable the Integration Service to create the view objects in the database.
The Integration Service creates a view object under the following conditions:
  • You configure pushdown optimization for a Source Qualifier or Lookup transformation configured with an SQL override.
  • You configure pushdown optimization for a Lookup transformation configured with a filter.
  • You configure pushdown optimization for an unconnected Lookup transformation.
When the Integration Service pushes a Source Qualifier or Lookup transformation to a database, it creates the view based on the transformation definition. For example, when the Integration Service creates a view based on a Lookup transformation with a filter, it creates the view that contains only the non-filtered rows. When the Integration Service pushes a Lookup transformation with an SQL override to a database, it creates a view based on all the lookup ports, not only the projected lookup ports.
The Integration Service does not parse or validate the SQL overrides. If you configure a session to push the Source Qualifier or Lookup transformation with an SQL override to the database, test the SQL override against the database before you run the session.
If you push Source Qualifier transformation logic to Teradata with temporary views, the data dictionary in Teradata can cause the SQL statements to fail. The SQL statements fail due to the dynamic creation and deletion of views in the environment that uses many pushdown optimization sessions. You can disable the creation of temporary views for pushdown optimization to Teradata when the Source Qualifier transformation contains a source filter, user-defined joins, or an SQL override. The Integration Service creates derived tables instead of views.
If you configure the session for pushdown optimization with views, the Integration Service completes the following tasks:
  1. Creates a view in the database
    . The Integration Service creates a view in the database based on the lookup filter, unconnected lookup, or SQL override in the Source Qualifier or Lookup transformation. To create a unique view name, the Integration Service adds the prefix PM_V to a value generated by a hash function.
  2. Executes an SQL query against the view
    . After the Integration Service creates a view object, it executes an SQL query against the view created in the database to push the transformation logic to the source.
  3. Drops the view from the database.
    When the transaction completes, the Integration Service drops the view it created.


Updated November 14, 2019