analytics/configuration/schemas/content/db2/dbMakeConsistent_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).


insert into CMOBJNAMES (CMID, LOCALEID, MAPDLOCALEID, ISDEFAULT, NAME) select CMID, 24, 24, 1, CONCAT( 'REPAIRED BY CONSISTENCY CHECK ', VARCHAR( TIMESTAMP( GENERATE_UNIQUE() ))) from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES);

insert into CMPOLICIES (CMID) select CMID from CMOBJECTS where CMID not in (select CMID from CMPOLICIES);

insert into CMSTOREIDS (CMID, STOREID) select CMID, SUBSTR('i00000000000000000000000000000000',1,33 - LENGTH(RTRIM(CAST(CMID AS CHAR(10))))) || RTRIM(CAST(CMID AS CHAR(10))) from CMOBJECTS where CMID not in (select CMID from CMSTOREIDS);

update CMSTOREIDS set STOREID = NULL where UPPER(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);

update CMSTOREIDS set STOREID = SUBSTR('i00000000000000000000000000000000',1,33 - LENGTH(RTRIM(CAST(CMID AS CHAR(10))))) || RTRIM(CAST(CMID AS CHAR(10))) where CMID in (select CMID from CMSTOREIDS where STOREID is null);

delete from CMOBJNAMES where CMID not in (select CMID from CMOBJECTS);

delete from CMDATA where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS1 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS10 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS11 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS13 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS14 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS15 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS16 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS17 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS18 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS2 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS20 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS23 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS24 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS25 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS26 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS27 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS28 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS30 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS31 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS32 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS33 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS34 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS35 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS36 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS37 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS38 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS39 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS3 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS4 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS40 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS41 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS42 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS43 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS44 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS45 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS46 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS47 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS48 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS49 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS5 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS50 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS51 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS52 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS53 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS54 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS55 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS56 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS57 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS58 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS59 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS6 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS60 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS61 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS62 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS63 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS64 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS65 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS66 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS67 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS68 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS69 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS7 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS70 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS71 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS72 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS73 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS74 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS75 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS76 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS77 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS78 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS79 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS80 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS81 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS82 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS83 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS84 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS85 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS86 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS87 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS88 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS89 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS9 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS90 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS91 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS92 where CMID not in (select CMID from CMOBJECTS);

delete from CMOBJPROPS93 where CMID not in (select CMID from CMOBJECTS);

delete from CMPOLICIES where CMID not in (select CMID from CMOBJECTS);

delete from CMVIEWED where CMID not in (select CMID from CMOBJECTS);

delete from CMGUIDS where CMID not in (select CMID from CMOBJECTS);

delete from CMREFNOORD1 where CMID not in (select CMID from CMOBJECTS);

delete from CMREFNOORD2 where CMID not in (select CMID from CMOBJECTS);

delete from CMREFORD1 where CMID not in (select CMID from CMOBJECTS);

delete from CMREFORD2 where CMID not in (select CMID from CMOBJECTS);

delete from CMSTOREIDS where CMID not in (select CMID from CMOBJECTS);

delete from CMARCHIVESTATUS where CMID not in (select CMID from CMOBJECTS);

delete from CMTAGS where CMID not in (select CMID from CMOBJECTS);