This page explains how to execute the DETAIL and TOTAL procedures manually against the KB (i.e. execute them out of snapshot's generation process) to identify the objects that are involved in a Quality Rule computation.
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
Get the DETAIL and TOTAL procedure
Note: All the procedure's scripts are available under CAST installation folder in the InstallScript\<RDBMS>\DIAG or \InstallScripts\<RDBMS>\DSS\4_u - CAST_LOCAL_DSS_PACKAGE.SQL
Clean the Procedure script
Here to explain the process we have taken an example quality rule - ABAP: Avoid using "SELECT DISTINCT", use DELETE-ADJACENT with Metric_ID=7594
Example - DIAG_ABAP_ANA_ARTIF_T_TOTAL
CREATE OR REPLACE FUNCTION DIAG_ABAP_ANA_ARTIF_T_TOTAL
(
I_SNAPSHOT_ID IN INT,
- the metric SNAPSHOT id I_METRIC_PARENT_ID IN INT,
- the metric parent id I_METRIC_ID IN INT,
- the metric id I_METRIC_VALUE_INDEX IN INT
)
RETURN INT
IS
ERRORCODE INT := 0;
BEGIN
--<<NAME>>DIAG_ABAP_ANA_ARTIF_T_TOTAL<</NAME>>*/
--<<COMMENT>> Template name = DSSGENERICTOTAL. <</COMMENT>>
--<<COMMENT>> Definition = Number of ABAP Artifacts with OpenSQL queries. <</COMMENT>>
INSERT
INTO DSS_METRIC_RESULTS
(
METRIC_NUM_VALUE ,
METRIC_OBJECT_ID ,
OBJECT_ID ,
METRIC_ID ,
METRIC_VALUE_INDEX,
SNAPSHOT_ID
)
SELECT COUNT( T1.OBJECT_ID),
0 ,
SC.OBJECT_PARENT_ID ,
I_METRIC_ID ,
I_METRIC_VALUE_INDEX,
I_SNAPSHOT_ID
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC
WHERE SC.SNAPSHOT_ID = I_SNAPSHOT_ID
AND SC.METRIC_PARENT_ID = I_METRIC_PARENT_ID
AND SC.METRIC_ID = I_METRIC_ID
AND SC.COMPUTE_VALUE = 0
AND MO.TECHNO_TYPE = -15 - Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_ID
AND T1.APPLICATION_ID = SC.OBJECT_ID
AND NOT EXISTS
( SELECT 1
FROM DSS_OBJECT_EXCEPTIONS E
WHERE E.METRIC_ID = I_METRIC_ID
AND E.OBJECT_ID = T1.OBJECT_ID
)
- OPEN SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
GROUP BY SC.OBJECT_PARENT_ID,
SC.OBJECT_ID ;
RETURN ERRORCODE;
END DIAG_ABAP_ANA_ARTIF_T_TOTAL;
Remove the header and the footer and only keep the SELECT statement
You will have as result the following:
SELECT COUNT( T1.OBJECT_ID),
0 ,
SC.OBJECT_PARENT_ID ,
I_METRIC_ID ,
I_METRIC_VALUE_INDEX,
I_SNAPSHOT_ID
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC
WHERE SC.SNAPSHOT_ID = I_SNAPSHOT_ID
AND SC.METRIC_PARENT_ID = I_METRIC_PARENT_ID
AND SC.METRIC_ID = I_METRIC_ID
AND SC.COMPUTE_VALUE = 0
AND MO.TECHNO_TYPE = -15 - Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_ID
AND T1.APPLICATION_ID = SC.OBJECT_ID
AND NOT EXISTS
( SELECT 1
FROM DSS_OBJECT_EXCEPTIONS E
WHERE E.METRIC_ID = I_METRIC_ID
AND E.OBJECT_ID = T1.OBJECT_ID
)
- OPEN SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
GROUP BY SC.OBJECT_PARENT_ID,
SC.OBJECT_ID
Remove the exceptions in the Select Statement
Remove the select clause for thr table DSS_OBJECT_EXCEPTIONS. As result of this removal, you will have the following:
SELECT COUNT( T1.OBJECT_ID),
0 ,
SC.OBJECT_PARENT_ID ,
I_METRIC_ID ,
I_METRIC_VALUE_INDEX,
I_SNAPSHOT_ID
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC
WHERE SC.SNAPSHOT_ID = I_SNAPSHOT_ID
AND SC.METRIC_PARENT_ID = I_METRIC_PARENT_ID
AND SC.METRIC_ID = I_METRIC_ID
AND SC.COMPUTE_VALUE = 0
AND MO.TECHNO_TYPE = -15 - Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_IDAnd T1.APPLICATION_ID = SC.OBJECT_ID-- Open SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
GROUP BY SC.OBJECT_PARENT_ID,
SC.OBJECT_ID
Remove all the sections refering to procedure parameter
Remove all the sections refering to procedure parameter starting with I_ and only keep the count column and substitute the value of Metric ID for the condition SC.METRIC_ID =
SELECT COUNT( T1.OBJECT_ID)
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC
WHERE SC.METRIC_ID = 7594
AND SC.COMPUTE_VALUE = 0
AND MO.TECHNO_TYPE = -15 - Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_ID And T1.APPLICATION_ID = SC.OBJECT_ID-- Open SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
GROUP BY SC.OBJECT_PARENT_ID,
SC.OBJECT_ID
Remove the GROUP BY clause
Remove the GROUP BY clause if exists
SELECT COUNT( T1.OBJECT_ID)
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC
WHERE SC.METRIC_ID =7594
AND SC.COMPUTE_VALUE = 0
AND MO.TECHNO_TYPE = -15 - Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_ID And T1.APPLICATION_ID = SC.OBJECT_ID-- Open SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
Remove SC.COMPUTE_VALUE = 0
Below is the SQL Query which can be executed. It will return the results on Application Level
Select Count( T1.OBJECT_ID) From ObjInf T2 , DSSAPP_ARTIFACTS T1, DSSAPP_MODULES MO, DSS_METRIC_SCOPES SC Where WHERE SC.METRIC_ID =7594 MO.TECHNO_TYPE = -15 - Technologic ABAP object And MO.MODULE_ID = SC.OBJECT_ID And T1.APPLICATION_ID = SC.OBJECT_ID-- Open SQL and T1.OBJECT_ID = T2.IdObj and T2.InfTyp = 9 and T2.InfSubTyp = 1538 and T2.InfVal > 0
Populate the working tables
If the procedure contains tables that begin with WK then Populate the working tables using the page - SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to populate the working tables present in DETAIL and TOTAL procedure
Run the Cleaned Procedure
select all the objects from KEYS table and add a constraint on IdKey.
SELECT k.*
FROM ObjInf T2 ,
DSSAPP_ARTIFACTS T1 ,
DSSAPP_MODULES MO ,
DSS_METRIC_SCOPES SC,
Keys k
WHERE SC.METRIC_ID = 7594
AND MO.TECHNO_TYPE = -15 -- Technologic ABAP object
AND MO.MODULE_ID = SC.OBJECT_ID
AND T1.APPLICATION_ID = SC.OBJECT_ID
-- Open SQL
AND T1.OBJECT_ID = T2.IdObj
AND T2.InfTyp = 9
AND T2.InfSubTyp = 1538
AND T2.InfVal > 0
AND T1.OBJECT_ID = k.IdKey
