analytics/configuration/schemas/ans/sybase/ANS2_UPDATE_Sybase.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_sybase.SQL	   *DO NOT EDIT*	Data Store Version: 2		 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------
CREATE TABLE BAK_DIMENSIONMEMBER (id NUMERIC(38) NOT NULL, compositeID VARCHAR(255) NOT NULL, dimensionId NUMERIC(38) NOT NULL, PRIMARY KEY (id))
go
CREATE TABLE BAK_CONTEXT (id NUMERIC(38) NOT NULL, compositeID VARCHAR(255), PRIMARY KEY (id))
go
CREATE TABLE BAK_DIMENSION_METADATAITEM (dimensionId NUMERIC(38), metadataId NUMERIC(38))
go
CREATE TABLE BAK_MODEL_DIMENSION (modelId NUMERIC(38), dimensionId NUMERIC(38))
go
CREATE TABLE BAK_DIMENSION (id NUMERIC(38) NOT NULL, compositeID VARCHAR(255), PRIMARY KEY (id))
go

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

-------------------------------------------------------------------------------
--$140-- tables drop begin
-------------------------------------------------------------------------------
DROP TABLE ANN_DIMENSIONMEMBER
go
DROP TABLE ANN_CONTEXT
go
DROP TABLE ANN_DIMENSION_METADATAITEM
go
DROP TABLE ANN_MODEL_DIMENSION
go
DROP TABLE ANN_DIMENSION
go

-------------------------------------------------------------------------------
--$200-- recreate tables begin
-------------------------------------------------------------------------------
CREATE TABLE ANN_DIMENSIONMEMBER (id NUMERIC(38) NOT NULL, compID VARCHAR(255) NOT NULL, dimId NUMERIC(38) NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_dimIdcompID UNIQUE (dimId, compID))
go
CREATE TABLE ANN_CONTEXT (id NUMERIC(38) NOT NULL, compID VARCHAR(255), PRIMARY KEY (id))
go
CREATE TABLE ANN_DIMENSION_METADATAITEM (dimId NUMERIC(38), metadataId NUMERIC(38))
go
CREATE TABLE ANN_MODEL_DIMENSION (modelId NUMERIC(38), dimId NUMERIC(38))
go
CREATE TABLE ANN_DIMENSION (id NUMERIC(38) NOT NULL, compID VARCHAR(255), PRIMARY KEY (id))
go
-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

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

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

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

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

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