SQL Export and Import and Clone schema definition

export_import_schema_definition.sql

-- Export_schema_script and import_schema_script uses IBM generate_sql_objects  
-- for building a export and import script in "perfect order" with IBM's SQL statements
-- in "perfect order"
--
-- After exporting a schema definition you can then use the output script 
-- to build an empty new version of the schema OR you can migrate 
-- and upgrade an existing older version of the same schema with 
-- a new layout. Perfect for CI/CD and development/production environment
-- 
-- The clone_empty_schema is a combination of first running 
-- the Export_schema_script and then run the import_schema_script in one go
-- 
-- Simply paste this gist into ACS SQL and select "run all" 
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------


-- The export process:
create or replace procedure qusrsys.export_schema_script (
    export_schema_name varchar(256),
    output_file    char(10),
    output_library char(10)
)

begin 

    declare continue handler for sqlstate '38501' begin
    end;
    
    -- Bulid the output file for the final script
    call qcmdexc('CRTSRCPF FILE(' || trim(output_library) || '/' || trim(output_file) || ') RCDLEN(92) MBR(*FILE)'); 
    call qcmdexc('CLRPFM  FILE(' || trim(output_library) || '/' || trim(output_file) || ') mbr('|| trim(output_file) || ')'); 
    call qcmdexc('CHGPFM FILE(' || trim(output_library) || '/' || trim(output_file) || ') mbr('|| trim(output_file) || ') srctype(SQL) text(''Export of schema'')'); 
    call qcmdexc('OVRDBF FILE(xxmysrc) TOFILE(' || trim(output_library) || '/' || trim(output_file) || ') mbr('|| trim(output_file) || ') OVRSCOPE(*JOB)'); 

    -- This will contain the list of objects to export:    
    create or replace table qtemp.inorder (
        object_schema varchar(258), 
        object_name varchar(258), 
        sql_object_type char(10)
    )   on replace delete rows;
    
    -- The first statement will be the schema it self
    insert into QTEMP.INORDER  values(
        'QSYS' , export_schema_name , 'SCHEMA' 
    );

    -- First time we replace the rows in the source hench REPLACE_OPTION => '1'
    call qsys2.generate_sql_objects('INORDER','QTEMP', 
        database_source_file_name => output_file,
        database_source_file_library_name => output_library,
        database_source_file_member => output_file,
        replace_option => '1',
        --statement_formatting_option =>
        --date_format =>
        create_or_replace_option => '1',
        qualified_name_option=>'1'
    );
    
    -- Since we run "unqualified", we absolutley need a SET SCHEMA in our final script
    insert into xxmysrc ( srcdta) values ('SET SCHEMA ' ||  export_schema_name || ';');
    
    -- Now start over again with the content: tables, indexs etc. 
    truncate QTEMP.INORDER;
    
    insert into  QTEMP.INORDER  
    with a as (
        Select table_schema , table_name , 
            case 
                when table_type in ('P' , 'T') then 'TABLE'
                when table_type in ('L'      ) then 'INDEX'
                when table_type in ('V'      ) then 'VIEW'
                else null 
            end sql_object_type
            from qsys2.systables
            where table_schema = export_schema_name 
            and system_table = 'N'
            and file_type = 'D'
            and not ( table_text like 'Old name %' and table_text like '%owned by%')
        union
            Select specific_schema , routine_name  , 'PROCEDURE'
            from qsys2.sysprocs 
            where specific_schema = export_schema_name 
        union
            Select specific_schema , routine_name  , 'FUNCTION'
            from qsys2.sysfuncs 
            where specific_schema = export_schema_name 
        union 
            Select table_schema , index_name , 'INDEX'
            from qsys2.sysindexes
            where table_schema = export_schema_name 
            
    ) select * from a where SQL_OBJECT_TYPE is not null;
    
    -- Second time we append  the rows in the source hench REPLACE_OPTION => '0'
    call qsys2.generate_sql_objects('INORDER','QTEMP', 
        database_source_file_name => output_file,
        database_source_file_library_name => output_library,
        database_source_file_member => output_file,
        replace_option => '0',
        --statement_formatting_option =>
        --date_format =>
        create_or_replace_option => '1',
        qualified_name_option=>'1'
    );
    -- TODO: Other objects: constraints, triggersetc.  
    
    -- Finally we replace all row with the schema name to a marker we can replace on the target system
    update xxmysrc set srcdta = replace(srcdta , 'SCHEMA ' || trim(export_schema_name) , 'SCHEMA ${SCHEMA_NAME}'); 
end;

-- Usecase
call qusrsys.export_schema_script (
    export_schema_name => 'MYSCHEMA',
    output_file     => 'MYSCHEMA',
    output_library  => 'QGPL'
);

-----------------------------------------------------------------------------
-- Next up:
-- The Import process
-----------------------------------------------------------------------------

-- just to let import_schema_script compile 
cl: CRTSRCPF FILE(qtemp/xximpsrc)  RCDLEN(92) MBR(*FILE); 

