SQL Migrate database

migrate_database.sql

-- migrate_database: 
-- This copies and replaces all tables and files in the target schema ( library ) 
-- with data from  tables and files in the source schema ( library) 
-- Also it wil cater for identity columns in the target to be set to the next available value.
-- Be carefull to use this since it will not keep a bacup of your target data
-- So be sure that it works for you. Take a backup of you traget before you begin.
-- Both clasic PF and SQL tables are supported. 
-- Note: I am using QUSRSYS here , but i suggest that you place it in your own system schema 
-- (C) Niels Liisberg 2020
------------------------------------------------------------------------------
create or replace procedure qusrsys.migrate_database (
    in source_lib char(10),
    in target_lib char(10)
)
begin 
    
    declare continue handler for sqlstate value '38501' begin
    end;

    for c1 cursor for 
        with source  as ( 
            Select table_schema slib, system_table_name sobj,  table_name sname 
            from qsys2.systables
            where table_schema = source_lib
            and table_type in ( 'P' , 'T')  
            and FILE_type = 'D'

        ) , target  as ( 
            Select table_schema tlib, system_table_name tobj,  table_name tname 
            from qsys2.systables
            where table_schema = target_lib
            and table_type in ( 'P' , 'T')  
            and FILE_type = 'D'
        ) 
        Select *  from source, target 
        where SNAME = TNAME 
        
    do
        --call joblog ('CPYF FROMFILE('|| trim(SLIB)  || '/' ||  trim(SOBJ) || ') TOFILE(' || trim(TLIB)  || '/' ||  trim(TOBJ) || ') MBROPT(*REPLACE) FMTOPT(*MAP *DROP)');     
        call qcmdexc('CPYF FROMFILE('|| trim(SLIB)  || '/' ||  trim(SOBJ) || ') TOFILE(' || trim(TLIB)  || '/' ||  trim(TOBJ) || ') MBROPT(*REPLACE) FMTOPT(*MAP *DROP)'); 
        if  tname is not null then
            call qusrsys.reset_identity_columns ( 
                table_schema  => TLIB,
                table_name   => TNAME
            ); 
        end if;
    end for;
end;
   


call  qusrsys.migrate_database (
    source_lib => 'ICEBOX2', 
    target_lib => 'ICEBOX'
);