SQL Queries - CAST Measure Base - Checking and removing corruptions - Ghost technologies

Purpose of Query

This pages helps you to identify and remove ghost technologies.

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

Run the query to return the snapshot Id and the ghost technology Id

SELECT dli1.next_object_id AS TECHNO_ID, 
       dli1.snapshot_id, 
       dli2.next_object_id 
FROM   dss_objects dos 
       JOIN dss_link_info dli1 
         ON dos.object_type_id = -102 
            AND dli1.link_type_id = 2 
            AND dli1.previous_object_id = dos.object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli1.next_object_id 
                 AND doi.snapshot_id = dli1.snapshot_id 
       LEFT JOIN dss_link_info dli2 
              ON dli2.previous_object_id = dli1.next_object_id 
                 AND dli2.link_type_id = 1 
                 AND dli2.snapshot_id = dli1.snapshot_id 
WHERE  doi.object_id IS NULL 
ORDER  BY dli1.next_object_id

Query result example

1;192994;10003000;

Query result interpretation

The query returns the snapshot Id and the ghost technology Id

How to fix the issue?

Remove the corruption from the central schema corresponding to the corrupted snapshot - SQL Queries - CAST Central Base - Checking and removing corruptions - Ghost technologies
Remove the snapshot from the measure schema
Upload the snapshot from the central schema to the measure schema

Query for Oracle

Run the query to return the snapshot Id and the ghost technology Id

SELECT dli1.next_object_id AS TECHNO_ID, 
       dli1.snapshot_id, 
       dli2.next_object_id 
FROM   dss_objects dos 
       JOIN dss_link_info dli1 
         ON dos.object_type_id = -102 
            AND dli1.link_type_id = 2 
            AND dli1.previous_object_id = dos.object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli1.next_object_id 
                 AND doi.snapshot_id = dli1.snapshot_id 
       LEFT JOIN dss_link_info dli2 
              ON dli2.previous_object_id = dli1.next_object_id 
                 AND dli2.link_type_id = 1 
                 AND dli2.snapshot_id = dli1.snapshot_id 
WHERE  doi.object_id IS NULL 
ORDER  BY dli1.next_object_id

Query result example

1;192994;10003000;

Query result interpretation

The query returns the snapshot Id and the ghost technology Id

How to fix the issue?
1. Remove the corruption from the central schema corresponding to the corrupted snapshot - SQL Queries - CAST Central Base - Checking and removing corruptions - Ghost technologies
2. Remove the snapshot from the measure schema
3. Upload the snapshot from the central schema to the measure schema

Query for SQL Server

Run the query to return the snapshot Id and the ghost technology Id.

SELECT dli1.next_object_id AS TECHNO_ID, 
       dli1.snapshot_id, 
       dli2.next_object_id 
FROM   dss_objects dos 
       JOIN dss_link_info dli1 
         ON dos.object_type_id = -102 
            AND dli1.link_type_id = 2 
            AND dli1.previous_object_id = dos.object_id 
       LEFT JOIN dss_object_info doi 
              ON doi.object_id = dli1.next_object_id 
                 AND doi.snapshot_id = dli1.snapshot_id 
       LEFT JOIN dss_link_info dli2 
              ON dli2.previous_object_id = dli1.next_object_id 
                 AND dli2.link_type_id = 1 
                 AND dli2.snapshot_id = dli1.snapshot_id 
WHERE  doi.object_id IS NULL 
ORDER  BY dli1.next_object_id

Query result example

1;192994;10003000;

Query result interpretation

The query returns the snapshot Id and the ghost technology Id

How to fix the issue?

Remove the corruption from the central schema corresponding to the corrupted snapshot - SQL Queries - CAST Central Base - Checking and removing corruptions - Ghost technologies
Remove the snapshot from the measure schema
Upload the snapshot from the central schema to the measure schema

Notes/comments

Related Pages