DB2 stored procedure to load data in different database

Question

I am creating a stored procedure to load data from source database: MYDB -> target database: NEWDB.

I will load the data in tables SCHEMA1.EMPLOYEE1, SCHEMA1.EMPLOYEE2, ...

Edit 1:

CREATE or replace PROCEDURE SCHEMA1.PROC_LOAD ()
    SPECIFIC PROC_LOAD
    LANGUAGE SQL
BEGIN
    DECLARE v_table varchar(100);--
    DECLARE truncate_stmt varchar(1000);--
    DECLARE load_stmt varchar(1000);--

    for v_table as select rtrim(tabname) as tabname from syscat.tables where tabschema='SCHEMA1' and tabname like '%EMPLOYEE%'
    do  
        -- Truncate the table first
        set truncate_stmt = 'ALTER TABLE SCHEMA1.'||v_table.tabname||' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';--
        prepare s1 from truncate_stmt;--
        execute s1;--

        -- Load the data
        set load_stmt = 'LOAD FROM (DATABASE MYDB SELECT * FROM SCHEMA1.'||v_table.tabname||'_HIST) OF CURSOR MESSAGES ON SERVER INSERT INTO SCHEMA1.'||v_table.tabname||' NONRECOVERABLE';--
        CALL SYSPROC.ADMIN_CMD (load_stmt);--
    end for;--
END;

Above is the code of my db2 stored procedure, I have created it successfully, but when I call it, it returns the error:

ERROR [24501] [IBM][DB2/NT64] SQL0501N  The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.

In the target database, I select the data from SCHEMA1.EMPLOYEE1 and it shows that the data is loaded successfully, but for EMPLOYEE2,3,..., the old data is still there, it seems that only the first table in the loop is loaded successfully.

Any idea? Also my db2 platform is db2 11.1 on luw. Thanks in advance.

Mark Barinstein Mark Barinstein · Accepted Answer · 2019-05-27T05:52:14

LOAD commits implicitly, so you have to use WITH HOLD clause of the FOR statement to not close the cursor upon such a commit.

DB2 stored procedure to load data in different database

1 Answers