analytics/configuration/schemas/content/db2/dbInitScript_db2.sql

-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2020
--
-- 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 (
	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			integer not null,
	DISPSEQ			integer with default 0 not null,
	OWNER			integer, 
	TENANTID		integer with default 0 not null,
	constraint PK_CMOBJECTS primary key (CMID),
	constraint PK_CMPARENT unique (PCMID, CMID)
)

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 table CMOBJNAMES (
	CMID			integer			not null,
	LOCALEID		smallint		not null,
	MAPDLOCALEID	smallint,
	ISDEFAULT		decimal(1,0)	not null,
	NAME			varchar(255)	not null,
	constraint		PK_CMOBJNAMES	primary key (CMID, LOCALEID)
)
		
create table CMDATA (
	CMID			integer NOT NULL ,
	CONTENTTYPE		varchar (256),
	DATASIZE		bigint,
	DATACOMP		decimal(1,0) default 0 not null,
	DATAPROP		blob(1G), 
	DATAURI			varchar (256),
	RESOURCETYPE	varchar (32),
	constraint PK_CMDATA primary key (CMID)
) CMSCRIPT_CMDATA

CREATE TABLE CMARCHIVESTATUS (
	CMID			integer NOT NULL,
	ARCHSTAT		smallint default 0 not null, 
	CONSTRAINT PK_CMARCHIVESTATUS PRIMARY KEY (CMID)
)

CREATE TABLE CMSTOREIDS (
	CMID        integer NOT NULL,
	STOREID     char(33),
	CONSTRAINT PK_CMSTOREIDS PRIMARY KEY (CMID)
)

create table CMOBJPROPS1 (
	CMID				integer not null,
	BUSPHONE			varchar (32),
	COMMONNAME			varchar (64),
	EMAIL				varchar (128),
	FAXPHONE			varchar (32),
	GIVENNAME			varchar (128),
	HOMEPHONE			varchar (32),
	MOBILEPHONE			varchar (32),
	PAGERPHONE			varchar (32),
	PADDRESS			varchar (512),
	SURNAME				varchar (128),
	TIMEZONE			varchar (128),
	CLOCALEID			smallint ,
	PRODLOCALE			varchar (64),
	OBJID				varchar (1024),
	USEACCESSIBILITY		decimal(1,0) default 0,
	USERNAME			varchar (128),
	ORIENTATION			smallint,
	HORIZRENDERLIMIT	integer,
	VERTRENDERLIMIT		integer,
	MOBILEDEVID			varchar (512),
	NOTIFICATIONEMAIL		varchar (512),
	constraint PK_CMOBJPROPS1 primary key (CMID)
)

create table CMOBJPROPS10 (
	CMID			integer not null ,
	CONTACTEMAIL	varchar (128) ,
	CONTACT			clob (4000), 
	constraint PK_CMOBJPROPS10 primary key (CMID) 
)

create table CMOBJPROPS11 (
	CMID			integer not null ,
	STATUSID		smallint,
	EVENTID			varchar(45),
	OWNEREVENTID		varchar(45),
	DISPATCHERID		varchar(50),
	MAXDETAILSEVTY		smallint,
	SCHEDTRIGNAME		varchar(512),
	SCHEDTYPE		smallint,
	RESTARTEVENTID		varchar(45),
	CONSTRAINT PK_CMOBJPROPS11 PRIMARY KEY (CMID) 
)

create index IDX_CMOWNEREVENTID on CMOBJPROPS11(OWNEREVENTID)

create index IDX_CMRESTARTID on CMOBJPROPS11(RESTARTEVENTID)

create table CMOBJPROPS13 (
	CMID			integer not null,
	ORD			integer not null,
	PARMNAME		varchar(512) not null,
	CPARMVALUE		blob(1G),
	constraint PK_CMOBJPROPS13 primary key (CMID, ORD)
)

