analytics/configuration/schemas/content/db2/dbUpgrade4_0023-to-4_0024_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 CMOBJPROPS60 (
	CMID            integer not null,
	CONTEXT         clob(64K),
	CONTEXTCOUNT	integer,
	LASTPAGE	varchar(512),
	constraint PK_CMOBJPROPS60 primary key (CMID)
)

create table CMOBJPROPS61 (
	CMID			integer not null,
	BURSTKEY		varchar (3874),
	BURSTID			clob(64K),
	constraint PK_CMOBJPROPS61 primary key (CMID) 
)

alter table CMOBJPROPS28 add SPEC clob(1G)

alter table CMOBJPROPS20 drop constraint PK_CMOBJPROPS20

rename CMOBJPROPS20 to CMOBJPROPS20_OLD

create table CMOBJPROPS20 (
	CMID			integer not null,
	EXECLOCALEID	smallint,
	EXECFORMAT		smallint,
	EXECPAGEORIENT	smallint,
	EXECPROMPT		decimal(1,0) default 1 not null,
	DEFPORTACT		smallint,
	ALLOWSUBSCRIBE		decimal(1,0),
	constraint PK_CMOBJPROPS20 primary key (CMID)
)

insert into CMOBJPROPS20(CMID,EXECLOCALEID,EXECFORMAT,EXECPAGEORIENT,EXECPROMPT,DEFPORTACT) select CMID,EXECLOCALEID,EXECFORMAT,EXECPAGEORIENT,EXECPROMPT,DEFPORTACT from CMOBJPROPS20_old

drop table CMOBJPROPS20_OLD

UPDATE CMOBJPROPS20 set ALLOWSUBSCRIBE=0 WHERE CMID IN (SELECT CMID FROM CMOBJECTS WHERE CLASSID IN (10,19,37,80,139))

insert into CMOBJPROPS60(CMID) SELECT CMID FROM CMOBJECTS WHERE CLASSID=179

insert into CMOBJPROPS61(CMID, BURSTKEY) SELECT CMID, varchar(BURSTKEY, 3874) FROM CMOBJPROPS6A WHERE CMID in (SELECT CMID FROM CMOBJECTS WHERE CLASSID in (27,179))

drop table CMOBJPROPS6A