OPEN statement

The OPEN statement opens a cursor so that it can be used to process rows from its result table.

Invocation for OPEN

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.

Authorization for OPEN

See DECLARE CURSOR statement for the authorization required to use a cursor.

Syntax for OPEN

Notes:

¹ A global variable must only be specified in an SQL PL context.
² An array element must only be specified in an SQL PL context.

Description for OPEN

cursor-name

Identifies the cursor to be opened. The cursor-name must identify a declared cursor as explained in DECLARE CURSOR statement. When the OPEN statement is executed, the cursor must be in the closed state.

The SELECT statement of the cursor is either one of the following types of SELECT statements:

The select-statement that is specified in the DECLARE CURSOR statement
The prepared select-statement that is identified by the statement-name that is specified in the DECLARE CURSOR statement.

If the statement has not been successfully prepared, or is not a select-statement, the cursor cannot be successfully opened.

The result table of the cursor is derived by evaluating the SELECT statement. The evaluation uses the current values of any special registers or PREVIOUS VALUE expressions that are specified in the SELECT statement, and the current values of any host variables that are specified in the SELECT statement or the USING clause of the OPEN statement. The rows of the result table can be derived during the execution of the OPEN statement, and a temporary copy of a result table can be created to hold those rows. They can be derived during the execution of later FETCH statements. In either case, the cursor is placed in the open state and positioned before the first row of its result table.

If the table is empty, the position of the cursor is effectively “after the last row.” The Db2 system does not indicate an empty table when the OPEN statement is executed. A subsequent fetch for the cursor might return the SQLSTATE warning of '02000'.

USING

FL 500Introduces a list of variables or array element expressions whose values are substituted for the parameter markers (question marks) in the statement of the cursor, depending on the declaration of the cursor: End of change

If the DECLARE CURSOR statement included statement-name, the statement was prepared with a PREPARE statement. The variables specified in the USING clause of the OPEN statement replace any parameter markers in the prepared statement. This reflects the typical use of the USING clause of the OPEN statement. For an explanation of parameter marker replacement, see PREPARE statement.
If the prepared statement includes parameter markers, you must use USING. If the prepared statement does not include parameter markers, USING is ignored.
If the DECLARE CURSOR statement included select-statement and the SELECT statement included variables, the USING clause of the OPEN statement can be used to specify variables that are to override the values that were specified when the cursor was defined. In this case, the OPEN statement is executed as if each variable in the SELECT statement were a parameter marker except that the attributes of the target variable are the same as the variables in the SELECT statement. The effect is to override the values of the variables in the SELECT statement of the cursor with the values of the variables specified in the USING clause. The overriding value is always the value of the main variable because indicator variables are ignored in this context without warning.

The nth variable corresponds to the nth parameter marker in the prepared statement. Where appropriate, locator variables and file reference variables can be provided as the source of values for parameter markers.

variable

Identifies a variable or a host structure that is declared in the application program in accordance with the rules for declaring variables and host structures. When the statement is executed, a reference to a structure is replaced by a reference to each of its variables. The number of variables must be the same as the number of parameter markers in the prepared statement.

Start of change An array global variable must only be specified if the OPEN statement is issued in SQL PL. End of change

array-variable [array-index]

Identifies an array element. An array element must only be specified if the OPEN statement is issued in SQL PL.

array-variable

Specifies an array variable.

[array-index]

An expression that specifies which element in the array to use.

For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.

For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.

array-index must not be:

An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
A nondeterministic function
A function that is defined with EXTERNAL ACTION
A function that is defined with MODIFIES SQL DATA
A sequence expression

DESCRIPTOR descriptor-name

Identifies an SQLDA that contains a valid description of the input host variables.

Before the OPEN statement is processed, the user must set the following fields in the SQLDA:

SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
A REXX SQLDA does not contain this field.
SQLABC to indicate the number of bytes of storage allocated for the SQLDA
SQLD to indicate the number of variables used in the SQLDA when processing the statement
SQLVAR occurrences to indicate the attributes of the variables

