db2/scripts/openidm_luw.sql

	openidm_luw.sql revision 10ce1c22b1cd83482f0127254fe8a27610bb25f5
CONNECT TO DOPENIDM;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
UNQUIESCE DATABASE;
CONNECT RESET;
DEACTIVATE DATABASE DOPENIDM;
DROP DATABASE DOPENIDM;
-- DROP STOGROUP GOPENIDM;
-- COMMIT;
-- CREATE STOGROUP GOPENIDM
--       VOLUMES ('*')
--       VCAT     VSDB2T
--;
CREATE DATABASE   DOPENIDM
--       STOGROUP   GOPENIDM
--       BUFFERPOOL BP2
;

CONNECT TO DOPENIDM;

-- http://db2-vignettes.blogspot.com/2013/07/a-temporary-table-could-not-be-created.html
CREATE BUFFERPOOL BPOIDMTEMPPOOL SIZE 500 PAGESIZE 32K;

CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k
       MANAGED BY AUTOMATIC STORAGE
       BUFFERPOOL BPOIDMTEMPPOOL;

CREATE SCHEMA SOPENIDM;

-- -----------------------------------------------------
-- Table openidm.objecttypes
-- -----------------------------------------------------
CREATE TABLESPACE SOIDM00
       MANAGED BY AUTOMATIC STORAGE
;

CREATE TABLE SOPENIDM.OBJECTTYPES
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPE                 VARCHAR(255)   NOT NULL
       ,PRIMARY KEY (ID)
       )
       IN DOPENIDM.SOIDM00
;
COMMENT ON TABLE SOPENIDM.OBJECTTYPES IS
'OPENIDM - Dictionary table for object types';

CREATE UNIQUE INDEX SOPENIDM.IDX_OBJECTTYPES_OBJECTTYPE
       ON SOPENIDM.OBJECTTYPES
       (OBJECTTYPE                 ASC
       )
;


