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

Lookup Transformation

Lookup Transformation

When you configure a Lookup transformation for pushdown optimization, the database performs a lookup on the database lookup table. The database incurs the cost of an extra subquery for each row if you push a Lookup transformation to the database. Enable lookup caching in PowerCenter instead of pushdown optimization to increase performance for mappings with large number of lookups.
The following table shows the pushdown types for each database to which you can push the Lookup transformation:
Database
Pushdown Type
Amazon Redshift
Source-side, Full
Greenplum
Source-side, Full
IBM DB2
Source-side, Target-side, Full
Microsoft SQL Server
Source-side, Full
Netezza
Source-side, Full
Oracle
Source-side, Target-side, Full
PostgreSQL
Source-side, Full
SAP HANA
Source-side, Target-side, Full
Snowflake
Source-side, Full
Sybase ASE
Source-side, Full
Teradata
Source-side, Full
ODBC
Source-side, Full
Use the following rules and guidelines when you configure the Integration Service to push Lookup transformation logic to a database:
  • The database does not use PowerCenter caches when processing transformation logic.
  • The Integration Service processes all transformations after a pipeline branch when multiple Lookup transformations are present in different branches of pipeline, and the branches merge downstream.
  • A session configured for target-side pushdown optimization fails if the session requires datatype conversion.
  • Unlike the Integration Service, a Netezza database may return multiple rows for a single lookup.
  • Configure pushdown optimization with a view if the Lookup transformation contains an SQL override, contains a filter, or is an unconnected Lookup transformation.
  • Pushdown optimization stops at the Lookup transformation when the mapping contains a lookup on a Netezza, Redshift, or Snowflake table and the lookup match policy is set to policy options other than
    Use All Values
    . Pushdown optimization stops for all other databases when the lookup match policy is set to policy options other than
    Use All Values
    or
    Report Error
    .
The Integration Service processes the Lookup transformation if any of the following conditions are true:
  • The transformation is a pipeline lookup.
  • The transformation uses a dynamic cache.
  • The transformation is configured to return the first, last, or any matching value. To use pushdown optimization, you must configure the Lookup transformation to report an error on multiple matches.
  • The transformation requires a view to be created in a database, and the database providing the lookup input is different from the database where the view is created.
  • The transformation is pushed to Microsoft SQL Server, Sybase, or Teradata and is downstream from a Sorter transformation, which is downstream from an Aggregator transformation.
  • The session is configured to mark all source rows as updates and configured for pushdown optimization to Teradata.
  • The session is configured for source-side pushdown optimization and the lookup table and source table are in different relational database management systems.
  • The session is configured for target-side pushdown optimization and the lookup table and target table are in different relational database management systems.
  • The Integration Service tries to push the transformation to a Netezza database target.

0 COMMENTS

We’d like to hear from you!