A Optimization

Optimizing the Mapping • •

Reduce the number of transformations in the mapping and delete unnecessary links between transformations. Limiting the number of connected input/output or output ports reduces the amount of data the transformations store in the data cache.

You can also perform the following tasks to optimize the mapping: • • • • •

Configure single-pass reading. Optimize data type conversions. Eliminate transformation errors. Optimize transformations. Optimize expressions.

Configuring Single-Pass Reading: Single-pass reading allows you to populate multiple targets with one source qualifier. Separate mappings and use only one source qualifier for each source, the Informatica Server then reads each source only once, and then sends the data into separate data flows. For example You have the PURCHASING source table, and you use that source daily to perform an aggregation and a ranking. If you place the Aggregator and Rank transformations in separate mappings and sessions, you force the Informatica Server to read the same source table twice. However, if you join the two mappings, using one source qualifier, the Informatica Server reads PURCHASING only once, and then sends the appropriate data to the two separate data flows.

Optimizing Data type Conversions Forcing the Informatica Server to make unnecessary data type conversions slows performance. For example Convert your zip code data to an Integer data type, the lookup database stores the zip code 943031234 as 943031234. Eliminating Transformation Errors With each transformation error, the Informatica Server pauses to determine the cause of the error and to remove the row causing the error from the data flow. Then the Informatica Server typically writes the row into the session log file.

Optimizing Lookup Transformations • Caching Lookups • Reducing the Number of Cached Rows • Optimizing the Lookup Condition • Indexing the Lookup Table • Optimizing Multiple Lookups Optimizing Filter Transformations • Move the Filter transformation as close to the source qualifier as possible to remove unnecessary data early in the data flow. • Instead of using a Filter transformation halfway through the mapping to remove a sizable amount of data, use a source qualifier filter to remove those same rows at the source. Optimizing Aggregator Transformations We can optimize Aggregator transformations by performing the following tasks: • Group by simple columns. • Use sorted input. • Use incremental aggregation. Session Tuning We can optimize session performance as follows: • Dropping Indexes And Key constraints. • Increasing Checkpoint Intervals. • Bulk Loading On Sybase, Oracle, And Microsoft SQL Server. • External Loading On DB2, Teradata, Oracle, and Sybase IQ. • Increasing Database Network Packet Size. • Optimizing Oracle Target Databases. Joiner You can use the following sources: Two relational tables existing in separate databases Two flat files in potentially different file systems Two different ODBC sources Two instances of the same XML source A relational table and a flat file source A relational table and an XML source If two relational sources contain keys, then a Source Qualifier transformation can easily join the sources on those keys. Joiner Transformations typically combine information from two different sources that do not have matching keys, such as flat file sources. Configure the following settings in each Joiner transformation: Master and detail source

Type of join Condition of the join The Joiner transformation supports the following join types, which you set in the Properties tab: Normal (Default) Master Outer Detail Outer Full Outer The result of this join depends on the type of join. With a normal join, the result set of the transformation discards any row of data From the master source that does not match a row of data from the detail source based on the condition. Any rows that has matching Item numbers in the two sources appear in the result set. Normal Join With a normal join, the Informatica Server discards all rows of data from the master and detail source that do not match, based on the Condition. Master Outer Join A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the Unmatched rows from the master source. Detail Outer Join A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the Unmatched rows from the detail source. Full Outer Join A full outer join keeps all rows of data from both the master and detail sources.

Table 7-3. Lookup Transformation Properties Option Lookup SQL Override

Description Overrides the default SQL statement to query the lookup table. Specifies the SQL statement you want the Informatica Server to use for querying lookup values. Use only with the lookup cache enabled. Enter only the SELECT, FROM, and WHERE clauses when you enter the SQL override.

Do not enter an ORDER BY clause unless you follow the tip found in Lookup Transformation Tips. The Informatica Server always generates an ORDER BY clause, even if you enter one in the override.

Lookup Table Name

Specifies the name of the table from which the transformation looks up and caches values. You can import a table, view, or synonym from another database by selecting the Import button on the dialog box that displays when you first create a Lookup transformation. If you enter a lookup SQL override, you do not need to add an entry for this option.

Indicates whether the Informatica Server caches lookup values during the session. When you enable lookup caching, the Informatica Server queries the lookup table Lookup Caching once, caches the values, and looks up values in the cache during the session. This Enabled can improve session performance. When you disable caching, each time a row passes into the transformation, the Informatica Server issues a select statement to the lookup table for lookup values.

