analytics/configuration/schemas/ans/db2/ANS2_UPDATE_Db2.sql

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


-------------------------------------------------------------------------------
-- ANS2_UPDATE_Db2.SQL	   *DO NOT EDIT*	Data Store Version: 2		 
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------
CREATE TABLE BAK_DIMENSIONMEMBER (id BIGINT NOT NULL, compositeID VARCHAR(255) NOT NULL, dimensionId BIGINT NOT NULL, PRIMARY KEY (id));
CREATE TABLE BAK_CONTEXT (id BIGINT NOT NULL, compositeID VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE BAK_DIMENSION_METADATAITEM (dimensionId BIGINT, metadataId BIGINT);
CREATE TABLE BAK_MODEL_DIMENSION (modelId BIGINT, dimensionId BIGINT);
CREATE TABLE BAK_DIMENSION (id BIGINT NOT NULL, compositeID VARCHAR(255), PRIMARY KEY (id));

-------------------------------------------------------------------------------
--$110-- backup inserts begin
-------------------------------------------------------------------------------
INSERT INTO BAK_DIMENSIONMEMBER (id, compositeID, dimensionId) SELECT id, compID, dimId  FROM ANN_DIMENSIONMEMBER;
INSERT INTO BAK_CONTEXT (id, compositeID) SELECT id, compID FROM ANN_CONTEXT;
INSERT INTO BAK_DIMENSION_METADATAITEM (dimensionId, metadataId)SELECT dimId, metadataId FROM ANN_DIMENSION_METADATAITEM;
INSERT INTO BAK_MODEL_DIMENSION (modelId, dimensionId)SELECT modelId, dimId FROM ANN_MODEL_DIMENSION;
INSERT INTO BAK_DIMENSION (id, compositeID)SELECT id, compID FROM ANN_DIMENSION;
-------------------------------------------------------------------------------
--$130-- views drop begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$140-- tables drop begin
-------------------------------------------------------------------------------

DROP INDEX I_NN_CTXT_COMPOSIT;
DROP INDEX I_NN_DNSN_COMPOSIT;
DROP INDEX I_NN_DMBR_COMPOSIT;
DROP INDEX I_NN_DMBR_DIMENSIO;
DROP INDEX I_NN_DTTM_DIMENSIO;
DROP INDEX I_NN_MNSN_ELEMENT;
DROP INDEX I_NN_MNSN_MODELID;

DROP TABLE ANN_DIMENSIONMEMBER;
DROP TABLE ANN_CONTEXT;
DROP TABLE ANN_DIMENSION_METADATAITEM;
DROP TABLE ANN_MODEL_DIMENSION;
DROP TABLE ANN_DIMENSION;

-------------------------------------------------------------------------------
--$200-- recreate tables begin
-------------------------------------------------------------------------------

CREATE TABLE ANN_DIMENSIONMEMBER (id BIGINT NOT NULL, compID VARCHAR(255) NOT NULL, dimId BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_dimIdcompID UNIQUE (dimId, compID));
CREATE TABLE ANN_CONTEXT (id BIGINT NOT NULL, compID VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE ANN_DIMENSION_METADATAITEM (dimId BIGINT, metadataId BIGINT);
CREATE TABLE ANN_MODEL_DIMENSION (modelId BIGINT, dimId BIGINT);
CREATE TABLE ANN_DIMENSION (id BIGINT NOT NULL, compID VARCHAR(255), PRIMARY KEY (id));
-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$203-- alter tables begin
-------------------------------------------------------------------------------

CREATE UNIQUE INDEX I_NN_CTXT_COMPOSIT ON ANN_CONTEXT (compID);
CREATE UNIQUE INDEX I_NN_DNSN_COMPOSIT ON ANN_DIMENSION (compID);
CREATE INDEX I_NN_DMBR_COMPOSIT ON ANN_DIMENSIONMEMBER (compID);
CREATE INDEX I_NN_DMBR_DIMENSIO ON ANN_DIMENSIONMEMBER (dimId);
CREATE INDEX I_NN_DTTM_DIMENSIO ON ANN_DIMENSION_METADATAITEM (dimId);
CREATE INDEX I_NN_MNSN_ELEMENT ON ANN_MODEL_DIMENSION (dimId);

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

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------
INSERT INTO ANN_VERSION(id, version) VALUES ('2', 2);
 -------------------------------------------------------------------------------
 --$230-- restore inserts begin
 -------------------------------------------------------------------------------
INSERT INTO ANN_DIMENSIONMEMBER (id, compID, dimId) SELECT id, compositeID, dimensionId  FROM BAK_DIMENSIONMEMBER;
INSERT INTO ANN_CONTEXT (id, compID) SELECT id, compositeID FROM BAK_CONTEXT;
INSERT INTO ANN_DIMENSION_METADATAITEM (dimId, metadataId)SELECT dimensionId, metadataId FROM BAK_DIMENSION_METADATAITEM;
INSERT INTO ANN_MODEL_DIMENSION (modelId, dimId)SELECT modelId, dimensionId FROM BAK_MODEL_DIMENSION;
INSERT INTO ANN_DIMENSION (id, compID)SELECT id, compositeID FROM BAK_DIMENSION;
 -------------------------------------------------------------------------------
 --$250-- backup drop begin
 -------------------------------------------------------------------------------
DROP TABLE BAK_DIMENSIONMEMBER;
DROP TABLE BAK_CONTEXT;
DROP TABLE BAK_DIMENSION_METADATAITEM;
DROP TABLE BAK_MODEL_DIMENSION;
DROP TABLE BAK_DIMENSION;

-------------------------------------------------------------------------------
--$260-- end sql by function
-------------------------------------------------------------------------------
-- END ANS2_UPDATE_Db2.SQL			*	DO NOT EDIT	 * 		 
-------------------------------------------------------------------------------