SQL Queries - CAST Central Base - How to check central consistency on the basis of idkey generator

Purpose of Query

The queries in this page run on a central database will provide information on the idkey_generator sequence which is used to assign object ids.

Prior problems in this area caused behavior like the following during a snapshot:
Consolidate this snapshot…
An error occurred during consolidation.
An error occured during local site processing

Applicable CAST Version

Applicable RDBMS

Query for CSS

The following query will show the last_value for the idkey_generator sequence:

SELECT last_value
FROM   idkey_generator;

Query result example

11501

Query result interpretation

The last value used by the sequence idkey_generator

The following query will show the maximum value for object_id in the dss_objects table. The value should be less than the value for the last value used by the idkey_generator sequence obtained above:

SELECT MAX(object_id)
FROM   dss_objects;

Query result example

11456

Query result interpretation

The maximum value used by the dss_objects table

If the last value for the idkey_generator sequence is not less than the maximum value for object_id in the dss_objects table then it can be updated with the following query to be the maximum value found above plus 1:

NOTE: THIS UPDATE SHOULD NOT BE DONE WHEN WORK IS IN PROGRESS ON THE DATABASE

ALTER sequence idkey_generator restart WITH <max value + 1>

Query result example

No results - just indication that above query returned successfully

Query result interpretation

No interpretation

Query for Oracle

The following query will show the next value for the idkey_generator sequence:

SELECT idkey_generator.nextval
FROM   dual;

Query result example

11501

Query result interpretation

The next value used by the sequence idkey_generator

The following query will show the maximum value for object_id in the dss_objects table. The value should be less than the value for the last value used by the idkey_generator sequence obtained above:

SELECT MAX(object_id)
FROM   DSS_OBJECTS

Query result example

11456

Query result interpretation

The maximum value used by the dss_objects table

If the next value for the idkey_generator sequence is not less than the maximum value for object_id in the dss_objects table then it can be updated with the following query to be the maximum value found above plus 1:

NOTE: THIS UPDATE SHOULD NOT BE DONE WHEN WORK IS IN PROGRESS ON THE DATABASE

 
ALTER sequence idkey_generator increment BY <ANY value higher than MAX(Object_id)>;
SELECT idkey_generator.nextval
FROM   dual;

ALTER sequence idkey_generator increment BY 1;
COMMIT;

Query result example

No results - just indication that above query returned successfully

Query result interpretation

No interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages