SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to run DETAIL and TOTAL procedures manually with working tables
Purpose of Query
This page will explain how to customize the detail procedure of the Quality rule Persistent class methods equals and hashCode must access its fields through getter methods diag to execute it manually.
This is a particular J2EE diag. The DETAIL procedure uses work tables that are populated by other stored procedure and are truncated at the end of the Snapshot computation. If we run the detail query unchanged, no rows will be raised.
Applicable in CAST Version
| Release | Yes/No |
|---|---|
| 8.3.x | ✅ |
Applicable RDBMS
| RDBMS | Yes/No |
|---|---|
| Oracle Server | ✅ |
| Microsoft SQL Server | ❓ |
| CSS | ✅ |
The stored procedure that calculates this rule is : <LOCAL_DATA_BASE>.DIAG_SCOPE_JEEUB001
The detail query is:
INSERT
INTO DSS_METRIC_SCOPES
(
OBJECT_ID ,
METRIC_PARENT_ID ,
METRIC_ID ,
OBJECT_PARENT_ID ,
SNAPSHOT_ID ,
METRIC_NUM_VALUE ,
METRIC_CHAR_VALUE,
METRIC_OBJECT_ID ,
COMPUTE_VALUE
)
SELECT DISTINCT P1.OBJECT_ID ,
I_METRIC_ID ,
I_METRIC_CHILD_ID,
SC.MODULE_ID ,
I_SNAPSHOT_ID ,
0 ,
NULL ,
0 ,
0
FROM WK_DIA_INHPRSTCLS T1
JOIN DSSAPP_MODULES SC
ON T1.APPLICATION_ID = SC.MODULE_ID
JOIN DIAG_OBJECT_PARENTS P1
ON T1.TARGET_OBJECT_ID = P1.PARENT_ID
JOIN CDT_OBJECTS TN
ON P1.OBJECT_ID = TN.OBJECT_ID
JOIN WK_DIA_INHPRSTCLS T2
ON T1.HIBERNATE_ID = T2.HIBERNATE_ID --- T1 and T2 are in the same java class.
AND T1.SOURCE_OBJECT_ID = T2.SOURCE_OBJECT_ID
AND T1.TARGET_OBJECT_ID = T2.TARGET_OBJECT_ID
AND T1.APPLICATION_ID = T2.APPLICATION_ID
JOIN DIAG_OBJECT_PARENTS P2
ON T2.TARGET_OBJECT_ID = P2.PARENT_ID
JOIN DIAG_CTV_LINKS_SIMPLE IL
ON IL.CALLER_ID = P1.OBJECT_ID
AND IL.CALLED_ID = P2.OBJECT_ID
WHERE NOT EXISTS
( SELECT 1
FROM DSS_OBJECT_EXCEPTIONS E
WHERE E.METRIC_ID = I_METRIC_ID
AND E.OBJECT_ID = P1.OBJECT_ID
)
AND SC.TECHNO_TYPE = 140029
/* JEE Module */
AND P1.OBJECT_TYPE IN (102,988) -- method, gen method
AND TN.OBJECT_NAME IN ('equals',
'hashCode')
AND P2.OBJECT_TYPE = 103 -- field
We cannot run this query because the WK_DIA_INHPRSTCLS table is a work table that is populated by the <LOCAL_DATA_BASE>.DIAG_HIBER_JAVA (When the snapshot ends, all the work table are truncated). An other Work table is needed to run the detail query.
To be able to run the detail query to create this table -
Query for CSS
CREATE TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP AS
SELECT *
FROM <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT;
Query for Oracle
CREATE GLOBAL TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP AS
SELECT *
FROM <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT;
Query to populate the temporary WK_DIA_JAVAINHERIT_TMP
Query for CSS
For CAST 8.3.36 and lower:
INSERT
INTO <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP
(
SOURCE_OBJECT_ID,
TARGET_OBJECT_ID,
LEV ,
APPLICATION_ID
)
SELECT DISTINCT T1.OBJECT_ID,
T1.OBJECT_ID,
0 ,
T1.APPLICATION_ID
FROM <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1,
<LOCAL_DATA_BASE>.DSSAPP_MODULES SC
WHERE SC.TECHNO_TYPE = 140029
AND T1.APPLICATION_ID = SC.MODULE_ID
AND T1.OBJECT_TYPE IN
(SELECT IdTyp
FROM <LOCAL_DATA_BASE>.TypCat
WHERE IdCatParent IN ( 10024,
1103,1099 )
)
AND T1.PROPERTIES = 0;
For CAST 8.3.37 and higher:
INSERT
INTO <LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP
(
SOURCE_OBJECT_ID,
TARGET_OBJECT_ID,
LEV ,
APPLICATION_ID
)
SELECT DISTINCT T1.OBJECT_ID,
T1.OBJECT_ID,
0 ,
T1.APPLICATION_ID
FROM <LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1,
<LOCAL_DATA_BASE>.DSSAPP_MODULES SC
WHERE SC.TECHNO_TYPE = 140029
AND T1.APPLICATION_ID = SC.MODULE_ID
AND T1.OBJECT_TYPE IN
(SELECT IdTyp
FROM <LOCAL_DATA_BASE>.TypCat
WHERE IdCatParent IN ( 10024,
1103,1099 )
)
AND T1.PROPERTIES & 255 = 0;
Query for Oracle
Same as CSS
Create another temporary table - WK_DIA_INHPRSTCLS_TMP
Query for CSS
CREATE TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP AS
SELECT *
FROM <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS;
Query for Oracle
CREATE GLOBAL TEMPORARY TABLE <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP AS
SELECT *
FROM <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS;
Query to populate the temporary worktable WK_DIA_INHPRSTCLS_TMP
Query for CSS
For CAST 8.3.36 and below:
INSERT
INTO <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP
(
HIBERNATE_ID ,
SOURCE_OBJECT_ID,
TARGET_OBJECT_ID,
LEV ,
APPLICATION_ID
)
SELECT DISTINCT T2.OBJECT_ID ,
DIL.SOURCE_OBJECT_ID,
DIL.TARGET_OBJECT_ID,
DIL.LEV ,
DIL.APPLICATION_ID
FROM <LOCAL_DATA_BASE>.CTV_OBJECTS T2 ,
<LOCAL_DATA_BASE>.DIAG_CTV_LINKS IL ,
<LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP DIL,
<LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1
WHERE T1.OBJECT_TYPE IN (100,989)
AND T1.PROPERTIES = 0
AND T2.OBJECT_TYPE IN
( SELECT IdTyp
FROM <LOCAL_DATA_BASE>.TypCat
WHERE IdCatParent = 136996
)
AND IL.CALLER_ID = T1.OBJECT_ID
AND IL.CALLED_ID = T2.OBJECT_ID
AND IL.LINK_TYPE_LO = 32768
AND IL.LINK_TYPE_HI = 0
AND T1.OBJECT_ID = DIL.SOURCE_OBJECT_ID
AND T1.APPLICATION_ID = DIL.APPLICATION_ID;
For CAST 8.3.37 and higher:
INSERT
INTO <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP
(
HIBERNATE_ID ,
SOURCE_OBJECT_ID,
TARGET_OBJECT_ID,
LEV ,
APPLICATION_ID
)
SELECT DISTINCT T2.OBJECT_ID ,
DIL.SOURCE_OBJECT_ID,
DIL.TARGET_OBJECT_ID,
DIL.LEV ,
DIL.APPLICATION_ID
FROM <LOCAL_DATA_BASE>.CTV_OBJECTS T2 ,
<LOCAL_DATA_BASE>.DIAG_CTV_LINKS IL ,
<LOCAL_DATA_BASE>.WK_DIA_JAVAINHERIT_TMP DIL,
<LOCAL_DATA_BASE>.CTT_OBJECT_APPLICATIONS T1
WHERE T1.OBJECT_TYPE IN (100,989)
AND T1.PROPERTIES & 255 = 0
AND T2.OBJECT_TYPE IN
( SELECT IdTyp
FROM <LOCAL_DATA_BASE>.TypCat
WHERE IdCatParent = 136996
)
AND IL.CALLER_ID = T1.OBJECT_ID
AND IL.CALLED_ID = T2.OBJECT_ID
AND IL.LINK_TYPE_LO = 32768
AND IL.LINK_TYPE_HI = 0
AND T1.OBJECT_ID = DIL.SOURCE_OBJECT_ID
AND T1.APPLICATION_ID = DIL.APPLICATION_ID;
Query for Oracle
Same as CSS
Below is the detail query with the two new temporary work tables -
Query for CSS
SELECT DISTINCT P1.OBJECT_ID,
k1.keynam ,
k2.keynam
FROM <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP T1
JOIN <LOCAL_DATA_BASE>.DSSAPP_MODULES SC
ON T1.APPLICATION_ID = SC.MODULE_ID
JOIN <LOCAL_DATA_BASE>.DIAG_OBJECT_PARENTS P1
ON T1.TARGET_OBJECT_ID = P1.PARENT_ID
JOIN <LOCAL_DATA_BASE>.keys k1
ON P1.OBJECT_ID = k1.idkey
JOIN <LOCAL_DATA_BASE>.CDT_OBJECTS TN
ON k1.idkey = TN.OBJECT_ID
JOIN <LOCAL_DATA_BASE>.WK_DIA_INHPRSTCLS_TMP T2
ON T1.HIBERNATE_ID = T2.HIBERNATE_ID
AND T1.SOURCE_OBJECT_ID = T2.SOURCE_OBJECT_ID
AND T1.TARGET_OBJECT_ID = T2.TARGET_OBJECT_ID
AND T1.APPLICATION_ID = T2.APPLICATION_ID
JOIN <LOCAL_DATA_BASE>.DIAG_OBJECT_PARENTS P2
ON T2.TARGET_OBJECT_ID = P2.PARENT_ID
JOIN <LOCAL_DATA_BASE>.keys k2
ON P2.PARENT_ID = k2.idkey
JOIN <LOCAL_DATA_BASE>.DIAG_CTV_LINKS_SIMPLE IL
ON IL.CALLER_ID = P1.OBJECT_ID
AND IL.CALLED_ID = P2.OBJECT_ID
WHERE NOT EXISTS
( SELECT 1
FROM <LOCAL_DATA_BASE>.DSS_OBJECT_EXCEPTIONS E
WHERE E.METRIC_ID = 7494
AND E.OBJECT_ID = P1.OBJECT_ID
)
AND SC.TECHNO_TYPE = 140029
AND P1.OBJECT_TYPE IN (102,988)
AND TN.OBJECT_NAME IN ('equals',
'hashCode')
AND P2.OBJECT_TYPE = 103;
Query result example
object_id ;keynam ;keynam
1112 ; arbitratory_sign ; arbitratory_sign
Query result interpretation
This query returns the object_id and object_name (of the objects violating this quality rule / execution of detail procedure )
Query for Oracle
Same as CSS