analytics/configuration/schemas/delivery/sybase/NC835_UPDATE_SYBASE.sql

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


-------------------------------------------------------------------------------
-- NC835_UPDATE_SYBASE.SQL	       *DO NOT EDIT*		Data Store Version: 835 
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------

CREATE TABLE BC_NC_SCHEDULE(
   PK_SCHEDULE_ID                INTEGER NOT NULL,
   NAME                          NVARCHAR(255) NULL,
   DESCRIPTION                   NVARCHAR(1000) NULL,
   LASTMOD_BY                    CHAR(40) NULL,
   LASTMOD_TIME                  DATETIME DEFAULT GETDATE() NOT NULL,
   MODCOUNT                      INTEGER DEFAULT (-1),
   SCHEDULE_TYPE                 CHAR(6) NULL,
   TIMEZONE                      VARCHAR(100) NULL,
   START_DATE                    NUMERIC NULL,
   END_DATE                      NUMERIC NULL,
   REPEAT_FOREVER                INTEGER NULL,
   ACTIVE                        INTEGER NULL,
   VALID_SCHEDULE                INTEGER NULL,
   USE_DAY_NUMBER                INTEGER NULL,
   DAY_OF_MONTH                  INTEGER NULL,
   DAY_OF_WEEK                   INTEGER NULL,
   DAY_OF_WEEK_IN_MONTH          INTEGER NULL,
   MONTH_OF_YEAR                 INTEGER NULL,
   REPEAT_INTERVAL               INTEGER NULL,
   DAYS_OF_WEEK                  INTEGER NULL,
   FIXED_INTERVAL_TYPE           INTEGER NULL,
   FIXED_INTERVAL_IN_MILLIS      NUMERIC NULL,
   TRIGGER_ID                    NVARCHAR(255) NULL,
   PERIODICAL_PRODUCER_ID        CHAR(33) NULL,
   INTERDAY_RECUR_START          INTEGER NULL,
   INTERDAY_RECUR_END            INTEGER NULL,
   INTERDAY_RECUR_INTERVAL       INTEGER NULL
) lock datarows
go

CREATE TABLE BC_NC_SCHEDULE_QUEUE(
    SCHEDULE_ID         INTEGER NOT NULL,
    RUN_DATE            NUMERIC NULL
) lock datarows
go

CREATE TABLE BC_NC_TASKSCHEDULE(
   PK_TASKSCHEDULE_ID            INTEGER NOT NULL,
   FK_TASK_ID                    INTEGER NULL,
   NAME                          NVARCHAR(255) NULL,
   DESCRIPTION                   NVARCHAR(1000) NULL,
   LASTMOD_BY                    CHAR(40) NULL,
   MODCOUNT                      INTEGER DEFAULT (-1) NOT NULL,
   RUN_REQUIREMENTS              INTEGER NULL,
   LAST_EXECUTION_AT             NUMERIC NULL,
   NEXT_EXECUTION_REQUEST        NUMERIC NULL,
   SCHEDULE_DELAY                NUMERIC NULL,
   DELETE_AFTER_LAST_RUN         INTEGER NULL,
   PRIORITY          INTEGER DEFAULT (3) NOT NULL,
   SCHEDULED_BY          NVARCHAR(256) NULL
) lock datarows
go

CREATE TABLE BC_NC_SCHEDULED_EVENT_OVERRIDE(
    FK_TASKSCHEDULE_ID      INTEGER NOT NULL,
    EVENT_DATE          NUMERIC NOT NULL,
    DELAY_UNTIL         NUMERIC NULL,
    EVENT_STATUS                INTEGER NULL,
    EVENT_PRIORITY              INTEGER NULL,
    FK_SCHEDULE_ID          INTEGER NULL,
    CANCELLED_BY            NVARCHAR(2000) NULL
) lock datarows
go

-------------------------------------------------------------------------------
--$110-- backup inserts begin
-------------------------------------------------------------------------------

INSERT INTO BC_NC_SCHEDULE (PK_SCHEDULE_ID, NAME,DESCRIPTION, LASTMOD_BY, LASTMOD_TIME, MODCOUNT,SCHEDULE_TYPE, TIMEZONE, START_DATE, END_DATE, REPEAT_FOREVER, ACTIVE, VALID_SCHEDULE, USE_DAY_NUMBER, DAY_OF_WEEK, DAY_OF_WEEK_IN_MONTH, MONTH_OF_YEAR, REPEAT_INTERVAL, DAYS_OF_WEEK, FIXED_INTERVAL_TYPE, FIXED_INTERVAL_IN_MILLIS, TRIGGER_ID, PERIODICAL_PRODUCER_ID, INTERDAY_RECUR_START, INTERDAY_RECUR_END, INTERDAY_RECUR_INTERVAL) 
    SELECT PK_SCHEDULE_ID, NAME,DESCRIPTION, LASTMOD_BY, LASTMOD_TIME, MODCOUNT,SCHEDULE_TYPE, TIMEZONE, START_DATE, END_DATE, REPEAT_FOREVER, ACTIVE, VALID_SCHEDULE, USE_DAY_NUMBER, DAY_OF_WEEK, DAY_OF_WEEK_IN_MONTH, MONTH_OF_YEAR, REPEAT_INTERVAL, DAYS_OF_WEEK, FIXED_INTERVAL_TYPE, FIXED_INTERVAL_IN_MILLIS, TRIGGER_ID, PERIODICAL_PRODUCER_ID, INTERDAY_RECUR_START, INTERDAY_RECUR_END, INTERDAY_RECUR_INTERVAL FROM NC_SCHEDULE                                                                      
go


INSERT INTO BC_NC_SCHEDULE_QUEUE (SCHEDULE_ID, RUN_DATE) 
    SELECT SCHEDULE_ID, RUN_DATE FROM NC_SCHEDULE_QUEUE
go  

INSERT INTO BC_NC_TASKSCHEDULE (PK_TASKSCHEDULE_ID, FK_TASK_ID, NAME, DESCRIPTION, LASTMOD_BY, MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST, SCHEDULE_DELAY, DELETE_AFTER_LAST_RUN, PRIORITY, SCHEDULED_BY) 
    SELECT PK_TASKSCHEDULE_ID, FK_TASK_ID, NAME, DESCRIPTION, LASTMOD_BY, MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST, SCHEDULE_DELAY, DELETE_AFTER_LAST_RUN, PRIORITY, SCHEDULED_BY FROM NC_TASKSCHEDULE
go 

INSERT INTO BC_NC_SCHEDULED_EVENT_OVERRIDE (FK_TASKSCHEDULE_ID, EVENT_DATE, DELAY_UNTIL, EVENT_STATUS, EVENT_PRIORITY, FK_SCHEDULE_ID, CANCELLED_BY) 
    SELECT FK_TASKSCHEDULE_ID, EVENT_DATE, DELAY_UNTIL, EVENT_STATUS, EVENT_PRIORITY, FK_SCHEDULE_ID, CANCELLED_BY FROM NC_SCHEDULED_EVENT_OVERRIDE
go 
-------------------------------------------------------------------------------
--$120-- drop constraint begin
-------------------------------------------------------------------------------
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   drop CONSTRAINT F2R_TASKSCHEDULE_S 
go

ALTER TABLE NC_SCHEDULE
   drop CONSTRAINT PN_SCHEDULE 
go

ALTER TABLE NC_SCHEDULE_QUEUE
   drop CONSTRAINT PN_SCHEDULE_QUEUEI 
go

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   drop CONSTRAINT F1R_TASKSCHEDULE_S 
go
   
ALTER TABLE NC_TASKSCHEDULE
   drop CONSTRAINT PN_TASKSCHEDULE 
go

ALTER TABLE NC_TASKSCHEDULE
   drop CONSTRAINT F1N_TASKSCHEDULE     
go

ALTER TABLE NC_SCHEDULED_EVENT_OVERRIDE
    drop CONSTRAINT PN_EVENT_DATES 
go

ALTER TABLE NC_JMSQUEUE
   drop CONSTRAINT PN_NC_JMSQUEUE
go

ALTER TABLE NC_JOBQUEUE 
   drop CONSTRAINT PN_JOBQUEUE 
go

ALTER TABLE NC_METRICS
   drop CONSTRAINT PN_METRICS 
go

ALTER TABLE NC_SDS_INSTANCE
	drop CONSTRAINT PN_SDS_INSTANCE 
go

ALTER TABLE NC_TASK_QUEUE drop CONSTRAINT PN_TASK_QUEUE 
go
-------------------------------------------------------------------------------                                                                                                                         
--$130-- tables drop begin                                                                                                                                                                              
-------------------------------------------------------------------------------                                                                                                                         

DROP TABLE NC_SCHEDULE
go

DROP TABLE NC_SCHEDULE_QUEUE
go

DROP TABLE  NC_TASKSCHEDULE
go

DROP TABLE  NC_SCHEDULED_EVENT_OVERRIDE
go

DROP TABLE NC_JMSQUEUE
go

DROP TABLE NC_JOBQUEUE
go

DROP TABLE NC_METRICS
go

DROP TABLE  NC_SDS_INSTANCE
go

DROP TABLE  NC_TASK_QUEUE
go
-------------------------------------------------------------------------------
--$140-- recreate tables begin
-------------------------------------------------------------------------------

CREATE TABLE NC_SCHEDULE(
   PK_SCHEDULE_ID                INTEGER NOT NULL,
   NAME                          NVARCHAR(255) NULL,
   DESCRIPTION                   NVARCHAR(1000) NULL,
   LASTMOD_BY                    CHAR(40) NULL,
   LASTMOD_TIME                  DATETIME DEFAULT GETDATE() NOT NULL,
   MODCOUNT                      INTEGER DEFAULT (-1),
   SCHEDULE_TYPE                 CHAR(6) NULL,
   TIMEZONE                      VARCHAR(100) NULL,
   START_DATE                    NUMERIC(19) NULL,
   END_DATE                      NUMERIC(19) NULL,
   REPEAT_FOREVER                INTEGER NULL,
   ACTIVE                        INTEGER NULL,
   VALID_SCHEDULE                INTEGER NULL,
   USE_DAY_NUMBER                INTEGER NULL,
   DAY_OF_MONTH                  INTEGER NULL,
   DAY_OF_WEEK                   INTEGER NULL,
   DAY_OF_WEEK_IN_MONTH          INTEGER NULL,
   MONTH_OF_YEAR                 INTEGER NULL,
   REPEAT_INTERVAL               INTEGER NULL,
   DAYS_OF_WEEK                  INTEGER NULL,
   FIXED_INTERVAL_TYPE           INTEGER NULL,
   FIXED_INTERVAL_IN_MILLIS      NUMERIC(19) NULL,
   TRIGGER_ID                    NVARCHAR(255) NULL,
   PERIODICAL_PRODUCER_ID        CHAR(33) NULL,
   INTERDAY_RECUR_START          INTEGER NULL,
   INTERDAY_RECUR_END            INTEGER NULL,
   INTERDAY_RECUR_INTERVAL       INTEGER NULL
) lock datarows
go

CREATE TABLE NC_SCHEDULE_QUEUE(
    SCHEDULE_ID         INTEGER NOT NULL,
    RUN_DATE            NUMERIC(19) NULL
) lock datarows
go

CREATE TABLE NC_TASKSCHEDULE(
   PK_TASKSCHEDULE_ID            INTEGER NOT NULL,
   FK_TASK_ID                    INTEGER NULL,
   NAME                          NVARCHAR(255) NULL,
   DESCRIPTION                   NVARCHAR(1000) NULL,
   LASTMOD_BY                    CHAR(40) NULL,
   MODCOUNT                      INTEGER DEFAULT (-1) NOT NULL,
   RUN_REQUIREMENTS              INTEGER NULL,
   LAST_EXECUTION_AT             NUMERIC(19) NULL,
   NEXT_EXECUTION_REQUEST        NUMERIC(19) NULL,
   SCHEDULE_DELAY                NUMERIC(19) NULL,
   DELETE_AFTER_LAST_RUN         INTEGER NULL,
   PRIORITY          INTEGER DEFAULT (3) NOT NULL,
   SCHEDULED_BY          NVARCHAR(256) NULL
) lock datarows
go

CREATE TABLE NC_SCHEDULED_EVENT_OVERRIDE(
    FK_TASKSCHEDULE_ID      INTEGER NOT NULL,
    EVENT_DATE          NUMERIC(19) NOT NULL,
    DELAY_UNTIL         NUMERIC(19) NULL,
    EVENT_STATUS                INTEGER NULL,
    EVENT_PRIORITY              INTEGER NULL,
    FK_SCHEDULE_ID          INTEGER NULL,
    CANCELLED_BY            NVARCHAR(2000) NULL
) lock datarows
go

CREATE TABLE NC_JMSQUEUE (
	QUEUE_ENTRY_ID               INTEGER NOT NULL,
	QUEUE_NAME                   NVARCHAR(50) NULL,
	QUEUE_ENTRY                  IMAGE NULL,
	SDS_INSTANCE_ID		         CHAR (32) NULL,
	DATE_ENTERED 		         NUMERIC(19) NOT NULL,
	REDELIVER_DATE 			NUMERIC(19) NULL,
   	REDELIVER_COUNT 		INTEGER DEFAULT (0)
) lock datarows
go

CREATE TABLE NC_JOBQUEUE (
    PK_RUN_ID          	CHAR (45) NOT NULL ,
    SERVICE_NAME 	CHAR (40) NULL,
    SERVER_GROUP 	CHAR (40) NULL,
    DATE_ENTERED 	NUMERIC(19) NOT NULL ,
    JOB 		IMAGE NOT NULL 
) lock datarows
go

CREATE TABLE NC_METRICS(
   NAME 		NVARCHAR(255) 	NOT NULL,
   LASTMOD_TIME 	NUMERIC(19, 0)	DEFAULT (0) NOT NULL,
   VALUE 		NUMERIC(19, 0)	DEFAULT (0) NOT NULL,
   ACTIVE 		INTEGER 	DEFAULT (0) NOT NULL
) lock datarows
go

CREATE TABLE NC_SDS_INSTANCE (
    PK_INSTANCE_ID 		CHAR (32) NOT NULL ,
    PK_INSTANCE_URL 		NVARCHAR (255) NOT NULL ,
    LAST_UPDATE 		NUMERIC(19) NOT NULL ,
    CLEANUP_INSTANCE_ID 	CHAR (32) NULL 
) lock datarows
go

CREATE TABLE NC_TASK_QUEUE (
	TASK_ID 			CHAR(45) NOT NULL,
	SERVICE_NAME 		NVARCHAR(40) NULL,
	SERVER_GROUP 		NVARCHAR(40) NULL,
	DATE_ENTERED 		NUMERIC(19)NOT NULL,
	TASK 				IMAGE NULL,
	STATUS 				INTEGER NOT NULL,
	PRIORITY 			INTEGER NOT NULL,
	RESTART_ID              CHAR(45) NULL,
	SDS_INSTANCE_ID 	CHAR (32) NULL,
	HISTORY_STORE_ID 	CHAR (34) NULL,
	ACTUAL_EXECUTION_TIME   NUMERIC(19) NULL,
	ACTUAL_COMPLETION_TIME  NUMERIC(19) NULL,
	DISPATCHER_ID		CHAR(45) NULL,
	SCHEDULE_TRIGGER_NAME   NVARCHAR(255) NULL,
	SCHEDULE_TYPE		CHAR(15) NULL,
	ACCOUNT_PATH		NVARCHAR(1024) NULL,
	STOREID 		CHAR(34) NULL,
	OBJECT_CLASS 		CHAR(64) NULL,
	OWNER_STOREID 		NVARCHAR(256) NULL,
	PROCESS_ID          INTEGER NULL
) lock datarows
go

