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


-------------------------------------------------------------------------------
-- NC315_UPDATE_ORA.SQL	       *DO NOT EDIT*		Data Store Version: 315		 
-------------------------------------------------------------------------------

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

CREATE TABLE BC_NC_TS_CANCELLED_RUNS(
   FK_TASKSCHEDULE_ID    INTEGER NOT NULL,
   CANCELLED_DATE	 NUMBER NOT NULL,
   FK_SCHEDULE_ID        INTEGER,
   AUDIT_USER            VARCHAR2(2000)
);

--Bug #552783 START
--Create backup table for table NC_JMSQUEUE 
--The backup table will not backup the LONG RAW (QUEUE_ENTRY) field
CREATE TABLE BC_NC_JMSQUEUE ( 
  QUEUE_ENTRY_ID                NUMBER, 
  QUEUE_NAME                    VARCHAR2 (440), 
  SDS_INSTANCE_ID	        CHAR (32),
  DATE_ENTERED 		        NUMBER NOT NULL
);
--BUG #552783 END

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

INSERT INTO BC_NC_TS_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;
    
--Bug #552783 START
--Script to transfer data from the table NC_JMSQUEUE to the backup table BC_NC_JMSQUEUE
INSERT INTO BC_NC_JMSQUEUE (QUEUE_ENTRY_ID, QUEUE_NAME, SDS_INSTANCE_ID, DATE_ENTERED) SELECT QUEUE_ENTRY_ID, QUEUE_NAME, SDS_INSTANCE_ID, DATE_ENTERED FROM NC_JMSQUEUE;
--Bug #552783 END    

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

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

DROP TABLE NC_TASKSCHEDULE_CANCELLED_RUNS;

--cant update the entry from long raw to blob
DROP TABLE NC_JMSQUEUE;

DROP TABLE NC_TMP_PAGING; 

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

CREATE TABLE NC_SCHEDULED_EVENT_OVERRIDE(
   FK_TASKSCHEDULE_ID    	INTEGER NOT NULL,
   EVENT_DATE		 	NUMBER NOT NULL,
   EVENT_STATUS                 INTEGER,
   EVENT_PRIORITY               INTEGER,
   FK_SCHEDULE_ID        	INTEGER,
   CANCELLED_BY            	VARCHAR2(2000)
);

CREATE TABLE NC_JMSQUEUE ( 
  QUEUE_ENTRY_ID                 NUMBER, 
  QUEUE_NAME                     VARCHAR2 (440), 
  QUEUE_ENTRY                    BLOB,
  SDS_INSTANCE_ID 		        CHAR (32),
  DATE_ENTERED 			        NUMBER NOT NULL
);


CREATE GLOBAL TEMPORARY TABLE NC_TMP_PAGING (
    STOREID CHAR(45),
    SEVERITY INTEGER
) ON COMMIT PRESERVE ROWS;
-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE INDEX I1N_EVENT_DATES ON NC_SCHEDULED_EVENT_OVERRIDE (FK_TASKSCHEDULE_ID);

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

CREATE INDEX IX_NC_JMSQUEUE1 ON NC_JMSQUEUE(QUEUE_NAME, SDS_INSTANCE_ID);

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

ALTER TABLE NC_SCHEDULED_EVENT_OVERRIDE
	ADD CONSTRAINT PN_EVENT_DATES PRIMARY KEY (FK_TASKSCHEDULE_ID, EVENT_DATE);
	
ALTER TABLE NC_JMSQUEUE
   ADD CONSTRAINT PN_NC_JMSQUEUE PRIMARY KEY (QUEUE_ENTRY_ID);

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

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

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

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

 -------------------------------------------------------------------------------
 --$230-- restore inserts begin
 -------------------------------------------------------------------------------
 
INSERT INTO NC_SCHEDULED_EVENT_OVERRIDE ( FK_TASKSCHEDULE_ID, EVENT_DATE, FK_SCHEDULE_ID, CANCELLED_BY )
    SELECT FK_TASKSCHEDULE_ID, CANCELLED_DATE, FK_SCHEDULE_ID, AUDIT_USER FROM BC_NC_TS_CANCELLED_RUNS;
    
--Bug #552783 START
--Restore the data from the backup table BC_NC_JMSQUEUE to the table NC_JMSQUEUE
--The restore will not restore the contents of the BLOB field QUEUE_ENTRY field
INSERT INTO NC_JMSQUEUE (QUEUE_ENTRY_ID, QUEUE_NAME, SDS_INSTANCE_ID, DATE_ENTERED) SELECT QUEUE_ENTRY_ID, QUEUE_NAME, SDS_INSTANCE_ID, DATE_ENTERED FROM BC_NC_JMSQUEUE;
--Bug #552783 END    

 -------------------------------------------------------------------------------
 --$250-- backup drop begin
 -------------------------------------------------------------------------------
 
DROP TABLE BC_NC_TS_CANCELLED_RUNS;

--Bug #552783 START
--Drop the backup table BC_NC_JMSQUEUE
DROP TABLE BC_NC_JMSQUEUE;
--Bug #552783 END

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