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


-------------------------------------------------------------------------------
-- NC836_UPDATE_IFX.SQL	       *DO NOT EDIT*		Data Store Version: 836		 
-------------------------------------------------------------------------------

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

CREATE TABLE B_NC_TASK(
    PK_TASK_ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    DESCRIPTION LVARCHAR(1000),
    FK_BIE_ID INTEGER,
    THRESHOLD INTEGER,
    WEIGHTS VARCHAR(200),
    TASK_TYPE INTEGER,
    DATEFORMAT CHAR(19),
    DETECTION_OPTION INTEGER,
    LASTMOD_TIME DATETIME YEAR TO FRACTION (5) DEFAULT CURRENT YEAR TO FRACTION(5),
    MODCOUNT INTEGER DEFAULT -1,
    LASTMOD_BY CHAR(40),
    LOCATION_ROOT LVARCHAR(256),
    OBJECT_CLASS LVARCHAR(256),
    OWNED_BY LVARCHAR(1024)
);

CREATE TABLE B_NC_TASKSCHEDULE(
    PK_TASKSCHEDULE_ID INTEGER NOT NULL,
    FK_TASK_ID INTEGER,
    NAME VARCHAR(255),
    DESCRIPTION LVARCHAR(1000),
    LASTMOD_BY CHAR(40),
    MODCOUNT INTEGER DEFAULT -1 NOT NULL,
    RUN_REQUIREMENTS INTEGER,
    LAST_EXECUTION_AT DECIMAL(19,0),
    NEXT_EXECUTION_REQUEST DECIMAL(19,0),
    SCHEDULE_DELAY DECIMAL(19,0),
    DELETE_AFTER_LAST_RUN INTEGER,
    PRIORITY INTEGER DEFAULT 3 NOT NULL,
    SCHEDULED_BY LVARCHAR(1024)
);

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

INSERT INTO B_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;

INSERT INTO B_NC_TASK (PK_TASK_ID, NAME, DESCRIPTION, FK_BIE_ID, THRESHOLD, WEIGHTS, TASK_TYPE, DATEFORMAT, DETECTION_OPTION, LASTMOD_TIME, MODCOUNT, LASTMOD_BY, LOCATION_ROOT, OBJECT_CLASS, OWNED_BY)
SELECT PK_TASK_ID, NAME, DESCRIPTION, FK_BIE_ID, THRESHOLD, WEIGHTS, TASK_TYPE, DATEFORMAT, DETECTION_OPTION, LASTMOD_TIME, MODCOUNT, LASTMOD_BY, LOCATION_ROOT, OBJECT_CLASS, OWNED_BY
FROM NC_TASK;

-------------------------------------------------------------------------------
--$120-- foreign keys drop begin
-------------------------------------------------------------------------------

ALTER TABLE NC_TASKSCHEDULE DROP CONSTRAINT F1N_TASKSCHEDULE;

ALTER TABLE R_TASKSCHEDULE_SCHEDULE DROP CONSTRAINT F1R_TASKSCHEDULE_S;

ALTER TABLE R_MESSAGESTRUCT_TASK DROP CONSTRAINT F2R_MESSAGESTRUCT_;

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

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

DROP TABLE NC_TASK;

DROP TABLE NC_TASKSCHEDULE;

DROP TABLE NC_TASK_QUEUE;

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

CREATE TABLE NC_TASK(
    PK_TASK_ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    DESCRIPTION LVARCHAR(1000),
    FK_BIE_ID INTEGER,
    THRESHOLD INTEGER,
    WEIGHTS VARCHAR(200),
    TASK_TYPE INTEGER,
    DATEFORMAT CHAR(19),
    DETECTION_OPTION INTEGER,
    LASTMOD_TIME DATETIME YEAR TO FRACTION (5) DEFAULT CURRENT YEAR TO FRACTION(5),
    MODCOUNT INTEGER DEFAULT -1,
    LASTMOD_BY CHAR(40),
    LOCATION_ROOT LVARCHAR(256),
    OBJECT_CLASS LVARCHAR(256),
    OWNED_BY LVARCHAR(1024)
) lock mode row;