The SQLDA must have enough storage to contain all SQLVAR occurrences. If LOBs or distinct types are present in the result table, there must be additional SQLVAR entries for each input host variable. For more information on the SQLDA, which includes a description of the SQLVAR and an explanation on how to determine the number of SQLVAR occurrences, see SQL descriptor area (SQLDA).

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. It must be the same as the number of parameter markers in the prepared statement.

See Identifying an SQLDA in C or C++ for how to represent descriptor-name in C.

Notes for OPEN

Errors occurring on OPEN: In local and remote processing, the DEFER(PREPARE) and REOPT(ALWAYS)/REOPT(ONCE) bind options can cause some SQL statements to receive delayed errors. For example, an OPEN statement might receive an SQLCODE that normally occurs during PREPARE processing. Or a FETCH statement might receive an SQLCODE that normally occurs at OPEN time.

Closed state of cursors: All cursors in an application process are in the closed state when:

The application process is started.
A new unit of work is started for the application process unless the WITH HOLD option has been used in the DECLARE CURSOR statement.
The application was precompiled with the CONNECT(1) option (which implicitly closes any open cursors).

A cursor can also be in the closed state because:

A CLOSE statement was executed.
An error was detected that made the position of the cursor unpredictable.

To retrieve rows from the result table of a cursor, you must execute a FETCH statement when the cursor is open. The only way to change the state of a cursor from closed to open is to execute an OPEN statement.

Effect of a temporary copy of a result table: Db2 can process a cursor in two different ways:

It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a temporary copy of the result table.
It can derive the result table rows as they are needed during the execution of later FETCH statements.

If the result table is not read-only, Db2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:

When a temporary copy of the result table is used: An error can occur that would otherwise not occur until some later FETCH statement. insert operations that are executed while the cursor is open cannot affect the result table once all the rows have been materialized in the temporary copy of the result table. For a scrollable insensitive cursor, update and delete operations that are executed while the cursor is open cannot affect the result table. For a scrollable sensitive static cursor, update and delete operations can affect the result table if the rows are subsequently fetched with sensitive FETCH statements.

When a temporary copy of the result table is not used: Insert, update, and delete operations that are executed while the cursor is open can affect the result table. The effect of such operations is not always predictable.

For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A later FETCH C might or might not retrieve the new row of T. To avoid these changes, you can specify INSENSITIVE SCROLL for the cursor to force the use of a temporary copy of the result table.

Parameter marker replacement: Before the OPEN statement is executed, each parameter marker in the query is effectively replaced by its corresponding host variable. The replacement is an assignment operation in which the source is the value of the host variable and the target is a variable within Db2. The assignment rules are those described for assignment to a column in Assignment and comparison. For a typed parameter marker, the attributes of the target variable are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable are determined according to the context of the parameter marker. For the rules that affect parameter markers, see Parameter markers.

Let V denote a host variable that corresponds to parameter marker P. The value of V is assigned to the target variable for P in accordance with the rules for assigning a value to a column:

V must be compatible with the target.
If V is a string, its length (excluding trailing blanks) must not be greater than the length attribute of the target.
If V is a number, the absolute value of its integral part must not be greater than the maximum absolute value of the integral part of the target.
If the attributes of V are not identical to the attributes of the target, the value is converted to conform to the attributes of the target.
If the target cannot contain nulls, V must not be null.

When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by the value of its corresponding host variable. For example, if V is CHAR(6) and the target is CHAR(8), the value used in place of P is the value of V padded on the right with two blanks. For more on the process of replacement, see Parameter marker replacement.

Considerations for scrollable cursors: Following an OPEN cursor statement, a GET DIAGNOSTICS statement can be used to get the attributes of the cursor such as the following information (for more information, see GET DIAGNOSTICS statement):

