When to Use Composite Keys
We recommend that you create a composite key for data sets that do not have a unique key. Creating a composite key is required for the following data sets:
- Data sets that do not have valid AA values, such as COMPACT, ORDERED, and VIRTUAL data sets
- Data sets that use AA values and for which garbage collection reorganizations are frequently performed.
Caution: If the composite key that you create is not unique, the following can occur:
- If a duplicate record is encountered after you clone the data set, the index creation for the resulting table fails.
- If a duplicate record is encountered while attempting to insert a record during an update, the original record is deleted and replaced with the new, duplicate one.
When you create a composite key, make sure that you enter the value "user_set" into the set_name column. If you do not, one of two things happens, as follows:
- If the set_name value is "aa_set", a column named my_aa, which contains the AA value of the record is automatically included in the table.
- If the set_name value is blank, the program does not create an index, regardless of the values of the item_key column of the various DMS_ITEMS client control table entries.
Once you are familiar with the concepts in this section, and you determine which data sets require composite keys, you must include the SQL statements in the data set mapping customization script for the data set (script.user_layout.primary_tablename).