SQL Queries - Common SQL Queries - How to check the consistency on existing objects between the central and local databases

Purpose of Query

This query checks the consistency on existing objects between the central and local databases.

Applicable CAST Version
Release
Yes/No
8.3.x(tick)
Applicable RDBMS
RDBMS
Yes/No
CSS(tick)

.

Query for CSS

First obtain information on the local sites used by the central database with the following query (update the query below with the name of your central database):

SELECT SITE_ID             ,
       SITE_NAME           ,
       SITE_SYSTEM         ,
       SITE_SERVER         ,
       SITE_USER           ,
       SITE_SRVADDRESS_TYPE,
       SITE_SRVADDRESS
FROM             <Central Name>.DSS_SITES
WHERE  SITE_TYPE =2
Query result example
 248851048;"test820_local";"N/A";"host";"operator";"PostgreSQL";"//<server>:2280/postgres"
Query result interpretation
 Site information for local sites


The check for consistency in the following queries using the site_id of the local database from the above query which you wish to check


The first query checks for objects which have the same id but a distinct name. Replace the site id and local and central database names in the query below:


SELECT OC.OBJECT_ID       ,
       OC.OBJECT_TYPE_ID  ,
       OL.OBJECT_TYPE_ID  ,
       OC.OBJECT_FULL_NAME,
       OL.OBJECT_FULL_NAME
FROM                    <Central Name>.DSS_OBJECTS OC         ,
                        <Central Name>.DSS_TRANSLATION_TABLE T,
                        <Local Name>.DSS_OBJECTS OL
WHERE  OC.OBJECT_ID     = T.OBJECT_ID
AND    T.SITE_OBJECT_ID = OL.OBJECT_ID
AND    T.SITE_ID        = <site id>
AND
       (
              OC.OBJECT_NAME != OL.OBJECT_NAME
       )
Query result example

No results are expected

Query result interpretation

 No rows should be returned for consistent data. If you do have rows, returned then please contact CAST support: CAST Technical Support.

with dumps of your databases and the results of the query above.


The second query checks for objects which have the same name and same id but a distinct type. Replace the site id and local and central database names in the query below:


SELECT OC.OBJECT_ID       ,
       OC.OBJECT_TYPE_ID  ,
       OL.OBJECT_TYPE_ID  ,
       OC.OBJECT_FULL_NAME,
       OL.OBJECT_FULL_NAME
FROM                      <Central Name>.DSS_OBJECTS OC         ,
                          <Central Name>.DSS_TRANSLATION_TABLE T,
                          <Local Name>.DSS_OBJECTS OL
WHERE  OC.OBJECT_ID       = T.OBJECT_ID
AND    T.SITE_OBJECT_ID   = OL.OBJECT_ID
AND    T.SITE_ID          = <Site id>
AND    OC.OBJECT_NAME     = OL.OBJECT_NAME
AND    OC.OBJECT_TYPE_ID != OL.OBJECT_TYPE_ID
Query result example

 No results are expected

Query result interpretation
No rows should be returned for consistent data. If you do have rows, returned then please contact CAST support: CAST Technical Support.

with dumps of your databases and the results of the query above.

Notes/comments



Related Pages