analytics/configuration/schemas/delivery/sqlserver/NC31_UPDATE_MS.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.
--    


-------------------------------------------------------------------------------
-- NC31_UPDATE_MS.SQL	       *DO NOT EDIT*		Data Store Version: 31		 
-------------------------------------------------------------------------------

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

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

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

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

CREATE TABLE BC_NC_TASK_LOG(
   LOG_ID                        INTEGER NOT NULL,
   TASK_NAME                     NVARCHAR(255) NOT NULL,
   TASK_ID                       INTEGER,
   REQUIRED_RUN_TIME             NUMERIC,
   ACTUAL_RUN_TIME               NUMERIC,
   NEXT_RUN_TIME                 NUMERIC,
   FINAL_STATE                   INTEGER NOT NULL
)
go

CREATE TABLE BC_NC_TASKSCHEDULE_CANCELLED_RUNS(
	FK_TASKSCHEDULE_ID 	INTEGER NOT NULL,
	CANCELLED_DATE		NUMERIC NOT NULL,
	FK_SCHEDULE_ID      	INTEGER,
	AUDIT_USER          	NVARCHAR(2000)
)
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_MONTH,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) 
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_MONTH,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
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) 
       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 
       FROM NC_TASKSCHEDULE
go

INSERT INTO BC_NC_TASK_LOG(
       LOG_ID,TASK_NAME,TASK_ID,REQUIRED_RUN_TIME,ACTUAL_RUN_TIME,NEXT_RUN_TIME,FINAL_STATE)
       SELECT LOG_ID,TASK_NAME,TASK_ID,REQUIRED_RUN_TIME,ACTUAL_RUN_TIME,NEXT_RUN_TIME,FINAL_STATE 
       FROM NC_TASK_LOG
go
       
INSERT INTO BC_NC_TASKSCHEDULE_CANCELLED_RUNS (
       FK_TASKSCHEDULE_ID,CANCELLED_DATE,FK_SCHEDULE_ID,AUDIT_USER)
       SELECT FK_TASKSCHEDULE_ID,CANCELLED_DATE,FK_SCHEDULE_ID,AUDIT_USER
       FROM NC_TASKSCHEDULE_CANCELLED_RUNS
go

-------------------------------------------------------------------------------
--$130-- views drop begin
-------------------------------------------------------------------------------

DROP VIEW V_NC_TASK_LINK
go

DROP VIEW V_NC_AGENT_LOG
go

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

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   DROP CONSTRAINT F1R_TASKSCHEDULE_S
go
   
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   DROP CONSTRAINT F2R_TASKSCHEDULE_S
go

DROP TABLE NC_SCHEDULE
go

DROP TABLE NC_SCHEDULE_QUEUE
go

DROP TABLE NC_TASKSCHEDULE
go

DROP TABLE NC_TASK_LOG
go

DROP TABLE NC_TASKSCHEDULE_CANCELLED_RUNS
go

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

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

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

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

CREATE TABLE NC_TASK_LOG(
   LOG_ID                        INTEGER NOT NULL,
   TASK_NAME                     NVARCHAR(255) NOT NULL,
   TASK_ID                       INTEGER,
   REQUIRED_RUN_TIME             NUMERIC(19),
   ACTUAL_RUN_TIME               NUMERIC(19),
   NEXT_RUN_TIME                 NUMERIC(19),
   FINAL_STATE                   INTEGER NOT NULL
)
go

CREATE TABLE NC_TASKSCHEDULE_CANCELLED_RUNS(
	FK_TASKSCHEDULE_ID 	INTEGER NOT NULL,
	CANCELLED_DATE		NUMERIC(19) NOT NULL,
	FK_SCHEDULE_ID      	INTEGER,
	AUDIT_USER          	NVARCHAR(2000)
)
go


-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------
   
CREATE INDEX I1N_TASKSCHEDULE ON NC_TASKSCHEDULE (
   FK_TASK_ID)
go
   
CREATE INDEX I1N_CANCELLED_DATE ON NC_TASKSCHEDULE_CANCELLED_RUNS (
   FK_TASKSCHEDULE_ID)
go

-------------------------------------------------------------------------------
--$202-- 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 NC_TASKSCHEDULE
   ADD CONSTRAINT PN_TASKSCHEDULE PRIMARY KEY (PK_TASKSCHEDULE_ID)
go
   
ALTER TABLE NC_TASK_LOG
   ADD CONSTRAINT PN_TASK_LOG PRIMARY KEY (LOG_ID)
go
   
ALTER TABLE NC_TASKSCHEDULE_CANCELLED_RUNS
   ADD CONSTRAINT PN_CANCELLED_DATES PRIMARY KEY (FK_TASKSCHEDULE_ID,CANCELLED_DATE)
go

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

