SQL Queries - CAST Knowledge Base - How to check the status of the idkey generator and repair it

Purpose of Query

The purpose of this query is to check the status of the idkey_generator.  If the idkey_generator is broken, then the query will also generate a statement to repair it.

Applicable CAST Version

Applicable RDBMS

Query for CSS

On the local database run the following query:

WITH MaxIds
(
IdObj
)
AS
( SELECT MAX(IdKey)
FROM Keys

UNION ALL

SELECT MAX(IdAcc)
FROM Acc

UNION ALL

SELECT MAX(IdAcc)
FROM AccSymb

UNION ALL

SELECT MAX(IdKeyPar)
FROM KeyPar

UNION ALL

SELECT MAX(IdFus)
FROM FusAcc

UNION ALL

SELECT MAX(IdSel)
FROM AnaSel
)
,
NextId
(
IdObj
)
AS
( SELECT MAX(IdObj) + 1
FROM MaxIds
)
SELECT IdObj AS NextAvailableId,
CASE
WHEN last_value < IdObj
THEN 'IDKEY_GENERATOR is broken'
ELSE 'IDKEY_GENERATOR is healthy'
END AS SequenceStatus,
CASE
WHEN last_value < IdObj
THEN 'alter sequence IDKEY_GENERATOR restart with '
|| CAST(IdObj AS VARCHAR)
|| ';'
END AS RepairingStatement
FROM NextId
CROSS JOIN IDKEY_GENERATOR;

Query result example

The query returns one of the following:

• The value of the next available id that could be used, the statement ‘IDKEY_GENERATOR is healthy’, and a null value for the repairing statement:
  • 2106678;“IDKEY_GENERATOR is healthy”;""
• The value of the next available id that could be used, the statement ‘IDKEY_GENERATOR is broken’, and a value for the repairing statement:
  • 2106678;“IDKEY_GENERATOR is broken”;“alter sequence IDKEY_GENERATOR restart with 2106678;”

Query result interpretation

When the message is ‘IDKEY_GENERATOR is healthy’ nothing needs to be done.

When the message is ‘IDKEY_GENERATOR is broken’, then:

1. Stop or wait until all CAST processes on the database have completed (analyses, snapshots, dashboards).
2. Rerun the query to make sure that nothing has changed for the repairing statement.
3. Run the repairing statement on the local database.

Query for Oracle

Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages