analytics/configuration/schemas/ans/zosdb2/ANS2_CREATE_Db2Zos.sql

--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: HTS
--    
--    (c) Copyright IBM Corp. 2003, 2011.
--    
--    US Government Users Restricted RigANS - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    

--
--  Modifications:
--
--  1) Change NCCOG  to the desired database name.
--  2) Note: CCSID is an estimated value, your
--           requirements may differ.

-------------------------------------------------------------------------------
-- ANS_CREATE_Db2Zos.sql         *  DO NOT EDIT  *          Data Store Version: 2
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$200-- tables create begin
-------------------------------------------------------------------------------

CREATE TABLE ANN_ANNOTATION(
    id BIGINT NOT NULL,
    creationTime TIMESTAMP,
    lastModificationTime TIMESTAMP,
    locale VARCHAR(255),
    owner VARCHAR(255),
    parentId VARCHAR(33),
    secondaryParentId VARCHAR(33),
    contextId BIGINT,
    textId BIGINT NOT NULL
) IN NCCOG.ANNANNOT CCSID UNICODE;

CREATE TABLE ANN_ANNOTATION_PROPS(
    id BIGINT NOT NULL,
    name VARCHAR(255),
    value CLOB,
    annotationId BIGINT NOT NULL
) IN NCCOG.ANNANNPR CCSID UNICODE;

CREATE TABLE ANN_ANNOTATION_TEXT(
    id BIGINT NOT NULL,
    description VARCHAR(3600),
    name VARCHAR(128)
) IN NCCOG.ANNANNTX CCSID UNICODE;

CREATE TABLE ANN_CONTEXT(
    id BIGINT NOT NULL,
    compID VARCHAR(255)
) IN NCCOG.ANNANNCN CCSID UNICODE;

CREATE TABLE ANN_CONTEXT_DIMENSIONMEMBER(
    contextId BIGINT,
    dimensionMemberId BIGINT
) IN NCCOG.ANNCTXDI CCSID UNICODE;

CREATE TABLE ANN_DATAITEM(
    id BIGINT NOT NULL,
    name VARCHAR(64) NOT NULL,
    vE SMALLINT NOT NULL,
    value VARCHAR(255) NOT NULL
) IN NCCOG.ANNDATIT CCSID UNICODE;

CREATE TABLE ANN_DIMENSION(
    id BIGINT NOT NULL,
    compID VARCHAR(255)
) IN NCCOG.ANNDIMEN CCSID UNICODE;

CREATE TABLE ANN_DIMENSIONMEMBER(
    id BIGINT NOT NULL,
    compID VARCHAR(255) NOT NULL,
    dimId BIGINT NOT NULL
) IN NCCOG.ANNDIMMM CCSID UNICODE;

CREATE TABLE ANN_DIMENSIONMEMBER_DATAITEM(
    dimensionMemberId BIGINT,
    dataItemId BIGINT
) IN NCCOG.ANNDIMDI CCSID UNICODE;

CREATE TABLE ANN_DIMENSION_METADATAITEM(
    dimId BIGINT,
    metadataId BIGINT
) IN NCCOG.ANNDIMMT CCSID UNICODE;

CREATE TABLE ANN_IDTABLE(
    PK VARCHAR(255) NOT NULL,
    IDMAX BIGINT
) IN NCCOG.ANNIDTBL CCSID UNICODE;

CREATE TABLE ANN_METADATAITEM(
    id BIGINT NOT NULL,
    name VARCHAR(64) NOT NULL,
    vE SMALLINT NOT NULL,
    value VARCHAR(255) NOT NULL
) IN NCCOG.ANNMETIT CCSID UNICODE;

CREATE TABLE ANN_MODEL(
    id BIGINT NOT NULL
) IN NCCOG.ANNMODEL CCSID UNICODE;

CREATE TABLE ANN_MODEL_DIMENSION(
    modelId BIGINT,
    dimId BIGINT
) IN NCCOG.ANNMDLDI CCSID UNICODE;

CREATE TABLE ANN_VERSION(
    id VARCHAR(32) NOT NULL,
    version INTEGER
) IN NCCOG.ANNVERSI CCSID UNICODE;

CREATE AUX TABLE ANN_PROPSAUX
      IN NCCOG.ANNLOBPR
      STORES ANN_ANNOTATION_PROPS
      COLUMN value;

-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE UNIQUE INDEX PN_ANSANN ON ANN_ANNOTATION(id);

CREATE UNIQUE INDEX PN_ANSANNP ON ANN_ANNOTATION_PROPS(id);

