Specify Column Maps in a Table Map

You can include Column Maps in a Table Map to map the columns in source and destination tables or to compare the columns in Source 1 and Source 2 tables. By default, columns that have the same names and matching attributes are mapped to each other.

Use Column Maps to accomplish the following:

Specify the name of the Column Map in a Table Map Editor in the following ways:

Edit a Column Map

When you specify the name of an existing Column Map in the Column Map or “LOCAL” grid column, you can use the Column Map as it is or you can edit the Column Map. If changes were made to the database tables since the last time the Column Map was used, the specifications may no longer be valid.

To view or edit the Column Map, right-click and select Open Column Map from the shortcut menu to display the Column Map Editor. For complete details on how to create and edit Column Maps, see Column Maps.

Create a New Column Map

Use the Column Map or “LOCAL” grid column to specify a new Local or Named Column Map in a Table Map:

Local
Local Column Maps are saved as part of the Table Map definition and can be used by that Table Map only. Type the word LOCAL in the Column Map or “LOCAL” grid column. Right-click and select Open Column Map from the shortcut menu to open the Column Map Editor.

If you want to use a Local Column Map with other Table Maps, select Save Copy As from the File menu. For complete details about using the Column Map Editor, see Column Maps.

Named
Named Column Maps are saved in the Optim Directory and can be reused with any other Table Map. Type the fully qualified name of a new Column Map in the Column Map or “LOCAL” grid column. Right-click and select Open Column Map from the shortcut menu to open the Column Map Editor. You can edit and save the Column Map.

The Column Map Status changes to Unknown if you specify local or if you specify a Column Map name that does not exist. A message is displayed at the bottom of the editor to indicate that you must define the Column Map before you can save the Table Map.

Select Column Maps from a List

You can select an existing Column Map from a list. To display a list of Column Map names, right-click and select List Column Maps from the shortcut menu.

List Column Maps Dialog

The List Column Maps dialog shows all available Column Maps, whether or not the table names match names in the Table Map Editor. (You can use a Column Map defined for tables with different names as long as one or more column names match.)

list column maps dialog, explained below

Pattern

Use a Pattern to limit the list of Column Maps in the List Column Maps dialog. After you specify a pattern, click Refresh to redisplay the list based on your criteria. See Use a Pattern for more information.

Populate Column Maps in a Table Map

Populate automatically inserts the names of Column Maps into the Column Map or “LOCAL” grid column in the Table Map Editor. You can specify criteria for the Column Map names.

In the Table Map Editor, select Populate... from the Tools menu (or right-click and select Populate... from the shortcut menu) to open the Populate Column Maps dialog. Specify the criteria for selecting Column Maps.

populate column maps dialog, explained below

Matching

A two-part qualifier for the Column Map names. You can use SQL LIKE syntax to specify the qualifier and limit the candidate list of Column Maps. Initially, %.% (the default) to search all Column Maps is displayed.

Merge Type

The method Populate uses to insert Column Map names in the Column Map or “LOCAL” grid cells in the Table Map Editor. Select one of the following:

Replace
Replace any Column Map name, LOCAL, or blank cell with a Column Map name that satisfies the Matching and Match Type criteria. If a Column Map that meets the criteria is not found, no change is made. If several Column Maps that satisfy the criteria exist for a set of tables, a list displays from which you can select one.
Clear
Clear all Column Map or “LOCAL” grid cells and insert a Column Map name in each cell for which a Column Map exists that satisfies the Matching and Match Type criteria. If a Column Map that meets the criteria is not found, the cell remains blank. If several Column Maps satisfy the criteria, you can select from a list.
Add
Insert Column Map names in blank Column Map or “LOCAL” grid cells that satisfy Matching and Match Type criteria. If a Column Map that meets the criteria is not found, the cell remains blank. If several Column Maps satisfy the criteria, you can select from a list.

Match Type

Indicate how Populate matches Column Maps to the Table Map. Select one of the following:

Use Only Full Name Matches
Select this option to display a list of Column Maps for which the fully qualified names of the source and destination tables in the Column Map match those in the Table Map.
Use Table and Full Name Matches
Select this option to display a list of Column Maps where:
  • Fully qualified names of the tables referenced in the Column Map match those in the Table Map, and
  • Fully qualified names of one or both tables referenced in the Column Map have Creator IDs different from those in the Table Map.

Always Fully Qualify Names

Select this check box to ensure that Populate inserts fully qualified names of Column Maps into the Table Map. This option is useful when you do not specify a default Column Map ID in the Table Map Editor or when the default does not contain the appropriate Creator ID.

Note: If you specify several Column Map names that differ only by the Identifier, and the Column Map ID you specified in the Table Map Editor is inappropriate or inaccurate, Populate may generate unexpected results.

Processing Sequence

Populate scans each pair of source and destination tables in the Table Map Editor to generate a list of Column Maps that match your criteria.

  • If a single Column Map satisfies your criteria, Populate replaces, inserts, or adds the Column Map name in the Table Map automatically.
  • If several Column Maps satisfy your criteria, a list displays from which you can select a Column Map or bypass the match.
  • If no matches are found, Populate skips the pair of tables and processes the next pair.

The method for populating the Column Map or “LOCAL” grid column is determined by your Merge Type selection. The extent of the search is determined by your Match Type selection.

  • If you select Only Use Full Name Matches, the search ends when the routine finds all full matches.
  • If you select Use Both Table and Full Name Matches, the routine search includes table matches.

Select One Column Map

When Populate finds several matches for a pair of tables, the Select One Column Map dialog lists the candidate Column Maps. The full matches are listed first, followed by table matches. The names of the two tables that are being processed are displayed in the message box.

select one column map dialog
  • To select a Column Map, click OK.
  • To bypass a map selection for a pair of tables and continue processing with the next pair, click Skip. When no pairs of tables remain, the Select One Column Map dialog closes.

The Column Map or “LOCAL” grid column of the Table Map Editor lists the selected Column Maps and any inserted automatically.

Field name Description
Matching SQL LIKE syntax you specified as the Matching criteria in the Populate Column Maps dialog.
Map ID Qualifier of the Column Map name that matches your criteria.
Map Name Name of the Column Map that matches your criteria.
Source Type of match between the source table in the Column Map and the source table in the Table Map.
Full Match
Names and Creator Ids of the source tables match.
Table Match
Names of the source tables match, but the Creator IDs differ.
Destination Type of match between the destination table in the Column Map and the destination table in the Table Map.
Full Match
Names and Creator IDs of the destination tables match.
Table Match
Names of the destination tables match, but the Creator IDs differ.