SQL Queries - CAST Central Base - Queries on Artifacts - How to get the list of deleted artifacts between two snapshots

Purpose of Query

This document provides a query to list the deleted artifacts between two snapshots.

Applicable CAST Version

Applicable RDBMS

Query for CSS

SELECT *
FROM   dss_objects
WHERE  object_id IN
                     ( SELECT DISTINCT cs1.OBJECT_ID
                     FROM             ADGV_COST_STATUSES cs1,
                                      DSS_LINKS l           ,
                                      DSS_MODULE_LINKS m
                     WHERE            m.OBJECT_ID          = <APPLICATION_ID>      -- here 3
                     AND              m.SNAPSHOT_ID IN (   <PREVIOUS_SNAPSHOT_ID>, -- here 1
                                                           <NEXT_SNAPSHOT_ID>)     -- here 2
                     AND              l.PREVIOUS_OBJECT_ID = m.MODULE_ID
                     AND              l.LINK_TYPE_ID       = 3
                     AND              cs1.OBJECT_ID        = l.NEXT_OBJECT_ID
                     AND              cs1.SNAPSHOT_ID      = <NEXT_SNAPSHOT_ID>    -- here 2
                     AND              cs1.CHANGE_TYPE      = 2
                     )

Query result example

805;571;“KRWSKP01”;"";"[D:\DEPLOY\APP1\BATCH\JCL].KRWSKP01"
744;571;“KRWSTRPP”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRWSTRPP"
1095;571;“KRWILSR1”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRWILSR1"
3375;139145;“SORT”;"";"[Utilities].SORT"
2499;571;“KRDCUAT”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRDCUAT"
1435;571;“KRWBLUE1”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRWBLUE1"
2568;571;“KRDBOU04”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRDBOU04"
1531;571;“KRUNOFOK”;"";"[D:\DEPLOY\APP1\MF\BATCH\JCL].KRUNOFOK"

Query result interpretation

This is the list of artifacts that belonged to Snapshot <PREVIOUS_SNAPSHOT_ID> and that are not present in Snapshot <NEXT_SNAPSHOT_ID>, with their ID, name, description, and full-name.

Query for Oracle

SELECT *
FROM   dss_objects
WHERE  object_id IN
                     ( SELECT DISTINCT cs1.OBJECT_ID
                     FROM             ADGV_COST_STATUSES cs1,
                                      DSS_LINKS l           ,
                                      DSS_MODULE_LINKS m
                     WHERE            m.OBJECT_ID          = <APPLICATION_ID>      -- here 25308
                     AND              m.SNAPSHOT_ID IN (   <PREVIOUS_SNAPSHOT_ID>, -- here 4
                                                           <NEXT_SNAPSHOT_ID>)     -- here 86
                     AND              l.PREVIOUS_OBJECT_ID = m.MODULE_ID
                     AND              l.LINK_TYPE_ID       = 3
                     AND              cs1.OBJECT_ID        = l.NEXT_OBJECT_ID
                     AND              cs1.SNAPSHOT_ID      = <NEXT_SNAPSHOT_ID>    -- here 86
                     AND              cs1.CHANGE_TYPE      = 2
                     )

Query result example

OBJECT_ID OBJECT_TYPE_ID OBJECT_NAME OBJECT_DESCRIPTION OBJECT_FULL_NAME

25507 102 init (null) com.xxxx.services.language.Language.init
25508 102 doFilter (null) com.xxxxi.w3g.services.language.Language.doFilter
25509 102 destroy (null) com.xxxx.w3g.services.language.Language.destroy
25513 102 sessionCreated (null) com.xxxx.w3g.services.language.LanguageListener.sessionCreated
25514 102 sessionDestroyed (null) com.xxxx.w3g.services.language.LanguageListener.sessionDestroyed
25523 102 destroy (null) version.GetVersion.destroy
25524 102 init (null) version.GetVersion.init

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

[Executed: 31/01/17 09:19:39 GMT ] [Execution: 354/ms]

Query result interpretation

This is the list of artifacts that belonged to Snapshot <PREVIOUS_SNAPSHOT_ID> and that are not present in Snapshot <NEXT_SNAPSHOT_ID>, with their ID, name, description, and full-name.

Notes/comments

Related Pages