Lookup trans

Nota:

• does not change the number of rows that pass through the transformation, maintains the Transaction boundary, and maintains the row type.

1. single value

Nota:

• pass through the transformation. Change the transaction boundary. Change the row type.

1. Multiple values returned

1. Cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports.
2. Can return multiple columns from the same row or insert into the dynamic lookup cache
3. no match for the lookup condition,
   1. IS returns the default value for all output ports
4. Pass multiple output values
5. Link lookup/output ports
6. Supports user-defined default values
7. How its processed
   1. Receives input values directly another transformation in the from the pipeline.
   2. each input row, IS queries source or cache based on lookup ports & condition
      1. dynamic cache
         1. IS inserts the row when it does not find the row; updates the row or leaves the row unchanged.

            Nota:

            • can pass rows to a Filter or Router transformation to filter new rows to the target
            1. flag-insert
            2. flag-update
            3. unchanged
      2. static cache
         1. IS returns values from lookup query
      3. uncached
         1. IS returns values from lookup query
   3. IS passes return values from the query to the next transformation

Nota:

• not connected to other transformations in the mapping. An unconnected transformation is called within another transformation, and returns a value to that transformation.

1. Receives input values from the result of a :LKP expression in another transformation
2. one return port (R). Returns one column per row.
3. is no match, IS returns NULL
4. match, IS returns the result to return port
5. passes value to transformation calling :LKP expression
6. one output value
7. Does not support user-defined default values.
8. how its procecessed
   1. static cache
      1. all lookup/output ports in the lookup condition and the lookup/return port
   2. Receives input values from the result of a :LKP expression in another transformation
   3. lookup source
   4. IS returns one value into the return port
   5. passes the return value into the :LKP expression

1. relational

   Nota:

   • connect to the lookup source using ODBC and import the table definition
   1. Override the default SQL statement to add a WHERE clause or to query multiple tables
   2. Sort null data high or low, based on database support
   3. Perform case-sensitive comparisons based on the database support
2. flat file

   Nota:

   • select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file lookup source, the Designer invokes the Flat File Wizard.
   1. sorted input
      1. input needs sorted&grouped
   2. Use indirect files as lookup sources by configuring a file list as the lookup file name
   3. Use sorted input for the lookup.
   4. Sort null data high or low.
   5. Use case-sensitive string comparison with flat file lookups
3. piplines

   Nota:

   • to perform a lookup on an application source that is not a relational table or flat file.  has a source qualifier as source. except on Application Multi-Group Source Qualifier
   1. the lookup source and source qualifier are in a different pipeline

      Nota:

      • The source and source qualifier are in a partial pipeline that contains no target. The Integration Service reads the source data in this pipeline and passes the data to the Lookup transformation to create the cache
   2. You can create multiple partitions in the partial pipeline to improve performance.
   3. improve performance when processing relational or flat file lookup sources, create a pipeline Lookup transformation instead of a relational or flat file Lookup transformation. You can create partitions to process the lookup source and pass it to the Lookup transformation.
   4. connected
   5. unconnected
   6. IS retrieves the lookup source data in this pipeline and passes the data to the lookup cache.
   7. separate target load order group

1. Relational source or target definition in the repository
2. Flat file source or target definition in the repository
3. Table or file that the Integration Service and PowerCenter Client machine can connect to
4. Source qualifier definition in a mapping

1. Cached lookups
   1. indexing the columns in the lookup ORDER BY
2. Uncached lookups
   1. indexing the columns in the lookup condition.