1. 扩展表空间
扩展SMARTSYS_HISTORY和SMARTSYS_HISTORY表空间,使当前使用率降至40%或以下。
2. 将现有历史事项表重命名
ALTER TABLE scada_event rename to scada_event_bak;
ALTER INDEX SCADA_EVENT_INDEX rename to SCADA_EVENT_INDEX_BAK;
ALTER INDEX SCADA_EVENT_INDEX2 rename to SCADA_EVENT_INDEX2_BAK;
3. 创建新的带自增字段的历史事项表
CREATE TABLE "SMARTSYS"."scada_event"
(
"ID" BIGINT IDENTITY(1,1) CLUSTER PRIMARY KEY,
"KEY_ID_TAG" VARCHAR(100) DEFAULT 'dummy' NOT NULL,
"TAG_ID" BIGINT NOT NULL,
"OCCUR_TIME" TIMESTAMP(0),
"MILLI_SECOND" INTEGER NOT NULL,
"SOE_TIME" TIMESTAMP(0),
"SOE_MSECOND" INTEGER,
"DOMAIN_ID" INTEGER,
"STATION_ID" BIGINT NOT NULL,
"REGION_ID" INTEGER,
"INFER_NAME" VARCHAR(64),
"ALARM_PRIORITY" INTEGER,
"STATUS" INTEGER,
"CONTENT" VARCHAR(256),
"CONFIRM_TIME" TIMESTAMP(0),
"CONFIRM_NODE_NAME" VARCHAR(64),
"ALARM_TYPE" INTEGER NOT NULL,
"APP_ID" INTEGER,
"CONTAINER1_ID" BIGINT,
"CONTAINER2_ID" BIGINT,
"DEV_ID" BIGINT,
"CONFIRM_USER_NAME" VARCHAR(64),
"CONTEXT" INTEGER,
"SYSID" INTEGER DEFAULT 1 NOT NULL,
"AREA_RESP" INTEGER DEFAULT (-1) NOT NULL,
"OBJ_ID" BIGINT,
"REASON_CODE" INTEGER,
"DICTIONARY" CHARACTER VARYING(512),
"VALUE" DOUBLE) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);
CREATE INDEX "SCADA_EVENT_INDEX" ON "SMARTSYS"."scada_event"("DEV_ID" ASC,"OCCUR_TIME" ASC,"OBJ_ID" ASC,"STATION_ID" ASC,"CONTAINER1_ID" ASC,"ALARM_TYPE" ASC,"STATUS" ASC) STORAGE(ON "SMART_HISTORY_INDEX", CLUSTERBTR) ;
CREATE INDEX "SCADA_EVENT_INDEX2" ON "SMARTSYS"."scada_event"("OCCUR_TIME" ASC,"ALARM_TYPE" ASC,"STATUS" ASC,"STATION_ID" ASC,"CONTAINER1_ID" ASC,"CONTAINER2_ID" ASC) STORAGE(ON "SMART_HISTORY_INDEX", CLUSTERBTR) ;
经过对金仓和达梦数据库的测试,在执行步骤1和2的时候不需要停Smartsys系统,切换过程持续几秒钟,事项可能会存在及少量的丢失,但不会影响切换结果。
4. 根据需要将部分旧的事项数据从SCADA_EVENT_BAK中,分批导入ES。
--1. 分批次迁移历史数据,分批的目的在于将每次的数据提交量控制在5000 0000条以下,以防止临时缓冲区不足。
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-08-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-07-01' and occur_time < '2019-08-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-06-01' and occur_time < '2019-07-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-05-01' and occur_time < '2019-06-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-04-01' and occur_time < '2019-05-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-03-01' and occur_time < '2019-04-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-02-01' and occur_time < '2019-03-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time >= '2019-01-01' and occur_time < '2019-02-01';
commit;
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE")
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak
where occur_time < '2019-01-01';
commit;
这一步骤需要花费较长时间,根据实测数据显示,插入效率大约为:4000 0000条/小时。完成后需要检查执行日志(打印信息),确认每条语句都已经执行成功,并提交完成。