-------------------------------------------------------------------------------
--$150-- primary keys add begin
-------------------------------------------------------------------------------

ALTER TABLE NC_SCHEDULE
   ADD CONSTRAINT PN_SCHEDULE PRIMARY KEY (PK_SCHEDULE_ID)
go

ALTER TABLE NC_SCHEDULE_QUEUE
   ADD CONSTRAINT PN_SCHEDULE_QUEUEI PRIMARY KEY (SCHEDULE_ID)
go

   
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F2R_TASKSCHEDULE_S FOREIGN KEY (FK_SCHEDULE_ID)
      REFERENCES NC_SCHEDULE
go

ALTER TABLE NC_TASKSCHEDULE
   ADD CONSTRAINT PN_TASKSCHEDULE PRIMARY KEY (PK_TASKSCHEDULE_ID)
go

ALTER TABLE NC_TASKSCHEDULE
   ADD CONSTRAINT F1N_TASKSCHEDULE FOREIGN KEY (FK_TASK_ID)
      REFERENCES NC_TASK
go

ALTER TABLE NC_SCHEDULED_EVENT_OVERRIDE
    ADD CONSTRAINT PN_EVENT_DATES PRIMARY KEY (FK_TASKSCHEDULE_ID, EVENT_DATE)
go

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F1R_TASKSCHEDULE_S FOREIGN KEY (FK_TASKSCHEDULE_ID)
      REFERENCES NC_TASKSCHEDULE
go

ALTER TABLE NC_JMSQUEUE
   ADD CONSTRAINT PN_NC_JMSQUEUE PRIMARY KEY (QUEUE_ENTRY_ID)
go

ALTER TABLE NC_JOBQUEUE 
   ADD CONSTRAINT PN_JOBQUEUE PRIMARY KEY (PK_RUN_ID)
go

ALTER TABLE NC_METRICS
   ADD CONSTRAINT PN_METRICS PRIMARY KEY (NAME)
go

ALTER TABLE NC_SDS_INSTANCE
	ADD CONSTRAINT PN_SDS_INSTANCE PRIMARY KEY (PK_INSTANCE_ID)
go

ALTER TABLE NC_TASK_QUEUE ADD CONSTRAINT PN_TASK_QUEUE PRIMARY KEY (TASK_ID)
go
-------------------------------------------------------------------------------
--$160-- indices create begin
-------------------------------------------------------------------------------
CREATE INDEX I1N_TASKSCHEDULE ON NC_TASKSCHEDULE (
   FK_TASK_ID)
go

CREATE INDEX I1N_EVENT_DATES ON NC_SCHEDULED_EVENT_OVERRIDE (
   FK_TASKSCHEDULE_ID)
go

CREATE INDEX IX_NC_JMSQUEUE ON NC_JMSQUEUE(QUEUE_NAME, DATE_ENTERED, SDS_INSTANCE_ID) 
go

CREATE INDEX IX_NC_JMSQUEUE1 ON NC_JMSQUEUE(QUEUE_NAME, SDS_INSTANCE_ID) 
go

CREATE INDEX IN_JOBQ_SELECTOR ON NC_JOBQUEUE (SERVICE_NAME, SERVER_GROUP, DATE_ENTERED)
go

CREATE INDEX IX_NC_TASK_QUEUE_SELECTOR ON NC_TASK_QUEUE (
	STATUS, SERVICE_NAME, SERVER_GROUP, DATE_ENTERED)
go
-------------------------------------------------------------------------------
--$170-- server data inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_DB_VERSION (DB_VERSION_ID,BUILD) VALUES(835,'build no.')
go

