SQL Queries - CAST Central Base - Corruption in Metric Results

Purpose of Query

The purpose of the queries on this page is to provide identification of corruption encountered in metric results which can impact other parts of the product especially when doing the report generation on the AED/ED dashboard.

Please see the following documentation page for more information on this topic:   CAST Dashboard Package - Engineering Dashboard - Report Generation

Applicable CAST Version

Applicable RDBMS

Query for CSS

This query checks for some discrepancies in the dss_metric_results table which can cause problems during reporting.  This query assumes that the latest snapshot is the one having the problem.

SELECT m.METRIC_ID,
m.METRIC_NAME
FROM DSS_METRIC_TYPES m
JOIN
( SELECT r.METRIC_ID,
COUNT(r.METRIC_ID)
FROM DSS_METRIC_RESULTS r
WHERE r.OBJECT_ID IN
(SELECT OBJECT_ID
FROM DSS_OBJECTS
WHERE OBJECT_TYPE_ID = -102
)
/* application */
AND r.SNAPSHOT_ID IN
(SELECT SNAPSHOT_ID
FROM ADG_DELTA_SNAPSHOTS
WHERE APPLICATION_ID = r.OBJECT_ID
AND LATEST =1
)
/* latest snapshot */
AND r.METRIC_ID IN
(SELECT METRIC_ID
FROM DSS_METRIC_TYPES
WHERE METRIC_GROUP = 1
)
/* quality rules */
AND r.METRIC_VALUE_INDEX IN (0,1,2)
GROUP BY r.METRIC_ID
HAVING COUNT(r.METRIC_ID) < 3
)
t
ON t.METRIC_ID = m.METRIC_ID

Query result example

1574;“Use at most one statement per line”

Query result interpretation

If no rows are returned, then there are no problems with any queries and this is not the problem with the corruption.

If rows are returned, it will provide the metric_id and metric_name of the query which needs to be re-mediated to resolve the corruption issues.

Support has found issues with the metric ‘Avoid Double Checked Locking’ which is fixed in 8.3.9.

Support also has found issues with some custom extensions having metric problems, which need to be addressed by the author of the custom extension.

Query for CSS

Enter the SQL query

Query result example

Query result interpretation

Query for Oracle

Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages