analytics/configuration/schemas/content/db2/dbCheckConsistency_db2.sql

-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2019
--
-- US Government Users Restricted Rights - Use, duplication or disclosure
-- restricted by GSA ADP Schedule Contract with IBM Corp.

-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).


-- create a temporary table

declare global temporary table CMCHECKCONSISTENCY (inconsistency integer not null, description varchar(255), CMID integer) with replace on commit preserve rows not logged;

-- Records with inconsistency = 1 are related to CMIDs in property tables and not in CMOBJECTS table.
-- Records with inconsistency = 2 are related to CMIDs in CMOBJECTS table but not in CMOBJNAMES or CMPOLICIES tables.
-- Records with inconsistency = 3 are related to CMIDs in CMSTOREIDS table but STOREID value is null.
-- Records with inconsistency = 4 are related to CMIDs in CMSTOREIDS table which have non-unique STOREIDs.

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJNAMES', CMID from CMOBJNAMES  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMDATA', CMID from CMDATA  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS1', CMID from CMOBJPROPS1  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS2', CMID from CMOBJPROPS2  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS3', CMID from CMOBJPROPS3  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS4', CMID from CMOBJPROPS4  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS5', CMID from CMOBJPROPS5  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS6', CMID from CMOBJPROPS6  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS7', CMID from CMOBJPROPS7  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS9', CMID from CMOBJPROPS9  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS10', CMID from CMOBJPROPS10  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS11', CMID from CMOBJPROPS11  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS13', CMID from CMOBJPROPS13  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS14', CMID from CMOBJPROPS14  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS15', CMID from CMOBJPROPS15  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS16', CMID from CMOBJPROPS16  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS17', CMID from CMOBJPROPS17  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS18', CMID from CMOBJPROPS18  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS20', CMID from CMOBJPROPS20  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS23', CMID from CMOBJPROPS23  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS24', CMID from CMOBJPROPS24  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS25', CMID from CMOBJPROPS25  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS26', CMID from CMOBJPROPS26  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS27', CMID from CMOBJPROPS27  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMPOLICIES', CMID from CMPOLICIES  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMVIEWED', CMID from CMVIEWED  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMGUIDS', CMID from CMGUIDS  where CMID not in (select CMID from CMOBJECTS);

-- 1.1 only tables
--#COMMENT 
--#COMMENT Ignore if the following insert failes

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS8', CMID from CMOBJPROPS8  where CMID not in (select CMID from CMOBJECTS);

--#COMMENT Ignore if the following insert failes

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS12', CMID from CMOBJPROPS12  where CMID not in (select CMID from CMOBJECTS);

--#COMMENT Ignore if the following insert failes

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS19', CMID from CMOBJPROPS19  where CMID not in (select CMID from CMOBJECTS);

--#COMMENT Ignore if the following insert failes

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS21', CMID from CMOBJPROPS21  where CMID not in (select CMID from CMOBJECTS);

--#COMMENT Ignore if the following insert failes

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS22', CMID from CMOBJPROPS22  where CMID not in (select CMID from CMOBJECTS);


-- 2.0 tables

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS28', CMID from CMOBJPROPS28  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS30', CMID from CMOBJPROPS30  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS31', CMID from CMOBJPROPS31  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS32', CMID from CMOBJPROPS32  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS33', CMID from CMOBJPROPS33  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS34', CMID from CMOBJPROPS34  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS35', CMID from CMOBJPROPS35  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS36', CMID from CMOBJPROPS36  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS37', CMID from CMOBJPROPS37  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS38', CMID from CMOBJPROPS38  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS39', CMID from CMOBJPROPS39  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS40', CMID from CMOBJPROPS40  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS41', CMID from CMOBJPROPS41  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS42', CMID from CMOBJPROPS42  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS43', CMID from CMOBJPROPS43  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS44', CMID from CMOBJPROPS44  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS45', CMID from CMOBJPROPS45  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS46', CMID from CMOBJPROPS46  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMREFNOORD1', CMID from CMREFNOORD1  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMREFNOORD2', CMID from CMREFNOORD1  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMREFORD1', CMID from CMREFORD1  where CMID not in (select CMID from CMOBJECTS);


insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMSTOREIDS', CMID from CMSTOREIDS  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(4 as integer),'CMSTOREIDS', s.CMID from CMSTOREIDS s where UPPER(s.STOREID) in (select UPPERSTOREIDS.STOREID from (select b.CMID, UPPER(b.STOREID) as STOREID from CMSTOREIDS b) as UPPERSTOREIDS group by UPPERSTOREIDS.STOREID having COUNT(UPPERSTOREIDS.CMID) > 1);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(3 as integer),'CMSTOREIDS', CMID from CMSTOREIDS  where STOREID is null;

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(2 as integer),'CMOBJECTS', CMID from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES) or CMID not in (select CMID from CMPOLICIES);


-- 3.0 tables:
insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS47', CMID from CMOBJPROPS47  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS48', CMID from CMOBJPROPS48  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS49', CMID from CMOBJPROPS49  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS50', CMID from CMOBJPROPS50  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS51', CMID from CMOBJPROPS51  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS52', CMID from CMOBJPROPS52  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS53', CMID from CMOBJPROPS53  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS54', CMID from CMOBJPROPS54  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS55', CMID from CMOBJPROPS55  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS56', CMID from CMOBJPROPS56  where CMID not in (select CMID from CMOBJECTS);

-- 3.0 tables

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS57', CMID from CMOBJPROPS57  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS58', CMID from CMOBJPROPS58  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS59', CMID from CMOBJPROPS59  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS60', CMID from CMOBJPROPS60  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS61', CMID from CMOBJPROPS61  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS62', CMID from CMOBJPROPS62  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS63', CMID from CMOBJPROPS63  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS64', CMID from CMOBJPROPS64  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS65', CMID from CMOBJPROPS65  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS66', CMID from CMOBJPROPS66  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS67', CMID from CMOBJPROPS67  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS68', CMID from CMOBJPROPS68  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS69', CMID from CMOBJPROPS69  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS70', CMID from CMOBJPROPS70  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS71', CMID from CMOBJPROPS71  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS72', CMID from CMOBJPROPS72  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS73', CMID from CMOBJPROPS73  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS74', CMID from CMOBJPROPS74  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS75', CMID from CMOBJPROPS75  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS76', CMID from CMOBJPROPS76  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS77', CMID from CMOBJPROPS77  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS78', CMID from CMOBJPROPS78  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS79', CMID from CMOBJPROPS79  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMREFORD2', CMID from CMREFORD2  where CMID not in (select CMID from CMOBJECTS); 

-- 6.0 tables

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS80', CMID from CMOBJPROPS80  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMARCHIVESTATUS', CMID from CMARCHIVESTATUS  where CMID not in (select CMID from CMOBJECTS);

-- 7.0 tables

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS81', CMID from CMOBJPROPS81  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS82', CMID from CMOBJPROPS82  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS83', CMID from CMOBJPROPS83  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS84', CMID from CMOBJPROPS84  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS85', CMID from CMOBJPROPS85  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS86', CMID from CMOBJPROPS86  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS87', CMID from CMOBJPROPS87  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS88', CMID from CMOBJPROPS88  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS89', CMID from CMOBJPROPS89  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS90', CMID from CMOBJPROPS90  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS91', CMID from CMOBJPROPS91  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS92', CMID from CMOBJPROPS92  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMOBJPROPS93', CMID from CMOBJPROPS93  where CMID not in (select CMID from CMOBJECTS);

insert into SESSION.CMCHECKCONSISTENCY(inconsistency, description, CMID)
select cast(1 as integer),'CMTAGS', CMID from CMTAGS  where CMID not in (select CMID from CMOBJECTS);


--CMSCRIPT_SKIP_COMMAND
--#COMMENT 
--#COMMENT CMIDs not in CMOBJECTS table
select description, CMID from SESSION.CMCHECKCONSISTENCY where inconsistency = 1 order by 1,2;

--CMSCRIPT_SKIP_COMMAND
--#COMMENT 
--#COMMENT CMIDs in CMOBJECTS table but not in CMOBJNAMES or CMPOLICIES tables
select description, CMID from SESSION.CMCHECKCONSISTENCY where inconsistency = 2 order by 1,2;

--CMSCRIPT_SKIP_COMMAND
--#COMMENT 
--#COMMENT CMIDs in CMSTOREIDS table where STOREID is null
select description, CMID from SESSION.CMCHECKCONSISTENCY where inconsistency = 3 order by 1,2;

--CMSCRIPT_SKIP_COMMAND
--#COMMENT 
--#COMMENT CMIDs in CMSTOREIDS table which have duplicate STOREIDs
select description, CMID from SESSION.CMCHECKCONSISTENCY where inconsistency = 4 order by 1,2;