3

We have to load a number of huge tables using Load From cursor. The query formed for the same is a dynamic query as the column names are to be specified. we do the same using the below code in a procedure 

For Mcnt As Mcur Cursor For 
  Select Di_tablename From file_mapping  Order By Id      
  Do


   For Mcnt1 As Mcur1 Cursor For Select Column_name  From Sysibm.Columns  Where Table_name=di_tablename 
   Do
       Set v_colquery = v_colquery || Column_name || ',' ;   
   End For;           

   Set v_colquery = Substr(v_colquery, 1, Length(v_colquery)-1);

 set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || Di_tablename  || ' ) OF CURSOR INSERT INTO ' || Di_tablename || '_LOG  ( ' || v_colquery || ') NONRECOVERABLE';
call admin_cmd(v_sql);   
commit;

 END for;

The first round in loop executes just fine. But when it comes to the second it gives the below error.

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.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.18.60

All the cursors are implicit cursors(do correct me if i am wrong). Unclear as to which cursor is being referenced and what exactly the issue is.

rrrohanb
  • 184
  • 2
  • 13

1 Answers1

2

It was because of Commit while using Cursor.

Adding WITH HOLD to the cursor definition resolved the Issue.

rrrohanb
  • 184
  • 2
  • 13