Managing High Sequence Numbers

If you receive the following warning: ConnectOCL - 970100: The following sequence(s) is approaching its maximum value : DISCREPANCY_ENTRY_SEQ (or another of the sequences mentioned below), you need to reseed the sequence; see Reseeding Sequences.

In all versions of Oracle Clinical since 4.5.1 the system enforces that the internal identifier for each of the following cannot exceed 9,999,999,999 (that is, 10^10 -1):

• Received DCMs (RDCMs)

• Received DCIs (RDCIs)

• Discrepancies

In addition, Response IDs cannot exceed 1,000,000,000,000,000 (10^15).

This is because in earlier versions, when the internal identifier for these records exceeded 2,147,483,647 the system incorrectly processed the identifiers and batch validation, data extract, replication, and procedure execution operations failed or ran incorrectly.

The OCL_DE_CONFIG Local Codelist includes a value, SEQUENCEBUFFER, which is assigned an initial long value of 1,000,000. At this setting, when a sequence number is within 1,000,000 of 9,999,999,999, the system displays the warning message above when a user attempts to use a relevant subsystem and the system exits the current screen. If you prefer a larger or smaller buffer, you can change the value in the reference codelist.

For more information , see:

• Assessing Sequence Sizes
  
• Reseeding Sequences
  

Assessing Sequence Sizes

Perform this test to proactively determine if your database is nearing the point where this situation may occur. If the result of this test for the number of Received DCMs, Received DCIs, or discrepancies is well below 9,999,999,999—or 1,000,000,000,000,000 for responses—you do not need to reseed the corresponding sequence.

For more information , see:

• Assess the Sequence Numbers of RDCMs, RDCIs, and Discrepancies
  
• Assess the Sequence Number of Responses
  

Assess the Sequence Numbers of RDCMs, RDCIs, and Discrepancies

To determine the next sequence number to be generated for an RDCM, RDCI, or discrepancy in your system:

1. Connect to your database through SQL*Plus as RXC.

2. Issue a command to determine the next sequence value to be assigned:

   • For RDCMs:

     SELECT received_dcm_seq.nextval FROM dual;
       

   • For RDCIs:

     SELECT received_dci_seq.nextval FROM dual;
       

   • For discrepancies:

     SELECT discrepancy_entry_seq.nextval FROM dual;
       

   The system returns a number after each command. If any internal identifier number is approaching 9,999,999,999, you should reseed the sequence; see Reseeding Sequences.

3. Use this query to check if the maximum sequence value is correctly set to 9999999999. (Releases prior to 4.5.1 had no maximum limit.)

   col max_value format 9999999999
   select sequence_name,max_value
   from dba_sequences
   where sequence_name in ('RECEIVED_DCM_SEQ','RECEIVED_DCI_SEQ','DISCREPANCY_ENTRY_SEQ');
    

   Note:

   If the nextval value retrieved in Step 2 is greater than 2,147,483,647 and the maximum has not been set to 9999999999, see My Oracle Support note ID 277278.1.

4. If the query finds that any of the maximum values is set to anything other than 9999999999, run a command to reset it:

   • For RDCMs:

     alter sequence received_dcm_seq maxvalue 9999999999;
     

   • For RDCIs:

     alter sequence received_dci_seq maxvalue 9999999999;
     

   • For discrepancies:

     alter sequence discrepancy_entry_seq maxvalue 9999999999;
     

Assess the Sequence Number of Responses

To determine the next sequence number to be generated for a response in your system:

1. Connect to your database through SQL*Plus as RXC.

2. Issue this command to determine the next sequence value to be assigned:

   SELECT response_seq.nextval FROM dual; 
     

   The system returns a number. If it is approaching 1000000000000000 (10^15), you should reseed the sequence; see Reseeding Sequences.

3. Use this query to check if the maximum sequence value is correctly set to 1000000000000000 (10^15). (Releases prior to 4.5.1 had no maximum limit.)

   col max_value format 9999999999999999
   select sequence_name,max_value
   from dba_sequences
   where sequence_name in ('RESPONSE_SEQ';
    

4. If the query finds that any of the maximum values is set to anything other than 1000000000000000, run a command to reset it:

   alter sequence response_seq maxvalue 1000000000000000 ;

Reseeding Sequences

If any of your sequence numbers are approaching or exceed the maximum value, you should reseed them. The system generates sequence numbers in increments of 100, so unused numbers are available.

When you reseed a sequence, the system prompts you to provide a previously unused start value. After reseeding, the system uses the same increment value to generate new values, ensuring that the new generated values are unique.

To reseed any of these sequences:

1. Stop all Oracle Clinical activity on the database until this procedure completes.

2. Connect to the database through SQL*Plus as RXC.

3. To determine which seed numbers are already in use for the sequence(s) you are reseeding, issue a command:

   • For RDCMs:

     SELECT distinct mod(received_dcm_id,100) 
     FROM received_dcms;
     

   • For RDCIs:

     SELECT distinct mod(received_dci_id,100) 
     FROM received_dcis;
     

   • For discrepancies:

     SELECT distinct mod(discrepancy_entry_id,100) 
     FROM discrepancy_entries;
     

   • For responses:

     SELECT distinct mod(response_id,100) 
     FROM responses;
     

   Note:

   If this is a replicated environment, run this command in all replicated instances.

4. Choose a new starting seed number value between 0 and 99 (inclusive) that is not in the list returned by the above step.

5. Issue a command to drop the sequence(s) you are reseeding:

   • For RDCMs:

     DROP sequence received_dcm_seq ;
     

   • For RDCIs:

     DROP sequence received_dci_seq ;
     

   • For discrepancies:

     DROP sequence discrepancy_entry_seq ;
     

   • For responses:

     DROP sequence response_seq ;
     

6. Issue a command to recreate the sequence(s) with a new starting value. The system prompts you to enter the new starting value (START WITH &SEQ_START_NO).

   • For RDCMs:

     CREATE SEQUENCE received_dcm_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For RDCIs:

     CREATE SEQUENCE received_dci_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For discrepancies:

     CREATE SEQUENCE discrepancy_entry_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For responses:

     CREATE SEQUENCE response_seq 
     INCREMENT BY 2000 
     START WITH &SEQ_START_NO 
     MAXVALUE 1000000000000000 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   Note:

   In the case of responses, the sequence uses 2000 as the increment and Oracle Clinical manipulates this value by adding 100 to it 20 times before going back to sequence.

7. Issue this command to grant access:

   • For RDCMs:

     GRANT SELECT on received_dcm_seq to RXCLIN_MOD;
     

   • For RDCIs:

     GRANT SELECT on received_dci_seq to RXCLIN_MOD;
     

   • For discrepancies:

     GRANT SELECT on discrepancy_entry_seq to RXCLIN_MOD;
     

   • For responses:

     GRANT SELECT on response_seq to RXCLIN_MOD;