Using the WITH HOLD keywords to create a hold cursor

Use the WITH HOLD keywords to create a hold cursor. A hold cursor allows uninterrupted access to a set of rows across multiple transactions.

Ordinarily, all cursors close at the end of a transaction. A hold cursor does not close; it remains open after a transaction ends.

A hold cursor can be either a sequential cursor or (in ESQL/C) a scroll cursor.

The WITH HOLD keywords are valid in SPL routines only for Select cursors. For the syntax of the DECLARE statement in SPL routines, see Declaring a Dynamic Cursor in an SPL Routine.

DECLARE hld_cur CURSOR WITH HOLD FOR
   SELECT customer_num, lname, city FROM customer;

You can use a select hold cursor as the following code example shows. This code fragment uses a hold cursor as a master cursor to scan one set of records and a sequential cursor as a detail cursor to point to records that are located in a different table. The records that the master cursor scans are the basis for updating the records to which the detail cursor points. The COMMIT WORK statement at the end of each iteration of the first WHILE loop leaves the hold cursor c_master open but closes the sequential cursor c_detail and releases all locks. This technique minimizes the resources that the database server must devote to locks and unfinished transactions, and it gives other users immediate access to updated rows.

EXEC SQL BEGIN DECLARE SECTION;
   int p_custnum, int save_status; long p_orddate;
EXEC SQL END DECLARE SECTION;

EXEC SQL prepare st_1 from
   'select order_date from orders where customer_num = ? for update';
EXEC SQL declare c_detail cursor for st_1;
EXEC SQL declare c_master cursor with hold for
   select customer_num from customer where city = 'Pittsburgh';

EXEC SQL open c_master;
if(SQLCODE==0) /* the open worked */
   EXEC SQL fetch c_master into :p_custnum; /* discover first customer */
while(SQLCODE==0) /* while no errors and not end of pittsburgh customers */
    {
   EXEC SQL begin work; /* start transaction for customer p_custnum */
   EXEC SQL open c_detail using :p_custnum;
   if(SQLCODE==0) /* detail open succeeded */
      EXEC SQL fetch c_detail into :p_orddate; /* get first order */
   while(SQLCODE==0) /* while no errors and not end of orders */
        {
      EXEC SQL update orders set order_date = '08/15/94'
         where current of c_detail;
      if(status==0) /* update was ok */
         EXEC SQL fetch c_detail into :p_orddate; /* next order */
      }
   if(SQLCODE==SQLNOTFOUND) /* correctly updated all found orders */
      EXEC SQL commit work; /* make updates permanent, set status */
   else /* some failure in an update */
        {
      save_status = SQLCODE; /* save error for loop control */
      EXEC SQL rollback work;
      SQLCODE = save_status; /* force loop to end */
       }
   if(SQLCODE==0) /* all updates, and the commit, worked ok */
      EXEC SQL fetch c_master into :p_custnum; /* next customer? */
   }
EXEC SQL close c_master;

Use either the CLOSE statement to close the hold cursor explicitly or the CLOSE DATABASE or DISCONNECT statements to close it implicitly. The CLOSE DATABASE statement closes all cursors.

Releases earlier than Version 9.40 of HCL OneDB™ do not support the PDQPRIORITY feature with cursors that were declared WITH HOLD.