analytics/configuration/schemas/ans/informix/ANS2_UPDATE_Ifx.sql

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


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

-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------
CREATE TABLE BAK_DIMENSIONMEMBER (id DECIMAL(32) NOT NULL, compositeID VARCHAR(255) NOT NULL, dimensionId DECIMAL(32) NOT NULL, PRIMARY KEY (id));
CREATE TABLE BAK_CONTEXT (id DECIMAL(32) NOT NULL, compositeID VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE BAK_DIMENSION_METADATAITEM (dimensionId DECIMAL(32), metadataId DECIMAL(32));
CREATE TABLE BAK_MODEL_DIMENSION (modelId DECIMAL(32), dimensionId DECIMAL(32));
CREATE TABLE BAK_DIMENSION (id DECIMAL(32) 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 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 DECIMAL(32) NOT NULL, compID VARCHAR(255) NOT NULL, dimId DECIMAL(32) NOT NULL, PRIMARY KEY (id)) lock mode row;
CREATE TABLE ANN_CONTEXT (id DECIMAL(32) NOT NULL, compID VARCHAR(255), PRIMARY KEY (id)) lock mode row; 
CREATE TABLE ANN_DIMENSION_METADATAITEM (dimId DECIMAL(32), metadataId DECIMAL(32)) lock mode row;
CREATE TABLE ANN_MODEL_DIMENSION (modelId DECIMAL(32), dimId DECIMAL(32)) lock mode row;
CREATE TABLE ANN_DIMENSION (id DECIMAL(32) NOT NULL, compID VARCHAR(255), PRIMARY KEY (id)) lock mode row;
-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

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

ALTER TABLE ANN_DIMENSIONMEMBER
 ADD CONSTRAINT UNIQUE (dimId, compID) CONSTRAINT UNQ_dimIdcompID;
 
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_Informix.SQL			*	DO NOT EDIT	 * 		 
-------------------------------------------------------------------------------