How to Prevent Duplicate Sequences After a Drop-and-Recreate

If an alteration to a sequence causes the object to be dropped and recreated, the default start value for the sequence (START WITH) is set to its default. The recreated sequence also does not use the current value for MAXASSIGNEDVAL (which specifies the last possible assigned value for the sequence). These improper settings cause duplicate sequence objects.
carcudb2
If an alteration to a sequence causes the object to be dropped and recreated, the default start value for the sequence (START WITH) is set to its default. The recreated sequence also does not use the current value for MAXASSIGNEDVAL (which specifies the last possible assigned value for the sequence). These improper settings cause duplicate sequence objects.
To resolve this issue, do the following:
  1. Install the RMRSQPE stored procedure.
    RMRSQPE fetches the MAXASSIGNEDVAL or RESTARTWITH values that exist for the sequence
    before
    it is dropped. After the sequence is recreated, the procedure restarts numbering for the sequence object based on the previous MAXASSIGNEDVAL value
  2. Define model utilities to call the procedure.
  3. Perform the alteration again.
Example: Invoke a Stored Procedure to Reset the Start Value for a Recreated Sequence
This describes how a database administrator can use the RMRSQPE stored procedure to restart numbering for an altered sequence that has been dropped and recreated. By using RMRSQPE, the sequence object resumes with the proper value, and you avoid encountering duplicate sequences. The scenario is as follows:
  1. You create an alteration strategy to alter sequence object SEQ1 by changing the schema name. This alteration requires a drop/create for the object, at which point START WITH (the start value for the sequence) is set to its default. This leads to duplicate sequences.
  2. To circumvent the issue, you install RMRSQPE on each subsystem (as needed) and create an CA RC/Migrator model with additional steps that call RMRSQPE.
  3. Now, when the sequence object is recreated after an alteration, RMRSQPE restarts numbering for the sequence object from the previous MAXASSIGNEDVAL. Therefore, the sequence object resumes properly.