A holdable cursor does not close, even when a COMMIT statement is executed.

To use a holdable cursor, declare the cursor by specifying UNTIL DISCONNECT or WITH HOLD in the DECLARE CURSOR statement. However, before the cursor is opened, a LOCK statement with UNTIL DISCONNECT specified must be issued to lock the table. When these statements are specified, the cursor remains open until execution of a CLOSE, DISCONNECT, or ROLLBACK statement (including ROLLBACK and DISCONNECT processing that is executed implicitly if an error occurs).

Using a holdable cursor can reduce the incidence of locked resources, because a COMMIT statement can be executed while retrieving or updating a large amount of data. Moreover, because a COMMIT statement can be executed while keeping the cursor open, a synchronization point can be activated even when a large amount of data is being retrieved or updated (i.e., when a transaction executes for an extended period of time), thus reducing the restart time.

When a holdable cursor is used, deletion of the work table file and freeing of the work buffer for the work table take place during commit processing after the holdable cursor for which the work table file was created is closed.

When a holdable cursor is opened, each back-end server process becomes occupied even if there are no transactions. Therefore, the maximum number of server processes must be estimated carefully when a holdable cursor is to be used.

The locked resources that are inherited beyond a transaction differ depending on whether a LOCK statement with UNTIL DISCONNECT specified is executed and whether a search in which a work table is created or a parallel scan is executed. The locked resources that are inherited are shown below.

Explanation:

The numbers in the figure are explained below.

No.	Execution of LOCK statement with UNTIL DISCONNECT specified	Search in which work table is created or parallel scan	Inherited locked resources
1	Executed	Not applicable	Only the resource of the LOCK statement
2	Not executed	Executed	All resources
3		Not executed	Only the resource at the cursor position

When a UAP executing in an OLTP environment is using a holdable cursor, use must be specified in the pd_oltp_holdcr operand of the HiRDB system definition.

For a UAP executing in an OLTP environment to use a holdable cursor, the following conditions must be satisfied:

• The UAP must use an X/Open-compliant API to access HiRDB.
• The service function of the UAP using the holdable cursor must perform cursor postprocessing sometime after the holdable cursor is opened but before the service function returns control.^*

^* The cursor postprocessing procedure is described below:

1. Close the cursor.
2. Execute the ROLLBACK statement.
3. Execute the DISCONNECT statement.
4. Terminate the UAP process.

The SQL execution sequence from the UAP service function is shown below. Note the sequential relationships between the start transaction API, OPEN cursor, CLOSE cursor, and COMMIT API steps in the figure.

Explanation:

1. Duration that the cursor is held
2. Duration of the transaction