DB2_SQL_ATTR_CURSOR _HOLD. Whether the cursor was defined with the WITH HOLD attribute.
DB2_SQL_ATTR_CURSOR_SCROLLABLE. Scrollability of the cursor.
DB2_SQL_ATTR_CURSOR_SENSITIVITY. Effective sensitivity of the cursor.
The sensitivity information can be used by applications (such as an ODBC driver) to determine what type of FETCH (INSENSITIVE or SENSITIVE) to issue for a cursor defined as ASENSITIVE.
DB2_SQL_ATTR_CURSOR_ROWSET. Whether the cursor can be used to access rowsets.
DB2_SQL_ATTR_CURSOR_TYPE. Whether a cursor type is forward-only, static, or dynamic.

The scrollability of the cursor is in SQLWARN1.
The sensitivity of the cursor is in SQLWARN4.
The effective capability of the cursor is in SQLWARN5.

Number of rows inserted: SQL data change statements and routines that modify SQL data embedded in the cursor definition are completely executed, and the result table is stored in a temporary table when the cursor opens. If statement execution is successful, the SQLERRD(3) field contains the sum of the number of rows that qualified for insert, update, and delete operations. If an error occurs during execution of an OPEN statement that involves a cursor that contains a data change statement within a fullselect, the results of that data change statement are rolled back.

Materialization of the rows of the result table and NEXT VALUE expressions: If the rows of the result table of a cursor are materialized when the cursor is opened and the SELECT statement of the cursor contains NEXT VALUE expressions, the expressions are processed when the cursor is opened. Otherwise, the NEXT VALUE expressions are evaluated as the rows of the result table are retrieved.

Opening the same cursor multiple times: A cursor in an SQL procedure that is declared as WITH RETURN TO CLIENT can be opened even when a cursor with the same name is already in the open state. In this case, the existing open cursor becomes a result set cursor and is no longer accessible by its cursor name. A new cursor is opened and becomes accessible by the cursor name. Closing the new cursor does not make the cursor that was previously accessible by that name accessible by the cursor name again. Cursors that become result set cursors in this way cannot be accessed at the server and can be processed only at the client.

Examples for OPEN

Example 1: Execute an OPEN statement, which places the cursor at the beginning of the rows to be fetched.

   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT DEPTNO, DEPTNAME, MGRNO FROM DSN8C10.DEPT
     WHERE ADMRDEPT = 'A00';
   EXEC SQL OPEN C1;
    DO WHILE (SQLCODE = 0);
     EXEC SQL FETCH C1 INTO :DNUM, :DNAME, :MNUM;
   END;
    EXEC SQL CLOSE C1;

Example 2: Suppose that the following array type, array variable, and table have been defined.

CREATE TYPE INTARRAY AS INTEGER ARRAY[100];
CREATE TYPE STRINGARRAY AS VARCHAR(10) ARRAY[100];
CREATE TABLE T1 (COL1 CHAR(10), COL2 INT);

Use an array variable as input for a dynamic SQL statement. The dynamic statement references an array element in the array variable. The dynamic statement contains two parameter markers, one for the array variable and one for the index of the array element. The OPEN statement provides two input values in the USING clause: the array variable, and a variable that contains the index for the array element.

CREATE PROCEDURE PROCESSPERSONS (OUT WITHO STRINGARRAY, INOUT INT0 INT)
BEGIN
 DECLARE INTA INTARRAY;
 DECLARE INTB INTARRAY;
 DECLARE INTV INTEGER;
 DECLARE STMT CHAR(100);
 DECLARE C2 CURSOR FOR S1;
--
-- Initialize the array
--
 SET INTA = ARRAY[1,INTEGER(2),3+0,4,5,6] ;
--
-- Use dynamic SQL with an array parameter marker and a parameter marker
-- containing the index to retrieve the value from the array parameter.
-- The array is referenced in a predicate.
--
 SET STMT = 'SELECT COL1 FROM T1 WHERE COL2 = CAST(? AS INTARRAY)[?]';
 PREPARE S1 FROM STMT;
 OPEN C2 USING INTA, INTV; -- Input: INTA is an array, and INTV is the 
                           -- index for the array element
 FETCH C2 INTO INTB ;      -- Output: INTB is an array variable
…
 CLOSE C2;
…
END