create table CMOBJPROPS14 (
	CMID			integer not null,
	RELATED			clob(1G),
	MYPAGE			clob(1G),
	RETENTION		clob(1G),
	RECIPSEMAIL		clob(1G),
	CONSTRAINT PK_CMOBJPROPS14 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS15 (
	CMID			integer not null,
	BRSHIAFFINECONS	integer,
	BRSAFFINECONS		integer,
	BRSMAXPROCS		integer,
	METADATAMODEL	clob(4000),
	BRSEXECTIMELIMIT	integer,
	BRSDATASOURCECHG	timestamp,
	constraint PK_CMOBJPROPS15 primary key (CMID)
)

create table CMOBJPROPS16 (
	CMID			integer not null,
	RSHIAFFINECONS	integer,
	RSAFFINECONS		integer,
	RSMAXPROCS		integer,
	RSQUEUELIMIT		integer,
	RUNNINGSTATE	smallint,
	ADVSETTINGS		clob(4000),
	RSEXECTIMELIMIT	integer,
	ROUTINGTABLE	blob(1M), 
	constraint PK_CMOBJPROPS16 primary key (CMID)
)

create table CMOBJPROPS17 (
	CMID				integer not null,
	NONPEAKBEGINHOUR	smallint,
	NONPEAKMAXJOBS		integer,
	PEAKBEGINHOUR		smallint,
	PEAKMAXJOBS			integer,
	NEWNONPEAKBEGINHOUR	smallint,
	NEWNONPEAKMAXJOBS		integer,
	NEWPEAKBEGINHOUR		smallint,
	NEWPEAKMAXJOBS			integer,
	USERCAPABILITY		smallint,
	STARTACTIVE			decimal(1,0),
	COMPONENTID 		smallint,
	PREVIMGLOCATION		varchar(255),
	RESRCELOCATION		varchar(255),
	constraint PK_CMOBJPROPS17 primary key (CMID)
)

create table CMOBJPROPS18 (
	CMID				integer not null,
	OUTPUTREFS			clob(1M),
	PATHS				clob(1M),
	constraint PK_CMOBJPROPS18 primary key (CMID)
)

create table CMOBJPROPS2 (
	CMID			integer not null,
	TASKID			varchar(32),
	STARTDATE		timestamp,
	ENDDATE			timestamp,
	ENDTYPE			smallint,
	EVERYNPERIODS	integer,
	DAILYPERIOD		smallint,
	MONTHLYABSDAY	smallint,
	MONTHLYRELDAY	smallint,
	MONTHLYRELWEEK	smallint,
	TYPE			smallint,
	YEARLYABSDAY	smallint,
	YEARLYABSMONTH	smallint,
	YEARLYRELDAY	smallint,
	YEARLYRELMONTH	smallint,
	YEARLYRELWEEK	smallint,
	ACTIVE			decimal(1,0) default 0 not null,
	WEEKLYMONDAY	decimal(1,0) default 0 not null,
	WEEKLYTUESDAY	decimal(1,0) default 0 not null,
	WEEKLYWEDNESDAY	decimal(1,0) default 0 not null,
	WEEKLYTHURSDAY	decimal(1,0) default 0 not null,
	WEEKLYFRIDAY	decimal(1,0) default 0 not null,
	WEEKLYSATURDAY	decimal(1,0) default 0 not null,
	WEEKLYSUNDAY	decimal(1,0) default 0 not null, 
	PRIORITY	integer,
	INTRARECURSTART		char (13),
	INTRARECUREND		char (13),
	INTRARECURINTERVAL	varchar (50),
	constraint PK_CMOBJPROPS2 primary key (CMID) 
)

create index IDX_TASKID on CMOBJPROPS2(TASKID)

create index IDX_PRIORITY on CMOBJPROPS2(PRIORITY)

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),
	ALLOWNOTIFICATION  decimal(1,0),
	OBJREFS            clob(1M),
	constraint PK_CMOBJPROPS20 primary key (CMID)
)

create table CMOBJPROPS23 (
	CMID			integer not null,
	LSAUDITADMINLVL	smallint,
	LSAUDITLVL		smallint,
	LSAUDITOTHERLVL	smallint,
	LSAUDITUSAGELVL	smallint,
	LSAUDITNATIVEQRY	decimal(1,0),
	QSRMEMCACHEROOT 	varchar(255),
	constraint PK_CMOBJPROPS23 primary key (CMID)
)

create table CMOBJPROPS24 (
	CMID			integer not null,
	LOCATION		varchar(1024),
	MODELNAME		varchar(512),
	PRINTERADDRESS	varchar(2048),
	constraint PK_CMOBJPROPS24 primary key (CMID)
)

create table CMOBJPROPS25 (
	CMID				integer not null,
	DEPLOYOBJECT		clob (4000),
	DEPLOYOBJANC		clob(1M),
	DEPLOYOBJCLASSID	integer,
	DEPLOYOBJDEFNAME	varchar(255),
	DEPLOYOBJDEFSTATUS	smallint,
	DEPLOYOBJUSAGE		smallint,
	MESSAGE				blob(1M),
	constraint PK_CMOBJPROPS25 primary key (CMID)
)

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

CREATE TABLE CMOBJPROPS27 (
	CMID				integer not null,
	CUBECONTNAME			varchar (2048),
	GATEWAY				varchar (1024),
	HASPROMPTS			decimal(1,0) default 0 not null,
	PPOPTIONS			clob (4000),
	DATABLOCKS			blob(1G),
	PPDEFPORTACT			smallint,
	constraint PK_CMOBJPROPS27 primary key (CMID)
)

CREATE TABLE CMOBJPROPS28 (
	CMID            integer not null,
	EVENTKEY        clob (4000),
	EVENTTYPES      clob (4000),
	FILTERDATAITEM	varchar (512),
	PARMASSIGN	blob(1M), 
	SEQUENCING	smallint,
	ALLOWNOTIFICATION decimal(1,0),
	DEFPORTACTION		smallint,
	RUNCOND		smallint,
	SPEC		clob(1G),	
	constraint PK_CMOBJPROPS28 primary key (CMID) 
)

create table CMOBJPROPS3 (
	CMID			integer not null,
	PROPNAME		varchar(50) not null,
	LOCALEID		smallint not null,
	VALUE			varchar (3700),
	ISDEFAULT		decimal(1,0),
	MAPDLOCALEID		smallint,
	constraint PK_CMOBJPROPS3 primary key (CMID, PROPNAME, LOCALEID) 
)

create table CMOBJPROPS30 (
	CMID            integer not null,
	HANDLE          varchar(255),
	BINDING         clob(1M),
	REGISTRATION    clob(1M),
	SERVICEDESCR    clob(1M),
	LAYOUT          clob(1M),
	REQUIREDCAP	clob(1M),
	CANCUSTOMIZE	decimal(1,0),
	CONTEXT         clob(100M),
	DEPLOYREFS	clob(1M),
	constraint PK_CMOBJPROPS30 primary key (CMID)
)

CREATE TABLE CMOBJPROPS31 (
	CMID			integer not null,
	ASAUDITLVL		smallint,
	BRSAUDITLVL		smallint,
	CMSAUDITLVL		smallint,
	DISPAUDITLVL		smallint,
	DSAUDITLVL		smallint,
	EMSAUDITLVL		smallint,
	JSAUDITLVL		smallint,
	MDSAUDITLVL		smallint,
	MMSAUDITLVL		smallint,
	MSAUDITLVL		smallint,
	PACSAUDITLVL		smallint,
	PDSAUDITLVL		smallint,
	PRSAUDITLVL		smallint,
	PSAUDITLVL		smallint,
	PTSAUDITLVL		smallint,
	RSAUDITLVL		smallint,
	SSAUDITLVL		smallint,
	BRSAUDITNATIVEQRY	decimal(1,0),
	RSAUDITNATIVEQRY	decimal(1,0),
	DISAUDITLVL		smallint,
	RSDATASOURCECHG	timestamp,
	IDSAUDITLVL		smallint,
	ISSAUDITLVL		smallint,
	IUSAUDITLVL		smallint,
	RDSAUDITLVL		smallint,
	RDSMAXDATASIZE		integer,
	MBSAUDITLVL		smallint,
	DDSAUDITLVL		smallint,
	DDSLISPERPRCSR		integer,
	DMSAUDITLVL		smallint,
	MISAUDITLVL		smallint,
	MDSAFFINECONNECTIONS	integer,
	MDSNONAFFINECONNCTS	integer,
	MDSPEAKCONS		integer,
	MDSPEAKNONAFFINECONNCTS	integer,
	MDSPEAKMAXPROCS 	integer,	
	MDSMAXPROCESSES		integer,	
	MDSEXECUTIONTIMELIMIT	integer,	
	MDSQUEUELIMIT		integer,
	MDINFOURI		clob(4000),
	DIMSAUDITLVL        	integer,
	EVSAUDITLVL        	integer,
	GSAUDITLVL		integer,
	AASAUDITLVL         	smallint,
	HTSAUDITLVL		smallint,
	CMCSAUDITLVL		smallint,
	QSAUDITLVL		smallint,
	BCSAUDITLVL		smallint,
	CSAUDITLVL		smallint,
	FSAUDITLVL		smallint,
	STSAUDITLVL		smallint,
	ANSAUDITLVL		smallint,
	RMDSAUDITLVL	smallint,
	IDVIZAUDITLVL 	smallint,
	REPOSAUDITLVL 	smallint,
	SACAMAUDITLVL 	smallint,
	constraint PK_CMOBJPROPS31 primary key (CMID)
)

create table CMOBJPROPS32 (
	CMID			integer not null,
	STOREDPROCNAME	varchar(255),
	constraint PK_CMOBJPROPS32 primary key (CMID)
)

create table CMOBJPROPS33 (
	CMID            integer not null,
	NAME            varchar(255),
	USERID          varchar(1024),
	LASTLOGIN       timestamp,
	CAPABILITIES    varchar(64),
	LICENSE         smallint,
	constraint PK_CMOBJPROPS33 primary key (CMID)
)

create table CMOBJPROPS34 (
	CMID			integer not null,
	ACTION			smallint,
	BOOKMARKITEM		varchar(255),
	BOOKMARKTEXT		varchar(255),
	DRILLPARMASSIGN		blob(1M),
	SCOPE			blob(1M),
	SPEC			clob(1G),	
	TARGETOPTIONS		clob(1M),
	DEPLOYREFS		clob(1M),
	constraint PK_CMOBJPROPS34 primary key (CMID)
)

create table CMOBJPROPS35 (
	CMID			integer not null,
	BINDINGNAME		varchar (512),
	INPUTMESSNAME		varchar (512),
	OPERATIONNAME		varchar (512),
	OUTPUTMESSNAME		varchar (512),
	SERVICENAME		varchar (512),
	BULKEVENTS		decimal(1,0),
	constraint PK_CMOBJPROPS35 primary key (CMID)
)

create table CMOBJPROPS36 (
	CMID			integer not null,
	ACTION			smallint,
	constraint PK_CMOBJPROPS36 primary key (CMID)
)

create table CMOBJPROPS37 (
	CMID			integer not null,
	ROUTINGHINTS	clob (4000),
	PROFILESETTINGS clob (100K),
	PROFILERANK     integer with default 0 not null,
	constraint PK_CMOBJPROPS37 primary key (CMID)
)

create table CMOBJPROPS38 (
	CMID				integer not null,
	CTITEMCOUNTDEFAULT	integer,
	CTITEMCOUNTLIMIT	integer,
	MBRITEMCOUNTDEFAULT	integer,
	MBRITEMCOUNTLIMIT	integer,
	constraint PK_CMOBJPROPS38 primary key (CMID)
)

CREATE TABLE CMOBJPROPS39 (
	PROPID		smallint NOT NULL,
	CMID		integer not null ,
	VALUE		smallint NOT NULL
)

create index IDX_CMOBJPROPS39 on CMOBJPROPS39 (CMID)
	
