analytics/configuration/schemas/delivery/db2/NC31_UPDATE_DB2.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_DB2.SQL		*DO NOT EDIT*		Data Store Version: 31		 
-------------------------------------------------------------------------------

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

CREATE TABLE BC_NC_SCHEDULE(
   PK_SCHEDULE_ID                INTEGER NOT NULL,
   NAME                          VARCHAR(255),
   DESCRIPTION                   VARCHAR(1000),
   LASTMOD_BY                    CHAR(40),
   LASTMOD_TIME                  TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL,
   MODCOUNT                      INTEGER WITH DEFAULT -1,
   SCHEDULE_TYPE                 CHAR(6),
   TIMEZONE                      VARCHAR(100),
   START_DATE                    NUMERIC(14),
   END_DATE                      NUMERIC(14),
   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                    VARCHAR(255)
);

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

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

CREATE TABLE BC_NC_TASK_LOG(
   LOG_ID                        INTEGER NOT NULL,
   TASK_NAME                     VARCHAR(255) NOT NULL,
   TASK_ID                       INTEGER,
   REQUIRED_RUN_TIME             NUMERIC(14),
   ACTUAL_RUN_TIME               NUMERIC(14),
   NEXT_RUN_TIME                 NUMERIC(14),
   FINAL_STATE                   INTEGER NOT NULL
);

CREATE TABLE BC_NC_TASKSCHEDULE_CANCELLED_RUNS (
   FK_TASKSCHEDULE_ID 	 INTEGER NOT NULL,
   CANCELLED_DATE	 NUMERIC(14) NOT NULL,
   FK_SCHEDULE_ID        INTEGER,
   AUDIT_USER            VARCHAR(2000)
);

-------------------------------------------------------------------------------
--$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;

INSERT INTO BC_NC_SCHEDULE_QUEUE (SCHEDULE_ID,RUN_DATE) 
       SELECT SCHEDULE_ID,RUN_DATE 
       FROM NC_SCHEDULE_QUEUE;
       
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;

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

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

DROP VIEW V_NC_TASK_LINK;

DROP VIEW V_NC_AGENT_LOG;

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

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   DROP CONSTRAINT F1R_TASKSCHEDULE_S;
   
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   DROP CONSTRAINT F2R_TASKSCHEDULE_S;

DROP TABLE NC_SCHEDULE;

DROP TABLE NC_SCHEDULE_QUEUE;

DROP TABLE NC_TASKSCHEDULE;

DROP TABLE NC_TASK_LOG;

DROP TABLE NC_TASKSCHEDULE_CANCELLED_RUNS;

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

CREATE TABLE NC_SCHEDULE(
   PK_SCHEDULE_ID                INTEGER NOT NULL,
   NAME                          VARCHAR(255),
   DESCRIPTION                   VARCHAR(1000),
   LASTMOD_BY                    CHAR(40),
   LASTMOD_TIME                  TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL,
   MODCOUNT                      INTEGER WITH 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                    VARCHAR(255)
);

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

CREATE TABLE NC_TASKSCHEDULE(
   PK_TASKSCHEDULE_ID            INTEGER NOT NULL,
   FK_TASK_ID                    INTEGER,
   NAME                          VARCHAR(255),
   DESCRIPTION                   VARCHAR(1000),
   LASTMOD_BY                    CHAR(40),
   MODCOUNT                      INTEGER WITH 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
);

CREATE TABLE NC_TASK_LOG(
   LOG_ID                        INTEGER NOT NULL,
   TASK_NAME                     VARCHAR(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
);

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

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

-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

ALTER TABLE NC_SCHEDULE
   ADD CONSTRAINT PN_SCHEDULE PRIMARY KEY (PK_SCHEDULE_ID);
  
ALTER TABLE NC_SCHEDULE_QUEUE
   ADD CONSTRAINT PN_SCHEDULE_QUEUEI PRIMARY KEY (SCHEDULE_ID);
   
ALTER TABLE NC_TASKSCHEDULE
   ADD CONSTRAINT PN_TASKSCHEDULE PRIMARY KEY (PK_TASKSCHEDULE_ID);
   
ALTER TABLE NC_TASK_LOG
   ADD CONSTRAINT PN_TASK_LOG PRIMARY KEY (LOG_ID);
   
ALTER TABLE NC_TASKSCHEDULE_CANCELLED_RUNS
   ADD CONSTRAINT PN_CANCELLED_DATES PRIMARY KEY (FK_TASKSCHEDULE_ID,CANCELLED_DATE);

-------------------------------------------------------------------------------
----$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;
    
    
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;   

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

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

 -------------------------------------------------------------------------------
 --$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;

INSERT INTO NC_SCHEDULE_QUEUE (SCHEDULE_ID,RUN_DATE) 
       SELECT SCHEDULE_ID,RUN_DATE 
       FROM BC_NC_SCHEDULE_QUEUE;
       
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;

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;
       
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;
       
-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------
      
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F1R_TASKSCHEDULE_S FOREIGN KEY (FK_TASKSCHEDULE_ID)
      REFERENCES NC_TASKSCHEDULE;
      
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F2R_TASKSCHEDULE_S FOREIGN KEY (FK_SCHEDULE_ID)
      REFERENCES NC_SCHEDULE;

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

 -------------------------------------------------------------------------------
 --$250-- backup drop begin
 -------------------------------------------------------------------------------

DROP TABLE BC_NC_SCHEDULE;

DROP TABLE BC_NC_SCHEDULE_QUEUE;

DROP TABLE BC_NC_TASKSCHEDULE;

DROP TABLE BC_NC_TASK_LOG;

DROP TABLE BC_NC_TASKSCHEDULE_CANCELLED_RUNS;

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