SQL Queries - CAST Central Base - Queries on applications - How to get the TQI by Technology TQI by Functional Module or global TQI for a Snapshot

Created by Salim Frontin on Feb 11, 2022
Purpose of Query

The Total Quality Index (TQI) is derived from a combination of the five health factors – Transferability, Changeability, Robustness, Performance, and Security.

You can measure it by technology, by functional module, or for the whole snapshot

Applicable CAST Version
Release
Yes/No
8.3.x(tick) 
8.2.x (tick) 
Applicable RDBMS
RDBMS
Yes/No
Oracle Server (tick) 
Microsoft SQL Server (question) 
CSS (tick) 
Query for CSS

Get the TQI by Technology


SELECT dso.object_name               AS technology,
       ROUND(dmr.metric_num_value,3) AS TQI
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id IN
       (SELECT dli.next_object_id
       FROM    dss_link_info dli
       WHERE   dli.previous_object_id = <APPLICATION_ID> --here 3
       AND     dli.link_type_id       = 2
       AND     dli.snapshot_id        = <SNAPSHOT_ID> --here 2
       )
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        = <SNAPSHOT_ID> --here 2
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

"JCL";3.043
"Cobol";2.324
"IMS";3.584
"DB2 Server";2.143

Query result interpretation

In Snapshot <SNAPSHOT_ID> of application <APPLICATION_ID> ,

  • The TQI computed on JCL objects is 3.043
  • The TQI computed on Cobol objects is 2.324
  • The TQI computed on IMS objects is 3.584
  • The TQI computed on DB2 Server objects is 2.143
Query for Oracle

Get the TQI by Technology


SELECT dso.object_name               AS technology,
       ROUND(dmr.metric_num_value,3) AS TQI
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id IN
       (SELECT dli.next_object_id
       FROM    dss_link_info dli
       WHERE   dli.previous_object_id =  <APPLICATION_ID> -- here 359323
       AND     dli.link_type_id       = 2
       AND     dli.snapshot_id        = <SNAPSHOT_ID> -- here 67
       )
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        =  <SNAPSHOT_ID> -- here 67
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

TECHNOLOGY TQI
---------------------- ------
.Net 2,724

1 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms]

[Executed: 25/01/17 09:06:57 GMT ] [Execution: 1508/ms]

Query result interpretation

In Snapshot 67 of application 359323, the TQI computed on .Net objects is 2.724

Query for CSS

Get the TQI by Functional Module


SELECT dso.object_name                AS module,
       ROUND(dmr.metric_num_value, 3) AS TQI
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id IN
       (SELECT dli.next_object_id
       FROM    dss_link_info dli
       WHERE   dli.previous_object_id = <APPLICATION_ID> -- here 3
       AND     dli.link_type_id       = 1
       AND     dli.snapshot_id        = <SNAPSHOT_ID> -- here 2
       )
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        = <SNAPSHOT_ID> -- here 2
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

"App1 full content";2.221

Query result interpretation

In Snapshot <SNAPSHOT_ID> of application <APPLICATION_ID> , the TQI computed on module "App1 full content" is 2.221

In this case, (full-content module), it is the same as computing the TQI on the whole application. For an example with several modules, see Oracle example.

Query for Oracle

Get the TQI by Functional Module


SELECT dso.object_name                AS module,
       ROUND(dmr.metric_num_value, 3) AS TQI
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id IN
       (SELECT dli.next_object_id
       FROM    dss_link_info dli
       WHERE   dli.previous_object_id = <APPLICATION_ID> --here 468616 
       AND     dli.link_type_id       = 1
       AND     dli.snapshot_id        = <SNAPSHOT_ID> --here 98
       )
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        = <SNAPSHOT_ID> --here 98
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

MODULE TQI
-------------------------------------------------- --------
"APP2"/APP2_My Package content 2,792
"APP2"/APP2_DBMS content 2,254

Query result interpretation

In Snapshot 98 of application 468616,

  • the TQI computed on module "APP2_My Package content" is 2.792
  • the TQI compmuted on module "APP2_DBMS content" is 2.254
Query for CSS

Get the global TQI


SELECT dso.object_name               AS application,
       ROUND(dmr.metric_num_value,3)    TQI        ,
       dmr.snapshot_id
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id = <APPLICATION_ID> -- here 3
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        = <SNAPSHOT_ID> --here 2
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

"App1";2.221;2

Query result interpretation

The TQI computed for Snapshot <SNAPSHOT_ID> of application <APPLICATION_ID> is 2.221

Query for Oracle

Get the global TQI


SELECT dso.object_name               AS application,
       ROUND(dmr.metric_num_value,3)    TQI        ,
       dmr.snapshot_id
FROM   dss_metric_results dmr,
       dss_objects dso
WHERE  dmr.object_id = <APPLICATION_ID> -- here 468616
AND    dmr.metric_id IN (60017)
       /* Health Factors */
AND    dmr.snapshot_id        = <SNAPSHOT_ID> --here 98
AND    dmr.metric_value_index = 0
AND    dmr.object_id          = dso.object_id
Query result example

APPLICATION TQI SNAPSHOT_ID
-------------------- ------ --------------
"App2" 2,44 98

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 9/ms]

[Executed: 31/01/17 08:41:52 GMT ] [Execution: 647/ms]

Query result interpretation

The TQI computed for Snapshot <SNAPSHOT_ID> of application <APPLICATION_ID> is 2.44

Notes/comments

Related Pages