This type of lookup table works with databases and unloads data from them by using SQL query. Database lookup table reads data from the specified database table. The key which serves to search records from this lookup tables is the "where = ? [and ...]" part of the query. Data records unloaded from database can be cached in memory keeping the LRU order (the least recently used items are discarded first). To cache them, you must specify the number of such records (Max cached records). In case no record can be found in database under some key value, this response can be saved if you check the Store negative key response checkbox. Then, lookup table will not search through the database table when the same key value is given again. Remember that Database lookup table allows to work with duplicate records (multiple records with the same key value).

When creating or editing a Database lookup table, you must check the Database lookup radio button and click Next. (See Figure 35.5, Lookup Table Wizard.)

Figure 35.11. Database Lookup Table Wizard

Then, in the Database lookup table wizard, you must give a Name to the selected lookup table, specify some Metadata and DB connection.

Remember that Metadata definition is not required for transformations written in Java. In them, you can simply select the no metadata option. However, with CTL it is indispensable to specify Metadata.

You must also type or edit some SQL query that serves to look up data records from database. When you want to edit the query, you must click the Edit button and, if your database connection is valid and working, you will be presented with the Query wizard, where you can browse the database, generate some query, validate it and view the resulting data. To specify some lookup table key, add a "where = ? [and ...]" part to the end of the query.

Then, you can click OK and then Finish. See Extracting Metadata from a Database for more details about extracting metadata from a database.