How to: |
The function DB_LOOKUP provides an alternative to a SQL JOIN to lookup a value in one (or more) columns of a table. When matching values are found, the value of another column is returned.
For example, a lookup table may contain a list of state codes and names. If you lookup the state code NY, you get back the state name New York.
This process can be expedited by loading the pairs of values into memory called a cache in order to reduce the number of file or database read operations.
There are two types of cache, depending on the type of the lookup table.
If each input value is only looked up once, there is no advantage to using a cache. But if the same input values are looked up multiple times throughput can be improved because the return value is read from the cache instead of from the database.
For database lookup tables, a system setting controls whether the values in the lookup table are loaded into the cache.
ENGINE INT CACHE {SET {ON [LIMIT nn] | OFF } | STAT}
where:
Enables caching.
Limits the number of rows added from cache to nn.
Disables caching.
Displays statistics for cache.
To enable the lookup cache for a data flow, create a stored procedure with the following line:
ENGINE INT CACHE SET ON
From the Process Flow tab for the flow drag the stored procedure into the workspace. Connect the Start object to the stored procedure and connect that to the data flow.
To see how the cache was used, create another stored procedure with the following line:
ENGINE INT CACHE STAT
Drag it onto the process flow after the Data Flow. When you submit the flow you will see lines, like shown in the following image, confirming that the cache was used.
iWay Software |