SQL Queries - CAST Central Base - Checking and removing corruptions in CB - List of functional modules shared between applications

This query will get the list of functional modules that are shared between two or more applications

SELECT next_object_id, 
       dso.object_name, 
       Count(previous_object_id) 
FROM   dss_links dl 
       JOIN dss_objects dso 
         ON dso.object_id = dl.next_object_id 
WHERE  link_type_id = 1 
       AND next_object_id IN (SELECT object_id 
                              FROM   dss_objects 
                              WHERE  object_type_id = 20000) 
GROUP  BY next_object_id, 
          dso.object_name 
HAVING Count(previous_object_id) > 1 

Remediation:

If the result of the above query returns rows then there is a corruption, only one application should use the module.

For example If there are 2 applications sharing the module A (say applications X & Y). Pick an application among the two which is smaller and follow the below steps as a workaround to remove the shared module from one of the applications:

1. Add a new module for the smaller application (smaller among X & Y)
2. Delete the already existing snapshot for that application
3. Re-run the analysis & snapshot for the small application (for which you have added new module)

SELECT next_object_id, 
       dso.object_name, 
       Count(previous_object_id) 
FROM   dss_links dl 
       JOIN dss_objects dso 
         ON dso.object_id = dl.next_object_id 
WHERE  link_type_id = 1 
       AND next_object_id IN (SELECT object_id 
                              FROM   dss_objects 
                              WHERE  object_type_id = 20000) 
GROUP  BY next_object_id, 
          dso.object_name 
HAVING Count(previous_object_id) > 1 

Remediation:

If the result of the above query returns rows then there is a corruption, only one application should use the module.

For example If there are 2 applications sharing the module A (say applications X & Y). Pick an application among the two which is smaller and follow the below steps as a workaround to remove the shared module from one of the applications:

1. Add a new module for the smaller application (smaller among X & Y)
2. Delete the already existing snapshot for that application
3. Re-run the analysis & snapshot for the small application (for which you have added the new module)

SELECT next_object_id, 
       dso.object_name, 
       Count(previous_object_id) 
FROM   dss_links dl 
       JOIN dss_objects dso 
         ON dso.object_id = dl.next_object_id 
WHERE  link_type_id = 1 
       AND next_object_id IN (SELECT object_id 
                              FROM   dss_objects 
                              WHERE  object_type_id = 20000) 
GROUP  BY next_object_id, 
          dso.object_name 
HAVING Count(previous_object_id) > 1 

Remediation:

If the result of the above query returns rows then there is a corruption, only one application should share the module.

For example If there are 2 applications sharing the module A (say applications X & Y). Pick an application among the two which is smaller and follow the below steps as a workaround to remove the shared module from one of the applications:

1. Add a new module for the smaller application (smaller among X & Y)
2. Delete the already existing snapshot for that application
3. Re-run the analysis & snapshot for the small application (for which you have added the new module)