Modifying Schema Entities › Record Modifications › Changing the DUPLICATES Option on a CALC or SORT Key

Changing the DUPLICATES Option on a CALC or SORT Key

Types of Changes

You can make the following changes to the DUPLICATES option on a record's CALC or sort key:

• Duplicates first to duplicates last
• Duplicates last to duplicates first
• Duplicates not allowed to duplicates first/last
• Duplicates first/last to duplicates not allowed

What components are affected

The schema definition is affected. Depending on the change, the data may also be affected. All subschemas and access modules referencing the record are also affected.

Steps to change the duplicates option

See 33.2, "Modifications to an Unloaded Database" at the beginning of this chapter for the steps to change the duplicates option from:

• Duplicates first/last to duplicates not allowed
• Duplicates not allowed to duplicates first/last

To change the duplicates option from first to last or from last to first:

1. Write a program using a subschema that specifies duplicates first for the CALC or sort key. The program must
   • Modify the CALC or sort key value to a dummy value
   • Modify the CALC or sort key value to its original value
   • Read each record that has duplicate values, using either OBTAIN CALC DUPLICATE or OBTAIN NEXT IN SET to retrieve duplicate records in the current order

   This has the effect of reversing the order of the duplicate records.

2. Modify the schema
3. Modify the record changing the duplicates option
4. Validate the schema
5. Regenerate any affected subschemas
6. Alter affected access modules using the REPLACE ALL option

Considerations

• When you change from duplicates first or last to duplicates not allowed, make sure that no duplicate key values exist in the database.
• When changing from duplicates first to last or last to first, write a conversion program to logically reorder the record occurrences in the database.

  Using the approach described above, the program must execute using a subschema specifying duplicates first. Therefore, it should use a subschema created either before or after the schema has been changed depending on whether the duplicates option is being changed from or to duplicates first.