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);

Consolidate the snapshot again

Query for Oracle

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);

Consolidate the snapshot again

Query for SQL Server

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);

Consolidate the snapshot again

Notes/comments

Related Pages