The Index tab shows the indexes that are used on existing target tables, or the indexes that will be created for new target tables. For new target tables there are Index edit functions:
Ability to propagate suggested indexes that it has harvested from the source tables to the target table
Ability to create a new index
Ability to delete an index
For Single Table Transforms, the defaults given might not require any user intervention. For SQL Statement transforms the unique indexes might not be able to be determined and the user will need to add them. The goal of index mapping is to select which indexes will be added to the target table when it is created. If the transform is going to an existing target table, the list of indexes is what is on the target table and cannot be changed. It is essential that a unique key be present in order for Incremental Synchronization to work. A unique key is not required in cases where Incremental Synchronization is not desired. When these transforms are synchronized, a full synchronization will be performed.
The screen may appear as:
The available actions are:
| Create an Index | By doing this, the user will be prompted to select the appropriate columns that make up the index. They will also determine whether it is unique, and the order of the columns (in case it is a compound key). This is caused by selecting the "CREATE INDEX" button. |
| Delete an Index | This will delete the index from the target table. This is caused by selecting the "DELETE INDEX" button. |
| Reset Index List | This will reset the indexes so that they appear as they were before entering this screen. |
Examples:
Deleting an Index
In this example, KEY3 is not needed in the target table so it will be deleted. Select the Index named KEY3 and click the delete button on the right side of the field. The index will be deleted immediately upon clicking the delete button.
Adding an Index
By pressing the Create Index Button, an empty row will appear.
The lower section of the screen will be highlighted. Select a column or columns that will make up the index as well as the Primary or Unique attribute. A customer name can also be specified.
Creating a Unique Index for a Transform that has multiple tables
DataSync attempts to determine the possible keys that a transform might have. To do this, it looks at the source feed that makes up the transform and checks to see whether these column(s) are indexed. If they are, they are possible candidates for target table indexes. It is up to the designer to decide whether the default indexes that are shown are valid for the target feed.
When a transform is composed of more than a single table, all potential keys are extracted from the source table inputs and are listed as defaults. DataSync cannot determine whether the key is unique, because it is a joined table. A unique key for a single table might not be a unique key for the joined transform.
Consider this example:
When the Index Mapper screen is reached, it displays all of the indexes that make up this transformation. It has retrieved this index information by displaying all the indexes for:
Orders_cisam
Customers_cisam
None of the Indexes show a unique key (If one existed, it would show up in the Attributes column for the specified key. Therefore, this transformation CANNOT be incrementally synchronized. The designer might intuitively know that a unique key could be composed of ORDERID, CUSTOMERID, PRODUCTID. In fact, the ORDERS_CISAM table recognizes that combination as a unique key. If the designer needs assistance on determining how to make an unique key, the following steps should be followed:
In the two table join, determine which table is considered the child table. This means when they are joined to each other, which table will produce more than 1 occurrence if there are matches. A good way to determine this is to examine the column(s) that join the tables. Determine which table represents the joined column(s) as a unique key. This is generally the Parent Table ( Customers_cisam in this example). The other table will be the Child Table ( Orders_cisam). In our example, Customers_cisam and Orders_cisam are joined on the CUSTOMERID column. This is a unique index in Customers_cisam but there can be multiple instances of a value in Orders_cisam.
Go to the Windows Start menu and Launch the CONNX Data Dictionary Tool. Look for the Orders_cisam table. When you have found
this table, look at the indexes for this table.
The highlighted Key #0 is unique, which represents the ORDERID, CUSTOMERID, and PRODUCTID. This could be considered a valid unique key.
Go back to the Index Mapper screen, and complete the Index Transformation by clicking the Unique checkbox in the Attributes section. This action will result in the Target Table having a unique key of ORDERID, CUSTOMERID,
and PRODUCTID. The only way to determine whether the key is unique is to run a manual synchronization of this transformation.
If there is a duplicate, an error will result, and the Transformation should be edited to identify a proper unique key.
Creating a Unique Index for a Transform that does not show a potential unique or non-unique key in the default list
When the potential indexes are shown, the user might not see a potential key that could be used as an unique key. The user has two choices:
If the Index is not shown, this could be due to the fact that a column that potentially identifies the unique key was not part of the transformation. Consider the last example. If PRODUCTID was not part of the select list, the key (ORDERID, CUSTOMERID, PRODUCTID) would not be displayed. The designer might have to revise the transformation to include this column.
If the index is not shown, the user may have to create an unique key by selecting the Create New Index button. This button can be used in creating all types of keys (unique, composite, non-unique). Please see the example on
adding an index earlier in this section
After the Index Mapping has been done, the main window will be displayed, and the transformation can be checked by running a manual synchronization on it.