FETCH Statement—Retrieve Row Values

Relational Data Framework Reference › SQL Statements › FETCH Statement—Retrieve Row Values

FETCH Statement—Retrieve Row Values

The FETCH statement lets you retrieve the values in the current row of a cursor operation. Usually this statement is executed in a loop that the SQLCODE variable controls so that CA OPS/MVS processes each row in a cursor operation. For a sample program that uses SQLCODE, see OPS/REXX Program That Demonstrates Cursor Operations in the chapter “Using the Relational Data Framework” in the User Guide.

Note: The SQL command that executes the FETCH 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 "FETCH keywords"

/* Specify these required keywords in the order shown:   */
{cursorname}
{INTO(hostvarlist)}
/* Optional keywords                */
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[SYSPLEX]
[SUBSYS(subsystem)]

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

OPSQL FETCH

/* Specify the following required keywords in the order shown:   */
{cursorname}
{INTO(hostvarlist)}

Specify these operands for the FETCH statement:

cursorname

Defines the name of the cursor operation from which you retrieve values.

hostvarlist

Defines a set of host variable names in which you can store the selected column values. Specify a name for each column that is defined in the SELECT clause of the DECLARE CURSOR statement, and in the same order.

SYSTEM

(Optional) Use the SYSTEM keyword to perform 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. Specify from one to eight system names as the value of sysnames.

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

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:

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.

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

To retrieve the values from the example cursor called STATDOWN, issue this SQL command:

ADDRESS SQL
  "FETCH STATDOWN INTO (:APPLID,:UPDATE,:STATUS)"