openidm_luw.sql revision 361ad5fce7939eb63cbb8dc8365e2564461986aa
CONNECT TO DOPENIDM;
CONNECT RESET;
-- DROP STOGROUP GOPENIDM;
-- COMMIT;
-- CREATE STOGROUP GOPENIDM
-- VOLUMES ('*')
-- VCAT VSDB2T
--;
-- STOGROUP GOPENIDM
-- BUFFERPOOL BP2
;
CONNECT TO DOPENIDM;
-- http://db2-vignettes.blogspot.com/2013/07/a-temporary-table-could-not-be-created.html
-- -----------------------------------------------------
-- Table openidm.objecttypes
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.OBJECTTYPES (
OBJECTTYPE VARCHAR(255) NOT NULL,
-- -----------------------------------------------------
-- Table openidm.genericobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.GENERICOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_GENERICOBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID ) REFERENCES SOPENIDM.OBJECTTYPES (ID ) ON DELETE CASCADE) IN DOPENIDM.SOIDM01;
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 TABLE SOPENIDM.GENERICOBJECTPROPERTIES (
genericobjects_id INTEGER NOT NULL,
propkey VARCHAR(255) NOT NULL,
proptype VARCHAR(255),
propvalue VARCHAR(2000),
CONSTRAINT FK_GENERICOBJECTPROPERTIES_GENERICOBJECTS
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_GENERICOBJECTPROPERTIES_GENERICOBJECTS ON SOPENIDM.GENERICOBJECTPROPERTIES (GENERICOBJECTS_ID ASC);
-- -----------------------------------------------------
-- Table openidm.managedobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.MANAGEDOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_MANAGEDOBJECTS_OBJECTTYPES
ON DELETE CASCADE
CREATE INDEX SOPENIDM.FK_MANAGEDOBJECTS_OBJECTTYPES ON SOPENIDM.MANAGEDOBJECTS (OBJECTTYPES_ID ASC);
-- -----------------------------------------------------
-- Table openidm.managedobjectproperties
-- -----------------------------------------------------
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 )
ON DELETE CASCADE
-- -----------------------------------------------------
-- Table openidm.configobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CONFIGOBJECTS (
objecttypes_id INTEGER NOT NULL,
objectid VARCHAR(255) NOT NULL,
rev VARCHAR(38) NOT NULL,
CONSTRAINT FK_CONFIGOBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
-- -----------------------------------------------------
-- Table openidm.configobjectproperties
-- -----------------------------------------------------
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 )
ON DELETE CASCADE
CREATE UNIQUE INDEX SOPENIDM.IDX_CONFIGOBJECTPROPERTIES_PROP ON SOPENIDM.CONFIGOBJECTPROPERTIES (PROPKEY ASC, CONFIGOBJECTS_ID ASC);
-- -----------------------------------------------------
-- Table openidm.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,
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.security
-- -----------------------------------------------------
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
-- -----------------------------------------------------
-- Table openidm.securitykeys
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.SECURITYKEYS (
objectid VARCHAR(38) NOT NULL,
rev VARCHAR(38) NOT NULL,
-- -----------------------------------------------------
-- Table `openidm`.`auditauthentication`
-- -----------------------------------------------------
CREATE TABLE sopenidm.auditauthentication (
objectid VARCHAR(38) NOT NULL,
transactionid VARCHAR(56) NOT NULL,
activitydate VARCHAR(29) NOT NULL,
userid VARCHAR(255) NULL,
eventname VARCHAR(50) NULL,
result VARCHAR(255) NULL,
sessionid VARCHAR(255),
-- -----------------------------------------------------
-- Table openidm.auditrecon
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITRECON (
objectid VARCHAR(38) NOT NULL ,
transactionid VARCHAR(56) NOT NULL ,
activitydate VARCHAR(29) NOT NULL ,
eventname VARCHAR(50) NULL ,
userid VARCHAR(255) NULL ,
activity VARCHAR(24) NULL ,
linkqualifier VARCHAR(255) NULL ,
mapping VARCHAR(511) NULL ,
situation VARCHAR(24) NULL ,
sourceobjectid VARCHAR(511) NULL ,
status VARCHAR(20) NULL ,
targetobjectid VARCHAR(511) NULL ,
reconciling VARCHAR(12) NULL ,
reconaction VARCHAR(36) NULL ,
entrytype VARCHAR(7) NULL ,
reconid VARCHAR(56) NULL ,
--CREATE INDEX sopenidm.idx_auditrecon_targetobjectid ON sopenidm.auditrecon (targetobjectid(28) ASC);
--CREATE INDEX sopenidm.idx_auditrecon_sourceobjectid ON sopenidm.auditrecon (sourceobjectid(28) ASC);
--CREATE INDEX sopenidm.idx_auditrecon_mapping ON sopenidm.auditrecon (mapping(255) ASC);
-- -----------------------------------------------------
-- Table openidm.auditsync
-- -----------------------------------------------------
objectid VARCHAR(38) NOT NULL,
transactionid VARCHAR(255),
activitydate VARCHAR(29),
situation VARCHAR(24),
activity VARCHAR(24),
status VARCHAR(7),
actionid VARCHAR(255),
mapping VARCHAR(511),
linkqualifier VARCHAR(255),
sourceobjectid VARCHAR(511),
targetobjectid VARCHAR(511),
-- -----------------------------------------------------
-- Table `openidm`.`auditconfig`
-- -----------------------------------------------------
CREATE TABLE sopenidm.auditconfig (
objectid VARCHAR(38) NOT NULL ,
activitydate VARCHAR(29) NOT NULL,
transactionid VARCHAR(56) NOT NULL ,
eventname VARCHAR(255) NULL ,
userid VARCHAR(255) NULL ,
runas VARCHAR(255) NULL ,
resource_uri VARCHAR(255) NULL ,
resource_protocol VARCHAR(10) NULL ,
resource_method VARCHAR(10) NULL ,
resource_detail VARCHAR(255) NULL ,
changedfields VARCHAR(255) NULL ,
rev VARCHAR(255) NULL,
-- -----------------------------------------------------
-- Table openidm.auditactivity
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITACTIVITY (
objectid VARCHAR(38) NOT NULL ,
activity VARCHAR(24) NULL ,
activitydate VARCHAR(29) NOT NULL,
transactionid VARCHAR(56) NOT NULL ,
eventname VARCHAR(255) NULL ,
userid VARCHAR(255) NULL ,
runas VARCHAR(255) NULL ,
resource_uri VARCHAR(255) NULL ,
resource_protocol VARCHAR(10) NULL ,
resource_method VARCHAR(10) NULL ,
resource_detail VARCHAR(255) NULL ,
changedfields VARCHAR(255) NULL ,
passwordchanged VARCHAR(5) NULL ,
subjectrev VARCHAR(255) NULL ,
activityobjectid VARCHAR(255) ,
status VARCHAR(20) ,
CREATE INDEX SOPENIDM.idx_auditactivity_transactionid ON SOPENIDM.AUDITACTIVITY (transactionid ASC);
-- -----------------------------------------------------
-- Table openidm.internaluser
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.INTERNALUSER (
objectid VARCHAR(254) NOT NULL,
rev VARCHAR(38),
pwd VARCHAR(510),
roles VARCHAR(1024),
-- -----------------------------------------------------
-- Table openidm.auditaccess
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.AUDITACCESS (
objectid VARCHAR(38) NOT NULL ,
activity VARCHAR(24) NULL ,
activitydate VARCHAR(29) NOT NULL,
transactionid VARCHAR(56) NOT NULL ,
eventname VARCHAR(255) ,
server_ip VARCHAR(40) ,
server_port VARCHAR(5) ,
client_host VARCHAR(255) ,
client_ip VARCHAR(40) ,
client_port VARCHAR(5) ,
userid VARCHAR(255) NULL ,
roles VARCHAR(1024) NULL ,
auth_component VARCHAR(255) NULL ,
resource_uri VARCHAR(255) NULL ,
resource_protocol VARCHAR(10) NULL ,
resource_method VARCHAR(10) NULL ,
resource_detail VARCHAR(255) NULL ,
http_method VARCHAR(10) NULL ,
http_path VARCHAR(255) NULL ,
http_querystring VARCHAR(255) NULL ,
status VARCHAR(20) NULL ,
elapsedtime VARCHAR(13) NULL ,
--CREATE INDEX SOPENIDM.idx_auditaccess_principal ON SOPENIDM.AUDITACCESS (principal(28) ASC);
-- -----------------------------------------------------
-- Table openidm.schedulerobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.SCHEDULEROBJECTS (
OBJECTTYPES_ID INTEGER NOT NULL,
OBJECTID VARCHAR(255) NOT NULL,
REV VARCHAR(38) NOT NULL,
CONSTRAINT FK_SCHEDULEROBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
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 TABLE SOPENIDM.SCHEDULEROBJECTPROPERTIES (
SCHEDULEROBJECTS_ID INTEGER NOT NULL,
PROPKEY VARCHAR(255) NOT NULL,
PROPTYPE VARCHAR(255),
PROPVALUE VARCHAR(2000),
FOREIGN KEY (SCHEDULEROBJECTS_ID )
ON DELETE CASCADE
-- -----------------------------------------------------
-- Table openidm.uinotification
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.UINOTIFICATION (
OBJECTID VARCHAR(38) NOT NULL,
REV VARCHAR(38) NOT NULL,
NOTIFICATIONTYPE VARCHAR(255) NOT NULL,
CREATEDATE VARCHAR(255) NOT NULL,
REQUESTER VARCHAR(255) NULL,
RECEIVERID VARCHAR(38) NOT NULL,
REQUESTERID VARCHAR(38) NULL,
NOTIFICATIONSUBTYPE VARCHAR(255) NULL,
-- -----------------------------------------------------
-- Table openidm.clusterobjects
-- -----------------------------------------------------
CREATE TABLE SOPENIDM.CLUSTEROBJECTS (
OBJECTTYPES_ID INTEGER NOT NULL,
OBJECTID VARCHAR(255) NOT NULL,
REV VARCHAR(38) NOT NULL,
CONSTRAINT FK_CLUSTEROBJECTS_OBJECTTYPES
FOREIGN KEY (OBJECTTYPES_ID )
ON DELETE CASCADE
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 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 )
ON DELETE CASCADE
CREATE INDEX SOPENIDM.IDX_CLUSTEROBJECTPROPERTIES_CLUSTEROBJECTS ON SOPENIDM.CLUSTEROBJECTPROPERTIES (CLUSTEROBJECTS_ID ASC);
-- -----------------------------------------------------
-- 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;