Lookup table - Connect_CDC - connect_cdc_mimix_share - 5 - 5.8

Connect CDC Advanced User Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC Advanced User Guide
Copyright
2023
First publish date
2003
ft:lastEdition
2023-09-27
ft:lastPublication
2023-09-27T18:12:55.651884

The lookup table feature is best suited for static, small tables.

Note: As soon as the lookup expression has been defined, the lookup table is loaded into memory to increase speed. If the lookup table is updated, you will not see the changes until you shut down and restart the kernel.Use a lookup for static tables only. If you know your tables will change or they have a large number of rows, follow the instructions for a join table.

A lookup table functions in the following manner:

  • A target column can have only one lookup table associated with it.  

  • If the lookup table is deleted or the unique key index is dropped, the request is terminated, and an alert is generated.

  • After the initial Copy is completed, only changes in the trigger table can trigger updates, deletes, and inserts. The trigger table is the sending table whose row triggers replication.

  • Lookup tables are available for all database platforms supported in the current version of the model.

  • A lookup table must have a single column that forms a unique key on which to perform a lookup from the trigger table.

Note: Only one input lookup key column name and one output lookup value column name can be specified in the function.
  • A lookup table must be located within the same database and same server as the associated source table.  On those platforms that may have multiple databases on a single server (MS SQL Server, Sybase, and Informix), table lookups may not be performed between databases.

  • Use only the character datatype for the columns you wish to return from lookup tables. To use a lookup table with another datatype, you must first convert the datatype in the expression you write, using one of the built-in conversions such as Numeric to character. Similarly, if you need the value that is returned to be a different datatype, you can convert it, using built-in conversion methods such as Character to numeric decimal, Character to numeric double, or Character to numeric integer.

Note: While you may model lookup tables to function either uni-directionally or bi-directionally in a gate condition, it is not recommended that lookups be used bi-directionally for enhancing existing columns.