CREATE UNIQUE INDEX PN_ANSANNT ON ANN_ANNOTATION_TEXT(id);

CREATE UNIQUE INDEX PN_ANSANC ON ANN_CONTEXT(id);

CREATE UNIQUE INDEX PN_ANSDAI ON ANN_DATAITEM(id);

CREATE UNIQUE INDEX I_ANN_DAI ON ANN_DATAITEM(name, value, vE);

CREATE UNIQUE INDEX PN_ANSDIM ON ANN_DIMENSION(id);

CREATE UNIQUE INDEX PN_ANSDIR ON ANN_DIMENSIONMEMBER(id);

CREATE UNIQUE INDEX I_ANN_DIR ON ANN_DIMENSIONMEMBER(dimId, compID);

CREATE UNIQUE INDEX PN_ANSIDT ON ANN_IDTABLE(PK);

CREATE UNIQUE INDEX PN_ANSMDI ON ANN_METADATAITEM(id);

CREATE UNIQUE INDEX I_ANN_MDI ON ANN_METADATAITEM(value, name, vE);

CREATE UNIQUE INDEX PN_ANSMDL ON ANN_MODEL(id);

CREATE UNIQUE INDEX PN_ANSVER ON ANN_VERSION(id);

CREATE INDEX I_NN_NTTN_CONTEXT ON ANN_ANNOTATION (contextId);

CREATE INDEX I_NN_NTTN_TEXT ON ANN_ANNOTATION (textId);

CREATE INDEX I_NN_NRPS_ANNOTATION ON ANN_ANNOTATION_PROPS (annotationId);

CREATE UNIQUE INDEX I_NN_CTXT_COMPID ON ANN_CONTEXT (compID);

CREATE INDEX I_NN_CMBR_CONTEXTID ON ANN_CONTEXT_DIMENSIONMEMBER (contextId);

CREATE INDEX I_NN_CMBR_ELEMENT ON ANN_CONTEXT_DIMENSIONMEMBER (dimensionMemberId);

CREATE UNIQUE INDEX I_NN_DNSN_COMPID ON ANN_DIMENSION (compID);

CREATE INDEX I_NN_DMBR_COMPID ON ANN_DIMENSIONMEMBER (compID);

CREATE INDEX I_NN_DMBR_DIMENSION ON ANN_DIMENSIONMEMBER (dimId);

CREATE INDEX I_NN_DTTM_DIMENSIONMEMBERID ON ANN_DIMENSIONMEMBER_DATAITEM (dimensionMemberId);

CREATE INDEX I_NN_DTTM_ELEMENT ON ANN_DIMENSIONMEMBER_DATAITEM (dataItemId);

CREATE INDEX I_NN_DTTM_DIMID ON ANN_DIMENSION_METADATAITEM (dimId);

CREATE INDEX I_NN_DTTM_ELEMENT1 ON ANN_DIMENSION_METADATAITEM (metadataId);

CREATE INDEX I_NN_MNSN_ELEMENT ON ANN_MODEL_DIMENSION (dimId);

CREATE INDEX I_NN_MNSN_MODELID ON ANN_MODEL_DIMENSION (modelId);

CREATE UNIQUE INDEX IX_ANN_PROPSAUX ON ANN_PROPSAUX;

-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

ALTER TABLE ANN_ANNOTATION ADD PRIMARY KEY (id);

ALTER TABLE ANN_ANNOTATION_PROPS ADD PRIMARY KEY (id);

ALTER TABLE ANN_ANNOTATION_TEXT ADD PRIMARY KEY (id);

ALTER TABLE ANN_CONTEXT ADD PRIMARY KEY (id);

ALTER TABLE ANN_DATAITEM ADD PRIMARY KEY (id);

ALTER TABLE ANN_DIMENSION ADD PRIMARY KEY (id);

ALTER TABLE ANN_DIMENSIONMEMBER ADD PRIMARY KEY (id);

ALTER TABLE ANN_IDTABLE ADD PRIMARY KEY (PK);

ALTER TABLE ANN_METADATAITEM ADD PRIMARY KEY (id);

ALTER TABLE ANN_MODEL ADD PRIMARY KEY (id);

ALTER TABLE ANN_VERSION ADD PRIMARY KEY (id);

-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------

INSERT INTO ANN_VERSION(id, version) VALUES ('2', 2);

-------------------------------------------------------------------------------
--	END ANS_CREATE_Db2Zos.sql        *  DO NOT EDIT  *
-------------------------------------------------------------------------------