DB2 迁移script

makala

--=======================================
-- DB2 migrations version:  GAL 5.0.0
--=======================================
--**********************************************clear procedure*********************************
--clear procedures that have exist
create procedure clear_proc(IN v_procedureName VARCHAR(100), OUT v_excuteInfo VARCHAR(100))
begin
--execute part
declare proc_count INTEGER default 0;
declare statement VARCHAR(100);
select count(procname) into proc_count from syscat.procedures where procname=UPPER(v_procedureName);
--judge whether exist or not
if(proc_count>0) then
set statement='drop procedure '||UPPER(v_procedureName);
execute immediate statement;
set v_excuteInfo='drop procedure '||UPPER(v_procedureName)||' successfully';
end if;
end@
--**********************************************migration procedure begin*********************************
--insert data into mapper table
create procedure insert_auditmapper_data(IN listenerName VARCHAR(255), OUT v_mapperId INTEGER, OUT v_insertRows INTEGER)
begin
--execute part
insert into GAL_AUDIT_MESSAGE_MAPPER (MAPPER_NAME, MAPPER_DESCRIPTION, CREATED_BY, CREATION_DATE) values(substr(listenerName||'_Mapper',1,255), substr('create a mapper for Listener: '||listenerName,1,255), 0, current timestamp);
GET DIAGNOSTICS v_insertRows = ROW_COUNT;
if(v_insertRows>0) then
select max(MAPPER_ID) into v_mapperId from GAL_AUDIT_MESSAGE_MAPPER;
end if;
end@
--insert data into the relation of mapper and listener table
create procedure insert_xrefmapperlistener_data(IN mapperId INTEGER, IN listenerId INTEGER, OUT v_insertRows INTEGER)
begin
--execute part
insert into GAL_XREF_MAPPER_LISTENER (MAPPER_ID, LISTENER_ID, CREATED_BY, CREATION_DATE) values(mapperId, listenerId, 0, current timestamp);
GET DIAGNOSTICS v_insertRows = ROW_COUNT;
end@
--insert mapper id, Mapper Selector for GAL_PUBLIC_KEY_FIELD table
create procedure update_publickeyfield_data(IN listenerId INTEGER, IN mapperId INTEGER, IN listenerName VARCHAR(255), OUT v_uptRows INTEGER, OUT v_insertRows INTEGER)
begin
declare v_pkf_id INTEGER;
--execute part
--insert mapper id
update GAL_PUBLIC_KEY_FIELD set mapper_id=mapperId where listener_id=listenerId;
GET DIAGNOSTICS v_uptRows = ROW_COUNT;
--insert Mapper Selector
SELECT COALESCE(MAX(PUBLIC_KEY_FIELD_ID),0)+1 into v_pkf_id from GAL_PUBLIC_KEY_FIELD;
insert into GAL_PUBLIC_KEY_FIELD (PUBLIC_KEY_FIELD_ID, MAPPER_ID, FIELD_NAME_DESCRIPTION, CONSTANT_VALUE, IS_XML_FIELD, FIELD_TYPE, CREATED_BY, CREATION_DATE, DEFAULT_VALUE)
values(v_pkf_id, mapperId, substr('Mapper Selector for '||listenerName,1,255), substr('Mapper Selector for '||listenerName,1,255), 'N', 'Mapper Selector', 0, current timestamp, 'N');
GET DIAGNOSTICS v_insertRows = ROW_COUNT;
end@
--insert data base on GAL_LISTENER TABLE
create procedure migrate_data(OUT output_migration_start VARCHAR(100), OUT output_listeners_amount VARCHAR(100), OUT output_mapper_affectRows VARCHAR(100), OUT output_xref_affectRows VARCHAR(100), OUT output_u_pkf_amount VARCHAR(100), OUT output_i_pkf_amount VARCHAR(100), OUT output_affectRows VARCHAR(100), OUT output_migration_end VARCHAR(100))
begin
--define v_mapperId var
declare v_mapperId INTEGER default 0;
declare u_pkf_affectRows INTEGER default 0;
declare v_listeners_amount INTEGER default 0;
declare mapper_affectRows INTEGER default 0;
declare xref_affectRows INTEGER default 0;
declare u_pkf_amount INTEGER default 0;
declare i_pkf_amount INTEGER default 0;
declare affectRows INTEGER default 0;
--execute part
set output_migration_start='migration data begin...';
--get the listener rows amount
select count(distinct listener_id) into v_listeners_amount from GAL_LISTENER;
set output_listeners_amount='the GAL_LISTENER table rows amount: '||CHAR(v_listeners_amount);
FOR  pkf_loop AS
select distinct listener_id, listener_name from GAL_LISTENER
DO
set affectRows=0;
--insert data into mapper table
call insert_auditmapper_data(pkf_loop.listener_name, v_mapperId, affectRows);
if(affectRows>0) then
--sum the affect rows
set mapper_affectRows=mapper_affectRows+affectRows;
set affectRows=0;
--insert data into the relation of mapper and listener table
call insert_xrefmapperlistener_data(v_mapperId, pkf_loop.listener_id, affectRows);
--sum the affect rows
set xref_affectRows=xref_affectRows+affectRows;
set affectRows=0;
set u_pkf_affectRows=0;
--insert mapper id for GAL_PUBLIC_KEY_FIELD table
call update_publickeyfield_data(pkf_loop.listener_id, v_mapperId, pkf_loop.listener_name, u_pkf_affectRows, affectRows);
--sum the affect rows
set u_pkf_amount=u_pkf_amount+u_pkf_affectRows;
set i_pkf_amount=i_pkf_amount+affectRows;
end if;
set affectRows=mapper_affectRows+xref_affectRows+u_pkf_amount+i_pkf_amount;
set output_mapper_affectRows='Below are the execute result: insert GAL_AUDIT_MESSAGE_MAPPER table rows amount: '||CHAR(mapper_affectRows);
set output_xref_affectRows='insert GAL_XREF_MAPPER_LISTENER table rows amount: '||CHAR(xref_affectRows);
set output_u_pkf_amount='update GAL_PUBLIC_KEY_FIELD table rows amount: '||CHAR(u_pkf_amount);
set output_i_pkf_amount='insert GAL_PUBLIC_KEY_FIELD table rows amount: '||CHAR(i_pkf_amount);
set output_affectRows='affect rows amount: '||CHAR(affectRows);
set output_migration_end='migration data end.';
END FOR;
end@