-------------------------------------------------------------------------------
 --$180-- restore inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_SCHEDULE (PK_SCHEDULE_ID, NAME,DESCRIPTION, LASTMOD_BY, LASTMOD_TIME, MODCOUNT,SCHEDULE_TYPE, TIMEZONE, START_DATE, END_DATE, REPEAT_FOREVER, ACTIVE, VALID_SCHEDULE, USE_DAY_NUMBER, DAY_OF_WEEK, DAY_OF_WEEK_IN_MONTH, MONTH_OF_YEAR, REPEAT_INTERVAL, DAYS_OF_WEEK, FIXED_INTERVAL_TYPE, FIXED_INTERVAL_IN_MILLIS, TRIGGER_ID, PERIODICAL_PRODUCER_ID, INTERDAY_RECUR_START, INTERDAY_RECUR_END, INTERDAY_RECUR_INTERVAL) 
    SELECT PK_SCHEDULE_ID, NAME,DESCRIPTION, LASTMOD_BY, LASTMOD_TIME, MODCOUNT,SCHEDULE_TYPE, TIMEZONE, START_DATE, END_DATE, REPEAT_FOREVER, ACTIVE, VALID_SCHEDULE, USE_DAY_NUMBER, DAY_OF_WEEK, DAY_OF_WEEK_IN_MONTH, MONTH_OF_YEAR, REPEAT_INTERVAL, DAYS_OF_WEEK, FIXED_INTERVAL_TYPE, FIXED_INTERVAL_IN_MILLIS, TRIGGER_ID, PERIODICAL_PRODUCER_ID, INTERDAY_RECUR_START, INTERDAY_RECUR_END, INTERDAY_RECUR_INTERVAL FROM BC_NC_SCHEDULE                                                                      
go 

INSERT INTO NC_SCHEDULE_QUEUE (SCHEDULE_ID, RUN_DATE) 
    SELECT SCHEDULE_ID, RUN_DATE FROM BC_NC_SCHEDULE_QUEUE
go  

INSERT INTO NC_TASKSCHEDULE (PK_TASKSCHEDULE_ID, FK_TASK_ID, NAME, DESCRIPTION, LASTMOD_BY, MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST, SCHEDULE_DELAY, DELETE_AFTER_LAST_RUN, PRIORITY, SCHEDULED_BY) 
    SELECT PK_TASKSCHEDULE_ID, FK_TASK_ID, NAME, DESCRIPTION, LASTMOD_BY, MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST, SCHEDULE_DELAY, DELETE_AFTER_LAST_RUN, PRIORITY, SCHEDULED_BY FROM BC_NC_TASKSCHEDULE
go

INSERT INTO NC_SCHEDULED_EVENT_OVERRIDE (FK_TASKSCHEDULE_ID, EVENT_DATE, DELAY_UNTIL, EVENT_STATUS, EVENT_PRIORITY, FK_SCHEDULE_ID, CANCELLED_BY) 
    SELECT FK_TASKSCHEDULE_ID, EVENT_DATE, DELAY_UNTIL, EVENT_STATUS, EVENT_PRIORITY, FK_SCHEDULE_ID, CANCELLED_BY FROM BC_NC_SCHEDULED_EVENT_OVERRIDE
go  
-------------------------------------------------------------------------------
 --$190-- backup drop begin
-------------------------------------------------------------------------------

DROP TABLE BC_NC_SCHEDULE
go

DROP TABLE BC_NC_SCHEDULE_QUEUE
go

DROP TABLE BC_NC_TASKSCHEDULE
go

DROP TABLE BC_NC_SCHEDULED_EVENT_OVERRIDE
go
-------------------------------------------------------------------------------
--$200-- end sql by function
-------------------------------------------------------------------------------
--  END NC835_UPDATE_SYBASE.SQL         *   DO NOT EDIT  *       
-------------------------------------------------------------------------------