DB2 迁移script
--=======================================-- DB2 migrations version:GAL 5.0.0--=======================================--**********************************************clear procedure*********************************--clear procedures that have existcreate procedure clear_proc(IN v_procedureName VARCHAR(100), OUT v_excuteInfo VARCHAR(100))begin--execute partdeclare 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 notif(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 tablecreate procedure insert_auditmapper_data(IN listenerName VARCHAR(255), OUT v_mapperId INTEGER, OUT v_insertRows INTEGER)begin--execute partinsert 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) thenselect max(MAPPER_ID) into v_mapperId from GAL_AUDIT_MESSAGE_MAPPER;end if;end@--insert data into the relation of mapper and listener tablecreate procedure insert_xrefmapperlistener_data(IN mapperId INTEGER, IN listenerId INTEGER, OUT v_insertRows INTEGER)begin--execute partinsert 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 tablecreate procedure update_publickeyfield_data(IN listenerId INTEGER, IN mapperId INTEGER, IN listenerName VARCHAR(255), OUT v_uptRows INTEGER, OUT v_insertRows INTEGER)begindeclare v_pkf_id INTEGER;--execute part--insert mapper idupdate GAL_PUBLIC_KEY_FIELD set mapper_id=mapperId where listener_id=listenerId;GET DIAGNOSTICS v_uptRows = ROW_COUNT;--insert Mapper SelectorSELECT 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 TABLEcreate 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 vardeclare 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 partset output_migration_start='migration data begin...';--get the listener rows amountselect 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);FORpkf_loop ASselect distinct listener_id, listener_name from GAL_LISTENERDOset affectRows=0;--insert data into mapper tablecall 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 datacall migrate_data(var1,var2,var3,var4,var5,var6,var7,var8);GET DIAGNOSTICS v_diagnostics = DB2_RETURN_STATUS;if(v_diagnostics=0) then--=============================-- UpdateDATABASE VERSION--=============================UPDATE GAL_SETTINGS SET SETTING_VALUE = '5.0.0' WHERE SETTING_NAME = 'DATABASE VERSION';--drop listener_id fieldexecute immediate 'alter table GAL_PUBLIC_KEY_FIELD drop column listener_id';--reorganize tablecall sysproc.admin_cmd ( 'reorg table GAL_LISTENER' );--drop procedurescall 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 tablecall 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.
页:
[1]