analytics/configuration/schemas/content/oracle/dbUpgrade4_0005-to-4_0006_oracle.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			number(10) NOT NULL ,
	SERVERGROUP		varchar2 (768) null,
	RUNASOWNER		number(1) null,
	CANBURST		number(1) null,
	SCHEDTRIGNAME		varchar2(768) NULL,
	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 (CANBURST number(1) null);

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

CREATE TABLE CMOBJPROPS52 (
	CMID			number(10) NOT NULL ,
	CONNECTSTR		varchar2 (2048) null,
	ISOLEVEL		number(3) null,
	QUALIFIER		number(3) null,
	REPLACEMENT		varchar2 (768) null,
	CLOSECONNECTCMD		clob null,
	OPENCONNECTCMD		clob null,
	CLOSESESSCMD		clob null,
	OPENSESSCMD		clob null,
	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			number(10) NOT NULL ,
	SEQUENCING		number(3) null,
	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 (SEQUENCING number(3) null, ALLOWNOTIFICATION number(1) null);

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 (CAPACITY float null, LOADBALMODE number(3) null);

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 LASTPAGE varchar2(768) null;

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

CREATE TABLE CMOBJPROPS54 (
	CMID			number(10) NOT NULL ,
	FORMAT			number(3) null,
	OLOCALEID		number(5) null,
	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 (CLOSECONNECTCMD,OPENCONNECTCMD,CLOSESESSCMD,OPENSESSCMD);

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 (SERVERGROUP, RUNASOWNER, CANBURST, SCHEDTRIGNAME, CONNECTSTR, ISOLEVEL, QUALIFIER, REPLACEMENT, SEQUENCING, ALLOWNOTIFICATION, CAPACITY, LOADBALMODE, LASTPAGE, FORMAT, OLOCALEID);