-- -- 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 * -------------------------------------------------------------------------------