SQL Queries - CAST Knowledge Base - Queries on objects - How to identify and clean up all the ghost properties in your Knowledge base

Purpose

This page provides you with the definition of ghost properties, how to identify them and how to clean them.

The following graph represent the simplified parenthood morphology of the objects in the knowledge base (Analysis Service database).

On one hand each object in the knowledge base have one and only one project parent that have one and only one Analysis unit parent, on the other hand each analysis unit may have one or several Projects child, and each Project have one or several objects child.

Each object may have one or several properties, and each property refers to one and only one object.

Fore some reason (As an example of root cause, there is manual deletion of entries from table keys), the properties can remain in database whereas their owner object has been deleted.

This act will result in ghost properties, In order to identify and clean up the list of ghost properties, please refer to 243193705

Applicable in CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS✅

Ghost Properties

Ghost Properties

The following query, will list the set of ghost properties (properties whose owner object does not exist in database)

SELECT IDKEY 
FROM   OBJECTS O
       JOIN
              ( SELECT DISTINCT oi.idobj
              FROM             objinf oi
                               LEFT JOIN keys k1
                               ON               k1.idkey=oi.idobj
              WHERE            k1.idkey           IS NULL
               
              UNION ALL
               
              SELECT DISTINCT od.idobj
              FROM            objdsc od
                              LEFT JOIN keys k2
                              ON              k2.idkey=od.idobj
              WHERE           k2.idkey          IS NULL
              )
              objprop
       ON     OBJPROP.IDOBJ=O.IDKEY;

As an example you can find:

Query result example

idkey

123456789
578462125

In order to clean up the list of Ghost Properties, please execute the following queries on you knowledge base.

Note that this clean up is irreversible, so we advice you to take a backup of your local (Analysis Service database), central (Dashboard Service database) and management base (Management Service database) even if the cleanup is done on only the knowledge base.

CREATE TEMPORARY TABLE WK_ObjToDelete AS
SELECT IDKEY AS IDOBJ
FROM   OBJECTS O
       JOIN
              ( SELECT DISTINCT oi.idobj
              FROM             objinf oi
                               LEFT JOIN keys k1
                               ON               k1.idkey=oi.idobj
              WHERE            k1.idkey           IS NULL
              
              UNION ALL
              
              SELECT DISTINCT od.idobj
              FROM            objdsc od
                              LEFT JOIN keys k2
                              ON              k2.idkey=od.idobj
              WHERE           k2.idkey          IS NULL
              )
              objprop
       ON     OBJPROP.IDOBJ=O.IDKEY;

INSERT
INTO   keys
       (
              idkey       ,
              keynam      ,
              keytyp      ,
              keysubtyp   ,
              keyclass    ,
              keyprop     ,
              objtyp      ,
              idusrdevpro ,
              keydevdat   ,
              keydevvlddat,
              status      ,
              sqlowner
       )
SELECT idkey,
       ''   ,
       'K'  ,
       1,1,1,
       objtyp,
       1     ,
       now() ,
       now() ,
       1     ,
       'O'
FROM   objects
WHERE  idkey IN
                 ( SELECT DISTINCT idobj
                 FROM             wk_ObjToDelete
                 );

SELECT INT_OBJECT_DEL();

DROP TABLE WK_ObjToDelete;

Notes/comments

Ticket # 8216

Related Pages