SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to detect a metric with its total procedure missing

Purpose of Query

The query detects the missing total/detailed procedure in a metric.

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x
7.3.x
7.2.x
7.0.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2
CSS1

Query for CSS

Run the below query for total procedure :

SELECT vt.metric_value_procedure_name, 
       t.metric_id   AS "METRIC ID", 
       t.metric_name AS "METRIC NAME", 
       CASE t.metric_type 
              WHEN 0 THEN 'deactivated' 
              ELSE 'activated' 
       END 
FROM   <knowledge_base>.dss_metric_value_types vt 
JOIN   <knowledge_base>.dss_metric_types t 
ON     vt.metric_id = t.metric_id 
WHERE  upper(vt.metric_value_procedure_name) NOT IN 
       ( 
              SELECT upper(sp.routine_name) 
              FROM   information_schema.routines sp 
              WHERE  sp.routine_schema = '<Knowledge_base>') 
AND    vt.metric_value_index = 2 
AND    t.metric_name NOT LIKE 'DELETED%'

Query result example

"DIAG_ABAP_ANA_MACRO_TOTAL";7084;"Macro Naming Convention";"deactivated"

"DIAG_ABAP_ANA_FORM_TOTAL";7086;"Form Naming Convention";"deactivated"

"DIAG_JAVA_ANA_CLSCLIENT_TOTAL";7244;"Avoid direct usage of EJB Entity from the client";"deactivated"

Query result interpretation

For each deactivated total procedure, the query returns the metric name and the metric id

Run the below query for detail procedure :

SELECT tt.metric_scope_procedure_name, 
       t.metric_id   AS "METRIC ID", 
       t.metric_name AS "METRIC NAME", 
       CASE t.metric_type 
              WHEN 0 THEN 'deactivated' 
              ELSE 'activated' 
       END 
FROM   <knowledge_base>.dss_metric_type_trees tt 
JOIN   <knowledge_base>.dss_metric_types t 
ON     tt.metric_parent_id = t.metric_id 
AND    t.metric_options = 0 
       /* not rationalized in 7.0*/ 
AND    t.metric_group = 1 
JOIN   <knowledge_base>.dss_metric_type_trees ttfolder 
ON     ttfolder.metric_id = tt.metric_parent_id 
WHERE  upper(tt.metric_scope_procedure_name) NOT IN 
       ( 
              SELECT upper(sp.routine_name) 
              FROM   information_schema.routines sp 
              WHERE  sp.routine_schema = '<Knowledge_base>.') 
AND    tt.metric_scope_procedure_name IS NOT NULL

Query result example

"DIAG_SCOPE_CPPNAM001";550;"Class naming convention - case control";"activated"

"DIAG_SCOPE_CPPNAM002";552;"Method naming convention - case control";"activated"

"DIAG_SCOPE_CPPNAM003";554;"Constant naming convention - case control";"activated"

"DIAG_SCOPE_CPPNAM004";556;"Macros naming convention - case control";"activated"

Query result interpretation

For each deactivated detailed procedure, the query returns the metric name and the metric id.

In the above queries replace <Knowledge_base> with the name of desired Knowledge base.

Query for Oracle

N/A

Query for SQL server

N/A

Notes/comments

Servman does not install procedures for deactivated quality rules.
But for migrated KB, those stored procedures still exists and the customer can enable it again if required.

Query result example