-- -----------------------------------------------------
-- Table openidm.genericobjects
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM01
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.GENERICOBJECTS
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPES_ID             INTEGER        NOT NULL
       ,OBJECTID                   VARCHAR(255)   NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,FULLOBJECT                 CLOB(2M)
       ,PRIMARY KEY (ID)
       ,CONSTRAINT FK_GENERICOBJECTS_OBJECTTYPES
          FOREIGN KEY
         (OBJECTTYPES_ID )
          REFERENCES SOPENIDM.OBJECTTYPES (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM01
;
COMMENT ON TABLE SOPENIDM.GENERICOBJECTS IS
'OPENIDM - Generic table For Any Kind Of Objects';

CREATE INDEX SOPENIDM.FK_GENERICOBJECTS_OBJECTTYPES
       ON SOPENIDM.GENERICOBJECTS
       (OBJECTTYPES_ID               ASC
       )
;
CREATE INDEX SOPENIDM.IDX_GENERICOBJECTS_OBJECTID
       ON SOPENIDM.GENERICOBJECTS
       (OBJECTID                     ASC
       ,OBJECTTYPES_ID               ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.genericobjectproperties
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM02
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.GENERICOBJECTPROPERTIES
       (GENERICOBJECTS_ID          INTEGER        NOT NULL
       ,PROPKEY                    VARCHAR(255)   NOT NULL
       ,PROPTYPE                   VARCHAR(255)
       ,PROPVALUE                  VARCHAR(2000)
       ,CONSTRAINT FK_GENERICOBJECTPROPERTIES_GENERICOBJECTS
          FOREIGN KEY
         (GENERICOBJECTS_ID )
          REFERENCES SOPENIDM.GENERICOBJECTS (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM02
;
COMMENT ON TABLE SOPENIDM.GENERICOBJECTPROPERTIES IS
'OPENIDM - Properties of Generic Objects';

CREATE INDEX SOPENIDM.IDX_GENERICOBJECTPROPERTIES_GENERICOBJECTS
       ON SOPENIDM.GENERICOBJECTPROPERTIES
       (GENERICOBJECTS_ID            ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.managedobjects
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM03
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.MANAGEDOBJECTS
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPES_ID             INTEGER        NOT NULL
       ,OBJECTID                   VARCHAR(255)   NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,FULLOBJECT                 CLOB(2M)
       ,PRIMARY KEY (ID)
       ,CONSTRAINT FK_MANAGEDOBJECTS_OBJECTTYPES
          FOREIGN KEY
         (OBJECTTYPES_ID )
          REFERENCES SOPENIDM.OBJECTTYPES (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM03
;
COMMENT ON TABLE SOPENIDM.MANAGEDOBJECTS IS
'OPENIDM - Generic Table For Managed Objects';

CREATE INDEX SOPENIDM.FK_MANAGEDOBJECTS_OBJECTTYPES
       ON SOPENIDM.MANAGEDOBJECTS
       (OBJECTTYPES_ID               ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.managedobjectproperties
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM04
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.MANAGEDOBJECTPROPERTIES
       (MANAGEDOBJECTS_ID          INTEGER        NOT NULL
       ,PROPKEY                    VARCHAR(255)   NOT NULL
       ,PROPTYPE                   VARCHAR(255)
       ,PROPVALUE                  VARCHAR(2000)
       ,CONSTRAINT FK_MANAGEDOBJECTPROPERTIES_MANAGEDOBJECTS
          FOREIGN KEY
         (MANAGEDOBJECTS_ID )
          REFERENCES SOPENIDM.MANAGEDOBJECTS (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM04
;
COMMENT ON TABLE SOPENIDM.MANAGEDOBJECTPROPERTIES IS
'OPENIDM - Properties of Managed Objects';

CREATE UNIQUE INDEX SOPENIDM.IDX_MANAGEDOBJECTPROPERTIES_PROP
       ON SOPENIDM.MANAGEDOBJECTPROPERTIES
       (PROPKEY                      ASC
       ,MANAGEDOBJECTS_ID            ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.configobjects
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM05
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.CONFIGOBJECTS
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPES_ID             INTEGER        NOT NULL
       ,OBJECTID                   VARCHAR(255)   NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,FULLOBJECT                 CLOB(2M)
       ,PRIMARY KEY (ID)
       ,CONSTRAINT FK_CONFIGOBJECTS_OBJECTTYPES
          FOREIGN KEY
         (OBJECTTYPES_ID )
          REFERENCES SOPENIDM.OBJECTTYPES (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM05
;
COMMENT ON TABLE SOPENIDM.CONFIGOBJECTS IS
'OPENIDM - Generic Table For Config Objects';

CREATE INDEX SOPENIDM.FK_CONFIGOBJECTS_OBJECTTYPES
       ON SOPENIDM.CONFIGOBJECTS
       (OBJECTTYPES_ID               ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.configobjectproperties
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM06
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.CONFIGOBJECTPROPERTIES
       (CONFIGOBJECTS_ID           INTEGER        NOT NULL
       ,PROPKEY                    VARCHAR(255)   NOT NULL
       ,PROPTYPE                   VARCHAR(255)
       ,PROPVALUE                  VARCHAR(2000)
       ,CONSTRAINT FK_CONFIGOBJECTPROPERTIES_CONFIGOBJECTS
          FOREIGN KEY
         (CONFIGOBJECTS_ID )
          REFERENCES SOPENIDM.CONFIGOBJECTS (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM06
;
COMMENT ON TABLE SOPENIDM.CONFIGOBJECTPROPERTIES IS
'OPENIDM - Properties of Config Objects';

CREATE UNIQUE INDEX SOPENIDM.IDX_CONFIGOBJECTPROPERTIES_PROP
       ON SOPENIDM.CONFIGOBJECTPROPERTIES
       (PROPKEY                      ASC
       ,CONFIGOBJECTS_ID             ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.links
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM07
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.LINKS
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,LINKTYPE                   VARCHAR(255)   NOT NULL
       ,LINKQUALIFIER              VARCHAR(255)   NOT NULL
       ,FIRSTID                    VARCHAR(255)   NOT NULL
       ,SECONDID                   VARCHAR(255)   NOT NULL
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM07
;
COMMENT ON TABLE SOPENIDM.LINKS IS
'OPENIDM - Object Links For Mappings And Synchronization';

CREATE INDEX SOPENIDM.IDX_LINKS_FIRST
       ON SOPENIDM.LINKS
       (LINKTYPE                    ASC
       ,LINKQUALIFIER               ASC
       ,FIRSTID                     ASC
       )
;
CREATE INDEX SOPENIDM.IDX_LINKS_SECOND
       ON SOPENIDM.LINKS
       (LINKTYPE                    ASC
       ,LINKQUALIFIER               ASC
       ,SECONDID                    ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.auditrecon
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM08
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.AUDITRECON
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,ENTRYTYPE                  VARCHAR(7)
       ,ROOTACTIONID               VARCHAR(255)
       ,RECONID                    VARCHAR(36)
       ,RECONACTION                VARCHAR(36)
       ,RECONCILING                VARCHAR(12)
       ,SOURCEOBJECTID             VARCHAR(511)
       ,TARGETOBJECTID             VARCHAR(511)
       ,AMBIGUOUSTARGETOBJECTIDS   CLOB(2M)
       ,ACTIVITYDATE               VARCHAR(29)
       ,SITUATION                  VARCHAR(24)
       ,ACTIVITY                   VARCHAR(24)
       ,STATUS                     VARCHAR(7)
       ,MESSAGE                    CLOB(2M)
       ,ACTIONID                   VARCHAR(255)
       ,EXCEPTIONDETAIL            CLOB(2M)
       ,MAPPING                    VARCHAR(511)
       ,LINKQUALIFIER              VARCHAR(255)
       ,MESSAGEDETAIL              CLOB(2M)
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM08
;
COMMENT ON TABLE SOPENIDM.AUDITRECON IS
'OPENIDM - Reconciliation Audit Log';

CREATE INDEX SOPENIDM.IDX_AUDITRECON_RECONID
       ON SOPENIDM.AUDITRECOn
       (RECONID            ASC
       )
;
CREATE INDEX SOPENIDM.IDX_AUDITRECON_ACTIVITYDATE
       ON SOPENIDM.AUDITRECON
       (ACTIVITYDATE           ASC
       )
;

CREATE INDEX SOPENIDM.IDX_AUDITRECON_ENTRYTYPE
       ON SOPENIDM.AUDITRECON
       (ENTRYTYPE           ASC
       )
;
CREATE INDEX SOPENIDM.IDX_AUDITRECON_SITUATION
       ON SOPENIDM.AUDITRECON
       (SITUATION           ASC
       )
;
CREATE INDEX SOPENIDM.IDX_AUDITRECON_STATUS
       ON SOPENIDM.AUDITRECON
       (STATUS           ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.auditactivity
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM09
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.AUDITACTIVITY
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,ROOTACTIONID               VARCHAR(512)
       ,PARENTACTIONID             VARCHAR(512)
       ,ACTIVITYID                 VARCHAR(511)
       ,ACTIVITYDATE               VARCHAR(29)
       ,ACTIVITY                   VARCHAR(24)
       ,MESSAGE                    CLOB(2M)
       ,SUBJECTID                  VARCHAR(511)
       ,SUBJECTREV                 VARCHAR(255)
       ,REQUESTER                  CLOB(2M)
       ,APPROVER                   CLOB(2M)
       ,SUBJECTBEFORE              CLOB(2M)
       ,SUBJECTAFTER               CLOB(2M)
       ,STATUS                     VARCHAR(7)
       ,CHANGEDFIELDS              VARCHAR(255)
       ,CHANGEDPASSWORD            VARCHAR(5)
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM09
;
COMMENT ON TABLE SOPENIDM.AUDITACTIVITY IS
'OPENIDM - Activity Audit Logs';

CREATE INDEX SOPENIDM.IDX_AUDITACTIVITY_ROOTACTIONID
       ON SOPENIDM.AUDITACTIVITY
       (ROOTACTIONID           ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.auditaccess
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM10
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.AUDITACCESS
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,ACTIVITYDATE               VARCHAR(29)
       ,ACTIVITY                   VARCHAR(24)
       ,IP                         VARCHAR(40)
       ,PRINCIPAL                  CLOB(2M)
       ,ROLES                      VARCHAR(1024)
       ,STATUS                     VARCHAR(7)
       ,USERID                     VARCHAR(24)
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM10
;
COMMENT ON TABLE SOPENIDM.AUDITACCESS IS
'OPENIDM - Audit Access';

-- -----------------------------------------------------
-- Table openidm.security
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM11
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.SECURITY
       (OBJECTID                   VARCHAR(38)     NOT NULL
       ,REV                        VARCHAR(38)     NOT NULL
       ,STORESTRING                CLOB(2M)        NOT NULL
       )
       IN DOPENIDM.SOIDM11
;
COMMENT ON TABLE SOPENIDM.SECURITY IS
'OPENIDM - Security data';

-- -----------------------------------------------------
-- Table openidm.securitykeys
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM12
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.SECURITYKEYS
       (OBJECTID                   VARCHAR(38)     NOT NULL
       ,REV                        VARCHAR(38)     NOT NULL
       ,KEYPAIR                    CLOB(2M)        NOT NULL
       )
       IN DOPENIDM.SOIDM12
;
COMMENT ON TABLE SOPENIDM.SECURITYKEYS IS
'OPENIDM - Security keys';

-- -----------------------------------------------------
-- Table openidm.auditsync
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM13
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.AUDITSYNC
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,ROOTACTIONID               VARCHAR(255)
       ,SOURCEOBJECTID             VARCHAR(511)
       ,TARGETOBJECTID             VARCHAR(511)
       ,ACTIVITYDATE               VARCHAR(29)
       ,SITUATION                  VARCHAR(24)
       ,ACTIVITY                   VARCHAR(24)
       ,STATUS                     VARCHAR(7)
       ,MESSAGE                    VARCHAR(30000)
       ,ACTIONID                   VARCHAR(255)
       ,EXCEPTIONDETAIL            CLOB(2M)
       ,MAPPING                    VARCHAR(511)
       ,LINKQUALIFIER              VARCHAR(255)
       ,MESSAGEDETAIL              CLOB(2M)
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM13
;
COMMENT ON TABLE SOPENIDM.AUDITSYNC IS
'OPENIDM - Sync Audit Log';

-- -----------------------------------------------------
-- Table openidm.internaluser
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM14
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.INTERNALUSER
       (OBJECTID                   VARCHAR(254)    NOT NULL
       ,REV                        VARCHAR(38)
       ,PWD                        VARCHAR(510)
       ,ROLES                      VARCHAR(1024)
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM14
;
COMMENT ON TABLE SOPENIDM.INTERNALUSER IS
'OPENIDM - Internal User';

-- -----------------------------------------------------
-- Table openidm.schedulerobjects
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM15
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.SCHEDULEROBJECTS
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPES_ID             INTEGER        NOT NULL
       ,OBJECTID                   VARCHAR(255)   NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,FULLOBJECT                 CLOB(2M)
       ,PRIMARY KEY (ID)
       ,CONSTRAINT FK_SCHEDULEROBJECTS_OBJECTTYPES
          FOREIGN KEY
         (OBJECTTYPES_ID )
          REFERENCES SOPENIDM.OBJECTTYPES (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM15
;
COMMENT ON TABLE SOPENIDM.SCHEDULEROBJECTS IS
'OPENIDM - Generic table for scheduler objects';

CREATE INDEX SOPENIDM.FK_SCHEDULEROBJECTS_OBJECTTYPES
       ON SOPENIDM.SCHEDULEROBJECTS
       (OBJECTTYPES_ID               ASC
       )
;
CREATE INDEX SOPENIDM.IDX_SCHEDULEROBJECTS_OBJECTID
       ON SOPENIDM.SCHEDULEROBJECTS
       (OBJECTID                     ASC
       ,OBJECTTYPES_ID               ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.schedulerobjectproperties
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM16
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.SCHEDULEROBJECTPROPERTIES
       (SCHEDULEROBJECTS_ID        INTEGER        NOT NULL
       ,PROPKEY                    VARCHAR(255)   NOT NULL
       ,PROPTYPE                   VARCHAR(255)
       ,PROPVALUE                  VARCHAR(2000)
       ,CONSTRAINT FK_SCHEDULEROBJECTPROPERTIES_SCHEDULEROBJECTS
          FOREIGN KEY
         (SCHEDULEROBJECTS_ID )
          REFERENCES SOPENIDM.SCHEDULEROBJECTS (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM16
;
COMMENT ON TABLE SOPENIDM.SCHEDULEROBJECTPROPERTIES IS
'OPENIDM - Properties of Generic Objects';

CREATE INDEX SOPENIDM.IDX_SCHEDULEROBJECTPROPERTIES_SCHEDULEROBJECTS
       ON SOPENIDM.SCHEDULEROBJECTPROPERTIES
       (SCHEDULEROBJECTS_ID            ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.clusterobjects
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM17
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.CLUSTEROBJECTS
       (ID                         INTEGER
                                   GENERATED BY DEFAULT
                                   AS IDENTITY
                                   ( CYCLE )
       ,OBJECTTYPES_ID             INTEGER        NOT NULL
       ,OBJECTID                   VARCHAR(255)   NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,FULLOBJECT                 CLOB(2M)
       ,PRIMARY KEY (ID)
       ,CONSTRAINT FK_CLUSTEROBJECTS_OBJECTTYPES
          FOREIGN KEY
         (OBJECTTYPES_ID )
          REFERENCES SOPENIDM.OBJECTTYPES (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM17
;
COMMENT ON TABLE SOPENIDM.CLUSTEROBJECTS IS
'OPENIDM - Generic table for cluster objects';

CREATE INDEX SOPENIDM.FK_CLUSTEROBJECTS_OBJECTTYPES
       ON SOPENIDM.CLUSTEROBJECTS
       (OBJECTTYPES_ID               ASC
       )
;
CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTS_OBJECTID
       ON SOPENIDM.CLUSTEROBJECTS
       (OBJECTID                     ASC
       ,OBJECTTYPES_ID               ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.clusterobjectproperties
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM18
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.CLUSTEROBJECTPROPERTIES
       (CLUSTEROBJECTS_ID        INTEGER        NOT NULL
       ,PROPKEY                    VARCHAR(255)   NOT NULL
       ,PROPTYPE                   VARCHAR(255)
       ,PROPVALUE                  VARCHAR(2000)
       ,CONSTRAINT FK_CLUSTEROBJECTPROPERTIES_CLUSTEROBJECTS
          FOREIGN KEY
         (CLUSTEROBJECTS_ID )
          REFERENCES SOPENIDM.CLUSTEROBJECTS (ID )
         ON DELETE CASCADE
       )
       IN DOPENIDM.SOIDM18
;
COMMENT ON TABLE SOPENIDM.CLUSTEROBJECTPROPERTIES IS
'OPENIDM - Properties of Generic Objects';

CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTPROPERTIES_CLUSTEROBJECTS
       ON SOPENIDM.CLUSTEROBJECTPROPERTIES
       (CLUSTEROBJECTS_ID            ASC
       )
;

-- -----------------------------------------------------
-- Table openidm.uinotification
-- -----------------------------------------------------

CREATE TABLESPACE SOIDM19
       MANAGED BY AUTOMATIC STORAGE
;
CREATE TABLE SOPENIDM.UINOTIFICATION
       (OBJECTID                   VARCHAR(38)    NOT NULL
       ,REV                        VARCHAR(38)    NOT NULL
       ,NOTIFICATIONTYPE           VARCHAR(255)   NOT NULL
       ,CREATEDATE                 VARCHAR(255)   NOT NULL
       ,MESSAGE                    CLOB(2M)       NOT NULL
       ,REQUESTER                  VARCHAR(255)       NULL
       ,RECEIVERID                 VARCHAR(38)    NOT NULL
       ,REQUESTERID                VARCHAR(38)        NULL
       ,NOTIFICATIONSUBTYPE        VARCHAR(255)       NULL
       ,PRIMARY KEY (OBJECTID)
       )
       IN DOPENIDM.SOIDM19
;
COMMENT ON TABLE SOPENIDM.UINOTIFICATION IS
'OPENIDM - Generic table for ui notifications';

COMMIT;

-- -----------------------------------------------------
-- Data for table openidm.internaluser
-- -----------------------------------------------------

INSERT INTO sopenidm.internaluser (objectid, rev, pwd, roles) VALUES ('openidm-admin', '0', 'openidm-admin', '["openidm-admin","openidm-authorized"]');
INSERT INTO sopenidm.internaluser (objectid, rev, pwd, roles) VALUES ('anonymous', '0', 'anonymous', '["openidm-reg"]');

COMMIT;