343729
| Question | Answer |
| Lookup SQL Override | Relational-Overrides the default SQL statement to query the lookup table. Specifies the SQL statement you want the Integration Service to use for querying lookup values. Use with the lookup cache enabled. |
| Lookup Table Name | Pipeline,Relational-The name of the table or the source qualifier from which the transformation looks up and caches values. When you create the Lookup transformation, choose a source, target, or source qualifier as the lookup source. You can also import a table, view, or synonym from another database when you create the Lookup transformation. If you enter a lookup SQL override, you do not need to enter the Lookup Table Name. |
| Lookup Source Filter | Relational-Restricts the lookups the Integration Service performs based on the value of data in any port in the Lookup transformation. Use with the lookup cache enabled. |
| Lookup Caching Enabled | Flat File, Pipeline, Relational -Indicates whether the Integration Service caches lookup values during the session. When you enable lookup caching, the Integration Service queries the lookup source once, caches the values, and looks up values in the cache during the session. Caching the lookup values can improve session performance. When you disable caching, each time a row passes into the transformation, the Integration Service issues a select statement to the lookup source for lookup values. Note: The Integration Service always caches the flat file lookups and the pipeline lookups. |
| Lookup Policy on Multiple Match | Flat File, Pipeline, Relational -Determines which rows to return when the Lookup transformation finds multiple rows that match the lookup condition. Select one of the following values: - Report Error. The Integration Service reports an error and does not return a row. - Use First Value. Returns the first row that matches the lookup condition. - Use Last Value. Return the last row that matches the lookup condition. - Use All Values. Return all matching rows. - Use Any Value. The Integration Service returns the first value that matches the lookup condition. It creates an index based on the key ports instead of all Lookup transformation ports. - Report Error. The Integration Service reports an error and does not return a row. If you do not enable the Output Old Value On Update option, the lookup Policy On Multiple Match option is set to Report Error for dynamic lookups. |
| Lookup Condition | Flat File, Pipeline,Relational -Displays the lookup condition you set in the Condition tab. |
| Connection Information | Relational-Specifies the database that contains the lookup table. You can define the database in the mapping, session, or parameter file: - Mapping. Select the connection object. You can also specify the database connection type. Type Relational:before the connection name if it is a relational connection. Type Application:before the connection name if it is an application connection. - Session. Use the $Source or $Target connection variable. If you use one of these variables, the lookup table must reside in the source or the target database. Specify the database connection in the session properties for each variable. - Parameter file. Use the session parameter $DBConnectionName or $AppConnectionName, and define it in the parameter file. By default, the Designer specifies $Source if you choose a source table and $Target if you choose a target table when you create the Lookup transformation. You can override these values in the session properties. The Integration Service fails the session if it cannot determine the type of database connection. |
| Source Type | Flat File, Pipeline, Relational -Indicates that the Lookup transformation reads values from a relational table, flat file, or source qualifier. |
| Tracing Level | Flat File, Pipeline, Relational -Sets the amount of detail included in the session log. |
| Lookup Cache Directory Name | Flat File, Pipeline, Relational -Specifies the directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source. Also saves the persistent lookup cache files when you select the Lookup Persistent option. By default, the Integration Service uses the $PMCacheDir directory configured for the Integration Service. |
| Lookup Cache Persistent | Flat File, Pipeline, Relational -Indicates whether the Integration Service 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 Integration Service creates the files during the session. Use with the lookup cache enabled. |
| Lookup Data Cache Size Lookup Index Cache Size | Flat File, Pipeline, Relational -Default is Auto. Indicates the maximum size the Integration Service allocates to the data cache and the index in memory. You can configure a numeric value, or you can configure the Integration Service to determine the cache size at run time. If you configure the Integration Service to determine the cache size, you can also configure a maximum amount of memory for the Integration Service to allocate to the cache. If the Integration Service cannot allocate the configured amount of memory when initializing the session, it fails the session. When the Integration Service cannot store all the data cache data in memory, it pages to disk. Use with the lookup cache enabled |
| Dynamic Lookup Cache | Flat File, Pipeline, Relational -Indicates to use a dynamic lookup cache. Inserts or updates rows in the lookup cache as it passes rows to the target table. Use with the lookup cache enabled |
| Output Old Value On Update | Flat File, Pipeline, Relational -Use with dynamic caching enabled. When you enable this property, the Integration Service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports. This property is enabled by default. |
| Update Dynamic Cache Condition | Flat File, Pipeline, Relational -An expression that indicates whether to update dynamic cache. Create an expression using lookup ports or input ports. The expression can contain input values or values in the lookup cache. The Integration Service updates the cache when the condition is true and the data exists in the cache. Use with dynamic caching enabled. Default is true. |
| Cache File Name Prefix | Flat File, Pipeline, Relational -Use with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files. The Integration Service uses the file name prefix as the file name for the persistent cache files it saves to disk. Enter the prefix. Do not enter .idx or .dat. You can enter a parameter or variable for the file name prefix. Use any parameter or variable type that you can define in the parameter file. If the named persistent cache files exist, the Integration Service builds the memory cache from the files. If the named persistent cache files do not exist, the Integration Service rebuilds the persistent cache files. |
| Recache From Lookup Source | Use with the lookup cache enabled. When selected, the Integration Service rebuilds the lookup cache from the lookup source 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 the memory before using the cache. |
| Insert Else Update | Flat File,Pipeline,Relational Use with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert. When enabled, the IntegrationService inserts rows in the cache and updates existing rows When disabled, the Integration Service does not update existing rows. |
| Update Else Insert | Flat File,Pipeline,Relational Use with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of update. When enabled, the Integration Service updates existing rows, and inserts a row if it is new. When disabled, the Integration Service does not insert new rows. |
| Datetime Format | Flat File- Click the Open button to select a datetime format. Define the format and the field width. Milliseconds, microseconds, or nanoseconds formats have a field width of 29. If you do not select a datetime format for a port, you can enter any datetime format. Default is MM/DD/YYYY HH24:MI:SS. The Datetime format does not change the size of the port. |
| Thousand Separator | Flat File - If you do not define a thousand separator for a port, the Integration Service uses the properties defined here. You can choose no separator, a comma, or a period. Default is no separator |
| Decimal Separator | Flat File - If you do not define a decimal separator for a particular field in the lookup definition or on the Ports tab, the Integration Service uses the properties defined here. You can choose a comma or a period decimal separator. Default is period |
| Case-Sensitive String Comparison | Flat File, Pipeline-The Integration Service uses case sensitive string comparisons when performing lookups on string columns. For relational lookups, the case sensitive comparison depends on the database support. |
| Null Ordering | Flat File, Pipeline -Determines how the Integration Service orders null values. You can choose to sort null values high or low. By default, the Integration Service sorts null values high. This overrides the Integration Service configuration to treat nulls in comparison operators as high, low, or null. For relational lookups, null ordering depends on the database default value. |
| Sorted Input | Flat File, Pipeline - Indicates whether or not the lookup file data is in sorted order. This increases lookup performance for file lookups. If you enable sorted input, and the condition columns are not grouped, the Integration Service fails the session. If the condition columns are grouped, but not sorted, the Integration Service processes the lookup as if you did not configure sorted input. |
| Lookup Source is Static | Flat File, Pipeline, Relational - The lookup source does not change in a session. |
| Pre-build Lookup Cache | Flat File , Pipeline, Relational-Allows the Integration Service to build the lookup cache before the Lookup transformation receives the data. The Integration Service can build multiple lookup cache files at the same time to improve performance. You can configure this option in the mapping or the session. The Integration Service uses the session-level setting if you configure the Lookup transformation option as Auto. Configure one of the following options: - Auto. The Integration Service uses the value configured in the session. - Always allowed. The Integration Service can build the lookup cache before the Lookup transformation receives the first source row. The Integration Service creates an additional pipeline to build the cache. - Always disallowed. The Integration Service cannot build the lookup cache before the Lookup transformation receives the first row. You must configure the number of pipelines that the Integration Service can build concurrently. Configure the Additional Concurrent Pipelines for Lookup Cache Creation session property. The Integration Service can pre-build lookup cache if this property is greater than zero. |
| Subsecond Precision | Relational -Specifies the subsecond precision for datetime ports. For relational lookups, you can change the precision for databases that have an editable scale for datetime data. You can change subsecond precision for Oracle Timestamp, Informix Datetime, and Teradata Timestamp datatypes. Enter a positive integer value from 0 to 9. Default is 6 microseconds. If you enable pushdown optimization, the database returns the complete datetime value, regardless of the subsecond precision setting. |
Want to create your own Flashcards for free with GoConqr? Learn more.