Step 5 - Configuring Indexes
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 the following 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, DataSync might not be able to determine the unique indexes, so you 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 transformation 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 is present so Incremental Synchronization can work. A unique key is not required in cases where Incremental Synchronization is not desired. When these transformations are synchronized, a full synchronization will be performed.
Typically, this screen will list the index name, attributes, and columns in the top half and details such as sort order, data type, and length of the highlighted index.
The available actions are:
Create an Index | Clicking this button will be prompt you to select the appropriate columns that make up the index. You also have to set whether the index is unique, and the order of the columns (in case it is a compound key). |
Delete an Index | Clicking this button will delete the selected index from the target table. |
Reset Index List | Clicking this button will reset the indexes so that they appear as they were before entering this screen. |
Deleting an Index
Select the Index you want to delete 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
Clicking the Create Index button will add a new, empty row at the top of the list. Highlight that row and select the column or columns that will make up the index as well as the Primary or Unique attribute in the lower half of the screen. If desired, you can specify a customer name.
Creating a Unique Index for a Transform that has multiple tables
DataSync attempts to determine the possible keys that a transformation might have. To do this, it looks at the source feed that makes up the transform and checks to see whether the column(s) are indexed. If they are, they are possible candidates for target table indexes. It is up to you 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.
For example, if you you have a transformation that is contains the tables "Oders_cisam" and "Customers_cisam", DataSync will display all of the indexes from those tables. The Index mapper screen might look like this:
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. You 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. Follow these steps if you need assistance on determining how to make an unique key:
1. Determine which table is considered the child table in the two table join. The child table is the one that produces 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. The other table will be the Child Table. 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.
2. Start the CONNX Data Dictionary Tool and look at the indexes of the Orders_cisam table.
The highlighted Key #0 is unique, which represents the ORDERID, CUSTOMERID, and PRODUCTID columns. This could be considered a valid unique key.
3. Go back to the Index Mapper screen, highlight the index that contains the ORDERID, CUSTOMERID, and PRODUCTID columns 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
You might not see a potential key that could be used as an unique key when the potential indexes are shown. In this case, you have the following options:
There is the possibility that a column that potentially identifies the unique key is not part of the transformation. Consider the last example - if PRODUCTID was not part of the selected list, the key (ORDERID, CUSTOMERID, PRODUCTID) would not be displayed. Revise the transformation to include all index columns.
If there are no common index columns between the two tables, the user may have to create an unique key by selecting the
Create New Index button. You can use this function to create all types of keys (unique, composite, non-unique). Please see the example on adding an index earlier in this section.
After you're done with the Index Mapping, the main window DataSync window will be displayed. You can check the transformation by running a manual synchronization on it.
