analytics/configuration/schemas/content/db2/dbUpgrade4_0102-to-4_0103_db2.sql

-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2009
--
-- 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 table CMOBJECTS_OLD (
	CMID		integer not null,
	PCMID		integer not null,
	VERSION		integer default 1 not null,
	CREATED		timestamp not null,
	MODIFIED	timestamp not null,
	DISABLED	smallint,
	CLASSID		smallint not null,
	DISPSEQ		integer with default 0 not null,
	OWNER		integer
)

insert into CMOBJECTS_OLD
	select CMID,PCMID,VERSION,CREATED,MODIFIED,DISABLED,CLASSID,DISPSEQ,OWNER
       	from CMOBJECTS

drop view CMOWNERCOUNT

drop table CMOBJECTS

alter table CMOBJECTS_OLD add constraint PK_CMOBJECTS primary key (CMID)

alter table CMOBJECTS_OLD add constraint PK_CMPARENT unique (PCMID,CMID)

rename table CMOBJECTS_OLD to CMOBJECTS

create index IDX_CLASS on CMOBJECTS(CLASSID)

create index IDX_CLASS1 on CMOBJECTS(CMID,CLASSID)

create index IDX_PARENT on CMOBJECTS(PCMID)

create index IDX_CREATED on CMOBJECTS(CREATED)

create view CMOWNERCOUNT(OBJID,OWN_NUMBER) as
	select p2.OBJID, count(o.CMID)
	from CMOBJECTS o left outer join CMREFNOORD2 p1 on p1.CMID=o.CMID
	left outer join CMOBJPROPS1 p2 on p1.REFCMID=p2.CMID
	group by p2.OBJID

alter table CMOBJPROPS55 add HIDDEN decimal(1,0) default 0 not null