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

DECLARE CURSOR Statement—Define a Cursor

Issue a DECLARE CURSOR statement to define a cursor. You will include a SELECT statement to establish the location in a table where the cursor operation is to occur.

Note: The SQL command that executes the DECLARE CURSOR statement 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]

Use this syntax to invoke the statement from a TSO/E REXX program or a TSO CLIST:

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 this cursorname keyword between the words DECLARE and CURSOR.

selectstatement

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

For more information, 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.

For more information, see Notes on Performing Cross-system SQL Operations in the chapter “Using the Relational Data Framework” in the User Guide.

SYSWAIT

(Optional) Defines the number of seconds the SQL processor waits for output from a remote system. You may specify a value between 1 and 300 seconds.

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. Specify OUTPUT to have output returned; otherwise, specify NOOUTPUT. NOOUTPUT is implied when:

• The ADDRESS SQL command is issued in an AOF rule.
• You specify more than one system name as the value of the SYSTEM(sysnames) keyword.
• You specify SYSTEM(ALL) or SYSTEM(EXT).

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.

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'"