analytics/configuration/schemas/content/db2/dbUpgrade4_0005-to-4_0006_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 CMOBJPROPS51 (
	CMID			integer not null ,
	SERVERGROUP		varchar (512),
	RUNASOWNER		decimal(1,0),
	CANBURST		decimal(1,0),
	SCHEDTRIGNAME	varchar(255),
	CONSTRAINT PK_CMOBJPROPS51 PRIMARY KEY (CMID) 
)

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (19))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (80))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (37))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (10))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (139))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (85))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (87))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (151))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (152))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (153))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (154))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (39))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (1))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (33))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (18))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (17))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (55))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (4))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (34))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (14))

insert into CMOBJPROPS51 (CMID,SERVERGROUP,RUNASOWNER,CANBURST,SCHEDTRIGNAME) select p.CMID, p.SERVERGROUP, p.RUNASOWNER, p.CANBURST, p.SCHEDTRIGNAME from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (187))

alter table CMOBJPROPS9 add column CANBURST decimal(1,0)

update CMOBJPROPS9 set CANBURST = (select CANBURST from CMOBJPROPS11 where CMOBJPROPS9.CMID = CMID)

CREATE TABLE CMOBJPROPS52 (
	CMID			integer NOT NULL ,
	CONNECTSTR		varchar (2048),
	ISOLEVEL		smallint,
	QUALIFIER		smallint,
	REPLACEMENT		varchar (512),
	CLOSECONNECTCMD		clob(1M),
	OPENCONNECTCMD		clob(1M),
	CLOSESESSCMD		clob(1M),
	OPENSESSCMD		clob(1M),
	CONSTRAINT PK_CMOBJPROPS52 PRIMARY KEY (CMID) 
)

insert into CMOBJPROPS52 (CMID,CLOSECONNECTCMD,OPENCONNECTCMD,CLOSESESSCMD,OPENSESSCMD) select p.CMID, p.CLOSECONNECTCMD, p.OPENCONNECTCMD, p.CLOSESESSCMD, p.OPENSESSCMD from CMOBJPROPS26 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (9))

insert into CMOBJPROPS52 (CMID,CLOSECONNECTCMD,OPENCONNECTCMD,CLOSESESSCMD,OPENSESSCMD) select p.CMID, p.CLOSECONNECTCMD, p.OPENCONNECTCMD, p.CLOSESESSCMD, p.OPENSESSCMD from CMOBJPROPS26 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (12))

insert into CMOBJPROPS52 (CMID,QUALIFIER,REPLACEMENT) select p.CMID, p.QUALIFIER, p.REPLACEMENT from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (42))

update CMOBJPROPS52 set CONNECTSTR = (select CONNECTSTR from CMOBJPROPS11 where CMOBJPROPS52.CMID = CMID), ISOLEVEL = (select ISOLEVEL from CMOBJPROPS11 where CMOBJPROPS52.CMID = CMID)

CREATE TABLE CMOBJPROPS53 (
	CMID			integer NOT NULL ,
	SEQUENCING		smallint,
	CONSTRAINT PK_CMOBJPROPS53 PRIMARY KEY (CMID) 
)

insert into CMOBJPROPS53 (CMID,SEQUENCING) select p.CMID, p.SEQUENCING from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (55))

alter table CMOBJPROPS28 add column SEQUENCING smallint add column ALLOWNOTIFICATION decimal(1,0)

insert into CMOBJPROPS28 (CMID,ALLOWNOTIFICATION) select p.CMID, p.ALLOWNOTIFICATION from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (87))

update CMOBJPROPS28 set SEQUENCING = (select SEQUENCING from CMOBJPROPS11 where CMOBJPROPS28.CMID = CMID), ALLOWNOTIFICATION = (select ALLOWNOTIFICATION from CMOBJPROPS11 where CMOBJPROPS28.CMID = CMID)

alter table CMOBJPROPS48 add column CAPACITY float add column LOADBALMODE smallint

update CMOBJPROPS48 set CAPACITY = (select CAPACITY from CMOBJPROPS11 where CMOBJPROPS48.CMID = CMID), LOADBALMODE = (select LOADBALMODE from CMOBJPROPS11 where CMOBJPROPS48.CMID = CMID)

alter table CMOBJPROPS50 add column LASTPAGE varchar(512)

update CMOBJPROPS50 set LASTPAGE = (select LASTPAGE from CMOBJPROPS11 where CMOBJPROPS50.CMID = CMID)

CREATE TABLE CMOBJPROPS54 (
	CMID			integer NOT NULL ,
	FORMAT			smallint,
	OLOCALEID		smallint,
	CONSTRAINT PK_CMOBJPROPS54 PRIMARY KEY (CMID) 
)

insert into CMOBJPROPS54 (CMID,FORMAT,OLOCALEID) select p.CMID, p.FORMAT, p.OLOCALEID from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (11))

insert into CMOBJPROPS54 (CMID,FORMAT,OLOCALEID) select p.CMID, p.FORMAT, p.OLOCALEID from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (179))

insert into CMOBJPROPS54 (CMID,FORMAT,OLOCALEID) select p.CMID, p.FORMAT, p.OLOCALEID from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (183))

insert into CMOBJPROPS54 (CMID,FORMAT,OLOCALEID) select p.CMID, p.FORMAT, p.OLOCALEID from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (27))

insert into CMOBJPROPS54 (CMID,FORMAT,OLOCALEID) select p.CMID, p.FORMAT, p.OLOCALEID from CMOBJPROPS11 p where p.CMID in (select o.CMID from CMOBJECTS o where o.CLASSID in (5))

delete from CMOBJPROPS26 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (9))

delete from CMOBJPROPS26 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (12))

alter table CMOBJPROPS26 drop constraint PK_CMOBJPROPS26

rename CMOBJPROPS26 to CMOBJPROPS26_OLD

CREATE TABLE CMOBJPROPS26 (
	CMID			integer not null,
	DELIVOPTIONS			clob(1M),
	constraint PK_CMOBJPROPS26 primary key (CMID)
)

insert into CMOBJPROPS26(CMID, DELIVOPTIONS) select CMID, DELIVOPTIONS from CMOBJPROPS26_OLD

drop table CMOBJPROPS26_OLD

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (1))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (4))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (5))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (9))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (10))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (11))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (14))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (17))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (18))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (19))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (27))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (33))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (34))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (37))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (39))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (42))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (55))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (80))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (85))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (87))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (139))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (151))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (152))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (153))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (154))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (179))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (183))

delete from CMOBJPROPS11 where CMID in (select o.CMID from CMOBJECTS o where CLASSID in (187))

alter table CMOBJPROPS11 drop constraint PK_CMOBJPROPS11

rename CMOBJPROPS11 to CMOBJPROPS11_OLD

create table CMOBJPROPS11 (
	CMID			integer not null ,
	STATUSID		smallint,
	EVENTID			varchar(45),
	OWNEREVENTID		varchar(45),
	CONSTRAINT PK_CMOBJPROPS11 PRIMARY KEY (CMID) 
)

insert into CMOBJPROPS11(CMID, STATUSID, EVENTID, OWNEREVENTID) select CMID, STATUSID, EVENTID, OWNEREVENTID from CMOBJPROPS11_OLD

drop table CMOBJPROPS11_OLD