run ACS SQL script

-- This stored procedure is used to 
-- Run a SQL script compatible with ACS 'Run SQL script' feature 

-- The purpose is to create schemas, tables procedures, UDTF, 
-- views etc., Alter tables, run update and delete statements.

-- This procedure is perfect for a DevOps loops. However statements
-- that produces result set are not supported.

-- The special case of running CL is also supported.

-- It supports the concept each statement is terminated a by semicolon. 
-- The tricky part is that the semicolon can occur multiple times and places
-- in a SQL script, so a parsing is necessary. The approach it to tokenize the 
-- the script and do a very simple parsing based on blocks and nested 
-- control structures in the SPL-PL language.

-- Simply paste this gist into ACS SQL and run it to create the procedure.

-- 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 2021

-- 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.run_sql_script (
    sql_srcipt_file varchar(256)
) 
external action 
modifies sql data 
    set option output=*print, commit=*chg, dbgview = *source --list ,
begin

    declare c               char(1);
    declare sql_script      clob ; 
    declare sql_statement   clob ;
    declare break           int; 
    declare i               int; 
    declare found           int; 
    declare end_pos         int; 
    declare pos             int; 
    declare statement_start int; 
    declare statement_end   int; 
    declare end_of_string   int; 
    declare nesting_level   int;
    declare token_pos       int;
    declare token_end       int;
    declare block_level     int;
    declare cl_pos          int;
    declare token           varchar(256); 
    declare text            varchar(256); 
    
    declare exit handler for sqlexception begin
        declare err_message_text  varchar(256); 
        declare err_db2_token_string varchar(30000);
        declare err_sqlstate  char(5); 
        declare err_sqlcode int; 
        declare err_failure_text  varchar(30000); 
        declare err_failure_text_256  varchar(256); 

        get diagnostics condition 1 err_sqlcode = db2_returned_sqlcode,
            err_sqlstate = returned_sqlstate,
            err_db2_token_string = db2_token_string,
            err_message_text = message_text;
        set err_failure_text = '!!! Failed with sqlcode=' concat
            err_sqlcode concat ' sqlstate=' concat err_sqlstate concat
            ' message= ' concat err_message_text concat ' at: ' concat err_db2_token_string concat ' for command: ' concat sql_script;
            set err_failure_text_256 = err_failure_text;
            call systools.LPRINTF (err_failure_text_256);
            signal sqlstate err_sqlstate set message_text = err_failure_text_256;
    end;

    -- Load the script file 
    begin 
        declare continue handler for sqlstate '42704' begin end;
        set sql_script =  get_clob_from_file(sql_srcipt_file,1);  
        if sql_script is null then
            set text = 'Input script file "' concat sql_srcipt_file concat '" does not exists';
            signal sqlstate 'NL999' set message_text = text;
        end if; 
    end; 

    set nesting_level = 0;
    set statement_start = 1;
    set statement_end   = 1;
    set pos = 1; 
    set end_of_string  = length (sql_script);
    set break = 0;
    set block_level = 0;
    set sql_statement = '';
    
    repeat 

        -- Tokenize the SQL string of multiple statements to determin each single SQL statement
        set found = 0;
        repeat
            
            set c = substr(sql_script , pos , 1);
            
            case
             
                -- Get tokens: any letter or digits - this is not perfect and will create tokens with invalid names, but sufficient for this purpose 
                when regexp_instr(c , '[a-zA-Z0-9_]' , 1 , 1, 0, 'i') > 0 then 
                    set token_end = regexp_instr(sql_script, '[^a-zA-Z0-9_]' , pos, 1, 0, 'i'); 
                    if token_end = 0 then 
                        set token_end = end_of_string;
                    end if;
                    set found = 1;
                    set token = lower(substr(sql_script, pos , token_end - pos )); 
                    set pos = token_end;

                -- Skip white space
                when c <= ' ' then 
                    set pos = pos + 1;

                -- Line comment
                when substr(sql_script , pos , 2) = '--' then 
                    -- end_of_line = regexp_instr(sql_script, '*$' , pos , 1, 1, 'i');
                    -- if end_of_line = 0 then 
                    -- end of line can not be found with locate (x'0d0a' not locate x'0d25') so we have to loop 
                    repeat 
                        set pos = pos + 1;
                    until pos  >= end_of_string or substr(sql_script , pos , 1) < ' '
                    end repeat;
                                    
                -- Block comments    
                when substr(sql_script , pos , 2) = '/*' then 
                    set end_pos = locate ( '*/' , sql_script , pos + 2);
                    if end_pos = 0 then set end_pos = end_of_string; end if;
                    set pos = end_pos + 2;
                    
                -- String constants    
                when c = '''' then    
                    set end_pos = locate ( '''' , sql_script , pos +1);
                    if end_pos = 0 then 
                        set pos = end_of_string; 
                    else 
                        set pos = end_pos + 1;
                    end if;
                    
                -- End of statement    
                when c = ';' and nesting_level = 0 then
                    set statement_end = pos;
                    set found = 1;
                    set token = ';';
                    set pos = pos + 1;
                
                -- Unhandled tokens - just skip     
                else 
                    set pos = pos + 1;
                    
            end case;
        until found = 1 or pos > end_of_string 
 
        end repeat;

        -- Token found
        
        case 
            when token in ('begin') then 
                set block_level = block_level + 1; 
                
            when token in ('case', 'if', 'while' , 'repeat', 'for') and block_level > 0 then 
                set nesting_level = nesting_level + 1;
                                
            when token = 'end' then 
                if nesting_level > 0 then
                    set nesting_level = nesting_level - 1;
                elseif block_level > 0 then
                    set block_level = block_level - 1;
                end if;
                  
                -- skip until end of statement
                while pos < end_of_string and substr(sql_script , pos , 1) <> ';' do
                    set pos = pos + 1;
                end while;

            -- Script interrupt  
            when token = 'stop' and nesting_level = 0 then 
                set break = 1;   

            -- End of statement, detection complete: 
            when token = ';' and block_level = 0 and nesting_level = 0 then
                set sql_statement = substr  (sql_script , statement_start ,statement_end - statement_start);

                -- Skip white space for next statement
                repeat
                    set pos = pos + 1;
                until pos  >= end_of_string or substr(sql_script , pos , 1) > ' '
                end repeat; 

                set statement_start = pos;
                
            -- Only one statement or lefterovers - in that case the termination ; is a part of the string 
            when pos >= end_of_string then
                set sql_statement = substr  (sql_script , statement_start);
                if substr(sql_statement, length(sql_statement),1) =';' then 
                    set sql_statement = substr (sql_statement , 1 , length(sql_statement)-1);
                end if;
            else 
                begin end;
        end case; 

        -- insert into values( 'nesting_level : ' concat  nesting_level concat ' block_level : ' concat block_level concat ' token: ' concat token);
        
        -- Did we pick up a statement 
        if sql_statement > '' then
         
            -- allow CL commands 
            set cl_pos = regexp_instr(sql_statement, 'cl:' , 1 , 1, 0, 'i'); 
                
            if cl_pos > 0  then 
                set sql_statement = 'call qcmdexc (''' concat substr(sql_statement , cl_pos+ 3) concat ''')';
            end if;  
            
            -- insert into trace values( sql_statement );
            execute immediate sql_statement; 
            -- prepare for next statement
            set sql_statement = '';
            set block_level = 0;
        end if;   
        
    until break = 1 or pos >= end_of_string
    end repeat;
 
  
end;

-- Usecases:
-- Inut files is from the IFS
---------------------------------------------

-- this fails if 'john' does not exists
call run_sql_script ('john'); 

-- will run if /prj/sql/create_product.sql is an ACS compatible SQL file
call run_sql_script ('/prj/sql/create_product.sql');
commit;

--or use it in a compound statement 
begin atomic
  call run_sql_script ('/prj/sql/create_product.sql');
  call run_sql_script ('/prj/sql/create_customer.sql');
end; 
commit;