OPEN

The OPEN statement opens a cursor.

Invocation

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

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

Syntax

>>-OPEN--cursor-name-------------------------------------------->
 
>--+---------------------------------------------------------+-><
   |        .-,--------.                                     |
   |        V          |                                     |
   +-USING----variable-+-------------------------------------+
   |                         .-LOCAL--.                      |
   +-USING--SQL--DESCRIPTOR--+--------+--SQL-descriptor-name-+
   |                         '-GLOBAL-'                      |
   '-USING DESCRIPTOR--descriptor-name-----------------------'
 

Description

cursor-name

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

The SELECT statement associated with the cursor is either:

• The select-statement specified in the DECLARE CURSOR statement, or
• The prepared select-statement identified by the statement-name 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 specified in the SELECT statement and the current values of any variables 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 table can be created to hold them; or they can be derived during the execution of subsequent 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."

USING

Introduces a list of variables whose values are substituted for the parameter markers (question marks) of a prepared statement. For an explanation of parameter markers, see PREPARE. If the DECLARE CURSOR statement names a prepared statement that includes parameter markers, you must use USING. If the prepared statement does not include parameter markers, USING is ignored.

USING variable,...

Identifies host structures or variables that must be declared in the program in accordance with the rules for declaring host structures and variables. A reference to a host 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. The nth variable corresponds to the nth parameter marker in the prepared statement.

USING SQL DESCRIPTOR SQL-descriptor-name

Identifies an SQL descriptor.

LOCAL

Specifies the scope of the name of the descriptor to be local to program invocation.

GLOBAL

Specifies the scope of the name of the descriptor to be global to the SQL session.

SQL-descriptor-name

Names the SQL descriptor. The name must identify a descriptor that already exists with the specified scope.

USING DESCRIPTOR descriptor-name

Identifies an SQLDA that must contain a valid description of input variables.

Before the OPEN statement is processed, the user must set the following fields in the SQLDA. (The rules for REXX are different. For more information see the Embedded SQL Programming book.)

• SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
• SQLDABC 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 results, there must be additional SQLVAR entries for each parameter. For more information about the SQLDA, which includes a description of the SQLVAR and an explanation on how to determine the number of SQLVAR occurrences, see Appendix D. SQLDA (SQL descriptor area).

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. The nth variable described by the SQLDA corresponds to the nth parameter marker in the prepared statement.

Note that because RPG/400® does not provide the facility for setting pointers and the SQLDA uses pointers to locate the appropriate variables, you will have to set these pointers outside your RPG/400 application.

Notes

Closed state of cursors: All cursors in a program are in the closed state when:

• The program is called:
  • If CLOSQLCSR(*ENDPGM) is specified, all cursors are in the closed state each time the program is called.
  • If CLOSQLCSR(*ENDSQL) is specified, all cursors are in the closed state only the first time the program is called as long as one SQL program remains on the call stack.
  • If CLOSQLCSR(*ENDJOB) is specified, all cursors are in the closed state only the first time the program is called as long as the job remains active.
  • If CLOSQLCSR(*ENDMOD) is specified, all cursors are in the closed state each time the module is initiated.
  • If CLOSQLCSR(*ENDACTGRP) is specified, all cursors are in the closed state only the first time the module in the program is initiated in the activation group.
• A program starts a new unit of work by executing a COMMIT or ROLLBACK statement without a HOLD option. Cursors declared with the HOLD option are not closed by a COMMIT statement.
• A CONNECT (Type 1) statement was executed.

A cursor can also be in the closed state because:

• A CLOSE statement was executed.
• A DISCONNECT statement disconnected the connection with which the cursor was associated.
• The connection with which the cursor was associated was in the release-pending state and a successful COMMIT occurred.
• A CONNECT (Type 1) statement was executed.

To retrieve rows from the result table of a cursor, the FETCH statement must be executed 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 temporary tables: If the result table of a cursor is not read-only, its rows are derived during the execution of subsequent FETCH statements. The same method may be used for a read-only result table. However, if a result table is read-only, DB2 UDB for iSeries may choose to use the temporary table method instead. With this method the entire result table is inserted into a temporary table during the execution of the OPEN statement. When a temporary table is used, the results of a program can differ in several ways:

• An error can occur during OPEN that would otherwise not occur until some later FETCH statement.
• The INSERT, UPDATE, and DELETE statements that are executed while the cursor is open cannot affect the result table.
• Any NEXT VALUE expressions in the SELECT statement are evaluated for every row of the result table during OPEN. Thus, sequence values are generated, for every row of the result table during OPEN.
• Any functions are evaluated for every row of the result table during OPEN. Thus, any external actions and SQL statements that modify SQL data within the functions are performed for every row of the result table during OPEN.

Conversely, if a temporary table is not used, INSERT, UPDATE, and DELETE statements executed while the cursor is open can affect the result table, and any NEXT VALUE expressions and functions in the SELECT statement are evaluated as each row is fetched. The effect of such operations is not always predictable. For example, if cursor CUR is positioned on a row of its result table defined as SELECT * FROM T, and a row is inserted into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A subsequent FETCH CUR might or might not retrieve the new row of T.

Parameter marker replacement: When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by its corresponding variable. The replacement of a parameter marker is an assignment operation in which the source is the value of the variable, and the target is a variable within the database manager. 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 Table 74.

Let V denote a 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. Thus:

• V must be compatible with 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, the value of V must not be null.

However, unlike the rules for assigning a value to a column:

• If V is a string, the value will be truncated (without an error), if its length is greater than the length attribute of the target.

When the SELECT statement of the cursor is evaluated, the value used in place of P is the value of the target variable for P. 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 with two blanks.

The USING clause is intended for a prepared SELECT statement that contains parameter markers. However, it can also be used when the SELECT statement of the cursor is part of the DECLARE CURSOR statement. 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 variables are the same as the attributes of 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.

Examples

Example 1: Write the embedded statements in a COBOL program that will:

1. Define a cursor C1 that is to be used to retrieve all rows from the DEPARTMENT table for departments that are administered by (ADMRDEPT) department 'A00'
2. Place the cursor C1 before the first row to be fetched.

   EXEC SQL  DECLARE C1 CURSOR FOR
              SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPARTMENT
                 WHERE ADMRDEPT = 'A00'  END-EXEC.

   EXEC SQL  OPEN C1  END-EXEC.

Example 2: Code an OPEN statement to associate a cursor DYN_CURSOR with a dynamically defined select-statement in a C program. Assume each prepared select-statement always defines two items in its select list with the first item having a data type of integer and the second item having a data type of VARCHAR(64). (The related host variable definitions, PREPARE statement, and DECLARE CURSOR statement are also shown in the example below.)

  EXEC SQL  BEGIN DECLARE SECTION;
     static short hv_int;
     char hv_vchar64[64];
     char stmt1_str[200];
  EXEC SQL  END DECLARE SECTION;

  EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;

  EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

  EXEC SQL  OPEN DYN_CURSOR USING :hv_int, :hv_vchar64;

Example 3: Code an OPEN statement as in example 3, but in this case the number and data types of the items in the select statement are not known.

  EXEC SQL  BEGIN DECLARE SECTION;
     char stmt1_str[200];
  EXEC SQL  END DECLARE SECTION;
  EXEC SQL  INCLUDE SQLDA;

  EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
  EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

  EXEC SQL  OPEN DYN_CURSOR USING DESCRIPTOR :sqlda;