Lookup Policy on Multiple Match

Available for Lookup transformations that are uncached or use a static cache. Determines what happens when the Lookup transformation finds multiple rows that match the lookup condition. You can select the first or last row returned from the cache or lookup table, or report an error. The Informatica Server fails a session when it encounters a multiple match while processing a Lookup transformation with a dynamic cache.

Lookup Condition

Displays the lookup condition you set in the Condition tab.

Location Information

Specifies the database containing the lookup table. You can select the exact database connection or you can use the $Source or $Target variable. If you use one of these variables, the lookup table must reside in the source or target database you specify when you configure the session. If you select the exact database connection, you can also specify what type of database connection it is. Type Application: before the connection name if it is an Application connection. Type Relational: before the connection name if it is a relational connection. If you do not specify the type of database connection, the Informatica Server fails the session if it cannot determine the type of database connection. For more information on using $Source and $Target, see Using $Source and $Target Variables.

Source Type

Indicates that the Lookup transformation reads values from a relational database.

The Recache from Database option replaces the Recache if Stale and Lookup Cache Recache if Stale Initialize options. For more information about Recache if Stale, see “Upgrading a Repository” in the Installation and Configuration Guide. Tracing Level

Sets the amount of detail included in the session log when you run a workflow containing this transformation.

Lookup Cache Specifies the directory used to build the lookup cache files when you configure the Directory Name Lookup transformation to cache the lookup table. Also used to save the persistent

lookup cache files when you select the Lookup Persistent option. By default, the Informatica Server uses the $PMCacheDir directory configured for the Informatica Server. Lookup Cache Initialize

The Recache from Database option replaces the Lookup Cache Initialize and Recache if Stale options. For more information about Lookup Cache Initialize, see “Upgrading a Repository” in the Installation and Configuration Guide.

Lookup Cache Persistent

Indicates whether the Informatica Server uses a persistent lookup cache, which consists of at least two cache files. If a Lookup transformation is configured for a persistent lookup cache and persistent lookup cache files do not exist, the Informatica Server creates the files during the session. You can use this only when you enable lookup caching.

Lookup Data Cache Size

Indicates the maximum size the Informatica Server allocates to the data cache in memory. If the Informatica Server cannot allocate the configured amount of memory when initializing the session, it fails the session. When the Informatica Server cannot store all the data cache data in memory, it pages to disk as necessary. The Lookup Data Cache Size is 2,000,000 bytes by default. The minimum size is 1,024 bytes. Use only with the lookup cache enabled.

Lookup Index Cache Size

Indicates the maximum size the Informatica Server allocates to the index cache in memory. If the Informatica Server cannot allocate the configured amount of memory when initializing the session, it fails the session. When the Informatica Server cannot store all the index cache data in memory, it pages to disk as necessary. The Lookup Index Cache Size is 1,000,000 bytes by default. The minimum size is 1,024 bytes. Use only with the lookup cache enabled.

Dynamic Lookup Cache

Indicates to use a dynamic lookup cache. Inserts or updates rows in the lookup cache as it passes rows to the target table. You can use this only when you enable lookup caching.

Cache File Name Prefix

Use only with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files. The Informatica Server uses the file name prefix as the file name for the persistent cache files it saves to disk. Only enter the prefix. Do not enter .idx or .dat. If the named persistent cache files exist, the Informatica Server builds the memory cache from the files. If the named persistent cache files do not exist, the Informatica Server rebuilds the persistent cache files.

Recache From Database

Use only with the lookup cache enabled. When selected, the Informatica Server rebuilds the lookup cache from the lookup table when it first calls the Lookup transformation instance. If you use a persistent lookup cache, it rebuilds the persistent cache files before using the cache. If you do not use a persistent lookup cache, it rebuilds the lookup cache in memory before using the cache. .

Insert Else Update

Use only with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert. When you select this property and the row type entering the Lookup transformation

is insert, the Informatica Server inserts the row into the cache if it is new, and updates the row if it exists. If you do not select this property, the Informatica Server only inserts new rows into the cache when the row type entering the Lookup transformation is insert. For more information on defining the row type, see Using Update Strategy Transformations with a Dynamic Cache.

Update Else Insert

Use only with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of update. When you select this property and the row type entering the Lookup transformation is update, the Informatica Server updates the row in the cache if it exists, and inserts the row if it is new. If you do not select this property, the Informatica Server only updates existing rows in the cache when the row type entering the Lookup transformation is update. For more information on defining the row type, see Using Update Strategy Transformations with a Dynamic