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

Purpose of Query

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

Applicable CAST Version

Applicable RDBMS

Query for CSS

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

Query result example

4;“Mymodule”;2

Query result interpretation

The query returns the module Id, the module name, and the number of application sharing the module

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)

Query for Oracle

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

Query result example

4;“Mymodule”;2

Query result interpretation

The query returns the module Id, the module name, and the number of application sharing the module

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)

Query for SQL server

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

Query result example

4;“Mymodule”;2

Query result interpretation

The query returns the module Id, the module name, and the number of application sharing the module

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)

Notes/comments

Related Pages