DB2 : Load from cursor Dynamic query

cursorsdb2load

We need to move multiple tables using Load from cursor. But the number columns in the source and destination tables are not the same.

Also the tables structures can change so we need the same to be dynamic.

the statement would be something like below.

 declare cur1 cursor for select col1,col2, col3  from test1;
 load from cur1 of cursor insert into test2 (col1,col2, col3);

I need to call this from a procedure using execute immediate so that we can append the columns in the query. The list of columns is in a variable V_COL_LIST so the query i need to execute for procedure is like below.

 execute  immediate ('declare cur1 cursor for select '||V_COL_LIST ||'  from test1');
 execute  immediate ('load from cur1 of cursor insert into test2 ('||V_COL_LIST ||')');

But Execute Immediate does not work for the above queries.
Is there any work around to this ?

Best Answer

Figured it out used ADMIN_CMD 

set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || srctablename  || ') OF CURSOR INSERT INTO ' || destablename || '_LOG  ( ' || v_colquery || ')';
call admin_cmd(v_sql);
Related Question