SQL Queries - CAST Central Base - Checking and removing corruptions in CB - Ghost objects

Purpose of Query

This page will help you to identify and cleanup ghost objects (objects that are linked to some other objects but that are not present as objects)

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Query for CSS

SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query for CSS

The query returns the Id's of the ghost objects and the snapshots that contains the ghost objects

Remediation :

Run the following on the CB:

DELETE FROM dss_link_info 
WHERE  next_object_id IN (SELECT dli.next_object_id 
                          FROM   dss_link_info dli 
                                 LEFT JOIN dss_objects o 
                                        ON o.object_id = dli.next_object_id 
                                 LEFT JOIN dss_object_info doi 
                                        ON doi.object_id = dli.next_object_id 
                                 LEFT JOIN dss_translation_table dtt 
                                        ON dtt.object_id = dli.next_object_id 
                          WHERE  dli.link_type_id = 2 
                                 AND o.object_id IS NULL 
                                 AND dtt.object_id IS NULL 
                                 AND doi.object_id IS NULL)

If snapshots were consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

Query for Oracle

SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query result interpretation

The query returns the Id's of the ghost objects and the snapshots that contains the ghost objects

Query for CSS

Remediation :

Run the following on the CB:

DELETE FROM dss_link_info 
WHERE  next_object_id IN (SELECT dli.next_object_id 
                          FROM   dss_link_info dli 
                                 LEFT JOIN dss_objects o 
                                        ON o.object_id = dli.next_object_id 
                                 LEFT JOIN dss_object_info doi 
                                        ON doi.object_id = dli.next_object_id 
                                 LEFT JOIN dss_translation_table dtt 
                                        ON dtt.object_id = dli.next_object_id 
                          WHERE  dli.link_type_id = 2 
                                 AND o.object_id IS NULL 
                                 AND dtt.object_id IS NULL 
                                 AND doi.object_id IS NULL)

If snapshots were consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

Query for SQL server

SELECT o.object_id, dli.snapshot_id
FROM   dss_link_info dli 
       LEFT JOIN dss_objects o 
              ON o.object_id = dli.next_object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli.next_object_id 
       LEFT JOIN dss_translation_table dtt 
              ON dtt.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND o.object_id IS NULL 
       AND dtt.object_id IS NULL 
       AND doi.object_id IS NULL

Query result example

53648, 1

Query result interpretation

The query returns the Id's of the ghost objects and the snapshots that contains the ghost objects

Query for CSS

Remediation :

Run the following on the CB:

DELETE FROM dss_link_info 
WHERE  next_object_id IN (SELECT dli.next_object_id 
                          FROM   dss_link_info dli 
                                 LEFT JOIN dss_objects o 
                                        ON o.object_id = dli.next_object_id 
                                 LEFT JOIN dss_object_info doi 
                                        ON doi.object_id = dli.next_object_id 
                                 LEFT JOIN dss_translation_table dtt 
                                        ON dtt.object_id = dli.next_object_id 
                          WHERE  dli.link_type_id = 2 
                                 AND o.object_id IS NULL 
                                 AND dtt.object_id IS NULL 
                                 AND doi.object_id IS NULL)

If snapshots was consolidated in the measure schema then reset the measurement base and perform a re-consolidation of CB

Notes/comments

Related Pages