Nonstop Database, HiRDB Version 9 UAP Development Guide
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. 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 a work table file and release of work table buffer space are executed during commit processing after the holdable cursor used to create the work table file has 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.
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 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.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.