analytics/configuration/schemas/content/sqlserver/dbUpgrade4_0005-to-4_0006_mssqlserver.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).


-- Reducing the number of columns and rows in CMOBJPROPS11
-- Move package, folder, content, baseReport, authoredReport, baseAgentDefinition(runAsOwner), baseDataIntegration, schedule, reportVersion(serverGroup), jobDefinition(serverGroup) configuration(serverGroup), configurationFolder(serverGroup) and dispatcher(serverGroup) object rows
CREATE TABLE CMOBJPROPS51 (
	CMID			int NOT NULL ,
	SERVERGROUP		nvarchar (256) null,
	RUNASOWNER		bit null,
	CANBURST		bit null,
	SCHEDTRIGNAME	nvarchar(256) 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))

-- Move reportVersion object rows
alter table CMOBJPROPS9 add CANBURST bit null

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

-- Consolidate dataSourceNameBinding, dataSourceConnection and dataSource object rows to new table
CREATE TABLE CMOBJPROPS52 (
	CMID			int NOT NULL ,
	CONNECTSTR		nvarchar (1024) NULL ,
	ISOLEVEL		tinyint NULL ,
	QUALIFIER		tinyint NULL ,
	REPLACEMENT		nvarchar (256) NULL ,
	CLOSECONNECTCMD		ntext NULL ,
	OPENCONNECTCMD		ntext NULL ,
	CLOSESESSCMD		ntext NULL ,
	OPENSESSCMD		ntext 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)

-- Move jobDefinition object rows
CREATE TABLE CMOBJPROPS53 (
	CMID			int NOT NULL ,
	SEQUENCING		tinyint 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))

-- Move agentDefinition and baseAgentDefinition(allowNotification) object rows
alter table CMOBJPROPS28 add SEQUENCING tinyint NULL, ALLOWNOTIFICATION bit 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)

-- Move configuration, configurationFolder and dispatcher object rows
alter table CMOBJPROPS48 add CAPACITY float null, LOADBALMODE tinyint null

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

-- Move output(lastPage) property rows to output table
alter table CMOBJPROPS50 add LASTPAGE nvarchar(256) null

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

-- Move special property (locale and format) rows of contact, documentContent, cacheOutput, output and account objects to new table
CREATE TABLE CMOBJPROPS54 (
	CMID			int NOT NULL ,
	FORMAT			tinyint NULL ,
	OLOCALEID		smallint 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 column CLOSECONNECTCMD, column OPENCONNECTCMD, column CLOSESESSCMD, column 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 column SERVERGROUP, column RUNASOWNER, column CANBURST, column SCHEDTRIGNAME, CONNECTSTR, column ISOLEVEL, column QUALIFIER, column REPLACEMENT, column SEQUENCING, column ALLOWNOTIFICATION, column CAPACITY, column LOADBALMODE, column LASTPAGE, column FORMAT, column OLOCALEID