global temporary table in db2 stored procedure

Question

Just getting into DB2, and have decided to use global temporary table in my stored proc for my task.

The task would be the next: just populate some data for each day (during for example 5 days), selecting random rows from the other table I'm getting my date like:

    select id from (
    select id, rand() rnd from source_table) 
    where rnd>0
    order by rnd
    fetch first 1000 rows only

I wanted to store somewhere that list of int to reuse them. The idea was the next -

            create table test (id int, dt date);


    create or replace procedure proc1 ()

    begin 

    declare v_start date default '2018-05-25'; 
    declare v_end date default '2018-05-30'; 
    declare v_dml varchar(8000);

    /*                                  this part so far doesn't work
    declare global temporary table
    session.temp_tab(id int)
    not logged on commit preserve;

    insert into session.temp_tab(id)
    select id from my_table;*/

    while v_start <= v_end DO 

        set v_dml = 'insert into test (id, dt)
                with t as (
                select 1 id, '''||v_start||''' dt from sysibm.dual
                union
                select 2 id, '''||v_start||''' dt from sysibm.dual
                union
                select 3 id, '''||v_start||''' dt from sysibm.dual)
                select *
                from t 
                where id in (1,3)';                         
    /*instead of 1,3 I would like to have list of values in some temp 
    table/array..., which I'll get 
    from the other table and can just use duriing this proc     
    I don't want to use sub select, because I'll get every time some random 
    data. But also I need that list for filter in several insert/update 
    statements*/ 
        set v_start = v_start +1 day;

        execute immediate v_dml;
        commit;
    end while;
    end

P.S. I use DB2 LUW v10.5.0.7

UPD_1: I would like to do DDL and DML operation in one loop. For example I want to add partititon and then insert the data to the same table. like this:

    create or replace procedure proc1 (
                                    in in_rep int)
    begin 
    declare v_dt date; 
    declare v_end_dt date;
    declare v_add_part varchar(1024);
    declare v_id int;
    declare v_next_id int;       

            select max(id), max(dt) 
            into v_id,  v_dt
            from test;                                                                  

            set v_end_dt  = v_dt + in_rep day; 

                        while v_dt < v_end_dt DO

                                   set v_dt = v_dt +1 day;
                                   set v_next_id = v_id+1;          
                                   set v_add_part = 'alter table TEST               
                                                      add PARTITION part_'||v_next_id||'
                                                      starting from '||v_next_id||' ending at '||v_next_id;     

                                   execute immediate v_add_part;                  

                                   insert into test (id, dt)
                                   select v_next_id, v_dt 
                                   from sysibm.dual;                   
                       end while;
    end

In this case I would get an error, SQLCODE=-327, SQLSTATE=22525 THE ROW CANNOT BE INSERTED BECAUSE IT IS OUTSIDE THE BOUND OF THE PARTITION RANGE FOR THE LAST PARTITION.

Cause of I'm trying to alter table and insert in the same time, not step by step. But can't really get how to do it step by step excep of replace insert with dynamic sql, like:

    set v_add_part = 'alter table TEST               
    add PARTITION part_'||v_next_id||'
    starting from '||v_next_id||' ending at '||v_next_id;     

    set v_ins = 'insert into test (id, dt)
    select '||v_next_id||','''||v_dt||'''
    from sysibm.dual'; 

    execute immediate v_add_part;
    execute immediate v_ins;

There are multiple syntax errors and typos in your code sample. Apart from that, it's unclear what you are trying to achieve by repeating the insert . Suggest you fix the errors and typos and improve your question. — mao
@mao apologize, I have changed the code. Now it works. And have tried to be more clear with my task. Thank you — Liubo

mao mao · Accepted Answer · 2018-05-29T19:09:43

Here is an example of using the RAND() function to populate the session table.

Notice that the insert statement gets compiled only one time, but executed as many times as the date-range.

For production use you should add relevant error-handling.

create or replace procedure proc1 ()
language sql
specific proc1
begin 
    declare v_start date default '2018-05-25'; 
    declare v_end date default '2018-05-30'; 
    declare v_dml varchar(8000);
    declare global temporary table 
        session.temp_tab(id int not null) 
        with replace not logged on commit preserve rows;
    insert into session.temp_tab(id) 
       select int(rand()*1000) as random 
       from my_table order by random fetch first 1000 rows only;
    set v_dml = 'insert into test (id, dt) 
                 select t.id ,cast(? as date)  from session.temp_tab as t ' ;
    prepare s1 from v_dml;
    while v_start <= v_end do 
        execute s1 using v_start;
        set v_start = v_start + 1 day;
    end while;
    commit;
    end

global temporary table in db2 stored procedure

2 Answers