create table CMOBJPROPS4 (
	CMID			integer not null,
	HEIGHT			varchar(20),
	WIDTH			varchar(20),
	UNIT			smallint, 
	constraint PK_CMOBJPROPS4 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS40 (
	CMID			integer not null,
	APPGUID			varchar(50),
	APPID			varchar (512),
	APPSTATE		smallint,
	APPURL			clob (4000),
	constraint PK_CMOBJPROPS40 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS41 (
	CMID			integer not null,
	DEPTH			integer,
	ITEMID			varchar (512),
	PARENTID		varchar (512),
	CONSTRAINT PK_CMOBJPROPS41 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS42 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS42 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS43 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS43 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS44 (
	CMID			integer not null,
	RDSGATEWMAPPING		blob(1M),
	CONSTRAINT PK_CMOBJPROPS44 primary key (CMID) 
)

create table CMOBJPROPS45 (
	CMID			integer not null,
	LINK			smallint,
	constraint PK_CMOBJPROPS45 primary key (CMID)
)

CREATE TABLE CMOBJPROPS46 (
	CMID			integer not null,
	DOCTYPE			varchar (512),
	constraint PK_CMOBJPROPS46 primary key (CMID)
)

CREATE TABLE CMOBJPROPS47 (
	CMID			integer not null,
	SYSTEMMETRIC		smallint,
	PROPERTIES		blob(1M),
	constraint PK_CMOBJPROPS47 primary key (CMID)
)

CREATE TABLE CMOBJPROPS48 (
	CMID			integer not null,
	ASCONNECTIONS		integer,
	CMSCONNECTIONS		integer,
	DDSCONNECTIONS		integer,
	DISCONNECTIONS		integer,
	DSCONNECTIONS		integer,
	IDSCONNECTIONS		integer,
	ISSCONNECTIONS		integer,
	IUSCONNECTIONS		integer,
	JSCONNECTIONS		integer,
	MBSCONNECTIONS		integer,
	MMSCONNECTIONS		integer,
	PACSCONNECTIONS		integer,
	PDSCONNECTIONS		integer,
	PRSCONNECTIONS		integer,
	PTSCONNECTIONS		integer,
	BRSCHHOTSPOTLIMIT	integer,
	RSCHHOTSPOTLIMIT	integer,
	CAPACITY		float,
	LOADBALMODE		smallint,
	DMSCONNECTIONS		integer,
	MISCONNECTIONS 		integer,
	DSCOMPATTACHLIMIT	integer,
	QSDIAGENABLED		decimal(1,0),
	QSMETENABLED		decimal(1,0),
	QSQUERYEXECTR		decimal(1,0),
	QSPLANTRC		decimal(1,0),
	BCSAFFCONS		integer,
	BCSEXECLMT		integer,
	BCSMAXPROCS		integer,
	BCSNONAFFCONS		integer,
	BCSPEAKAFFCONS		integer,
	BCSPEAKMAXPROCS		integer,
	BCSPEAKNONAFFCONS	integer,
	CSAFFCONS		integer,
	CSEXECLMT		integer,
	CSMAXPROCS		integer,
	CSNONAFFCONS		integer,
	CSPEAKAFFCONS		integer,
	CSPEAKMAXPROCS		integer,
	CSPEAKNONAFFCONS	integer,
	FSAFFCONS		integer,
	FSEXECLMT		integer,
	FSCMAXPROCS		integer,
	FSNONAFFCONS		integer,
	FSPEAKAFFCONS		integer,
	FSPEAKMAXPROCS		integer,
	FSPEAKNONAFFCONS	integer,
	RMDSAFFCONS		integer,
	RMDSEXECLMT		integer,
	RMDSNONAFFCONS		integer,
	RMDSPEAKAFFCONS		integer,
	RMDSPEAKNONAFFCONS	integer,
	DASAUDITLVL		smallint,
	RMDSCONS  		integer,
	RMDSPEAKCONS 	integer,
	QSVERBGCLOGLIMIT 	integer,
	QSMDQUERYSIZELIMIT integer,
	QSGENCOMINNATSQL 	decimal(1,0),
	QSDISVERBGCLOG 	decimal(1,0),
	QSINITNURSESIZE 	integer,
	QSNURSESIZELIMIT 	integer,
	QSGCPOLICY 		smallint,
	REPOSCACHEOBJTTL	integer,
	REPOSNUMOBJDISK		integer,
	REPOSNUMOBJMEM		integer,
	MAXSTORESZMB		integer,
	constraint PK_CMOBJPROPS48 primary key (CMID)
)

CREATE TABLE CMOBJPROPS49 (
	CMID			integer not null,
	URI				clob (4000),
	constraint PK_CMOBJPROPS49 primary key (CMID) 
)

create table CMOBJPROPS5 (
	CMID			integer not null,
	ACTEXECTIME		timestamp,
	ACTCOMPTIME		timestamp,
	REQEXECTIME		timestamp,
	USERREF			clob(4000), 
	DETAILTIME		timestamp,
	DETAIL			clob(1M),
	SEVERITY		smallint,
	DETAILTYPE		varchar(64),
	INFO			clob(64k), 
	constraint PK_CMOBJPROPS5 primary key (CMID) 
)

create index IDX_REQEXECTIME on CMOBJPROPS5(REQEXECTIME)

create table CMOBJPROPS50 (
	CMID            integer not null,
	CONTEXT         blob(1G),
	CONTEXTCOUNT    integer,
	LASTPAGE        varchar(512),
	DATADESCRIPTOR  clob(16K),
	METADATA        blob(10M),
	constraint PK_CMOBJPROPS50 primary key (CMID)
)

create table CMOBJPROPS51 (
	CMID                integer not null ,
	SERVERGROUP         varchar (512),
	RUNASOWNER          decimal(1,0),
	CANBURST            decimal(1,0),
	SCHEDTRIGNAME       varchar(255),
	RUNWITHOWNERCAPS	decimal(1,0),
	QUERYMODE           clob(4000),
	RUNINADVANCEDVIEWER decimal(1,0),
	DSMODE              varchar(3000),
	CONSTRAINT PK_CMOBJPROPS51 PRIMARY KEY (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),
	APITOKEN         clob(1M),
	NOTIFYONCHANGE   decimal(1,0),
	CONSTRAINT PK_CMOBJPROPS52 PRIMARY KEY (CMID) 
)

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

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

CREATE TABLE CMOBJPROPS55 (
	CMID			integer not null,
	ICONURI			varchar (2000),
	UCPOLICIES		blob(1M), 
	HIDDEN			decimal(1,0) default 0 not null,
	CONSTRAINT PK_CMOBJPROPS55 PRIMARY KEY (CMID) 
)

CREATE TABLE CMOBJPROPS56 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS56 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS57 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS57 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS58 (
	CMID			integer not null,
	PDSELISTAXSCACHE	integer,
	PDSSHOWCELLANN		decimal(1,0),
	PDSMAXPROCS		integer,
	PPSHIAFFINECONS		integer,
	PPSAFFINECONS		integer,
	PPSAUDITLEVEL		smallint,
	PPSQUEUELIMIT		integer,
	PPSEXECTIMELIMIT	integer,
	BRSPDFCHARENC		smallint,
	BRSPDFCOMPRSLVL		integer,
	BRSPDFCOMPRSTYPE	smallint,
	BRSPDFEMBEDFONTS	smallint,
	RSPDFCHARENC		smallint,
	RSPDFCOMPRSLVL		integer,
	RSPDFCOMPRSTYPE		smallint,
	RSPDFEMBEDFONTS		smallint,
	DMSAFFINECONNECTIONS	integer,
	DMSEXECUTIONTIMELIMIT	integer,
	DMSMAXPROCESSES		integer,
	DMSNONAFFINECONNCTS	integer,
	DMSPEAKMAXPROCESSES	integer,
	DMSPEAKNONAFFINECONNCTS	integer,
	DMSQUEUELIMIT		integer,
	PERDOCVERSRETAGE	varchar (50),
	PERDOCVERSRETCOUNT	integer,
	EDITION			varchar(20),
	EDITIONS		blob(10M),
	DIMSAFFINECONNECTIONS   integer,
    	DIMSEXECUTIONTIMELIMIT  integer,
    	DIMSMAXPROCESSES        integer,
    	DIMSQUEUELIMIT          integer,
	GLOSSARYURI			clob (4000),
	GSAFFINECONNECTIONS		integer,
	GSEXECUTIONTIMELIMIT		integer,
	GSMAXPROCESSES			integer,
	GSNONAFFINECONNCTS		integer,
	GSPEAKMAXPROCESSES		integer,
	GSPEAKNONAFFINECONNCTS		integer,
	GSQUEUELIMIT			integer,
	GSPEAKCONS			integer,
	AASAFFINECONNECTIONS	integer,
	AASEXECUTIONTIMELIMIT   integer,
	AASMAXPROCESSES		    integer,
	HTSCOMPTASKLIFE			varchar (50),
	CMCSHEAPLIM			integer,
	ANSANNOTATIONLIFE	varchar (50),
	constraint PK_CMOBJPROPS58 primary key (CMID) 
)

create table CMOBJPROPS59 (
	CMID			integer not null,
	CONFIGURATION		clob (1M),
	USERINTERFACE		smallint,
 	CONSTRAINT PK_CMOBJPROPS59 PRIMARY KEY (CMID)
)

create table CMOBJPROPS6 (
	CMID			integer not null,
	URI				clob (4000),
	TARGET			clob (4000),
	STEPOBJECT		clob (4000),
	BASE			clob (4000),
	CREDENTIAL		clob (4000),
	DISPATCHERPATH	varchar (3220),
	PAGE			clob (4000),
	CREDNAMESPACES	clob (4000),
	constraint PK_CMOBJPROPS6 primary key (CMID) 
)

create table CMOBJPROPS60 (
	CMID            integer not null,
	CONTEXT         blob(1G),
	CONTEXTCOUNT	integer,
	LASTPAGE	varchar(512),
	constraint PK_CMOBJPROPS60 primary key (CMID)
)

create table CMOBJPROPS61 (
	CMID			integer not null,
	BURSTKEY		varchar (3982),
	constraint PK_CMOBJPROPS61 primary key (CMID) 
)

create table CMOBJPROPS62 (
	CMID integer not null,
	EXPIRATION	timestamp,
	ALLOWANNOTATION 	decimal(1,0),
	constraint PK_CMOBJPROPS62 primary key (CMID) 
)

create index IDX_EXPIRATION_62 on CMOBJPROPS62(EXPIRATION)

CREATE TABLE CMOBJPROPS63 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS63 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS64 (
	CMID			integer NOT NULL ,
	CONTENTTYPE		varchar (256),
	DATASIZE		bigint,
	DATACOMP		decimal(1,0) default 0 not null,
	DATAPROP		blob(1G), 
	constraint PK_CMOBJPROPS64 primary key (CMID)
)

CREATE TABLE CMOBJPROPS65 (
	CMID			integer not null,
	BURSTID			varchar (3869),
	BURSTIDINDEX	character (44),
	constraint PK_CMOBJPROPS65 primary key (CMID) 
)

CREATE INDEX IDX_BURSTIDINDEX ON CMOBJPROPS65(BURSTIDINDEX)

CREATE TABLE CMOBJPROPS66 (
	CMID			integer not null,
	DEFPORTACT		smallint,
	SPEC			clob(1G),
	POWERPLAY8CONFIGURATION clob(1M),
	constraint PK_CMOBJPROPS66 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS67 (
	CMID				integer not null,
	NONPEAKDEMANDBGNH	integer,
	PEAKDEMANDBEGINH	integer,
	ASPEAKCONS			integer,
	BRSPEAKAFFINECONS	integer,
	BRSPEAKMAXPROCS		integer,
	BRSPEAKNONAFFCONS	integer,
	CMSPEAKCONS			integer,
	DDSPEAKCONS			integer,
	DISPEAKCONS			integer,
	DMSPEAKCONS			integer,
	DSPEAKCONS			integer,
	IDSPEAKCONS			integer,
	ISSPEAKCONS			integer,
	IUSPEAKCONS			integer,
	JSPEAKCONS			integer,
	MBSPEAKCONS			integer,			
	MISPEAKCONS			integer,
	MMSPEAKCONS			integer,
	PACSPEAKCONS		integer,
	PDSPEAKCONS			integer,
	PDSPEAKMAXPROCS		integer,
	PPSPEAKAFFINECONS	integer,
	PPSPEAKNONAFFCONS	integer,
	PRSPEAKCONS			integer,
	PTSPEAKCONS			integer,
	RSPEAKAFFINECONS	integer,
	RSPEAKMAXPROCS		integer,
	RSPEAKNONAFFCONS	integer,
    DIMSNONAFFINECONNCTS    integer,
    DIMSPEAKCONS         integer,
    DIMSPEAKMAXPROCS    integer,
   	DIMSPEAKNONAFFINECONNCTS    integer,
	TEMPOBJLOCATION		smallint,
	TEMPOBJLIFETIME		varchar (50),	
	AASNONAFFINECONNCTS     integer,  
	AASPEAKCONS             integer,
	AASPEAKMAXPROCS         integer,  
   	AASPEAKNONAFFINECONNCTS     integer,
	STSAFFCONS		integer,
	STSEXECLMT		integer,
	STSCMAXPROCS		integer,
	STSNONAFFCONS		integer,
	STSPEAKAFFCONS		integer,
	STSPEAKMAXPROCS		integer,
	STSPEAKNONAFFCONS	integer,	
	OVERRIDEOPTS		clob(1M),
	SERVRDEFOPTS		clob(1M),
	STSQUEUELIM			integer,
	COLLABDISCURI		clob(4000), 
	BRSMAXATTACHSIZE	integer,
	DSMAXATTACHSIZE		integer,
	RSMAXATTACHSIZE		integer,
	ASMAXATTACHSIZE		integer,
	PPSMAXATTACHSIZE	integer,
	QSIDLECONTIMEOUT integer,
	QSDUMPMODEL decimal(1,0),
	QSDISPLAN decimal(1,0),
	ACTIVEJMXPROXYURI	clob(4000),	
	QSADDJVMARG		varchar (2048),
	QSINITJVMHEAP		integer,
	QSMANCUBESTRT		decimal(1,0),
	QSMAXJVMHEAP		integer,
	QSROLAPCUBECONF		blob(1M),
	QSROLAPCADMINCT		integer,
	QSRSETQUERYTHRES		integer,
	COOKIECAMPASSHTTP		decimal(1,0),
	constraint PK_CMOBJPROPS67 primary key (CMID) 
)

CREATE TABLE CMOBJPROPS68 (
	CMID			integer not null,
	SPEC			clob(1G),
	constraint PK_CMOBJPROPS68 primary key (CMID) 
)


create table CMOBJPROPS69 (
	CMID			integer not null,
	ORD			integer not null,
	PARMNAME		varchar(512) not null,
	CPARMVALUE		blob(1G),
	constraint PK_CMOBJPROPS69 primary key (CMID, ORD)
)


create table CMOBJPROPS7 (
	CMID			integer not null,
	EXECERROR		clob(1G),
	CONFIGURATION	clob(1G),
	PORTALPREFS		clob(1G),
	CCREDENTIALS	clob(1G),
	CMODEL			blob(1G),
	SRC				clob(1G),
	SPEC			clob(1G),
	STATE			clob(1G),
	GOVERNORS		clob(1G),
	NAMESPFORMAT	clob (4000),
	constraint PK_CMOBJPROPS7 primary key (CMID) 
)

create table CMOBJPROPS70 (
	CMID            integer not null,
	CONTEXT         clob(64K),
	DEPLOYREFS	clob(1M),
	constraint PK_CMOBJPROPS70 primary key (CMID)
)

create table CMOBJPROPS71 (
	CMID            integer not null,
	CONTEXT         clob(64K),
	DEPLOYREFS	clob(1M),
	constraint PK_CMOBJPROPS71 primary key (CMID)
)

create table CMOBJPROPS72 (
	CMID            integer not null,
	CONTEXT         clob(64K),
	constraint PK_CMOBJPROPS72 primary key (CMID)
)


CREATE TABLE CMOBJPROPS73 (
	CMID         integer not null,
	ALIASROOT    varchar (512),
	URI          clob (4000),
	UNIXURI      clob (4000),
	WINDOWSURI   clob (4000),
	CONSTRAINT PK_CMOBJPROPS73 PRIMARY KEY (CMID)
)

CREATE TABLE CMOBJPROPS74 (
	CMID         	integer not null,
	DEPLOYREFS	clob(1M),
	LAUNCHTYPE	varchar (512),
	SPEC		blob(1G),
	CONSTRAINT PK_CMOBJPROPS74 PRIMARY KEY (CMID)
)

create index IDX_CMLAUNCHTYPE on CMOBJPROPS74(LAUNCHTYPE)

CREATE TABLE CMOBJPROPS75 (
	CMID         	integer not null,
	RESOURCETYPE	varchar (512),
	SPEC		blob(1G),
	CONSTRAINT PK_CMOBJPROPS75 PRIMARY KEY (CMID)
)

create index IDX_RESOURCETYPE on CMOBJPROPS75(RESOURCETYPE)

CREATE TABLE CMOBJPROPS76 (
	CMID         	integer not null,
	CONTENTTYPE	varchar (512),
	CONSTRAINT PK_CMOBJPROPS76 PRIMARY KEY (CMID)
)

CREATE TABLE CMOBJPROPS77 (
	CMID			integer NOT NULL ,
	DSCONNECTIONNAME	varchar (512),
	DSNAME				varchar (512),
	CONSTRAINT PK_CMOBJPROPS77 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS78 (
	CMID			integer not null,
	BINDINGNAME		varchar (512),
	INPUTMESSNAME		varchar (512),
	OPERATIONNAME		varchar (512),
	OUTPUTMESSNAME		varchar (512),
	SERVICENAME		varchar (512),
	BULKEVENTS		decimal(1,0),
	URI			clob (4000),
	constraint PK_CMOBJPROPS78 primary key (CMID)
)

CREATE TABLE CMOBJPROPS79 (
	CMID         	integer not null,
	PUBLISHED	decimal(1,0),
	SPEC		clob(1M),
	CONSTRAINT PK_CMOBJPROPS79 PRIMARY KEY (CMID)
)

create table CMOBJPROPS80 (
	CMID             integer not null,
	REPOSITORYRULES  clob(1G),
	NSCAPS           clob(4000),
	CONSTRAINT PK_CMOBJPROPS80 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS81 (
	CMID			integer not null,
	AGGREGATES		clob(1M),
	CONSTRAINT PK_CMOBJPROPS81 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS82 (
	CMID			integer not null,
	BPMRESTURI		clob (4000),
	CONSTRAINT PK_CMOBJPROPS82 PRIMARY KEY (CMID) 
)


create table CMOBJPROPS83 (
	CMID			integer not null,
	MOBILECONFIG		blob(4000),
	CONSTRAINT PK_CMOBJPROPS83 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS84 (
	CMID		integer not null,
	SPEC		clob(1M),
	DATASIZE	bigint,
	DATAMODTIME	timestamp,
	CONSTRAINT PK_CMOBJPROPS84 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS85 (
	CMID            integer not null,
	DATA            clob(50M),
	DEPLOYREFS      clob(1M),
	OBJREFS         clob(1M),
	BIVARS          clob(50M),
	EMBEDDING       clob(50M),
	STATUS          clob(50M),
	USAGETRACKMODEL clob(1M),
	CONSTRAINT PK_CMOBJPROPS85 PRIMARY KEY (CMID) 
)

CREATE TABLE CMOBJPROPS86 (
	CMID			integer not null,
	KSCHEMA			varchar (512),
	KCATALOG		varchar (512),
	CPHYSDEF		clob(10M),
	CPHYSANAL		clob(10M),
	CBASEDEF		clob(10M),
	CBASEOVER		clob(10M),
	CBASEANAL		clob(10M),
	STATUSID		smallint,
	SNAPSHOT       	clob(1M),
	HISTORY       	clob(10M),
	SNAPSHOTENBL	decimal(1,0),
	DEPLOYREFS		clob(1M),
	ALIASROOT		varchar (512),
	CONSTRAINT PK_CMOBJPROPS86 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS87 (
	CMID		integer not null,
	EXT         clob(100M),
	CONSTRAINT PK_CMOBJPROPS87 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS88 (
	CMID		integer not null,
	SCHEMATYPE		varchar (256),
	STATUS			varchar (256),
	STATUSINFO		clob(10M),
	CONSTRAINT PK_CMOBJPROPS88 PRIMARY KEY (CMID)
)

create table CMOBJPROPS89 (
     CMID        integer not null,
     DOCTYPE     varchar(64) not null,
     DOCID       varchar(64) not null,
     CONTENT     clob(10M),
     CONSTRAINT PK_CMOBJPROPS89 PRIMARY KEY (CMID)
)

create index IDX_CMPROPS89_1 on CMOBJPROPS89(DOCID)

create table CMOBJPROPS9 (
	CMID			integer not null,
	ASOFTIME		timestamp, 
	CANBURST		decimal(1,0),
	constraint PK_CMOBJPROPS9 primary key (CMID) 
)

create table CMOBJPROPS90 (
	CMID        integer not null,
	SPEC        clob(1M),
	CONSTRAINT PK_CMOBJPROPS90 PRIMARY KEY (CMID) 
)

create table CMOBJPROPS91 (
	CMID        integer not null,
	SKDATA      clob(1M),
	CONSTRAINT PK_CMOBJPROPS91 PRIMARY KEY (CMID) 
)

CREATE TABLE CMOBJPROPS92 (
	CMID        integer not null,
	CONTYPE     varchar(64),
	URI         clob(4000),
	SPEC        clob(64k),
	CONSTRAINT PK_CMOBJPROPS92 PRIMARY KEY (CMID) 
)

CREATE TABLE CMOBJPROPS93 (
	CMID                 integer not null,
	PROGRESS             varchar(1024),
	JOBID                varchar(64),
	STATE                varchar(64),
	CONFIGURATION        clob(2M),
	CONSTRAINT PK_CMOBJPROPS93 PRIMARY KEY (CMID) 
)

CREATE TABLE CMTAGS (
	CMID      integer not null,
	ORD       integer not null,
	TAG       varchar(256),
	constraint PK_CMTAGS primary key (CMID,ORD)
)

create index IDX_CMTAGS on CMTAGS(CMID, TAG)


create table CMPOLICIES (
	CMID			integer not null,
	POLICIES		blob(1M), 
	constraint PK_CMPOLICIES primary key (CMID) 
)

create table CMVIEWED (
	CMID			integer not null,
	OBJID			clob (4000) default '-',
	VIEWED			decimal(1,0) default 0, 
	constraint PK_CMVIEWED primary key (CMID) 
)

create table CMPROPERTIES (
	PROPID			smallint NOT NULL,
	NAME			varchar(50) NOT NULL,
	DEF				blob(10K),
	constraint PK_CMPROPERTIES primary key(PROPID)
)

create table CMCLASSES (
	CLASSID			integer NOT NULL,
	NAME			varchar(50) NOT NULL,
	USAGE			smallint,
	DEF				blob(100K),
	constraint PK_CMCLASSES primary key(CLASSID)
)

create table CMLOCALES (
	LOCALEID		smallint		not null,
	MAPDLOCALEID	smallint		not null,
	LOCALE			varchar(20)		not null,
	constraint PK_CMLOCALES primary key (LOCALEID)
)

create table CMGUIDS (
	CMID			integer not null ,
	GUID			varchar(50),
	constraint PK_CMGUIDS primary key (CMID)
)

create trigger CMGUIDS_TR
no cascade before insert on CMGUIDS
referencing new as NG
for each row mode db2sql
set NG.GUID = VARCHAR( TIMESTAMP( GENERATE_UNIQUE() ) )

CREATE TABLE  CMREFNOORD1 (
	PROPID		smallint NOT NULL,
	CMID		integer not null ,
	REFCMID		integer not null ,
	constraint PK_CMREFNOORD1 primary key (CMID, PROPID) )

create index IDX_CMREFNOORD1 on CMREFNOORD1(REFCMID, PROPID)

CREATE TABLE  CMREFNOORD2 (
	CMID		integer not null ,
	REFCMID		integer not null ,
	constraint PK_CMREFNOORD2 primary key (CMID) )

create index IDX_CMREFNOORD2 on CMREFNOORD2(REFCMID)

CREATE TABLE  CMREFORD1 (
	PROPID		smallint NOT NULL,
	CMID		integer not null ,
	ORD		integer NOT NULL,
	REFCMID		integer not null ,
	constraint PK_CMREFORD1 primary key (CMID, PROPID, ORD) )

create index IDX_CMREFORD1 on CMREFORD1(REFCMID, PROPID, ORD)

CREATE TABLE  CMREFORD2 (
	CMID		integer not null ,
	ORD		integer NOT NULL,
	REFCMID		integer not null ,
	constraint PK_CMREFORD2 primary key (CMID, ORD) )

create index IDX_CMREFORD2 on CMREFORD2(REFCMID, ORD)

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

CREATE TABLE CMDATAUPGRADE (
	PLUGINNAME 	varchar (255) NOT NULL ,
	CI_PLUGINNAME	varchar (255) NOT NULL ,
	VERSION		varchar (64) ,
	CONSTRAINT PK_CMDATAUPGRADE PRIMARY KEY (CI_PLUGINNAME)
)

CREATE TABLE CMSIZEINFO (
	STOREID			varchar (255) NOT NULL,
	CLASSID			integer NOT NULL,
	TOTALFORCLASSID		integer NOT NULL,
	TOTALCOUNT		bigint ,
        TOTALSIZE		bigint ,
	CONSTRAINT PK_CMSIZEINFO PRIMARY KEY (STOREID, TOTALFORCLASSID)
)

CREATE TABLE CMSPLIT  (
		CMID		int not null,
		TRANSACTIONID	varchar(255) not null,
		constraint PK_CMSPLIT  primary key (CMID, TRANSACTIONID)
)

CREATE TABLE CMARCHIVEQUEUE (
	OBJID 	 	VARCHAR(512) DEFAULT NULL,
	ITEMID	 	VARCHAR(255) NOT NULL,
	QUEUEID		VARCHAR(255) NOT NULL,
	WORKFLOW 	VARCHAR(255) NOT NULL,
	CONSUMERNAME	VARCHAR(255) DEFAULT NULL,
	STATE		INTEGER,
	INSERTIONTIME 	TIMESTAMP NOT NULL,
	CONSTRAINT PK_CMARCHIVEQUEUE  PRIMARY KEY (ITEMID)
)

CREATE TABLE CMDELETEQUEUE (
	OBJID 	 	VARCHAR(512) DEFAULT NULL,
	ITEMID	 	VARCHAR(255) NOT NULL,
	QUEUEID		VARCHAR(255) NOT NULL,
	WORKFLOW 	VARCHAR(255) NOT NULL,
	CONSUMERNAME	VARCHAR(255) DEFAULT NULL,
	STATE		INTEGER,
	INSERTIONTIME 	TIMESTAMP NOT NULL,
	CONSTRAINT PK_CMDELETEQUEUE PRIMARY KEY (ITEMID)
)

CREATE TABLE CMINDEXQUEUE (
	OBJID 	 		VARCHAR(1024) DEFAULT NULL,
	ITEMID	 		VARCHAR(50) NOT NULL,
	QUEUEID			VARCHAR(50) NOT NULL,
	WORKFLOW 		VARCHAR(50) NOT NULL,
	CONSUMERNAME	VARCHAR(50) DEFAULT NULL,
	STATE			INTEGER,
	INSERTIONTIME 	TIMESTAMP NOT NULL,
	INDEXACTION		VARCHAR(50) NOT NULL,
	INDEXCLASS		VARCHAR(255) NOT NULL,
	INDEXPROPS		clob (10M),
	CONSTRAINT PK_CMINDEXQUEUE  PRIMARY KEY (ITEMID)
)

CREATE INDEX IDX_CMINDEXQUEUE on CMINDEXQUEUE(STATE, QUEUEID)

CREATE TABLE CMQUEUELOCK (
	QUEUEID		VARCHAR(255) NOT NULL,
	CONSTRAINT PK_CMQUEUELOCK PRIMARY KEY (QUEUEID)
)

CREATE TABLE CMTENANTID (
	TENANTID	integer not null,
	TENANTNAME	varchar(255) not null,
	CONSTRAINT PK_CMTENANTID PRIMARY KEY (TENANTID)
)

INSERT INTO CMTENANTID (TENANTID,TENANTNAME) VALUES (0,'')

insert into CMSYSPROPS (PROPNAME, CI_PROPNAME, PROPVALUE)
	values ('CMStoreVersion', upper('CMStoreVersion'), '7.7003')