-- The import  process:
create or replace procedure qusrsys.import_schema_script (
    target_schema_name varchar(256),
    input_file    char(10),
    input_library char(10),
    index_sort_sequence char(10), --  '*LANGIDSHR'
    index_sort_language char(3)   --  'DAN'
)
begin

    declare setSchemaRow int; 
    declare target_release char(10);
    
    declare continue handler for sqlstate '38501' begin
    end;

    -- Get the target release ( TOTDO a better solution)
    select ptf_product_release_level
    into   target_release
    from   qsys2.PTF_INFO
    fetch first 1 row only;

    if input_library = 'QTEMP' then 
        signal sqlstate 'NL999' set message_text = 'QTEMP not allowed';
    end if;
    
    -- Point to the input script file 
    call qcmdexc('DLTF QTEMP/' || trim(input_file));
    call qcmdexc('CRTDUPOBJ OBJ(' || trim(input_file) || ') FROMLIB(' || trim(input_library) || ') OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES) CST(*NO) TRG(*NO) ACCCTL(*NONE)');
    call qcmdexc('OVRDBF FILE(xximpsrc) TOFILE(qtemp/' || trim(input_file) || ') OVRSCOPE(*JOB)');

    -- First we replace the schema name with a marker we can replace on the target system
    update xximpsrc
    set srcdta = replace(srcdta , 'SCHEMA ${SCHEMA_NAME}', 'SCHEMA ' || trim(target_schema_name));
    
    -- Get the row where we set the schema;
    select rrn(a)
    into setSchemaRow
    from xximpsrc a
    where srcdta like 'SET SCHEMA ' || trim(target_schema_name) || '%';

    -- Remove the create schema part if it already exists
    if (exists (select 1 from sysschemas where schema_name = target_schema_name)) then 
        update xximpsrc a
        set srcdta = '' 
        where rrn(a) < setSchemaRow; 

        -- Drop indexes which might exsists in traget schema 
        for c1 as 
            select
                case  
                    when srcdta like 'CREATE INDEX%' then  substr(srcdta , 14)
                    when srcdta like 'CREATE UNIQUE INDEX%' then  substr(srcdta , 20) 
                end as ixname , rrn(a) as rrn 
            from xximpsrc a
            where srcdta LIKE 'CREATE INDEX%'
            or    srcdta like 'CREATE UNIQUE INDEX%' do 
                update xximpsrc b
                set srcdta = 'drop index ' || trim(translate (ixname , '   ' , x'400d25' ) ) || ';'
                where rrn(b) = rrn - 1;
        end for;
    end if;
    
    -- ensure we have the schema on the library list for procedure and function references
    update xximpsrc a
    set srcdta = 'call qcmdexc(''addlible ' || trim(target_schema_name) || ' ''); '   
    where rrn(a) = setSchemaRow +1; 

    -- Set the target release to current system at import time
    update xximpsrc 
    set    srcdta  = ' TGTRLS = ' || target_release
    where  srcdta like '_TGTRLS_=_%';
    
    call qcmdexc('RUNSQLSTM SRCFILE(QTEMP/' || trim(input_file) || ') SRCMBR(' || trim(input_file) 
        || ') COMMIT(*NONE) MARGINS(80) ERRLVL(40) DATFMT(*ISO) DATSEP(*JOB) TIMFMT(*ISO) OPTION(*ERRLIST) ' 
        || ' SRTSEQ(' || index_sort_sequence || ') LANGID(' || index_sort_language || ')');
    call qcmdexc('dltf qtemp/' || trim(input_file));
end;

-- Usecase
call qusrsys.import_schema_script (
    target_schema_name => 'MYSCHEMA',
    input_file     => 'MYSCHEMA',
    input_library  => 'QGPL',
    index_sort_sequence => '*LANGIDSHR',  
    index_sort_language => 'DAN'
);

-----------------------------------------------------------------------------
-- Finally :
-- The Clone which is a export import combo
-----------------------------------------------------------------------------

create or replace procedure qusrsys.clone_empty_schema (
    source_schema_name varchar(256),
    target_schema_name varchar(256),
    index_sort_sequence char(10), --  '*LANGIDSHR'
    index_sort_language char(3)   --  'DAN'
)

begin 
    call qusrsys.export_schema_script (
        export_schema_name => source_schema_name,
        output_file     => 'CLONE',
        output_library  => 'QGPL'
    );
    
    call qusrsys.import_schema_script  (
        target_schema_name => target_schema_name,
        input_file     => 'CLONE',
        input_library  => 'QGPL',
        index_sort_sequence => index_sort_sequence,  
        index_sort_language => index_sort_language
    );
end;


-- Usecase
call qusrsys.clone_empty_schema (
    source_schema_name => 'MYSCHEMA',
    target_schema_name => 'MYTEST',
    index_sort_sequence => '*LANGIDSHR',  
    index_sort_language => 'DAN'
);