CMS Snapshot Analysis - Information - How to exclude objects from the dashboard using a SQL tool after module generation

Purpose

This page provides information on how to exclude objects from the dashboard using a sql tool after module generation.

Excluding objects from the dashboard is excluding objects from the module, so will have an impact on Transactions and values for transactions in TCC if these objects are required for transactions.

For more information on this action, please refer to:

For 8.3: CMS - Modules tab

Applicable CAST Version

Release	Yes/No
8.3.x

Applicable RDBMS

RDBMS	Yes/No
CSS

Details

To exclude objects from the dashboard so that they do not get metrics calculated for them and do not show up as being violated, you can create a sql tool job and have it executed after module generation.

A picture of where the tool would be placed in CAST-MS is shown here:

In sql, add this type of a query:

with CAST AIP < 8.3.37:

SET search_path=<local database>;
UPDATE ctt_object_applications
SET properties = 1
WHERE object_id IN (xxxx);

With CAST AIP >= 8.3.37:

SET search_path=<local database>;
UPDATE ctt_object_applications
SET properties = PROPERTIES | 256
WHERE object_id IN (xxxx);

There's no appreciable result from this query.

The query is setting the properties for all objects in the query so that they are considered external so will not have metrics calculated against them (set properties=1 in the table ctt_object_applications). The objects will still be in place in Enlighten and the KB.

The 'xxxx' represents a sub-query which identifies the objects which should be considered external.

An example of a job with a specific sub-query is the following:

With CAST AIP < 8.3.37:

SET search_path=<local database>;
UPDATE ctt_object_applications
SET properties = 1
WHERE object_id IN
(SELECT object_id
FROM ctt_object_applications
WHERE object_type = 1101006
AND object_id NOT IN
( SELECT DISTINCT a.IdCle
FROM Keys clr
JOIN Acc a
ON clr.IdKey = a.IdClr
JOIN Keys cle
ON cle.IdKey = a.IdCle
WHERE cle.ObjTyp IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
AND clr.ObjTyp NOT IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
)
AND application_id IN
( SELECT DISTINCT application.application_id
FROM Keys clr
JOIN Acc a
ON clr.IdKey = a.IdClr
JOIN Keys cle
ON cle.IdKey = a.IdCle
JOIN ctt_object_applications application
ON a.IdCle = application.object_id
WHERE cle.ObjTyp IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
AND clr.ObjTyp NOT IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
)
);

With CAST AIP >= 8.3.37:

SET search_path=<local database>;
UPDATE ctt_object_applications
SET properties = PROPERTIES | 256
WHERE object_id IN
(SELECT object_id
FROM ctt_object_applications
WHERE object_type = 1101006
AND object_id NOT IN
( SELECT DISTINCT a.IdCle
FROM Keys clr
JOIN Acc a
ON clr.IdKey = a.IdClr
JOIN Keys cle
ON cle.IdKey = a.IdCle
WHERE cle.ObjTyp IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
AND clr.ObjTyp NOT IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
)
AND application_id IN
( SELECT DISTINCT application.application_id
FROM Keys clr
JOIN Acc a
ON clr.IdKey = a.IdClr
JOIN Keys cle
ON cle.IdKey = a.IdCle
JOIN ctt_object_applications application
ON a.IdCle = application.object_id
WHERE cle.ObjTyp IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
AND clr.ObjTyp NOT IN
(SELECT IdTyp
FROM Typ
WHERE IdTyp BETWEEN 1101000 AND 1101100
)
)
);

This sub-query in this example is selecting ids of tables from the sql analyzer extension which do not make calls or are called by other non-sql parts of the code, so that these tables which are not involved in any calls with non-sql code (Cobol, java, etc) are not reported on the dashboard.

Notes/comments

Related Pages