SQL - Export source files members to IFS and preserving member text

copy_source_to_ifs.sql

-- Copy members from a source physical file to IFS directory and preserving the member text as part of the filename.
--
-- Notes: 
-- 1) I am using library QUSRSYS. I suggest you put it into your own tool library
-- 2) The output direcory has to exists. 
-- 3) Files contents are being replaces if they already exists. 
-- 4) IFS files CCSID is set to 1208 that is UTF-8
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2023
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace procedure qusrsys.copy_source_to_ifs (
    in library varchar(10),
    in source_file varchar(10),
    in output_ifs_path varchar(256)
)
    specific CPYSRC2IFS 
	set option dbgview = *source , output=*print , commit=*none, datfmt=*iso --<== options so we can debug it later
begin
    declare cmd varchar(4096);
    for select 
            cast ( 
                trim( table_partition) || '-' ||
                regexp_replace( 
                    trim(regexp_replace( trim(ifnull(partition_text,'')) , '[^a-zA-Z0-9]'   ,' ' ,  1 , 0,'i' ))
                    ,'( )+'   ,'-' ,  1 , 0,'i' 
                ) as varchar(256)
            ) as newf,
            table_partition as mbr,
            case when source_type is not null 
                then  '.' || trim(lower(source_type))
                else  '' 
            end extension,  
            table_partition
        from  qsys2.syspartitionstat 
        where table_schema = trim(library) 
        and table_name = trim(source_file) 
        --and  table_partition  like '%XXX%' -- here you can add members name filter if you like 
        order by 1
          
    do  
        set cmd = 'CPYTOSTMF FROMMBR(''/qsys.lib/' || trim(library) || '.lib/' || trim(source_file) || '.file/' || trim(mbr) || '.mbr'') TOSTMF(''' || trim(output_ifs_path) || '/' || trim(newf) || extension || ''') STMFOPT(*REPLACE) STMFCCSID(1208)';                                      
        call qcmdexc (cmd);
    end for;
end;

-- usage example. Output directory has to exsists

cl:mkdir '/prj';
cl:mkdir '/prj/mylib';

call copy_source_to_ifs ( 
    library => 'MYLIB',
    source_file => 'QRPGLESRC',
    output_ifs_path  => '/prj/mylib'
);