DB2 9.7迁移到DM8。表、视图、序列可以使用DTS工具进行迁移,但是存储过程、触发器等需要手动迁移。以下为触发器修改的案例
CREATE TRIGGER “N_I_1252219353”
after insert ON “XMGLUSER”.“HAL_COUNTY_NEWS”
REFERENCING NEW as NEWO
FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE
v_MsgTypeId integer;
DECLARE v_MsgServiceId integer;
DECLARE v_QueuedAt timestamp;
set v_QueuedAt = current timestamp;
SET v_MsgTypeId = (SELECT MsgTypeId FROM BCG_MsgType_DXP where MsgTypeName = ‘XMGLUSER.HAL_COUNTY_NEWS_out_do’);
SET v_MsgServiceId =(SELECT MsgServiceId FROM BCG_MsgService_DXP where MsgServiceName
= ‘XMGLUSER_HAL_COUNTY_NEWS’);
INSERT INTO BCG_MSG_DXP ( MsgServiceId,
MsgTypeId, MsgQueuedAt, MsgQueuedAtSeqNo, MsgStatus,MsgDeliveredAt, MsgFailedText,OperationType,MsgParam1,
MsgParam2, MsgParam3, MsgParam4, MsgParam5,MsgParam6, MsgParam7, MsgParam8,
MsgParam9, MsgParam10)
VALUES (v_MsgServiceId, v_MsgTypeId,v_QueuedAt,
null, ‘NEW’,null,‘’,‘insert’,char(NEWO.“NEWS_ID”),‘’,‘’,‘’,‘’,‘’,‘’,‘’,‘’,‘’);
end;

修改为DM语法
CREATE TRIGGER “N_I_1252219353”
after insert ON “XMGLUSER”.“HAL_COUNTY_NEWS”
REFERENCING NEW as NEW
FOR EACH ROW
v_MsgTypeId integer;
v_MsgServiceId integer;
v_QueuedAt timestamp;
begin
set v_QueuedAt = current_timestamp;
SET v_MsgTypeId = (SELECT MsgTypeId FROM BCG_MsgType_DXP where MsgTypeName = ‘XMGLUSER.HAL_COUNTY_NEWS_out_do’);
SET v_MsgServiceId = (SELECT MsgServiceId FROM BCG_MsgService_DXP where MsgServiceName= ‘XMGLUSER_HAL_COUNTY_NEWS’);
INSERT INTO BCG_MSG_DXP ( MsgServiceId,MsgTypeId, MsgQueuedAt, MsgQueuedAtSeqNo, MsgStatus,MsgDeliveredAt, MsgFailedText,OperationType,MsgParam1,
MsgParam2, MsgParam3, MsgParam4, MsgParam5,MsgParam6, MsgParam7, MsgParam8,
MsgParam9, MsgParam10) VALUES (v_MsgServiceId, v_MsgTypeId,v_QueuedAt,
null, ‘NEW’,null,‘’,‘insert’,char(NEW.“NEWS_ID”),‘’,‘’,‘’,‘’,‘’,‘’,‘’,‘’,‘’);
end;

DM中支持SET赋值语法，但是变量定义于与引用新值和旧值与DB2存在差异