Previous Topic: CREATE TABLE Statement—Define Relational TableNext Topic: DELETE FROM Statement—Delete a Row

Relational Data Framework Reference › SQL Statements › DECLARE CURSOR Statement—Define a Cursor

DECLARE CURSOR Statement—Define a Cursor

The DECLARE CURSOR statement lets you define a cursor. To establish the location in a table where the cursor operation occurs, include a SELECT statement.

Note: The SQL command that executes DECLARE CURSOR must be issued from an AOF rule, an OPS/REXX program, a TSO/E REXX program, or a TSO CLIST. You cannot invoke SQL commands that perform cursor operations from a TSO terminal.

Use this syntax in an AOF rule or an OPS/REXX program:

ADDRESS SQL "DECLARE {cursorname} CURSOR FOR {selectstatement} keywords"

/* Optional Keywords:                 */
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
{SYSPLEX]
[SUBSYS(subsystem)]

To invoke the statement from a TSO/E REXX program, use this syntax:

OPSQL DECLARE {cursorname} CURSOR FOR {selectstatement}

You can specify these operands for the DECLARE CURSOR statement:

cursorname

Defines the 1- to 18-character name of the cursor that you are defining.

Insert the cursorname keyword between the words DECLARE and CURSOR.

selectstatement

Defines the statement that contains the selection criteria for the cursor operation. Insert the selectstatement keyword after the word FOR.

Note: For more information about selectstatement, see SELECT Statement in this chapter.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of these values:

ALL

Routes the SQL command to all active MSF-defined systems, including the local system.

EXT

Routes the SQL command to all remote, active MSF-defined systems.

sysnames

Routes the SQL command to the specified systems. You may specify from one to eight system names as the value of sysnames.

Note: For more information about the SYSTEM operand, see Notes on Performing Cross-system SQL Operations in the User Guide.

SYSWAIT

(Optional) Defines the number of seconds the SQL processor waits for output from a remote system.

Limits: 1-300 seconds

Note: Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.

OUTPUT or NOOUTPUT

(Optional) Indicates whether the command returns output to the external data queue. To return output, specify OUTPUT; otherwise, specify NOOUTPUT. NOOUTPUT is implied when:

SYSPLEX

(Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.

SUBSYS

(Optional) Performs SQL operations on other CA OPS/MVS subsystems that are in communication with the current one. Specify the following value:

subsystem

Routes the SQL command to the specified subsystem. You can use only one four-character name of the subsystem as follows: OPSx.

Example: SQL command that defines a cursor operation

This example SQL command defines a cursor operation named STATDOWN that selects the application ID, update time, and status from a table named APPLICATIONS when the status is DOWN.

ADDRESS SQL
  "DECLARE STATDOWN CURSOR FOR",
    "SELECT APPLID, UPDATE, STATUS FROM APPLICATIONS",
      "WHERE STATUS = 'DOWN'"