CREATE TABLE NC_TASKSCHEDULE(
    PK_TASKSCHEDULE_ID INTEGER NOT NULL,
    FK_TASK_ID INTEGER,
    NAME VARCHAR(255),
    DESCRIPTION LVARCHAR(1000),
    LASTMOD_BY CHAR(40),
    MODCOUNT INTEGER DEFAULT -1 NOT NULL,
    RUN_REQUIREMENTS INTEGER,
    LAST_EXECUTION_AT DECIMAL(19,0),
    NEXT_EXECUTION_REQUEST DECIMAL(19,0),
    SCHEDULE_DELAY DECIMAL(19,0),
    DELETE_AFTER_LAST_RUN INTEGER,
    PRIORITY INTEGER DEFAULT 3 NOT NULL,
    SCHEDULED_BY LVARCHAR(1024)
) lock mode row;

CREATE TABLE NC_TASK_QUEUE(
    TASK_ID CHAR(45) NOT NULL,
    SERVICE_NAME VARCHAR(40),
    SERVER_GROUP VARCHAR(40),
    DATE_ENTERED DECIMAL(19, 0) NOT NULL,
    TASK BLOB,
    STATUS INTEGER NOT NULL,
    PRIORITY INTEGER NOT NULL,
    RESTART_ID CHAR(45),
    SDS_INSTANCE_ID CHAR(32),
    HISTORY_STORE_ID CHAR(34),
    ACTUAL_EXECUTION_TIME DECIMAL(19, 0),
    ACTUAL_COMPLETION_TIME DECIMAL(19, 0),
    DISPATCHER_ID CHAR(45),
    SCHEDULE_TRIGGER_NAME VARCHAR(255),
    SCHEDULE_TYPE CHAR(15),
    ACCOUNT_PATH LVARCHAR(1024),
    STOREID CHAR(34),
    OBJECT_CLASS CHAR(64),
    OWNER_STOREID LVARCHAR(1024),
    PROCESS_ID INTEGER
) lock mode row;

-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE INDEX I1N_TASKSCHEDULE ON NC_TASKSCHEDULE(FK_TASK_ID);

CREATE INDEX IX_NC_TSK_Q_SLTR
    ON NC_TASK_QUEUE(STATUS,SERVICE_NAME,SERVER_GROUP,DATE_ENTERED);

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

ALTER TABLE NC_TASK
 ADD CONSTRAINT  PRIMARY KEY(PK_TASK_ID) CONSTRAINT PN_TASK;

ALTER TABLE NC_TASKSCHEDULE
 ADD CONSTRAINT  PRIMARY KEY(PK_TASKSCHEDULE_ID) CONSTRAINT PN_TASKSCHEDULE;
 
ALTER TABLE NC_TASK_QUEUE
 ADD CONSTRAINT  PRIMARY KEY(TASK_ID) CONSTRAINT PN_TASK_QUEUE;

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

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

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

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

-------------------------------------------------------------------------------
--$230-- restore inserts begin
-------------------------------------------------------------------------------

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

INSERT INTO NC_TASK (PK_TASK_ID, NAME, DESCRIPTION, FK_BIE_ID, THRESHOLD, WEIGHTS, TASK_TYPE, DATEFORMAT, DETECTION_OPTION, LASTMOD_TIME, MODCOUNT, LASTMOD_BY, LOCATION_ROOT, OBJECT_CLASS, OWNED_BY)
SELECT PK_TASK_ID, NAME, DESCRIPTION, FK_BIE_ID, THRESHOLD, WEIGHTS, TASK_TYPE, DATEFORMAT, DETECTION_OPTION, LASTMOD_TIME, MODCOUNT, LASTMOD_BY, LOCATION_ROOT, OBJECT_CLASS, OWNED_BY
FROM B_NC_TASK;

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

ALTER TABLE NC_TASKSCHEDULE
 ADD CONSTRAINT  FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK CONSTRAINT F1N_TASKSCHEDULE;
 
ALTER TABLE R_TASKSCHEDULE_SCHEDULE
 ADD CONSTRAINT  FOREIGN KEY (FK_TASKSCHEDULE_ID) REFERENCES NC_TASKSCHEDULE CONSTRAINT F1R_TASKSCHEDULE_S;
      
ALTER TABLE R_MESSAGESTRUCT_TASK
 ADD CONSTRAINT  FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK CONSTRAINT F2R_MESSAGESTRUCT_;

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

DROP TABLE B_NC_TASK;

DROP TABLE B_NC_TASKSCHEDULE;

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