怎么样调用:
--=======================================
-- DB2 migrations version:  GAL 5.0.0
--=======================================
create procedure callMigrateData(OUT var1 VARCHAR(100), OUT var2 VARCHAR(100), OUT var3 VARCHAR(100), OUT var4 VARCHAR(100), OUT var5 VARCHAR(100), OUT var6 VARCHAR(100), OUT var7 VARCHAR(100), OUT var8 VARCHAR(100), OUT var9 VARCHAR(100), OUT var10 VARCHAR(100), OUT var11 VARCHAR(100), OUT var12 VARCHAR(100))
begin
declare v_diagnostics INTEGER default -1;
--migrate data
call migrate_data(var1,var2,var3,var4,var5,var6,var7,var8);
GET DIAGNOSTICS v_diagnostics = DB2_RETURN_STATUS;
if(v_diagnostics=0) then
--=============================
-- Update  DATABASE VERSION
--=============================
UPDATE GAL_SETTINGS SET SETTING_VALUE = '5.0.0' WHERE SETTING_NAME = 'DATABASE VERSION';
--drop listener_id field
execute immediate 'alter table GAL_PUBLIC_KEY_FIELD drop column listener_id';
--reorganize table
call sysproc.admin_cmd ( 'reorg table GAL_LISTENER' );
--drop procedures
call clear_proc('insert_auditmapper_data', var9);
call clear_proc('insert_xrefmapperlistener_data', var10);
call clear_proc('update_publickeyfield_data', var11);
call clear_proc('migrate_data', var12);
execute immediate 'drop procedure clear_proc';
end if;
end@
call callMigrateData(?,?,?,?,?,?,?,?,?,?,?,?)@
drop procedure callMigrateData@
--reorganize table
call sysproc.admin_cmd ( 'reorg table GAL_PUBLIC_KEY_FIELD' )@

怎么样执行:
(1)Run db2 "Command Window"
(2)Execute "db2 -tvf C:\424_TO_425\V9\424_to_500_first_execute.sql" command.