-- -- 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. -- ------------------------------------------------------------------------------- -- NC2_UPDATE_DB2.SQL * DO NOT EDIT * Beta1 -> Beta2 ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- --$100-- backup create begin ------------------------------------------------------------------------------- CREATE TABLE BC_AGENT_MESSAGE( LOG_ID INTEGER NOT NULL, AGENT_ID INTEGER NOT NULL, MESSAGE VARCHAR(1118) NOT NULL, TIMESTAMP NUMERIC(14) ); CREATE TABLE BC_AGGREGATE_RULE_TREE( FK_PARENT_ID INTEGER NOT NULL, FK_CHILD_ID INTEGER NOT NULL ); CREATE TABLE BC_BIE( PK_BIE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), LASTMOD_TIME TIMESTAMP, MOD_CNT INTEGER, LASTMOD_BY CHAR(40) ); CREATE TABLE BC_BIECALC( PK_CALC_ID INTEGER NOT NULL, LHS_TOPIC_ID INTEGER, TYPE_NAME VARCHAR(20), FK_RHSTYPE VARCHAR(20), OPERATOR CHAR(18), NUMERIC_LITERAL FLOAT, STRING_LITERAL VARCHAR(255), DATE_LITERAL TIMESTAMP, NAME VARCHAR(255), DESCRIPTION VARCHAR(255) ); CREATE TABLE BC_BIETOPIC( PK_BIETOPIC_ID INTEGER NOT NULL, FK_BIE_ID INTEGER, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), FK_DMT_ID INTEGER, TYPE VARCHAR(30), MODCOUNT INTEGER, FK_CALC_ID INTEGER, FK_BIETOPIC_ID INTEGER ); CREATE TABLE BC_COGNOS_DS( PK_PP_DS_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), SERVER_NAME VARCHAR(255), SECURITY_DETAILS VARCHAR(1700), PORT_NUMBER SMALLINT ); CREATE TABLE BC_CONFIG_DS( PK_DS_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), USERNAME VARCHAR(32), PASSWORD VARCHAR(32), LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, SERVER_NAME VARCHAR(50), SECURITY_DETAILS BLOB(2000) ); CREATE TABLE BC_CTMAP( OBJTYPE SMALLINT NOT NULL, CLASS VARCHAR(200), TABLENAME VARCHAR(200) NOT NULL, HELPER VARCHAR(200), REMOTECLASS VARCHAR(200) ); CREATE TABLE BC_DATAMAPPING( PK_DM_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), SQL_STRING CLOB(8000), FK_DS_ID INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, FK_PPDS_ID INTEGER, FK_COGNOSDS_ID INTEGER, URL VARCHAR(1000), NIC VARCHAR(1000) ); CREATE TABLE BC_DB_VERSION( DB_VERSION_ID INTEGER, BUILD VARCHAR(20) ); CREATE TABLE BC_DELIVERYADDRESS( PK_DELADDRESS_ID INTEGER NOT NULL, DELIVERY_ADDRESS VARCHAR(100), CHANNEL_TYPE INTEGER, IS_DEFAULT INTEGER NOT NULL, FK_RECIPIENT_ID INTEGER ); CREATE TABLE BC_DMTOPIC( PK_TOPIC_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), TSRC VARCHAR(100), TLINK VARCHAR(200), FK_DM_ID INTEGER NOT NULL, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, TABLE_NAME VARCHAR(64), COLUMN_NAME VARCHAR(64), DRE_COLUMN_NAME VARCHAR(64), COLUMN_TYPE INTEGER, PP_TOPIC_VALUE VARCHAR(255), OBJ_TYPE SMALLINT ); CREATE TABLE BC_DMT_LINK( PK_DM_ID INTEGER NOT NULL, FK_DM_ID INTEGER NOT NULL, FK_DATAMAPPING CHAR(40) NOT NULL ); CREATE TABLE BC_DSITEM_L1( PK_DSITEM1_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), FK_DS_ID INTEGER NOT NULL, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE BC_DSITEM_L2( PK_DSITEM2_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), FK_DSITEM1_ID INTEGER NOT NULL, TYPE INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE BC_DSPOOL( PK_DSPOOL_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), URL VARCHAR(128) NOT NULL, DRIVER VARCHAR(100) NOT NULL, DS_TYPE INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE BC_DYNAMIC_RECIPIENT_STRUCT( PK_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), TITLE VARCHAR(64), FIRSTNAME VARCHAR(64), MIDDLENAME VARCHAR(64), LASTNAME VARCHAR(64), PHONENUMBER VARCHAR(64), JOBTITLE VARCHAR(64), COMPANYNAME VARCHAR(255), DEPARTMENT VARCHAR(255), OFFICE VARCHAR(255), COUNTRY VARCHAR(255), TIMEZONE VARCHAR(64), LASTMOD_BY CHAR(40), MODCOUNT INTEGER WITH DEFAULT -1 ); CREATE TABLE BC_DYN_DELIVERY_INFO_STRUCT( PK_OID INTEGER NOT NULL, FK_DYNAMIC_RECIPIENT_STRUCT INTEGER, ADDRESS_DESCRIPTION CHAR(40), CHANNEL_TYPE INTEGER NOT NULL, ADDRESS_BIE_ID INTEGER NOT NULL, NAME VARCHAR(100), LASTMOD_BY VARCHAR(100), MODCOUNT INTEGER WITH DEFAULT -1, DESCRIPTION VARCHAR(100) ); CREATE TABLE BC_EXECUTABLE( PK_EXE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER, PATH VARCHAR(255) NOT NULL, FK_TASK_ID INTEGER NOT NULL ); CREATE TABLE BC_MACHINE_CONFIG( PK_OID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), LASTMOD_BY VARCHAR(32), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, TIMEZONE VARCHAR(50) ); CREATE TABLE BC_MESSAGELINE( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, LINE_NO INTEGER NOT NULL, LINE_TYPE INTEGER ); CREATE TABLE BC_MESSAGELINE_ELEMENT( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, ELEMENT_NUMBER INTEGER NOT NULL, LINE_NO INTEGER NOT NULL, TEXT CLOB(4000), FK_BIE_TOPIC_ID INTEGER ); CREATE TABLE BC_MESSAGESTRUCT( PK_MS_ID INTEGER NOT NULL, FK_DYNREC_ID INTEGER, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), SOURCE VARCHAR(100), SUBJECT VARCHAR(100), MODCOUNT INTEGER WITH DEFAULT -1, LASTMOD_BY VARCHAR(50), CHANNEL_TYPE INTEGER, CONTACT VARCHAR(50), AVOID_DUPLICATION INTEGER WITH DEFAULT 0 NOT NULL ); CREATE TABLE BC_MESSAGE_ATTACHMENT( PK_ATTACHMENT_ID INTEGER NOT NULL, FK_MESSAGE_ID INTEGER NOT NULL, ATTACHMENT_TYPE INTEGER NOT NULL, ATTACHMENT VARCHAR(100) NOT NULL, ATTACHMENT_SERVER VARCHAR(100), MIME_TYPE VARCHAR(100) NOT NULL ); CREATE TABLE BC_MSGDUPLICATION_STORE( MSG_PART_ID NUMERIC NOT NULL, TOPIC_STRING_VALUE VARCHAR(2000), FK_BIETOPIC_ID INTEGER NOT NULL, FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER, DATE_ TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL ); CREATE TABLE BC_MSGDUPLICATION_TEMP_STORE( MSG_PART_ID NUMERIC NOT NULL, TOPIC_STRING_VALUE VARCHAR(2000), FK_BIETOPIC_ID INTEGER NOT NULL, FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER, SESSION_ID NUMERIC NOT NULL ); CREATE TABLE BC_OBJECTCATALOGUE( PK_OID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), SUBTYPE INTEGER, TYPE INTEGER NOT NULL, MODCOUNT INTEGER WITH DEFAULT -1 ); CREATE TABLE BC_OBJECTID( OBJECTID INTEGER ); CREATE TABLE BC_OBJECT_PERMISSIONS( FK_OID INTEGER NOT NULL, PERMISSION INTEGER NOT NULL, ACL CHAR(50) NOT NULL ); CREATE TABLE BC_PERMISSION( PK_PERMISSION_ID INTEGER NOT NULL, DESCRIPTION VARCHAR(30) NOT NULL ); CREATE TABLE BC_RECIPIENT( PK_RECIPIENT_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, TITLE VARCHAR(10), FIRSTNAME VARCHAR(50), MIDDLENAME VARCHAR(50), LASTNAME VARCHAR(50), PHONENUMBER VARCHAR(50), JOBTITLE VARCHAR(50), COMPANY VARCHAR(255), DEPARTMENT VARCHAR(255), OFFICE VARCHAR(255), COUNTRY VARCHAR(50), TIMEZONE VARCHAR(50), TO_REDIRECT INTEGER NOT NULL, FK_REDIRECT_RID INTEGER, FK_REDIRECT_RGID INTEGER, IS_EXTERNAL INTEGER, IS_USEDEFAULTADDRESS INTEGER ); CREATE TABLE BC_RECIPIENTSCHEDULE( PK_SCHEDULE_ID INTEGER NOT NULL, SCHEDULE_NAME VARCHAR(100), FK_RECIPIENT_ID INTEGER, DELIVERY_SCHEDULE VARCHAR(2000), FK_DELADDRESS_ID INTEGER ); CREATE TABLE BC_RULE( PK_RULE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), FK_RULETYPE VARCHAR(20), FK_RHSTYPE VARCHAR(30), FK_BIE_ID INTEGER, LHS_TOPIC_ID INTEGER, OPERATOR VARCHAR(30), DATE_LITERAL TIMESTAMP, STRING_LITERAL VARCHAR(255), RHS_TOPIC_ID INTEGER, RHS_OPTION INTEGER, NUMERIC_LITERAL FLOAT, RHS_BIE_ID INTEGER ); CREATE TABLE BC_SCHEDULE( PK_SCHEDULE_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP, MODCOUNT INTEGER WITH DEFAULT -1, SCHEDULE_TYPE CHAR(6), EXECUTE_FIRST VARCHAR(100), END_AT VARCHAR(100), TIMEZONE VARCHAR(100), START_DATE NUMERIC(14), END_DATE NUMERIC(14), REPEAT_FOREVER INTEGER, ACTIVE INTEGER, VALID_SCHEDULE INTEGER, USE_DAY_NUMBER INTEGER, START_TIME_STRING VARCHAR(50), START_DATE_STRING VARCHAR(50), END_DATE_STRING VARCHAR(50), DAY_OF_MONTH INTEGER, DAY_OF_WEEK INTEGER, DAY_OF_WEEK_IN_MONTH INTEGER, MONTH_OF_YEAR INTEGER, REPEAT_INTERVAL INTEGER, ESCALATION_SCHEDULE INTEGER, DAYS_OF_WEEK INTEGER, FIXED_INTERVAL_TYPE INTEGER, FIXED_INTERVAL_IN_MILLIS NUMERIC(10) ); CREATE TABLE BC_TASK( PK_TASK_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), FK_BIE_ID INTEGER, THRESHOLD INTEGER, WEIGHTS VARCHAR(200), TASK_TYPE INTEGER, DATEFORMAT CHAR(19), DETECTION_OPTION INTEGER, LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, MODCOUNT INTEGER WITH DEFAULT -1, LASTMOD_BY CHAR(40) ); CREATE TABLE BC_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) ); CREATE TABLE BC_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_USER_SESSION( TICKET_ID VARCHAR(32) NOT NULL, NC_USER_ID INTEGER NOT NULL, USER_CLASS CHAR(50) NOT NULL ); CREATE TABLE B_AGENT_ESCALATIONAGENT( FK_CHILD_AGENT_ID INTEGER NOT NULL, FK_PARENT_AGENT_ID INTEGER NOT NULL ); CREATE TABLE B_BIECALC_RHSTOPIC( FK_BIETOPIC_ID INTEGER NOT NULL, FK_CALC_ID INTEGER NOT NULL ); CREATE TABLE B_DS_MACHINE_DSPOOL( PK_DSMAC_ID INTEGER NOT NULL, FK_DSPOOL_ID INTEGER NOT NULL, FK_MACHINE_ID INTEGER NOT NULL, FK_CONFIG_DS_ID INTEGER NOT NULL, LASTMOD_BY VARCHAR(32), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE B_MESSAGESTRUCT_TASK( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER NOT NULL ); CREATE TABLE B_MSGSTRUCT_AVOIDTOPICS( FK_MS_ID INTEGER NOT NULL, FK_BIETOPIC_ID INTEGER NOT NULL ); CREATE TABLE B_MSGSTRUCT_RECIPIENT_DELIVINF( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_DELIVERY_INFO_ID INTEGER, FK_RECIPIENT_ID INTEGER NOT NULL, ADDED_BY_RECIP_ID INTEGER ); CREATE TABLE B_NEWSITEMS_NCOBJECTS( FK_NCID INTEGER NOT NULL, NID CHAR(32) NOT NULL, VID CHAR(32), TYPE SMALLINT ); CREATE TABLE B_RECIPIENT_ACCMANUSERID( F_RECIPIENTID INTEGER NOT NULL, ACCMAN_UID NUMERIC(10) NOT NULL ); CREATE TABLE B_RULE_PARAMETER( FK_TASK_ID INTEGER NOT NULL, LITERAL_TYPE VARCHAR(200), FK_RULE_ID INTEGER NOT NULL ); CREATE TABLE B_TASKSCHEDULE_SCHEDULE( FK_TASKSCHEDULE_ID INTEGER NOT NULL, FK_SCHEDULE_ID INTEGER NOT NULL, IS_ACTIVE INTEGER, IS_ESCALATION INTEGER ); ------------------------------------------------------------------------------- --$110-- backup inserts begin ------------------------------------------------------------------------------- INSERT INTO BC_AGENT_MESSAGE (LOG_ID,AGENT_ID,MESSAGE,TIMESTAMP) SELECT LOG_ID,AGENT_ID,MESSAGE,TIMESTAMP FROM NC_AGENT_MESSAGE; INSERT INTO BC_AGGREGATE_RULE_TREE (FK_PARENT_ID,FK_CHILD_ID) SELECT FK_PARENT_ID,FK_CHILD_ID FROM NC_AGGREGATE_RULE_TREE; INSERT INTO BC_BIE (PK_BIE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,LASTMOD_BY) SELECT PK_BIE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,LASTMOD_BY FROM NC_BIE; INSERT INTO BC_BIECALC (PK_CALC_ID,LHS_TOPIC_ID,TYPE_NAME,FK_RHSTYPE,OPERATOR,NUMERIC_LITERAL,STRING_LITERAL,DATE_LITERAL,NAME,DESCRIPTION) SELECT PK_CALC_ID,LHS_TOPIC_ID,TYPE_NAME,FK_RHSTYPE,OPERATOR,NUMERIC_LITERAL,STRING_LITERAL,DATE_LITERAL,NAME,DESCRIPTION FROM NC_BIECALC; INSERT INTO BC_BIETOPIC (PK_BIETOPIC_ID,FK_BIE_ID,NAME,DESCRIPTION,FK_DMT_ID,TYPE,MODCOUNT,FK_CALC_ID,FK_BIETOPIC_ID) SELECT PK_BIETOPIC_ID,FK_BIE_ID,NAME,DESCRIPTION,FK_DMT_ID,TYPE,MODCOUNT,FK_CALC_ID,FK_BIETOPIC_ID FROM NC_BIETOPIC; INSERT INTO BC_COGNOS_DS (PK_PP_DS_ID,NAME,DESCRIPTION,SERVER_NAME,SECURITY_DETAILS,PORT_NUMBER) SELECT PK_PP_DS_ID,NAME,DESCRIPTION,SERVER_NAME,SECURITY_DETAILS,PORT_NUMBER FROM NC_COGNOS_DS; INSERT INTO BC_CONFIG_DS (PK_DS_ID,NAME,DESCRIPTION,USERNAME,PASSWORD,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,SERVER_NAME,SECURITY_DETAILS) SELECT PK_DS_ID,NAME,DESCRIPTION,USERNAME,PASSWORD,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,SERVER_NAME,SECURITY_DETAILS FROM NC_CONFIG_DS; INSERT INTO BC_DATAMAPPING (PK_DM_ID,NAME,DESCRIPTION,SQL_STRING,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,FK_PPDS_ID,FK_COGNOSDS_ID,URL,NIC) SELECT PK_DM_ID,NAME,DESCRIPTION,SQL_STRING,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,FK_PPDS_ID,FK_COGNOSDS_ID,URL,NIC FROM NC_DATAMAPPING; INSERT INTO BC_DB_VERSION (DB_VERSION_ID,BUILD) SELECT DB_VERSION_ID,BUILD FROM NC_DB_VERSION; INSERT INTO BC_DELIVERYADDRESS (PK_DELADDRESS_ID,DELIVERY_ADDRESS,CHANNEL_TYPE,IS_DEFAULT,FK_RECIPIENT_ID) SELECT PK_DELADDRESS_ID,DELIVERY_ADDRESS,CHANNEL_TYPE,IS_DEFAULT,FK_RECIPIENT_ID FROM NC_DELIVERYADDRESS; INSERT INTO BC_DMTOPIC (PK_TOPIC_ID,NAME,DESCRIPTION,TSRC,TLINK,FK_DM_ID,LASTMOD_BY,LASTMOD_TIME,TABLE_NAME,COLUMN_NAME,DRE_COLUMN_NAME,COLUMN_TYPE,PP_TOPIC_VALUE,OBJ_TYPE) SELECT PK_TOPIC_ID,NAME,DESCRIPTION,TSRC,TLINK,FK_DM_ID,LASTMOD_BY,LASTMOD_TIME,TABLE_NAME,COLUMN_NAME,DRE_COLUMN_NAME,COLUMN_TYPE,PP_TOPIC_VALUE,OBJ_TYPE FROM NC_DMTOPIC; INSERT INTO BC_DMT_LINK (PK_DM_ID,FK_DM_ID,FK_DATAMAPPING) SELECT PK_DM_ID,FK_DM_ID,FK_DATAMAPPING FROM NC_DMT_LINK; INSERT INTO BC_DSITEM_L1 (PK_DSITEM1_ID,NAME,DESCRIPTION,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSITEM1_ID,NAME,DESCRIPTION,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM NC_DSITEM_L1; INSERT INTO BC_DSITEM_L2 (PK_DSITEM2_ID,NAME,DESCRIPTION,FK_DSITEM1_ID,TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSITEM2_ID,NAME,DESCRIPTION,FK_DSITEM1_ID,TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM NC_DSITEM_L2; INSERT INTO BC_DSPOOL (PK_DSPOOL_ID,NAME,DESCRIPTION,URL,DRIVER,DS_TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSPOOL_ID,NAME,DESCRIPTION,URL,DRIVER,DS_TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM NC_DSPOOL; INSERT INTO BC_DYNAMIC_RECIPIENT_STRUCT (PK_ID,NAME,DESCRIPTION,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANYNAME,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,LASTMOD_BY,MODCOUNT) SELECT PK_ID,NAME,DESCRIPTION,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANYNAME,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,LASTMOD_BY,MODCOUNT FROM NC_DYNAMIC_RECIPIENT_STRUCT; INSERT INTO BC_DYN_DELIVERY_INFO_STRUCT (PK_OID,FK_DYNAMIC_RECIPIENT_STRUCT,ADDRESS_DESCRIPTION,CHANNEL_TYPE,ADDRESS_BIE_ID,NAME,LASTMOD_BY,MODCOUNT,DESCRIPTION) SELECT PK_OID,FK_DYNAMIC_RECIPIENT_STRUCT,ADDRESS_DESCRIPTION,CHANNEL_TYPE,ADDRESS_BIE_ID,NAME,LASTMOD_BY,MODCOUNT,DESCRIPTION FROM NC_DYN_DELIVERY_INFO_STRUCT; INSERT INTO BC_EXECUTABLE (PK_EXE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,PATH,FK_TASK_ID) SELECT PK_EXE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,PATH,FK_TASK_ID FROM NC_EXECUTABLE; INSERT INTO BC_MACHINE_CONFIG (PK_OID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,TIMEZONE) SELECT PK_OID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,TIMEZONE FROM NC_MACHINE_CONFIG; INSERT INTO BC_MESSAGELINE (FK_MESSAGESTRUCT_ID,LINE_NO,LINE_TYPE) SELECT FK_MESSAGESTRUCT_ID,LINE_NO,LINE_TYPE FROM NC_MESSAGELINE; INSERT INTO BC_MESSAGELINE_ELEMENT (FK_MESSAGESTRUCT_ID,ELEMENT_NUMBER,LINE_NO,TEXT,FK_BIE_TOPIC_ID) SELECT FK_MESSAGESTRUCT_ID,ELEMENT_NUMBER,LINE_NO,TEXT,FK_BIE_TOPIC_ID FROM NC_MESSAGELINE_ELEMENT; INSERT INTO BC_MESSAGESTRUCT (PK_MS_ID,FK_DYNREC_ID,NAME,DESCRIPTION,SOURCE,SUBJECT,MODCOUNT,LASTMOD_BY,CHANNEL_TYPE,CONTACT,AVOID_DUPLICATION) SELECT PK_MS_ID,FK_DYNREC_ID,NAME,DESCRIPTION,SOURCE,SUBJECT,MODCOUNT,LASTMOD_BY,CHANNEL_TYPE,CONTACT,AVOID_DUPLICATION FROM NC_MESSAGESTRUCT; INSERT INTO BC_MESSAGE_ATTACHMENT (PK_ATTACHMENT_ID,FK_MESSAGE_ID,ATTACHMENT_TYPE,ATTACHMENT,ATTACHMENT_SERVER,MIME_TYPE) SELECT PK_ATTACHMENT_ID,FK_MESSAGE_ID,ATTACHMENT_TYPE,ATTACHMENT,ATTACHMENT_SERVER,MIME_TYPE FROM NC_MESSAGE_ATTACHMENT; INSERT INTO BC_MSGDUPLICATION_STORE (MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,DATE_) SELECT MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,DATE_ FROM NC_MSGDUPLICATION_STORE; INSERT INTO BC_MSGDUPLICATION_TEMP_STORE (MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,SESSION_ID) SELECT MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,SESSION_ID FROM NC_MSGDUPLICATION_TEMP_STORE; INSERT INTO BC_OBJECTCATALOGUE (PK_OID,NAME,DESCRIPTION,SUBTYPE,TYPE,MODCOUNT) SELECT PK_OID,NAME,DESCRIPTION,SUBTYPE,TYPE,MODCOUNT FROM NC_OBJECTCATALOGUE; INSERT INTO BC_OBJECTID (OBJECTID) SELECT OBJECTID FROM NC_OBJECTID; INSERT INTO BC_OBJECT_PERMISSIONS (FK_OID,PERMISSION,ACL) SELECT FK_OID,PERMISSION,ACL FROM NC_OBJECT_PERMISSIONS; INSERT INTO BC_RECIPIENT (PK_RECIPIENT_ID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MODCOUNT,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANY,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,TO_REDIRECT,FK_REDIRECT_RID,FK_REDIRECT_RGID,IS_EXTERNAL,IS_USEDEFAULTADDRESS) SELECT PK_RECIPIENT_ID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MODCOUNT,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANY,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,TO_REDIRECT,FK_REDIRECT_RID,FK_REDIRECT_RGID,IS_EXTERNAL,IS_USEDEFAULTADDRESS FROM NC_RECIPIENT; INSERT INTO BC_RECIPIENTSCHEDULE (PK_SCHEDULE_ID,SCHEDULE_NAME,FK_RECIPIENT_ID,DELIVERY_SCHEDULE,FK_DELADDRESS_ID) SELECT PK_SCHEDULE_ID,SCHEDULE_NAME,FK_RECIPIENT_ID,DELIVERY_SCHEDULE,FK_DELADDRESS_ID FROM NC_RECIPIENTSCHEDULE; INSERT INTO BC_RULE (PK_RULE_ID,NAME,DESCRIPTION,FK_RULETYPE,FK_RHSTYPE,FK_BIE_ID,LHS_TOPIC_ID,OPERATOR,DATE_LITERAL,STRING_LITERAL,RHS_TOPIC_ID,RHS_OPTION,NUMERIC_LITERAL,RHS_BIE_ID) SELECT PK_RULE_ID,NAME,DESCRIPTION,FK_RULETYPE,FK_RHSTYPE,FK_BIE_ID,LHS_TOPIC_ID,OPERATOR,DATE_LITERAL,STRING_LITERAL,RHS_TOPIC_ID,RHS_OPTION,NUMERIC_LITERAL,RHS_BIE_ID FROM NC_RULE; INSERT INTO BC_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) 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 FROM NC_SCHEDULE; INSERT INTO BC_TASK (PK_TASK_ID,NAME,DESCRIPTION,FK_BIE_ID,THRESHOLD,WEIGHTS,TASK_TYPE,DATEFORMAT,DETECTION_OPTION,LASTMOD_TIME,MODCOUNT,LASTMOD_BY) SELECT PK_TASK_ID,NAME,DESCRIPTION,FK_BIE_ID,THRESHOLD,WEIGHTS,TASK_TYPE,DATEFORMAT,DETECTION_OPTION,LASTMOD_TIME,MODCOUNT,LASTMOD_BY FROM NC_TASK; INSERT INTO BC_TASKSCHEDULE (PK_TASKSCHEDULE_ID,FK_TASK_ID,NAME,DESCRIPTION,LASTMOD_BY,MODCOUNT,RUN_REQUIREMENTS,LAST_EXECUTION_AT,NEXT_EXECUTION_REQUEST,SCHEDULE_DELAY) SELECT PK_TASKSCHEDULE_ID,FK_TASK_ID,NAME,DESCRIPTION,LASTMOD_BY,MODCOUNT,RUN_REQUIREMENTS,LAST_EXECUTION_AT,NEXT_EXECUTION_REQUEST,SCHEDULE_DELAY FROM NC_TASKSCHEDULE; INSERT INTO BC_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_USER_SESSION (TICKET_ID,NC_USER_ID,USER_CLASS) SELECT TICKET_ID,NC_USER_ID,USER_CLASS FROM NC_USER_SESSION; INSERT INTO B_AGENT_ESCALATIONAGENT (FK_CHILD_AGENT_ID,FK_PARENT_AGENT_ID) SELECT FK_CHILD_AGENT_ID,FK_PARENT_AGENT_ID FROM R_AGENT_ESCALATIONAGENT; INSERT INTO B_BIECALC_RHSTOPIC (FK_BIETOPIC_ID,FK_CALC_ID) SELECT FK_BIETOPIC_ID,FK_CALC_ID FROM R_BIECALC_RHSTOPIC; INSERT INTO B_DS_MACHINE_DSPOOL (PK_DSMAC_ID,FK_DSPOOL_ID,FK_MACHINE_ID,FK_CONFIG_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSMAC_ID,FK_DSPOOL_ID,FK_MACHINE_ID,FK_CONFIG_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM R_DS_MACHINE_DSPOOL; INSERT INTO B_MESSAGESTRUCT_TASK (FK_MESSAGESTRUCT_ID,FK_TASK_ID) SELECT FK_MESSAGESTRUCT_ID,FK_TASK_ID FROM R_MESSAGESTRUCT_TASK; INSERT INTO B_MSGSTRUCT_AVOIDTOPICS (FK_MS_ID,FK_BIETOPIC_ID) SELECT FK_MS_ID,FK_BIETOPIC_ID FROM R_MSGSTRUCT_AVOIDTOPICS; INSERT INTO B_MSGSTRUCT_RECIPIENT_DELIVINF (FK_MESSAGESTRUCT_ID,FK_DELIVERY_INFO_ID,FK_RECIPIENT_ID,ADDED_BY_RECIP_ID) SELECT FK_MESSAGESTRUCT_ID,FK_DELIVERY_INFO_ID,FK_RECIPIENT_ID,ADDED_BY_RECIP_ID FROM R_MSGSTRUCT_RECIPIENT_DELIVINF; INSERT INTO B_NEWSITEMS_NCOBJECTS (FK_NCID,NID,VID,TYPE) SELECT FK_NCID,NID,VID,TYPE FROM R_NEWSITEMS_NCOBJECTS; INSERT INTO B_RECIPIENT_ACCMANUSERID (F_RECIPIENTID,ACCMAN_UID) SELECT F_RECIPIENTID,ACCMAN_UID FROM R_RECIPIENT_ACCMANUSERID; INSERT INTO B_RULE_PARAMETER (FK_TASK_ID,LITERAL_TYPE,FK_RULE_ID) SELECT FK_TASK_ID,LITERAL_TYPE,FK_RULE_ID FROM R_RULE_PARAMETER; INSERT INTO B_TASKSCHEDULE_SCHEDULE (FK_TASKSCHEDULE_ID,FK_SCHEDULE_ID,IS_ACTIVE,IS_ESCALATION) SELECT FK_TASKSCHEDULE_ID,FK_SCHEDULE_ID,IS_ACTIVE,IS_ESCALATION FROM R_TASKSCHEDULE_SCHEDULE; ------------------------------------------------------------------------------- --$120-- foreign keys drop begin ------------------------------------------------------------------------------- ALTER TABLE NC_AGGREGATE_RULE_TREE DROP CONSTRAINT F1N_AGGREGATE_RULE; ALTER TABLE NC_BIETOPIC DROP CONSTRAINT F1N_BIETOPIC; ALTER TABLE NC_DATAMAPPING DROP CONSTRAINT F1N_DATAMAPPING; ALTER TABLE NC_DYN_DELIVERY_INFO_STRUCT DROP CONSTRAINT F1N_DYN_DELIVERY_I; ALTER TABLE NC_EXECUTABLE DROP CONSTRAINT F1N_EXECUTABLE; ALTER TABLE NC_MESSAGELINE DROP CONSTRAINT F1N_MESSAGELINE; ALTER TABLE NC_MESSAGELINE_ELEMENT DROP CONSTRAINT F1N_MESSAGELINE_EL; ALTER TABLE NC_MESSAGESTRUCT DROP CONSTRAINT F1N_MESSAGESTRUCT; ALTER TABLE NC_MESSAGE_ATTACHMENT DROP CONSTRAINT F1N_MESSAGE_ATTACH; ALTER TABLE NC_TASKSCHEDULE DROP CONSTRAINT F1N_TASKSCHEDULE; ALTER TABLE R_AGENT_ESCALATIONAGENT DROP CONSTRAINT F1R_AGENT_ESCALATI; ALTER TABLE R_DS_MACHINE_DSPOOL DROP CONSTRAINT F1R_DS_MACHINE_DSP; ALTER TABLE R_MESSAGESTRUCT_TASK DROP CONSTRAINT F1R_MESSAGESTRUCT_; ALTER TABLE R_MSGSTRUCT_AVOIDTOPICS DROP CONSTRAINT F1R_MSGSTRUCT_AVOI; ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF DROP CONSTRAINT F1R_MSGSTRUCT_RECI; ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF DROP CONSTRAINT F2R_MSGSTRUCT_RECI; ALTER TABLE R_RECIPIENT_ACCMANUSERID DROP CONSTRAINT F1R_RECIPIENT_ACCM; ALTER TABLE R_RULE_PARAMETER DROP CONSTRAINT F1R_RULE_PARAMETER; ALTER TABLE R_TASKSCHEDULE_SCHEDULE DROP CONSTRAINT F1R_TASKSCHEDULE_S; ALTER TABLE NC_AGGREGATE_RULE_TREE DROP CONSTRAINT F2N_AGGREGATE_RULE; ALTER TABLE NC_BIETOPIC DROP CONSTRAINT F2N_BIETOPIC; ALTER TABLE NC_DATAMAPPING DROP CONSTRAINT F2N_DATAMAPPING; ALTER TABLE R_DS_MACHINE_DSPOOL DROP CONSTRAINT F2R_DS_MACHINE_DSP; ALTER TABLE R_MESSAGESTRUCT_TASK DROP CONSTRAINT F2R_MESSAGESTRUCT_; ALTER TABLE R_TASKSCHEDULE_SCHEDULE DROP CONSTRAINT F2R_TASKSCHEDULE_S; ------------------------------------------------------------------------------- --$130-- views drop begin ------------------------------------------------------------------------------- DROP VIEW V_BIECALC_LINK; DROP VIEW V_NC_TASK_LINK; DROP VIEW V_NC_AGENT_LOG; DROP VIEW V_BIE_COMPLETE; ------------------------------------------------------------------------------- --$140-- tables drop begin ------------------------------------------------------------------------------- DROP TABLE NC_AGENT_MESSAGE; DROP TABLE NC_AGGREGATE_RULE_TREE; DROP TABLE NC_BIE; DROP TABLE NC_BIECALC; DROP TABLE NC_BIETOPIC; DROP TABLE NC_COGNOS_DS; DROP TABLE NC_CONFIG_DS; DROP TABLE NC_CTMAP; DROP TABLE NC_DATAMAPPING; DROP TABLE NC_DB_VERSION; DROP TABLE NC_DELIVERYADDRESS; DROP TABLE NC_DMTOPIC; DROP TABLE NC_DMT_LINK; DROP TABLE NC_DSITEM_L1; DROP TABLE NC_DSITEM_L2; DROP TABLE NC_DSPOOL; DROP TABLE NC_DYNAMIC_RECIPIENT_STRUCT; DROP TABLE NC_DYN_DELIVERY_INFO_STRUCT; DROP TABLE NC_EXECUTABLE; DROP TABLE NC_MACHINE_CONFIG; DROP TABLE NC_MESSAGELINE; DROP TABLE NC_MESSAGELINE_ELEMENT; DROP TABLE NC_MESSAGESTRUCT; DROP TABLE NC_MESSAGE_ATTACHMENT; DROP TABLE NC_MSGDUPLICATION_STORE; DROP TABLE NC_MSGDUPLICATION_TEMP_STORE; DROP TABLE NC_OBJECTCATALOGUE; DROP TABLE NC_OBJECTID; DROP TABLE NC_OBJECT_PERMISSIONS; DROP TABLE NC_PERMISSION; DROP TABLE NC_RECIPIENT; DROP TABLE NC_RECIPIENTSCHEDULE; DROP TABLE NC_RULE; DROP TABLE NC_SCHEDULE; DROP TABLE NC_TASK; DROP TABLE NC_TASKSCHEDULE; DROP TABLE NC_TASK_LOG; DROP TABLE NC_USER_SESSION; DROP TABLE R_AGENT_ESCALATIONAGENT; DROP TABLE R_BIECALC_RHSTOPIC; DROP TABLE R_DS_MACHINE_DSPOOL; DROP TABLE R_MESSAGESTRUCT_TASK; DROP TABLE R_MSGSTRUCT_AVOIDTOPICS; DROP TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF; DROP TABLE R_NEWSITEMS_NCOBJECTS; DROP TABLE R_RECIPIENT_ACCMANUSERID; DROP TABLE R_RULE_PARAMETER; DROP TABLE R_TASKSCHEDULE_SCHEDULE; ------------------------------------------------------------------------------- --$200-- recreate tables begin ------------------------------------------------------------------------------- CREATE TABLE NC_ADMINISTRATOR( USER_CLASS CHAR(50) NOT NULL ); CREATE TABLE NC_AGENT_MESSAGE( LOG_ID INTEGER NOT NULL, AGENT_ID INTEGER NOT NULL, MESSAGE VARCHAR(1118) NOT NULL, TIMESTAMP NUMERIC(14) ); CREATE TABLE NC_AGGREGATE_RULE_TREE( FK_PARENT_ID INTEGER NOT NULL, FK_CHILD_ID INTEGER NOT NULL ); CREATE TABLE NC_BIE( PK_BIE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), LASTMOD_TIME TIMESTAMP, MOD_CNT INTEGER, LASTMOD_BY CHAR(40) ); CREATE TABLE NC_BIECALC( PK_CALC_ID INTEGER NOT NULL, LHS_TOPIC_ID INTEGER, TYPE_NAME VARCHAR(20), FK_RHSTYPE VARCHAR(20), OPERATOR CHAR(18), NUMERIC_LITERAL FLOAT, STRING_LITERAL VARCHAR(255), DATE_LITERAL TIMESTAMP, NAME VARCHAR(255), DESCRIPTION VARCHAR(255) ); CREATE TABLE NC_BIETOPIC( PK_BIETOPIC_ID INTEGER NOT NULL, FK_BIE_ID INTEGER, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), FK_DMT_ID INTEGER, TYPE VARCHAR(30), MODCOUNT INTEGER, FK_CALC_ID INTEGER, FK_BIETOPIC_ID INTEGER ); CREATE TABLE NC_COGNOS_DS( PK_PP_DS_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(1000), SERVER_NAME VARCHAR(255), SECURITY_DETAILS VARCHAR(2000), PORT_NUMBER SMALLINT ); CREATE TABLE NC_CONFIG_DS( PK_DS_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), USERNAME VARCHAR(32), PASSWORD VARCHAR(32), LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, SERVER_NAME VARCHAR(50), SECURITY_DETAILS BLOB(2000) ); CREATE TABLE NC_CTMAP( OBJTYPE SMALLINT NOT NULL, CLASS VARCHAR(200), TABLENAME VARCHAR(200) NOT NULL, HELPER VARCHAR(200), REMOTECLASS VARCHAR(200) ); CREATE TABLE NC_DATAMAPPING( PK_DM_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(1000), SQL_STRING CLOB(8000), FK_DS_ID INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, FK_PPDS_ID INTEGER, FK_COGNOSDS_ID INTEGER, URL CLOB(2000), NIC CLOB(2000) ); CREATE TABLE NC_DB_VERSION( DB_VERSION_ID INTEGER, BUILD VARCHAR(20) ); CREATE TABLE NC_DELIVERYADDRESS( PK_DELADDRESS_ID INTEGER NOT NULL, DELIVERY_ADDRESS VARCHAR(100), CHANNEL_TYPE INTEGER, IS_DEFAULT INTEGER NOT NULL, FK_RECIPIENT_ID INTEGER ); CREATE TABLE NC_DMTOPIC( PK_TOPIC_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), TSRC VARCHAR(100), TLINK VARCHAR(200), FK_DM_ID INTEGER NOT NULL, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, TABLE_NAME VARCHAR(64), COLUMN_NAME VARCHAR(64), DRE_COLUMN_NAME VARCHAR(64), COLUMN_TYPE INTEGER, PP_TOPIC_VALUE VARCHAR(255), OBJ_TYPE SMALLINT ); CREATE TABLE NC_DMT_LINK( PK_DM_ID INTEGER NOT NULL, FK_DM_ID INTEGER NOT NULL, FK_DATAMAPPING CHAR(40) NOT NULL ); CREATE TABLE NC_DSITEM_L1( PK_DSITEM1_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), FK_DS_ID INTEGER NOT NULL, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE NC_DSITEM_L2( PK_DSITEM2_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), FK_DSITEM1_ID INTEGER NOT NULL, TYPE INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE NC_DSPOOL( PK_DSPOOL_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), URL VARCHAR(128) NOT NULL, DRIVER VARCHAR(100) NOT NULL, DS_TYPE INTEGER, LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE NC_DYNAMIC_RECIPIENT_STRUCT( PK_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), TITLE VARCHAR(64), FIRSTNAME VARCHAR(64), MIDDLENAME VARCHAR(64), LASTNAME VARCHAR(64), PHONENUMBER VARCHAR(64), JOBTITLE VARCHAR(64), COMPANYNAME VARCHAR(255), DEPARTMENT VARCHAR(255), OFFICE VARCHAR(255), COUNTRY VARCHAR(255), TIMEZONE VARCHAR(64), LASTMOD_BY CHAR(40), MODCOUNT INTEGER WITH DEFAULT -1 ); CREATE TABLE NC_DYN_DELIVERY_INFO_STRUCT( PK_OID INTEGER NOT NULL, FK_DYNAMIC_RECIPIENT_STRUCT INTEGER, ADDRESS_DESCRIPTION CHAR(40), CHANNEL_TYPE INTEGER NOT NULL, ADDRESS_BIE_ID INTEGER NOT NULL, NAME VARCHAR(100), LASTMOD_BY VARCHAR(100), MODCOUNT INTEGER WITH DEFAULT -1, DESCRIPTION VARCHAR(100) ); CREATE TABLE NC_EXECUTABLE( PK_EXE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER, PATH VARCHAR(255) NOT NULL, FK_TASK_ID INTEGER NOT NULL ); CREATE TABLE NC_MACHINE_CONFIG( PK_OID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), LASTMOD_BY VARCHAR(32), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL, TIMEZONE VARCHAR(50) ); CREATE TABLE NC_MESSAGELINE( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, LINE_NO INTEGER NOT NULL, LINE_TYPE INTEGER ); CREATE TABLE NC_MESSAGELINE_ELEMENT( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, ELEMENT_NUMBER INTEGER NOT NULL, LINE_NO INTEGER NOT NULL, TEXT CLOB(4000), FK_BIE_TOPIC_ID INTEGER ); CREATE TABLE NC_MESSAGESTRUCT( PK_MS_ID INTEGER NOT NULL, FK_DYNREC_ID INTEGER, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), SOURCE VARCHAR(100), SUBJECT VARCHAR(100), MODCOUNT INTEGER WITH DEFAULT -1, LASTMOD_BY VARCHAR(50), CHANNEL_TYPE INTEGER, CONTACT VARCHAR(50), AVOID_DUPLICATION INTEGER WITH DEFAULT 0 NOT NULL ); CREATE TABLE NC_MESSAGE_ATTACHMENT( PK_ATTACHMENT_ID INTEGER NOT NULL, FK_MESSAGE_ID INTEGER NOT NULL, ATTACHMENT_TYPE INTEGER NOT NULL, ATTACHMENT VARCHAR(100) NOT NULL, ATTACHMENT_SERVER VARCHAR(100), MIME_TYPE VARCHAR(100) NOT NULL ); CREATE TABLE NC_MSGDUPLICATION_STORE( MSG_PART_ID NUMERIC NOT NULL, TOPIC_STRING_VALUE VARCHAR(100), FK_BIETOPIC_ID INTEGER NOT NULL, FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER, DATE_ TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL ); CREATE TABLE NC_MSGDUPLICATION_TEMP_STORE( MSG_PART_ID NUMERIC NOT NULL, TOPIC_STRING_VALUE VARCHAR(100), FK_BIETOPIC_ID INTEGER NOT NULL, FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER, SESSION_ID NUMERIC NOT NULL ); CREATE TABLE NC_OBJECTCATALOGUE( PK_OID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), SUBTYPE INTEGER, TYPE INTEGER NOT NULL, MODCOUNT INTEGER WITH DEFAULT -1 ); CREATE TABLE NC_OBJECTID( OBJECTID INTEGER ); CREATE TABLE NC_OBJECT_PERMISSIONS( FK_OID INTEGER NOT NULL, PERMISSION INTEGER NOT NULL, ACL CHAR(50) NOT NULL ); CREATE TABLE NC_PERMISSION( PK_PERMISSION_ID INTEGER NOT NULL, DESCRIPTION VARCHAR(30) NOT NULL ); CREATE TABLE NC_RECIPIENT( PK_RECIPIENT_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MODCOUNT INTEGER WITH DEFAULT -1, TITLE VARCHAR(10), FIRSTNAME VARCHAR(50), MIDDLENAME VARCHAR(50), LASTNAME VARCHAR(50), PHONENUMBER VARCHAR(50), JOBTITLE VARCHAR(50), COMPANY VARCHAR(255), DEPARTMENT VARCHAR(255), OFFICE VARCHAR(255), COUNTRY VARCHAR(50), TIMEZONE VARCHAR(50), TO_REDIRECT INTEGER NOT NULL, FK_REDIRECT_RID INTEGER, FK_REDIRECT_RGID INTEGER, IS_EXTERNAL INTEGER, IS_USEDEFAULTADDRESS INTEGER ); CREATE TABLE NC_RECIPIENTSCHEDULE( PK_SCHEDULE_ID INTEGER NOT NULL, SCHEDULE_NAME VARCHAR(100), FK_RECIPIENT_ID INTEGER, DELIVERY_SCHEDULE VARCHAR(2000), FK_DELADDRESS_ID INTEGER ); CREATE TABLE NC_RULE( PK_RULE_ID INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(2000), FK_RULETYPE VARCHAR(20), FK_RHSTYPE VARCHAR(30), FK_BIE_ID INTEGER, LHS_TOPIC_ID INTEGER, OPERATOR VARCHAR(30), DATE_LITERAL TIMESTAMP, STRING_LITERAL VARCHAR(255), RHS_TOPIC_ID INTEGER, RHS_OPTION INTEGER, NUMERIC_LITERAL FLOAT, RHS_BIE_ID INTEGER ); CREATE TABLE NC_SCHEDULE( PK_SCHEDULE_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), LASTMOD_BY CHAR(40), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MODCOUNT INTEGER WITH DEFAULT -1, SCHEDULE_TYPE CHAR(6), EXECUTE_FIRST VARCHAR(100), END_AT VARCHAR(100), TIMEZONE VARCHAR(100), START_DATE NUMERIC(14), END_DATE NUMERIC(14), REPEAT_FOREVER INTEGER, ACTIVE INTEGER, VALID_SCHEDULE INTEGER, USE_DAY_NUMBER INTEGER, START_TIME_STRING VARCHAR(50), START_DATE_STRING VARCHAR(50), END_DATE_STRING VARCHAR(50), DAY_OF_MONTH INTEGER, DAY_OF_WEEK INTEGER, DAY_OF_WEEK_IN_MONTH INTEGER, MONTH_OF_YEAR INTEGER, REPEAT_INTERVAL INTEGER, ESCALATION_SCHEDULE INTEGER, DAYS_OF_WEEK INTEGER, FIXED_INTERVAL_TYPE INTEGER, FIXED_INTERVAL_IN_MILLIS NUMERIC(10) ); CREATE TABLE NC_TASK( PK_TASK_ID INTEGER NOT NULL, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), FK_BIE_ID INTEGER, THRESHOLD INTEGER, WEIGHTS VARCHAR(200), TASK_TYPE INTEGER, DATEFORMAT CHAR(19), DETECTION_OPTION INTEGER, LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, MODCOUNT INTEGER WITH DEFAULT -1, LASTMOD_BY CHAR(40) ); CREATE TABLE NC_TASKSCHEDULE( PK_TASKSCHEDULE_ID INTEGER NOT NULL, FK_TASK_ID INTEGER, NAME VARCHAR(255), DESCRIPTION VARCHAR(2000), 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) ); CREATE TABLE 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 NC_USER_SESSION( TICKET_ID VARCHAR(32) NOT NULL, NC_USER_ID INTEGER NOT NULL, USER_CLASS CHAR(50) NOT NULL ); CREATE TABLE R_AGENT_ESCALATIONAGENT( FK_CHILD_AGENT_ID INTEGER NOT NULL, FK_PARENT_AGENT_ID INTEGER NOT NULL ); CREATE TABLE R_BIECALC_RHSTOPIC( FK_BIETOPIC_ID INTEGER NOT NULL, FK_CALC_ID INTEGER NOT NULL ); CREATE TABLE R_DS_MACHINE_DSPOOL( PK_DSMAC_ID INTEGER NOT NULL, FK_DSPOOL_ID INTEGER NOT NULL, FK_MACHINE_ID INTEGER NOT NULL, FK_CONFIG_DS_ID INTEGER NOT NULL, LASTMOD_BY VARCHAR(32), LASTMOD_TIME TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP NOT NULL, MOD_CNT INTEGER WITH DEFAULT -1 NOT NULL ); CREATE TABLE R_MESSAGESTRUCT_TASK( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_TASK_ID INTEGER NOT NULL ); CREATE TABLE R_MSGSTRUCT_AVOIDTOPICS( FK_MS_ID INTEGER NOT NULL, FK_BIETOPIC_ID INTEGER NOT NULL ); CREATE TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF( FK_MESSAGESTRUCT_ID INTEGER NOT NULL, FK_DELIVERY_INFO_ID INTEGER, FK_RECIPIENT_ID INTEGER NOT NULL, ADDED_BY_RECIP_ID INTEGER ); CREATE TABLE R_NEWSITEMS_NCOBJECTS( FK_NCID INTEGER NOT NULL, NID CHAR(32) NOT NULL, VID CHAR(32), TYPE SMALLINT ); CREATE TABLE R_RECIPIENT_ACCMANUSERID( F_RECIPIENTID INTEGER NOT NULL, ACCMAN_UID NUMERIC(10) NOT NULL ); CREATE TABLE R_RULE_PARAMETER( FK_TASK_ID INTEGER NOT NULL, LITERAL_TYPE VARCHAR(200), FK_RULE_ID INTEGER NOT NULL ); CREATE TABLE R_TASKSCHEDULE_SCHEDULE( FK_TASKSCHEDULE_ID INTEGER NOT NULL, FK_SCHEDULE_ID INTEGER NOT NULL, IS_ACTIVE INTEGER, IS_ESCALATION INTEGER ); ------------------------------------------------------------------------------- --$201-- indices create begin ------------------------------------------------------------------------------- CREATE INDEX I1N_MESSAGELINE_EL ON NC_MESSAGELINE_ELEMENT ( FK_MESSAGESTRUCT_ID); CREATE INDEX I2N_MESSAGELINE_EL ON NC_MESSAGELINE_ELEMENT ( LINE_NO,FK_MESSAGESTRUCT_ID); CREATE INDEX I1R_NEWSITEMS_NCOB ON R_NEWSITEMS_NCOBJECTS ( TYPE,VID,NID); ------------------------------------------------------------------------------- --$202-- primary keys add begin ------------------------------------------------------------------------------- ALTER TABLE NC_AGENT_MESSAGE ADD CONSTRAINT PN_AGENT_MESSAGE PRIMARY KEY (AGENT_ID,LOG_ID); ALTER TABLE NC_AGGREGATE_RULE_TREE ADD CONSTRAINT PN_AGGREGATE_RULE_ PRIMARY KEY (FK_CHILD_ID,FK_PARENT_ID); ALTER TABLE NC_BIE ADD CONSTRAINT PN_BIE PRIMARY KEY (PK_BIE_ID); ALTER TABLE NC_BIECALC ADD CONSTRAINT PN_BIECALC PRIMARY KEY (PK_CALC_ID); ALTER TABLE NC_BIETOPIC ADD CONSTRAINT PN_BIETOPIC PRIMARY KEY (PK_BIETOPIC_ID); ALTER TABLE NC_COGNOS_DS ADD CONSTRAINT PN_COGNOS_DS PRIMARY KEY (PK_PP_DS_ID); ALTER TABLE NC_CONFIG_DS ADD CONSTRAINT PN_CONFIG_DS PRIMARY KEY (PK_DS_ID); ALTER TABLE NC_CTMAP ADD CONSTRAINT PN_CTMAP PRIMARY KEY (OBJTYPE); ALTER TABLE NC_DATAMAPPING ADD CONSTRAINT PN_DATAMAPPING PRIMARY KEY (PK_DM_ID); ALTER TABLE NC_DELIVERYADDRESS ADD CONSTRAINT PN_DELIVERYADDRESS PRIMARY KEY (PK_DELADDRESS_ID); ALTER TABLE NC_DMTOPIC ADD CONSTRAINT PN_DMTOPIC PRIMARY KEY (PK_TOPIC_ID); ALTER TABLE NC_DSITEM_L1 ADD CONSTRAINT PN_DSITEM_L1 PRIMARY KEY (PK_DSITEM1_ID); ALTER TABLE NC_DSITEM_L2 ADD CONSTRAINT PN_DSITEM_L2 PRIMARY KEY (PK_DSITEM2_ID); ALTER TABLE NC_DSPOOL ADD CONSTRAINT PN_DSPOOL PRIMARY KEY (PK_DSPOOL_ID); ALTER TABLE NC_DYNAMIC_RECIPIENT_STRUCT ADD CONSTRAINT PN_DYNAMIC_RECIPIE PRIMARY KEY (PK_ID); ALTER TABLE NC_DYN_DELIVERY_INFO_STRUCT ADD CONSTRAINT PN_DYN_DELIVERY_IN PRIMARY KEY (PK_OID); ALTER TABLE NC_EXECUTABLE ADD CONSTRAINT PN_EXECUTABLE PRIMARY KEY (PK_EXE_ID); ALTER TABLE NC_MACHINE_CONFIG ADD CONSTRAINT PN_MACHINE_CONFIG PRIMARY KEY (PK_OID); ALTER TABLE NC_MESSAGELINE ADD CONSTRAINT PN_MESSAGELINE PRIMARY KEY (LINE_NO,FK_MESSAGESTRUCT_ID); ALTER TABLE NC_MESSAGELINE_ELEMENT ADD CONSTRAINT PN_MESSAGELINE_ELE PRIMARY KEY (LINE_NO,ELEMENT_NUMBER,FK_MESSAGESTRUCT_ID); ALTER TABLE NC_MESSAGESTRUCT ADD CONSTRAINT PN_MESSAGESTRUCT PRIMARY KEY (PK_MS_ID); ALTER TABLE NC_OBJECTCATALOGUE ADD CONSTRAINT PN_OBJECTCATALOGUE PRIMARY KEY (PK_OID); ALTER TABLE NC_OBJECT_PERMISSIONS ADD CONSTRAINT PN_OBJECT_PERMISSI PRIMARY KEY (ACL,PERMISSION,FK_OID); ALTER TABLE NC_PERMISSION ADD CONSTRAINT PN_PERMISSION PRIMARY KEY (PK_PERMISSION_ID); ALTER TABLE NC_RECIPIENT ADD CONSTRAINT PN_RECIPIENT PRIMARY KEY (PK_RECIPIENT_ID); ALTER TABLE NC_RECIPIENTSCHEDULE ADD CONSTRAINT PN_RECIPIENTSCHEDU PRIMARY KEY (PK_SCHEDULE_ID); ALTER TABLE NC_RULE ADD CONSTRAINT PN_RULE PRIMARY KEY (PK_RULE_ID); ALTER TABLE NC_SCHEDULE ADD CONSTRAINT PN_SCHEDULE PRIMARY KEY (PK_SCHEDULE_ID); ALTER TABLE NC_TASK ADD CONSTRAINT PN_TASK PRIMARY KEY (PK_TASK_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_USER_SESSION ADD CONSTRAINT PN_USER_SESSION PRIMARY KEY (USER_CLASS,NC_USER_ID,TICKET_ID); ALTER TABLE R_AGENT_ESCALATIONAGENT ADD CONSTRAINT PR_AGENT_ESCALATIO PRIMARY KEY (FK_PARENT_AGENT_ID,FK_CHILD_AGENT_ID); ALTER TABLE R_MESSAGESTRUCT_TASK ADD CONSTRAINT PR_MESSAGESTRUCT_T PRIMARY KEY (FK_TASK_ID,FK_MESSAGESTRUCT_ID); ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF ADD CONSTRAINT PR_MSGSTRUCT_RECIP PRIMARY KEY (FK_RECIPIENT_ID,FK_MESSAGESTRUCT_ID); ALTER TABLE R_NEWSITEMS_NCOBJECTS ADD CONSTRAINT PR_NEWSITEMS_NCOBJ PRIMARY KEY (FK_NCID); ALTER TABLE R_RECIPIENT_ACCMANUSERID ADD CONSTRAINT PR_RECIPIENT_ACCMA PRIMARY KEY (ACCMAN_UID,F_RECIPIENTID); ALTER TABLE R_TASKSCHEDULE_SCHEDULE ADD CONSTRAINT PR_TASKSCHEDULE_SC PRIMARY KEY (FK_SCHEDULE_ID,FK_TASKSCHEDULE_ID); ------------------------------------------------------------------------------- --$210-- views create begin ------------------------------------------------------------------------------- CREATE VIEW V_BIECALC_LINK AS SELECT CALC.*, LINK.FK_BIETOPIC_ID FROM NC_BIECALC CALC LEFT JOIN R_BIECALC_RHSTOPIC LINK ON CALC.PK_CALC_ID = LINK.FK_CALC_ID; 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_LOG.TASK_NAME AS AGENT_NAME, NC_TASK_LOG.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 AS MESSAGE FROM NC_AGENT_MESSAGE RIGHT OUTER JOIN NC_TASK_LOG ON NC_AGENT_MESSAGE.LOG_ID = NC_TASK_LOG.LOG_ID AND NC_AGENT_MESSAGE.AGENT_ID = NC_TASK_LOG.TASK_ID; CREATE VIEW V_BIE_COMPLETE AS SELECT NC_BIE.PK_BIE_ID AS BIE_PK_ID, NC_BIETOPIC.FK_BIE_ID AS BIETOPIC_FK_BIE_ID, NC_BIETOPIC.FK_CALC_ID AS BIETOPIC_FK_CALC_ID, V_BIECALC_LINK.PK_CALC_ID AS CALC_LINK_PK_ID, NC_BIE.NAME AS BIE_NAME, NC_BIE.DESCRIPTION AS BIE_DESCRIPTION, NC_BIE.MOD_CNT AS BIE_MODCOUNT, NC_BIETOPIC.NAME AS BIETOPIC_NAME, NC_BIETOPIC.DESCRIPTION AS BIETOPIC_DESCRIPTION, NC_BIETOPIC.PK_BIETOPIC_ID AS BIETOPIC_PK_ID, NC_BIETOPIC.TYPE AS BIETOPIC_TYPE, NC_BIETOPIC.MODCOUNT AS BIETOPIC_MODCOUNT, NC_BIETOPIC.FK_DMT_ID AS BIETOPIC_FK_DMT_ID, NC_DMTOPIC.OBJ_TYPE AS TOPIC_OBJ_TYPE, NC_DMTOPIC.PK_TOPIC_ID AS DMT_PK_ID, NC_DMTOPIC.NAME AS DMT_NAME, NC_DMTOPIC.DESCRIPTION AS DMT_DESCRIPTION, NC_DMTOPIC.COLUMN_NAME AS DMT_COLUMN_NAME, NC_DMTOPIC.DRE_COLUMN_NAME AS DMT_DRE_COLUMN_NAME, NC_DMTOPIC.TABLE_NAME AS DMT_TABLE_NAME, NC_DMTOPIC.COLUMN_TYPE AS DMT_COLUMN_TYPE, NC_DMTOPIC.FK_DM_ID AS DMT_FK_DM_ID, NC_DMTOPIC.PP_TOPIC_VALUE AS DMT_PP_VALUE, V_BIECALC_LINK.NAME AS CALC_LINK_NAME, V_BIECALC_LINK.DESCRIPTION AS CALC_LINK_DESCRIPTION, V_BIECALC_LINK.LHS_TOPIC_ID AS CALC_LINK_LHS_TOPIC_ID, V_BIECALC_LINK.FK_RHSTYPE AS CALC_LINK_FK_RHSTYPE, V_BIECALC_LINK.OPERATOR AS CALC_LINK_OPERATOR, V_BIECALC_LINK.NUMERIC_LITERAL AS CALC_LINK_NUMERIC_LITERAL, V_BIECALC_LINK.STRING_LITERAL AS CALC_LINK_STRING_LITERAL, V_BIECALC_LINK.DATE_LITERAL AS CALC_LINK_DATE_LITERAL, V_BIECALC_LINK.FK_BIETOPIC_ID AS CALC_LINK_FK_BIETOPIC_ID, NC_RULE.FK_BIE_ID AS RULE_FK_BIE_ID, NC_RULE.PK_RULE_ID AS RULE_PK_ID, NC_RULE.NAME AS RULE_NAME, NC_RULE.DESCRIPTION AS RULE_DESCRIPTION, NC_RULE.FK_RULETYPE AS RULE_FK_RULETYPE, NC_RULE.DATE_LITERAL AS RULE_DATE_LITERAL, NC_RULE.STRING_LITERAL AS RULE_STRING_LITERAL, NC_RULE.NUMERIC_LITERAL AS RULE_NUMERIC_LITERAL, NC_RULE.LHS_TOPIC_ID AS RULE_LHS_TOPIC_ID, NC_RULE.OPERATOR AS RULE_OPERATOR, NC_RULE.FK_RHSTYPE AS RULE_FK_RHSTYPE, NC_RULE.RHS_TOPIC_ID AS RULE_RHS_TOPIC_ID, NC_RULE.RHS_OPTION AS RULE_RHS_OPTION, NC_RULE.RHS_BIE_ID AS RULE_RHS_BIE_ID FROM V_BIECALC_LINK RIGHT OUTER JOIN NC_BIETOPIC ON V_BIECALC_LINK.PK_CALC_ID = NC_BIETOPIC.FK_CALC_ID RIGHT OUTER JOIN NC_BIE LEFT OUTER JOIN NC_RULE ON NC_BIE.PK_BIE_ID = NC_RULE.FK_BIE_ID ON NC_BIETOPIC.FK_BIE_ID = NC_BIE.PK_BIE_ID LEFT OUTER JOIN NC_DMTOPIC ON NC_BIETOPIC.FK_DMT_ID = NC_DMTOPIC.PK_TOPIC_ID; ------------------------------------------------------------------------------- --$220-- server data inserts begin ------------------------------------------------------------------------------- INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 110, 'noticecast.logictier.datasource.JDBCDataSourceImpl', 'NC_CONFIG_DS', 'noticecast.datatier.datasource.JDBCDhJDBCDataSourceImpl', 'noticecast.logictier.datasource.JDBCDataSourceRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 111, 'noticecast.logictier.datasource.MachineImpl', 'NC_MACHINE_CONFIG', 'noticecast.datatier.datasource.JDBCDhMachine', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 112, 'noticecast.logictier.datasource.DsPool', 'NC_DSPOOL', 'noticecast.datatier.datasource.JDBCDhDsPool', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 115, 'noticecast.logictier.datasource.cognos.CognosDataSourceImpl', 'NC_COGNOS_DS', 'noticecast.datatier.datasource.cognos.JDBCDhCognosDataSource', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 200, 'noticecast.logictier.datamapping.JDBCDataMappingImpl', 'NCDataMapping', 'noticecast.datatier.datamapping.JDBCDhJDBCDataMapping', 'noticecast.logictier.datamapping.JDBCDataMappingRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 210, 'noticecast.logictier.datamapping.JDBCDataMappingTopicImpl', 'NC_DMTOPIC', 'noticecast.datatier.datamapping.JDBCDhJDBCDataMappingTopic', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 220, 'noticecast.logictier.datamapping.powerplay.PPDataMappingImpl', 'NCDATAMAPPING', 'noticecast.datatier.datamapping.powerplay.JDBCDhPPDataMapping', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 221, 'noticecast.logictier.datamapping.powerplay.PPCellImpl', 'NC_DMTOPIC', 'noticecast.datatier.datamapping.powerplay.JDBCDhPPDataMappingTopic', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 222, 'noticecast.logictier.datamapping.powerplay.PPCellImpl', 'NC_DMTOPIC', 'noticecast.datatier.datamapping.powerplay.JDBCDhPPDataMappingTopic', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 223, 'noticecast.logictier.datamapping.powerplay.PPTopicImpl', 'NC_DMTOPIC', 'noticecast.datatier.datamapping.powerplay.JDBCDhPPDataMappingTopic', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 230, 'noticecast.logictier.datamapping.cognosquery.CQDataMappingImpl', 'NCDATAMAPPING', 'noticecast.datatier.datamapping.cognosquery.JDBCDhCQDataMapping', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 231, 'noticecast.logictier.datamapping.cognosquery.CQTopicImpl', 'NC_DMTOPIC', 'noticecast.datatier.datamapping.cognosquery.JDBCDhCQDataMappingTopic', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 300, 'noticecast.logictier.bie.BusinessInfoEntityImpl', 'NC_BIE', 'noticecast.datatier.bie.JDBCDhBusinessInfoEntityImpl', 'noticecast.logictier.bie.BusinessInfoEntityRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 301, 'noticecast.logictier.bie.BusinessInfoEntityImpl', 'NC_COGNOS_BIE', 'noticecast.datatier.bie.JDBCDhBusinessInfoEntityImpl', 'noticecast.logictier.bie.BusinessInfoEntityRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 310, 'noticecast.logictier.bie.BIETopicImpl', 'NC_BIETopic', 'noticecast.datatier.bie.JDBCDhBIETopicImpl', 'noticecast.logictier.bie.BIETopicRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 320, 'noticecast.logictier.bie.calculation.CalculationImpl', 'NC_CALCULATION', 'noticecast.datatier.bie.calculation.JDBCDhCalculationImpl', 'noticecast.logictier.bie.calculation.CalculationRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 330, 'noticecast.logictier.bie.rules.FilterRuleImpl', 'NC_FILTER_RULE', 'noticecast.datatier.bie.rules.JDBCDhFilterRuleImpl', 'noticecast.logictier.bie.rules.FilterRuleRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 340, 'noticecast.logictier.bie.rules.AggregateRuleImpl', 'NC_AGGREGATE_RULE', 'noticecast.datatier.bie.rules.JDBCDhAggregateRuleImpl', 'noticecast.logictier.bie.rules.AggregateRuleRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 350, 'noticecast.logictier.bie.rules.ComparativeRuleImpl', 'NC_COMPARATIVE_RULE', 'noticecast.datatier.bie.rules.JDBCDhComparativeRuleImpl', 'noticecast.logictier.bie.rules.ComparativeRuleRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 360, NULL, 'NC_RULE', 'noticecast.datatier.bie.rules.JDBCDhRuleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 700, 'noticecast.logictier.recipient.RecipientImpl', 'NC_RECIPIENT', 'noticecast.datatier.recipient.JDBCDhRecipientImpl', 'noticecast.logictier.recipient.RecipientRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 721, 'noticecast.logictier.message.attachment.MessageAttachment', 'NC_MESSAGE_ATTACHMENT', NULL, NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 740, 'noticecast.logictier.message.MessageStructImpl', 'NC_MESSAGESTRUCT', 'noticecast.datatier.message.JDBCDhJDBCMessageStruct', 'noticecast.logictier.message.MessageStructRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 741, 'noticecast.logictier.dynamicrecipient.DynamicRecipientStructImpl', 'NC_DYNAMIC_RECIPIENT_STRUCT', 'noticecast.datatier.dynamicrecipient.JDBCDhDynamicRecipientStructImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 742, 'noticecast.logictier.dynamicrecipient.DynamicDeliveryInformationStructImpl', 'NC_DYN_DELIVERY_INFO_STRUCT', 'noticecast.datatier.dynamicrecipient.JDBCDhDynamicDeliveryInfoStructImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 745, 'noticecast.logictier.recipient.RecipientDeliveryScheduleImpl', 'NC_RECIPIENTSCHEDULE', NULL, NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 746, 'noticecast.logictier.recipient.DeliveryAddressImpl', 'NC_DELIVERYADDRESS', NULL, NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 900, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', 'noticecast.logictier.agent.TaskRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 901, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 902, 'noticecast.scheduling.TaskScheduleImpl', 'NC_TASK_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhTaskScheduleImpl', 'noticecast.scheduling.TaskScheduleRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 905, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 906, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 907, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', 'noticecast.logictier.agent.TaskRImpl' ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 908, 'noticecast.logictier.agent.TaskImpl', 'NC_TASK', 'noticecast.datatier.task.JDBCDhJDBCTask', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 909, 'noticecast.logictier.executable.ExecutableImpl', 'NC_EXECUTABLE', 'noticecast.datatier.executable.JDBCDhJDBCExecuteable', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1000, 'noticecast.scheduling.YearlySchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1001, 'noticecast.scheduling.MonthlySchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1002, 'noticecast.scheduling.WeeklySchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1003, 'noticecast.scheduling.DailySchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1004, 'noticecast.scheduling.FixedIntervalSchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES( 1005, 'noticecast.scheduling.ISchedule', 'NC_SCHEDULE', 'noticecast.datatier.scheduling.JDBCDhScheduleImpl', NULL ); INSERT INTO NC_PERMISSION (PK_PERMISSION_ID,DESCRIPTION) VALUES( 1, 'Create' ); INSERT INTO NC_PERMISSION (PK_PERMISSION_ID,DESCRIPTION) VALUES( 2, 'Read' ); INSERT INTO NC_PERMISSION (PK_PERMISSION_ID,DESCRIPTION) VALUES( 3, 'Update' ); INSERT INTO NC_PERMISSION (PK_PERMISSION_ID,DESCRIPTION) VALUES( 4, 'Delete' ); INSERT INTO NC_PERMISSION (PK_PERMISSION_ID,DESCRIPTION) VALUES( 5, 'Execute' ); INSERT INTO NC_DB_VERSION (DB_VERSION_ID,BUILD) VALUES( 2, 'build no.' ); ------------------------------------------------------------------------------- --$230-- restore inserts begin ------------------------------------------------------------------------------- INSERT INTO NC_AGENT_MESSAGE (LOG_ID,AGENT_ID,MESSAGE,TIMESTAMP) SELECT LOG_ID,AGENT_ID,MESSAGE,TIMESTAMP FROM BC_AGENT_MESSAGE; INSERT INTO NC_AGGREGATE_RULE_TREE (FK_PARENT_ID,FK_CHILD_ID) SELECT FK_PARENT_ID,FK_CHILD_ID FROM BC_AGGREGATE_RULE_TREE; INSERT INTO NC_BIE (PK_BIE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,LASTMOD_BY) SELECT PK_BIE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,LASTMOD_BY FROM BC_BIE; INSERT INTO NC_BIECALC (PK_CALC_ID,LHS_TOPIC_ID,TYPE_NAME,FK_RHSTYPE,OPERATOR,NUMERIC_LITERAL,STRING_LITERAL,DATE_LITERAL,NAME,DESCRIPTION) SELECT PK_CALC_ID,LHS_TOPIC_ID,TYPE_NAME,FK_RHSTYPE,OPERATOR,NUMERIC_LITERAL,STRING_LITERAL,DATE_LITERAL,NAME,DESCRIPTION FROM BC_BIECALC; INSERT INTO NC_BIETOPIC (PK_BIETOPIC_ID,FK_BIE_ID,NAME,DESCRIPTION,FK_DMT_ID,TYPE,MODCOUNT,FK_CALC_ID,FK_BIETOPIC_ID) SELECT PK_BIETOPIC_ID,FK_BIE_ID,NAME,DESCRIPTION,FK_DMT_ID,TYPE,MODCOUNT,FK_CALC_ID,FK_BIETOPIC_ID FROM BC_BIETOPIC; INSERT INTO NC_COGNOS_DS (PK_PP_DS_ID,NAME,DESCRIPTION,SERVER_NAME,SECURITY_DETAILS,PORT_NUMBER) SELECT PK_PP_DS_ID,NAME,DESCRIPTION,SERVER_NAME,SECURITY_DETAILS,PORT_NUMBER FROM BC_COGNOS_DS; INSERT INTO NC_CONFIG_DS (PK_DS_ID,NAME,DESCRIPTION,USERNAME,PASSWORD,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,SERVER_NAME,SECURITY_DETAILS) SELECT PK_DS_ID,NAME,DESCRIPTION,USERNAME,PASSWORD,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,SERVER_NAME,SECURITY_DETAILS FROM BC_CONFIG_DS; INSERT INTO NC_DATAMAPPING (PK_DM_ID,NAME,DESCRIPTION,SQL_STRING,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,FK_PPDS_ID,FK_COGNOSDS_ID,URL,NIC) SELECT PK_DM_ID,NAME,DESCRIPTION,SQL_STRING,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,FK_PPDS_ID,FK_COGNOSDS_ID,URL,NIC FROM BC_DATAMAPPING; INSERT INTO NC_DB_VERSION (DB_VERSION_ID,BUILD) SELECT DB_VERSION_ID,BUILD FROM BC_DB_VERSION; INSERT INTO NC_DELIVERYADDRESS (PK_DELADDRESS_ID,DELIVERY_ADDRESS,CHANNEL_TYPE,IS_DEFAULT,FK_RECIPIENT_ID) SELECT PK_DELADDRESS_ID,DELIVERY_ADDRESS,CHANNEL_TYPE,IS_DEFAULT,FK_RECIPIENT_ID FROM BC_DELIVERYADDRESS; INSERT INTO NC_DMTOPIC (PK_TOPIC_ID,NAME,DESCRIPTION,TSRC,TLINK,FK_DM_ID,LASTMOD_BY,LASTMOD_TIME,TABLE_NAME,COLUMN_NAME,DRE_COLUMN_NAME,COLUMN_TYPE,PP_TOPIC_VALUE,OBJ_TYPE) SELECT PK_TOPIC_ID,NAME,DESCRIPTION,TSRC,TLINK,FK_DM_ID,LASTMOD_BY,LASTMOD_TIME,TABLE_NAME,COLUMN_NAME,DRE_COLUMN_NAME,COLUMN_TYPE,PP_TOPIC_VALUE,OBJ_TYPE FROM BC_DMTOPIC; INSERT INTO NC_DMT_LINK (PK_DM_ID,FK_DM_ID,FK_DATAMAPPING) SELECT PK_DM_ID,FK_DM_ID,FK_DATAMAPPING FROM BC_DMT_LINK; INSERT INTO NC_DSITEM_L1 (PK_DSITEM1_ID,NAME,DESCRIPTION,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSITEM1_ID,NAME,DESCRIPTION,FK_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM BC_DSITEM_L1; INSERT INTO NC_DSITEM_L2 (PK_DSITEM2_ID,NAME,DESCRIPTION,FK_DSITEM1_ID,TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSITEM2_ID,NAME,DESCRIPTION,FK_DSITEM1_ID,TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM BC_DSITEM_L2; INSERT INTO NC_DSPOOL (PK_DSPOOL_ID,NAME,DESCRIPTION,URL,DRIVER,DS_TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSPOOL_ID,NAME,DESCRIPTION,URL,DRIVER,DS_TYPE,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM BC_DSPOOL; INSERT INTO NC_DYNAMIC_RECIPIENT_STRUCT (PK_ID,NAME,DESCRIPTION,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANYNAME,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,LASTMOD_BY,MODCOUNT) SELECT PK_ID,NAME,DESCRIPTION,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANYNAME,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,LASTMOD_BY,MODCOUNT FROM BC_DYNAMIC_RECIPIENT_STRUCT; INSERT INTO NC_DYN_DELIVERY_INFO_STRUCT (PK_OID,FK_DYNAMIC_RECIPIENT_STRUCT,ADDRESS_DESCRIPTION,CHANNEL_TYPE,ADDRESS_BIE_ID,NAME,LASTMOD_BY,MODCOUNT,DESCRIPTION) SELECT PK_OID,FK_DYNAMIC_RECIPIENT_STRUCT,ADDRESS_DESCRIPTION,CHANNEL_TYPE,ADDRESS_BIE_ID,NAME,LASTMOD_BY,MODCOUNT,DESCRIPTION FROM BC_DYN_DELIVERY_INFO_STRUCT; INSERT INTO NC_EXECUTABLE (PK_EXE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,PATH,FK_TASK_ID) SELECT PK_EXE_ID,NAME,DESCRIPTION,LASTMOD_TIME,MOD_CNT,PATH,FK_TASK_ID FROM BC_EXECUTABLE; INSERT INTO NC_MACHINE_CONFIG (PK_OID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,TIMEZONE) SELECT PK_OID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MOD_CNT,TIMEZONE FROM BC_MACHINE_CONFIG; INSERT INTO NC_MESSAGELINE (FK_MESSAGESTRUCT_ID,LINE_NO,LINE_TYPE) SELECT FK_MESSAGESTRUCT_ID,LINE_NO,LINE_TYPE FROM BC_MESSAGELINE; INSERT INTO NC_MESSAGELINE_ELEMENT (FK_MESSAGESTRUCT_ID,ELEMENT_NUMBER,LINE_NO,TEXT,FK_BIE_TOPIC_ID) SELECT FK_MESSAGESTRUCT_ID,ELEMENT_NUMBER,LINE_NO,TEXT,FK_BIE_TOPIC_ID FROM BC_MESSAGELINE_ELEMENT; INSERT INTO NC_MESSAGESTRUCT (PK_MS_ID,FK_DYNREC_ID,NAME,DESCRIPTION,SOURCE,SUBJECT,MODCOUNT,LASTMOD_BY,CHANNEL_TYPE,CONTACT,AVOID_DUPLICATION) SELECT PK_MS_ID,FK_DYNREC_ID,NAME,DESCRIPTION,SOURCE,SUBJECT,MODCOUNT,LASTMOD_BY,CHANNEL_TYPE,CONTACT,AVOID_DUPLICATION FROM BC_MESSAGESTRUCT; INSERT INTO NC_MESSAGE_ATTACHMENT (PK_ATTACHMENT_ID,FK_MESSAGE_ID,ATTACHMENT_TYPE,ATTACHMENT,ATTACHMENT_SERVER,MIME_TYPE) SELECT PK_ATTACHMENT_ID,FK_MESSAGE_ID,ATTACHMENT_TYPE,ATTACHMENT,ATTACHMENT_SERVER,MIME_TYPE FROM BC_MESSAGE_ATTACHMENT; INSERT INTO NC_MSGDUPLICATION_STORE (MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,DATE_) SELECT MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,DATE_ FROM BC_MSGDUPLICATION_STORE; INSERT INTO NC_MSGDUPLICATION_TEMP_STORE (MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,SESSION_ID) SELECT MSG_PART_ID,TOPIC_STRING_VALUE,FK_BIETOPIC_ID,FK_MESSAGESTRUCT_ID,FK_TASK_ID,SESSION_ID FROM BC_MSGDUPLICATION_TEMP_STORE; INSERT INTO NC_OBJECTCATALOGUE (PK_OID,NAME,DESCRIPTION,SUBTYPE,TYPE,MODCOUNT) SELECT PK_OID,NAME,DESCRIPTION,SUBTYPE,TYPE,MODCOUNT FROM BC_OBJECTCATALOGUE; INSERT INTO NC_OBJECTID (OBJECTID) SELECT OBJECTID FROM BC_OBJECTID; INSERT INTO NC_OBJECT_PERMISSIONS (FK_OID,PERMISSION,ACL) SELECT FK_OID,PERMISSION,ACL FROM BC_OBJECT_PERMISSIONS; INSERT INTO NC_RECIPIENT (PK_RECIPIENT_ID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MODCOUNT,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANY,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,TO_REDIRECT,FK_REDIRECT_RID,FK_REDIRECT_RGID,IS_EXTERNAL,IS_USEDEFAULTADDRESS) SELECT PK_RECIPIENT_ID,NAME,DESCRIPTION,LASTMOD_BY,LASTMOD_TIME,MODCOUNT,TITLE,FIRSTNAME,MIDDLENAME,LASTNAME,PHONENUMBER,JOBTITLE,COMPANY,DEPARTMENT,OFFICE,COUNTRY,TIMEZONE,TO_REDIRECT,FK_REDIRECT_RID,FK_REDIRECT_RGID,IS_EXTERNAL,IS_USEDEFAULTADDRESS FROM BC_RECIPIENT; INSERT INTO NC_RECIPIENTSCHEDULE (PK_SCHEDULE_ID,SCHEDULE_NAME,FK_RECIPIENT_ID,DELIVERY_SCHEDULE,FK_DELADDRESS_ID) SELECT PK_SCHEDULE_ID,SCHEDULE_NAME,FK_RECIPIENT_ID,DELIVERY_SCHEDULE,FK_DELADDRESS_ID FROM BC_RECIPIENTSCHEDULE; INSERT INTO NC_RULE (PK_RULE_ID,NAME,DESCRIPTION,FK_RULETYPE,FK_RHSTYPE,FK_BIE_ID,LHS_TOPIC_ID,OPERATOR,DATE_LITERAL,STRING_LITERAL,RHS_TOPIC_ID,RHS_OPTION,NUMERIC_LITERAL,RHS_BIE_ID) SELECT PK_RULE_ID,NAME,DESCRIPTION,FK_RULETYPE,FK_RHSTYPE,FK_BIE_ID,LHS_TOPIC_ID,OPERATOR,DATE_LITERAL,STRING_LITERAL,RHS_TOPIC_ID,RHS_OPTION,NUMERIC_LITERAL,RHS_BIE_ID FROM BC_RULE; 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) 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 FROM BC_SCHEDULE; INSERT INTO NC_TASK (PK_TASK_ID,NAME,DESCRIPTION,FK_BIE_ID,THRESHOLD,WEIGHTS,TASK_TYPE,DATEFORMAT,DETECTION_OPTION,LASTMOD_TIME,MODCOUNT,LASTMOD_BY) SELECT PK_TASK_ID,NAME,DESCRIPTION,FK_BIE_ID,THRESHOLD,WEIGHTS,TASK_TYPE,DATEFORMAT,DETECTION_OPTION,LASTMOD_TIME,MODCOUNT,LASTMOD_BY FROM BC_TASK; 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) SELECT PK_TASKSCHEDULE_ID,FK_TASK_ID,NAME,DESCRIPTION,LASTMOD_BY,MODCOUNT,RUN_REQUIREMENTS,LAST_EXECUTION_AT,NEXT_EXECUTION_REQUEST,SCHEDULE_DELAY FROM BC_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_TASK_LOG; INSERT INTO NC_USER_SESSION (TICKET_ID,NC_USER_ID,USER_CLASS) SELECT TICKET_ID,NC_USER_ID,USER_CLASS FROM BC_USER_SESSION; INSERT INTO R_AGENT_ESCALATIONAGENT (FK_CHILD_AGENT_ID,FK_PARENT_AGENT_ID) SELECT FK_CHILD_AGENT_ID,FK_PARENT_AGENT_ID FROM B_AGENT_ESCALATIONAGENT; INSERT INTO R_BIECALC_RHSTOPIC (FK_BIETOPIC_ID,FK_CALC_ID) SELECT FK_BIETOPIC_ID,FK_CALC_ID FROM B_BIECALC_RHSTOPIC; INSERT INTO R_DS_MACHINE_DSPOOL (PK_DSMAC_ID,FK_DSPOOL_ID,FK_MACHINE_ID,FK_CONFIG_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT) SELECT PK_DSMAC_ID,FK_DSPOOL_ID,FK_MACHINE_ID,FK_CONFIG_DS_ID,LASTMOD_BY,LASTMOD_TIME,MOD_CNT FROM B_DS_MACHINE_DSPOOL; INSERT INTO R_MESSAGESTRUCT_TASK (FK_MESSAGESTRUCT_ID,FK_TASK_ID) SELECT FK_MESSAGESTRUCT_ID,FK_TASK_ID FROM B_MESSAGESTRUCT_TASK; INSERT INTO R_MSGSTRUCT_AVOIDTOPICS (FK_MS_ID,FK_BIETOPIC_ID) SELECT FK_MS_ID,FK_BIETOPIC_ID FROM B_MSGSTRUCT_AVOIDTOPICS; INSERT INTO R_MSGSTRUCT_RECIPIENT_DELIVINF (FK_MESSAGESTRUCT_ID,FK_DELIVERY_INFO_ID,FK_RECIPIENT_ID,ADDED_BY_RECIP_ID) SELECT FK_MESSAGESTRUCT_ID,FK_DELIVERY_INFO_ID,FK_RECIPIENT_ID,ADDED_BY_RECIP_ID FROM B_MSGSTRUCT_RECIPIENT_DELIVINF; INSERT INTO R_NEWSITEMS_NCOBJECTS (FK_NCID,NID,VID,TYPE) SELECT FK_NCID,NID,VID,TYPE FROM B_NEWSITEMS_NCOBJECTS; INSERT INTO R_RECIPIENT_ACCMANUSERID (F_RECIPIENTID,ACCMAN_UID) SELECT F_RECIPIENTID,ACCMAN_UID FROM B_RECIPIENT_ACCMANUSERID; INSERT INTO R_RULE_PARAMETER (FK_TASK_ID,LITERAL_TYPE,FK_RULE_ID) SELECT FK_TASK_ID,LITERAL_TYPE,FK_RULE_ID FROM B_RULE_PARAMETER; INSERT INTO R_TASKSCHEDULE_SCHEDULE (FK_TASKSCHEDULE_ID,FK_SCHEDULE_ID,IS_ACTIVE,IS_ESCALATION) SELECT FK_TASKSCHEDULE_ID,FK_SCHEDULE_ID,IS_ACTIVE,IS_ESCALATION FROM B_TASKSCHEDULE_SCHEDULE; ------------------------------------------------------------------------------- --$240-- foreign keys add begin ------------------------------------------------------------------------------- ALTER TABLE NC_AGGREGATE_RULE_TREE ADD CONSTRAINT F2N_AGGREGATE_RULE FOREIGN KEY (FK_CHILD_ID) REFERENCES NC_RULE; ALTER TABLE NC_AGGREGATE_RULE_TREE ADD CONSTRAINT F1N_AGGREGATE_RULE FOREIGN KEY (FK_PARENT_ID) REFERENCES NC_RULE; ALTER TABLE NC_BIETOPIC ADD CONSTRAINT F5N_BIETOPIC FOREIGN KEY (FK_DMT_ID) REFERENCES NC_DMTOPIC; ALTER TABLE NC_BIETOPIC ADD CONSTRAINT F2N_BIETOPIC FOREIGN KEY (FK_BIE_ID) REFERENCES NC_BIE; ALTER TABLE NC_DATAMAPPING ADD CONSTRAINT F9N_DATAMAPPING FOREIGN KEY (FK_PPDS_ID) REFERENCES NC_COGNOS_DS; ALTER TABLE NC_DATAMAPPING ADD CONSTRAINT F5N_DATAMAPPING FOREIGN KEY (FK_DS_ID) REFERENCES NC_CONFIG_DS; ALTER TABLE NC_DYN_DELIVERY_INFO_STRUCT ADD CONSTRAINT F2N_DYN_DELIVERY_I FOREIGN KEY (FK_DYNAMIC_RECIPIENT_STRUCT) REFERENCES NC_DYNAMIC_RECIPIENT_STRUCT; ALTER TABLE NC_EXECUTABLE ADD CONSTRAINT F7N_EXECUTABLE FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK; ALTER TABLE NC_MESSAGELINE ADD CONSTRAINT F1N_MESSAGELINE FOREIGN KEY (FK_MESSAGESTRUCT_ID) REFERENCES NC_MESSAGESTRUCT; ALTER TABLE NC_MESSAGELINE_ELEMENT ADD CONSTRAINT F1N_MESSAGELINE_EL FOREIGN KEY (LINE_NO,FK_MESSAGESTRUCT_ID) REFERENCES NC_MESSAGELINE; ALTER TABLE NC_MESSAGESTRUCT ADD CONSTRAINT F2N_MESSAGESTRUCT FOREIGN KEY (FK_DYNREC_ID) REFERENCES NC_DYNAMIC_RECIPIENT_STRUCT; ALTER TABLE NC_MESSAGE_ATTACHMENT ADD CONSTRAINT F2N_MESSAGE_ATTACH FOREIGN KEY (FK_MESSAGE_ID) REFERENCES NC_MESSAGESTRUCT; ALTER TABLE NC_TASKSCHEDULE ADD CONSTRAINT F2N_TASKSCHEDULE FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK; ALTER TABLE R_AGENT_ESCALATIONAGENT ADD CONSTRAINT F2R_AGENT_ESCALATI FOREIGN KEY (FK_PARENT_AGENT_ID) REFERENCES NC_TASK; ALTER TABLE R_DS_MACHINE_DSPOOL ADD CONSTRAINT F3R_DS_MACHINE_DSP FOREIGN KEY (FK_MACHINE_ID) REFERENCES NC_MACHINE_CONFIG; ALTER TABLE R_DS_MACHINE_DSPOOL ADD CONSTRAINT F2R_DS_MACHINE_DSP FOREIGN KEY (FK_DSPOOL_ID) REFERENCES NC_DSPOOL; ALTER TABLE R_MESSAGESTRUCT_TASK ADD CONSTRAINT F2R_MESSAGESTRUCT_ FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK; ALTER TABLE R_MESSAGESTRUCT_TASK ADD CONSTRAINT F1R_MESSAGESTRUCT_ FOREIGN KEY (FK_MESSAGESTRUCT_ID) REFERENCES NC_MESSAGESTRUCT; ALTER TABLE R_MSGSTRUCT_AVOIDTOPICS ADD CONSTRAINT F1R_MSGSTRUCT_AVOI FOREIGN KEY (FK_MS_ID) REFERENCES NC_MESSAGESTRUCT; ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF ADD CONSTRAINT F3R_MSGSTRUCT_RECI FOREIGN KEY (FK_RECIPIENT_ID) REFERENCES NC_RECIPIENT; ALTER TABLE R_MSGSTRUCT_RECIPIENT_DELIVINF ADD CONSTRAINT F1R_MSGSTRUCT_RECI FOREIGN KEY (FK_MESSAGESTRUCT_ID) REFERENCES NC_MESSAGESTRUCT; ALTER TABLE R_RECIPIENT_ACCMANUSERID ADD CONSTRAINT F1R_RECIPIENT_ACCM FOREIGN KEY (F_RECIPIENTID) REFERENCES NC_RECIPIENT; ALTER TABLE R_RULE_PARAMETER ADD CONSTRAINT F1R_RULE_PARAMETER FOREIGN KEY (FK_TASK_ID) REFERENCES NC_TASK; ALTER TABLE R_TASKSCHEDULE_SCHEDULE ADD CONSTRAINT F2R_TASKSCHEDULE_S FOREIGN KEY (FK_SCHEDULE_ID) REFERENCES NC_SCHEDULE; ALTER TABLE R_TASKSCHEDULE_SCHEDULE ADD CONSTRAINT F1R_TASKSCHEDULE_S FOREIGN KEY (FK_TASKSCHEDULE_ID) REFERENCES NC_TASKSCHEDULE; ------------------------------------------------------------------------------- --$250-- backup drop begin ------------------------------------------------------------------------------- DROP TABLE BC_AGENT_MESSAGE; DROP TABLE BC_AGGREGATE_RULE_TREE; DROP TABLE BC_BIE; DROP TABLE BC_BIECALC; DROP TABLE BC_BIETOPIC; DROP TABLE BC_COGNOS_DS; DROP TABLE BC_CONFIG_DS; DROP TABLE BC_CTMAP; DROP TABLE BC_DATAMAPPING; DROP TABLE BC_DB_VERSION; DROP TABLE BC_DELIVERYADDRESS; DROP TABLE BC_DMTOPIC; DROP TABLE BC_DMT_LINK; DROP TABLE BC_DSITEM_L1; DROP TABLE BC_DSITEM_L2; DROP TABLE BC_DSPOOL; DROP TABLE BC_DYNAMIC_RECIPIENT_STRUCT; DROP TABLE BC_DYN_DELIVERY_INFO_STRUCT; DROP TABLE BC_EXECUTABLE; DROP TABLE BC_MACHINE_CONFIG; DROP TABLE BC_MESSAGELINE; DROP TABLE BC_MESSAGELINE_ELEMENT; DROP TABLE BC_MESSAGESTRUCT; DROP TABLE BC_MESSAGE_ATTACHMENT; DROP TABLE BC_MSGDUPLICATION_STORE; DROP TABLE BC_MSGDUPLICATION_TEMP_STORE; DROP TABLE BC_OBJECTCATALOGUE; DROP TABLE BC_OBJECTID; DROP TABLE BC_OBJECT_PERMISSIONS; DROP TABLE BC_PERMISSION; DROP TABLE BC_RECIPIENT; DROP TABLE BC_RECIPIENTSCHEDULE; DROP TABLE BC_RULE; DROP TABLE BC_SCHEDULE; DROP TABLE BC_TASK; DROP TABLE BC_TASKSCHEDULE; DROP TABLE BC_TASK_LOG; DROP TABLE BC_USER_SESSION; DROP TABLE B_AGENT_ESCALATIONAGENT; DROP TABLE B_BIECALC_RHSTOPIC; DROP TABLE B_DS_MACHINE_DSPOOL; DROP TABLE B_MESSAGESTRUCT_TASK; DROP TABLE B_MSGSTRUCT_AVOIDTOPICS; DROP TABLE B_MSGSTRUCT_RECIPIENT_DELIVINF; DROP TABLE B_NEWSITEMS_NCOBJECTS; DROP TABLE B_RECIPIENT_ACCMANUSERID; DROP TABLE B_RULE_PARAMETER; DROP TABLE B_TASKSCHEDULE_SCHEDULE; ------------------------------------------------------------------------------- -- END NC2_UPDATE_DB2.SQL * DO NOT EDIT * -------------------------------------------------------------------------------