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

Created by John McMahon, last modified by Alex Thottunkel on Dec 28, 2023


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


Release
Yes/No
8.3.x  (tick)
8.2.x  (tick)
Applicable RDBMS
RDBMS
Yes/No
CSS2(tick)
CSS3(tick)
CSS4(tick)


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