CREATE VIEW V_NC_TASK_LINK
AS
SELECT NC_TASK.PK_TASK_ID, NC_TASK.NAME, 
    NC_TASK.DESCRIPTION, 
    NC_OBJECTCATALOGUE.SUBTYPE AS AGENT_SUBTYPE, 
    NC_OBJECTCATALOGUE.TYPE AS AGENT_TYPE, 
    NC_TASK.FK_BIE_ID, NC_TASK.THRESHOLD, 
    NC_TASK.WEIGHTS, NC_TASK.TASK_TYPE, 
    NC_TASK.DATEFORMAT, NC_TASK.DETECTION_OPTION, 
    NC_TASK.LASTMOD_TIME, NC_TASK.MODCOUNT, 
    NC_TASK.LASTMOD_BY, 
    NC_TASKSCHEDULE.PK_TASKSCHEDULE_ID, 
    R_MESSAGESTRUCT_TASK.FK_MESSAGESTRUCT_ID, 
    R_AGENT_ESCALATIONAGENT.FK_CHILD_AGENT_ID, 
    R_RULE_PARAMETER.LITERAL_TYPE, 
    R_RULE_PARAMETER.FK_RULE_ID, 
    NC_RULE.FK_RULETYPE
FROM NC_TASKSCHEDULE RIGHT OUTER JOIN
    NC_TASK INNER JOIN
    NC_OBJECTCATALOGUE ON 
    NC_TASK.PK_TASK_ID = NC_OBJECTCATALOGUE.PK_OID LEFT OUTER
     JOIN
    R_MESSAGESTRUCT_TASK ON 
    NC_TASK.PK_TASK_ID = R_MESSAGESTRUCT_TASK.FK_TASK_ID
     LEFT OUTER JOIN
    R_AGENT_ESCALATIONAGENT ON 
    NC_TASK.PK_TASK_ID = R_AGENT_ESCALATIONAGENT.FK_PARENT_AGENT_ID
     LEFT OUTER JOIN
    R_RULE_PARAMETER ON 
    NC_TASK.PK_TASK_ID = R_RULE_PARAMETER.FK_TASK_ID ON
     NC_TASKSCHEDULE.FK_TASK_ID = NC_TASK.PK_TASK_ID LEFT
     OUTER JOIN
    NC_RULE ON 
    R_RULE_PARAMETER.FK_RULE_ID = NC_RULE.PK_RULE_ID
go

CREATE VIEW V_NC_AGENT_LOG AS SELECT     
	NC_TASK_LOG.LOG_ID,
	NC_TASK.NAME AS AGENT_NAME, 
	NC_TASK.PK_TASK_ID as AGENT_ID,   
	NC_TASK_LOG.REQUIRED_RUN_TIME,  
	NC_TASK_LOG.ACTUAL_RUN_TIME, 
	NC_TASK_LOG.NEXT_RUN_TIME, 
	NC_TASK_LOG.FINAL_STATE AS FINAL_STATUS, 
	NC_AGENT_MESSAGE.TIMESTAMP AS MESSAGE_LOG_TIME, 
	NC_AGENT_MESSAGE.MESSAGE
FROM NC_TASK LEFT OUTER JOIN
	NC_TASK_LOG ON NC_TASK.PK_TASK_ID = NC_TASK_LOG.TASK_ID 
LEFT OUTER JOIN
	NC_AGENT_MESSAGE ON NC_TASK_LOG.LOG_ID = NC_AGENT_MESSAGE.LOG_ID
go

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------

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

 -------------------------------------------------------------------------------
 --$230-- 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_MONTH,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) 
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_MONTH,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
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) 
       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 
       FROM BC_NC_TASKSCHEDULE
go

INSERT INTO NC_TASK_LOG(
       LOG_ID,TASK_NAME,TASK_ID,REQUIRED_RUN_TIME,ACTUAL_RUN_TIME,NEXT_RUN_TIME,FINAL_STATE)
       SELECT LOG_ID,TASK_NAME,TASK_ID,REQUIRED_RUN_TIME,ACTUAL_RUN_TIME,NEXT_RUN_TIME,FINAL_STATE 
       FROM BC_NC_TASK_LOG
go
       
INSERT INTO NC_TASKSCHEDULE_CANCELLED_RUNS (
       FK_TASKSCHEDULE_ID,CANCELLED_DATE,FK_SCHEDULE_ID,AUDIT_USER)
       SELECT FK_TASKSCHEDULE_ID,CANCELLED_DATE,FK_SCHEDULE_ID,AUDIT_USER
       FROM BC_NC_TASKSCHEDULE_CANCELLED_RUNS
go

-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F1R_TASKSCHEDULE_S FOREIGN KEY (FK_TASKSCHEDULE_ID)
      REFERENCES NC_TASKSCHEDULE
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 F1N_TASKSCHEDULE FOREIGN KEY (FK_TASK_ID)
      REFERENCES NC_TASK
go
 
 -------------------------------------------------------------------------------
 --$250-- 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_TASK_LOG
go

DROP TABLE BC_NC_TASKSCHEDULE_CANCELLED_RUNS
go

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