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

Purpose of Query

This pages will help you to identify ghost technologies and will help you to remove them if it is there.

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 identify the ghost technology.

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?

Run the following query on the central schema

DELETE FROM dss_links dl WHERE dl.previous_object_id IN (SELECT DISTINCT dli1.next_object_id AS TECHNO_ID FROM dss_objects dos JOIN dss_link_info dli1 ON dos.object_type_id IN ( -102, 20000 ) 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_objects dos1 ON dos1.object_id = dli1.next_object_id WHERE doi.object_id IS NULL AND dos1.object_id IS NULL);

DELETE FROM dss_links dl WHERE dl.next_object_id IN (SELECT DISTINCT dli1.next_object_id AS TECHNO_ID FROM dss_objects dos JOIN dss_link_info dli1 ON dos.object_type_id IN ( -102, 20000 ) 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_objects dos1 ON dos1.object_id = dli1.next_object_id WHERE doi.object_id IS NULL AND dos1.object_id IS NULL);

DELETE FROM dss_link_info dli WHERE dli.previous_object_id IN (SELECT DISTINCT dli1.next_object_id AS TECHNO_ID FROM dss_objects dos JOIN dss_link_info dli1 ON dos.object_type_id IN ( -102, 20000 ) 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_objects dos1 ON dos1.object_id = dli1.next_object_id WHERE doi.object_id IS NULL AND dos1.object_id IS NULL);

DELETE FROM dss_link_info dli WHERE dli.next_object_id IN (SELECT DISTINCT dli1.next_object_id AS TECHNO_ID FROM dss_objects dos JOIN dss_link_info dli1 ON dos.object_type_id IN ( -102, 20000 ) 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_objects dos1 ON dos1.object_id = dli1.next_object_id WHERE doi.object_id IS NULL AND dos1.object_id IS NULL);

DELETE FROM dss_metric_results dmr WHERE dmr.object_id IN (SELECT DISTINCT dli1.next_object_id AS TECHNO_ID FROM dss_objects dos JOIN dss_link_info dli1 ON dos.object_type_id IN ( -102, 20000 ) 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_objects dos1 ON dos1.object_id = dli1.next_object_id WHERE doi.object_id IS NULL AND dos1.object_id IS NULL);

2. Consolidate the snapshot again

**Query for Oracle**

Run the query to identify the ghost technology.

```sql
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?

Run the following query on the central schema

2. Consolidate the snapshot again

**Query for SQL Server**

Run the query to identify the ghost technology.

```sql
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?

Run the following query on the central schema

2. Consolidate the snapshot again

**Notes/comments**

**Related Pages**