SQL export all source physical files (like QRPGLESRC) to a passive git repo

export_source_to_git.sql

-- This is a tool to export all source physical filemembers on IBM i
-- to a git repo to keep track of changes over time
-- This requires the BASH stored procedure found on my gist
-------------------------------------------------------------------

-- Change the names to your names/repos/mail addr and Run this once:

call qusrsys.bash ('
    mkdir /passivegit;
    cd /passivegit;
    git init;
    git remote add origin [email protected]:Liisberg/passive-git-myibmi.git;
    git config --global user.email "[email protected]";
    git config --global user.name "Niels Liisberg"
');    
 
-- Change the ccsid and which libraries and files to your needs in the following  
-- and build this stored procedure in you own library ( here I am just using QGPL)

create or replace procedure qgpl.export_source_to_git 
  set option dbgview=*source, output=*print, commit=*none, datfmt=*iso

begin
    declare cmd varchar(4096);
    declare err int;
    declare sqlcode int;
    declare lib char(10);
    declare file char(10);
    declare mbr char(10);
    declare ext char(10);
    declare ts  char(19) ;
    
    declare c1 cursor for   
        Select
           TABLE_NAME ,
           TABLE_SCHEMA,
           TABLE_PARTITION,
           source_type
         from systables join TABLE (
            QSYS2.PARTITION_STATISTICS(
                cast (TABLE_SCHEMA as char(10)) , cast(TABLE_NAME as char(10)) 
            )
         ) a on 1=1 
         where file_type = 'S' 
         and   system_table_schema not like 'Q%'; 
         -- and   system_table_schema = 'MYONLYLIB';
         -- and   TABLE_NAME = 'QMYONLYSRCE';
    declare continue handler for sqlstate '38501' set  err = 1 ;

    set ts = to_char(now(),  'YYYY-MM-DD-HH24-MM-SS');
 
    open c1;
    fetch c1 into file, lib , mbr , ext ;
    while sqlcode = 0 do
        
        set err = 0;
        if mbr is not null then   
            call qusrsys.bash ('mkdir -p /passivegit/' || rtrim(lower(lib)) || '/' || rtrim(lower(file)) ||  ' 2>/dev/null');
            if  err = 0 then 
                set cmd  =  'CPYTOSTMF FROMMBR(''/QSYS.LIB/' 
                        || rtrim(lib) || '.LIB/' 
                        || rtrim(file)   || '.FILE/' 
                        || rtrim(mbr) || '.MBR'')'
                        || ' TOSTMF(''' 
                        || lower('/passivegit/') 
                        || lower(rtrim(lib)) || '/' 
                        || lower(rtrim(file)) || '/' 
                        || lower(rtrim(mbr)) || '.'
                        || lower(rtrim(ifnull(ext,'UNKNOWN'))) || ''') STMFOPT(*REPLACE)  DBFCCSID(277) STMFCCSID(1208)';                                       
                call qcmdexc (cmd) ; 
            end if;
        end if;   
        fetch c1 into file, lib , mbr , ext ;
    end while;   
    call qusrsys.bash  ('cd /passivegit;git add .;git commit -m "' || ts || '";git push');   

end;

-- This is the call 
call qgpl.export_source_to_git;

/* And you can now add this to the job-scheduler runnineach night:
RUNSQL SQL('call qgpl.export_source_to_git') 
       COMMIT(*NONE)                         
                           
*/