There are two forms of this statement:
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
To execute a FETCH command on an opened cursor, the user requires the same authority needed to declare that cursor for a given table or set of tables.
FETCH [SENSITIVE | INSENSITIVE] fetch-orientation [FROM]
cursor-name [single-row-fetch | multiple-row-fetch]
fetch-orientation
{BEFORE |
AFTER |
row-positioned |
rowset-positioned}
row-positioned
[NEXT |
PRIOR |
FIRST |
LAST |
CURRENT |
ABSOLUTE {host-variable | integer-constant} |
RELATIVE {host-variable | integer-constant}]
rowset-positioned
[NEXT ROWSET |
PRIOR ROWSET |
FIRST ROWSET |
LAST ROWSET |
CURRENT ROWSET |
ROWSET STARTING AT {ABSOLUTE | RELATIVE} {host-variable | integer-constant}]
single-row-fetch
[INTO {host-variable [,...] | DESCRIPTOR descriptor-name}]
multiple-row-fetch
[FOR {host-variable | integer-constant} ROWS]
[INTO {host-variable-array [,...] | DESCRIPTOR descriptor-name}]
The FETCH command retrieves data from the next row of a cursor result table. A cursor defines the current row of the result. A FETCH statement also advances this cursor to the next available row in the result table. The result table is a collection of rows of data that satisfy the conditions of a SELECT statement defined in a DECLARE CURSOR statement.
If currently positioned before a row of its result table, the FETCH command moves the cursor onto that row and assigns the column values in that row to the host variables as specified by either the INTO or USING clauses. If positioned on a row (other than the last row), the FETCH command moves the cursor onto the next row, assigning the column values of that row to the host variables specified. Once the cursor becomes positioned on or after the last row, a value of +100 is assigned to the SQLCODE indicator field (SQLCA), and the FETCH command positions the cursor after the last row. No values are assigned to the host variables indicated.
Returns the row from the result table as it is. If the row has been previously fetched with a FETCH SENSITIVE, it reflects changes made outside this cursor before the FETCH SENSITIVE statement was issued. Positioned updates and deletes are reflected with FETCH INSENSITIVE if the same cursor was used for the positioned update or delete.
INSENSITIVE can only be specified for cursors declared as INSENSITIVE or SENSITIVE STATIC (or if the cursor is declared as ASENSITIVE and DB2 defaults to INSENSITIVE). Otherwise, if the cursor is declared as SENSITIVE DYNAMIC (or if the cursor is declared as ASENSITIVE and DB2 defaults to SENSITIVE DYNAMIC), an error occurs and the FETCH statement has no effect. For an INSENSITIVE cursor, specifying INSENSITIVE is optional because it is the default.
Updates the fetched row in the result table from the corresponding row in the base table of the cursor's SELECT statement and returns the current values. Thus, it reflects changes made outside this cursor. SENSITIVE can only be specified for a sensitive cursor. Otherwise, if the cursor is insensitive, an error occurs and the FETCH statement has no effect. For a SENSITIVE cursor, specifying SENSITIVE is optional because it is the default.
When the cursor is declared as SENSITIVE STATIC and a FETCH SENSITIVE is requested, the following steps are taken:
Positions the cursor after the last row of the result table. Values are not assigned to host variables. The number of rows of the result table are returned in the SQLERRD1 and SQLERRD2 fields of the SQLCA for cursors with an effective sensitivity of INSENSITIVE or SENSITIVE STATIC.
Positions the cursor before the first row of the result table. Values are not assigned to host variables.
Positioning of the cursor with row-positioned fetch orientations NEXT, PRIOR, and RELATIVE is done in relation to the current cursor position. Following a successful row-positioned FETCH statement, the cursor is positioned on a single row of data. If the cursor is enabled for rowsets, positioning is performed relative to the first row of the current rowset, and the cursor is positioned on a rowset consisting of a single row.
Positions the cursor on the next row or rows of the result table relative to the current cursor position, and returns data if a target is specified. NEXT is the only row-positioned fetch operation that can be explicitly specified for cursors that are defined as NO SCROLL. NEXT is the default if no other cursor positioning is specified. If a specified row reflects a hole, a warning is issued and data values are not assigned to host variables for that row.
The following table shows situations for different cursor positions and the results when NEXT is used.
| Current state of the cursor | Result of FETCH NEXT |
|---|---|
| Before the first row | Cursor is positioned on the first row and data is returned if requested. |
| On the last row or after the last row | A warning occurs, values are not row assigned to host variables, and the cursor is positioned after the last row. |
| Before a hole | For a SENSITIVE STATIC cursor, a warning occurs for a delete hole or an update hole, values are not assigned to host variables, and the cursor is positioned on the hole. |
| Unknown | An error occurs, values are not assigned to host variables, and the cursor position remains unknown. |
Positions the cursor on the previous row or rows of the result table relative to the current cursor position, and returns data if a target is specified. If a specified row reflects a hole, a warning is issued, and data values are not assigned to host variables for that row.
The following table shows situations for different cursor positions and the results when PRIOR is used.
| Current state of the cursor | Result of FETCH PRIOR |
|---|---|
| Before or on the first row | A warning occurs, values are not first row assigned to host variables, and the cursor is positioned before the first row. |
| After a hole | For a SENSITIVE STATIC cursor, a warning occurs for a delete hole or an update hole, values are not assigned to host variables, and the cursor is positioned on the hole. |
| After the last row | Cursor is positioned on the last row. |
| Unknown | An error occurs, values are not assigned to host variables, and the cursor position remains unknown. |
Positions the cursor on the first row of the result table, and returns data if a target is specified. For a SENSITIVE STATIC cursor, if the first row of the result table is a hole, a warning occurs for a delete hole or an update hole and values are not assigned to host variables.
Positions the cursor on the last row of the result table, and returns data if a target is specified. The number of rows of the result table is returned in the SQLERRD1 and SQLERRD2 fields of the SQLCA for an insensitive or sensitive static cursor. For a SENSITIVE STATIC cursor, if the last row of the result table is a hole, a warning occurs for a delete hole or an update hole and values are not assigned to host variables.
The cursor position is not changed, data is returned if a target is specified. If the cursor was positioned on a rowset of more than one row, then the cursor position is on the first row of the rowset.
The following table shows situations in which errors occur with CURRENT.
| Current state of the cursor | Result of FETCH CURRENT |
|---|---|
| Before the first row or after the last row | A warning occurs, values are not last row assigned to host variables. |
| On a hole | For a SENSITIVE STATIC or a SENSITIVE DYNAMIC cursor, a warning occurs for a delete hole or an update hole, values are not assigned to host variables, and the cursor is positioned on the hole. A hole is detected for a SENSITIVE DYNAMIC cursor when the current row is deleted or updated so that it no longer meets the selection criterion, and a FETCH CURRENT or a FETCH RELATIVE +0 is executed without repositioning the cursor. |
| Unknown | An error occurs, values are not assigned to host variables, and the cursor position remains unknown. |
host-variable or integer-constant is assigned to an integral value k. If a host-variable is specified, it must be an exact numeric type with zero scale and must not include an indicator variable. The possible data types for the host variable are DECIMAL(n,0) or integer. The DECIMAL data type is limited to DECIMAL(18,0). An integer-constant can be up to 31 digits, depending on the application language.
If k=0, the cursor is positioned before the first row of the result table. Otherwise, ABSOLUTE positions the cursor to row k of the result table if k>0, or to k rows from the bottom of the table if k<0. For example, "ABSOLUTE -1" is the same as "LAST".
Data is returned if the specified position is within the rows of the result table, and a target is specified.
If an absolute position is specified that is before the first row or after the last row of the result table, a warning occurs, values are not assigned to host variables, and the cursor is positioned either before the first row or after the last row. If the resulting cursor position is after the last row for INSENSITIVE and SENSITIVE STATIC scrollable cursors, the number of rows of the result table are returned in the SQLERRD1 and SQLERRD2 fields of the SQLCA. If row k of the result table is a hole, a warning occurs and values are not assigned to host variables.
FETCH ABSOLUTE 0 results in positioning before the first row and a warning is issued. FETCH BEFORE results in positioning before the first row and no warning is issued.
The following table shows some synonymous specifications.
| Specification | Alternative |
|---|---|
| ABSOLUTE 0 (but with a warning) | BEFORE (without a warning) |
| ABSOLUTE +1 | FIRST |
| ABSOLUTE -1 | LAST |
| ABSOLUTE -m, 0<m=n | ABSOLUTE n+1-m |
| ABSOLUTE n | LAST |
| ABSOLUTE -n | FIRST |
| ABSOLUTE x (with a warning) | AFTER (without a warning) |
| ABSOLUTE -x (with a warning) | BEFORE (without a warning) |
host-variable or integer-constant is assigned to an integral value k. If a host-variable is specified, it must be an exact numeric type with zero scale and must not include an indicator variable. The possible data types for the host variable are DECIMAL(n,0) or integer. The DECIMAL data type is limited to DECIMAL(18,0). An integer-constant can be up to 31 digits, depending on the application language.
Data is returned if the specified position is within the rows of the result table, and a target is specified.
RELATIVE positions the cursor to the row in the result table that is either k rows after the current row if k>0, or ABS(k) rows before the current row if k<0. For example, "RELATIVE -1" is the same as "PRIOR". If k=0, the position of the cursor does not change (that is, "RELATIVE 0" is the same as "CURRENT").
If a relative position is specified that results in positioning before the first row or after the last row, a warning is issued, values are not assigned to host variables, and the cursor is positioned either before the first row or after the last row. If the resulting cursor position is after the last row for INSENSITIVE and SENSITIVE STATIC scrollable cursors, the number of rows of the result table is returned in the SQLERRD1 and SQLERRD2 fields of the SQLCA. If the cursor is positioned on a hole and RELATIVE 0 is specified or if the target row is a hole, a warning occurs and values are not assigned to host variables. If the cursor position is unknown and RELATIVE 0 is specified, an error occurs.
The following table shows some synonymous specifications.
| Specification | Alternative |
|---|---|
| RELATIVE +1 | NEXT |
| RELATIVE -1 | PRIOR |
| RELATIVE 0 | CURRENT |
| RELATIVE +r (with a warning) | AFTER (without a warning) |
| RELATIVE -r (with a warning) | BEFORE (without a warning) |
Positioning of the cursor with rowset-positioned fetch orientations NEXT ROWSET, PRIOR ROWSET, and ROWSET STARTING AT RELATIVE is done in relation to the current rowset. The number of rows in the rowset is determined either explicitly or implicitly. The FOR n ROWS clause in the multiple-row-fetch clause is used to explicitly specify the size of the rowset. Following a successful rowset-positioned FETCH statement, the cursor is positioned on all rows of the rowset.
A rowset-positioned fetch orientation must not be specified if the current cursor position is not defined to access rowsets. NEXT ROWSET is the only rowset-positioned fetch orientation that can be specified for cursors that are defined as NO SCROLL.
If a row of the rowset reflects a hole, a warning is returned, data values are not assigned to host variable arrays for that row (that is, the corresponding positions in the target host variable arrays are untouched), and -3 is returned in all provided indicator variables for that row. If a hole is detected, and at least one indicator variable is not provided, an error occurs.
Positions the cursor on the next rowset of the result table relative to the current cursor position, and returns data if a target is specified. The next rowset is logically obtained by fetching the row that follows the current rowset and fetching additional rows until the number of rows that is specified implicitly or explicitly in the FOR n ROWS clause is obtained or the last row of the result table is reached.
If a row of the rowset reflects a hole, the following actions occur:
If a hole is detected and at least one indicator variable is not provided, an error is returned.
If the cursor is not positioned because of a prior error, values are not assigned to the host-variable-array, and an error is returned. If a row of the rowset would be after the last row of the result table, values are not assigned to host-variable-arrays for that row and any subsequent requested rows of the rowset, and a warning is returned.
NEXT ROWSET is the only rowset positioned fetch orientation that can be explicitly be specified for cursors that are defined as NO SCROLL.
Positions the cursor on the previous rowset of the result table relative to the current position, and returns data if a target is specified.
The prior rowset is logically obtained by fetching the row that follows the current rowset and fetching additional rows until the number of rows that is specified implicitly or explicitly in the FOR n ROWS clause is obtained or the last row of the result table is reached.
If a row would be before the first row of the result table, the cursor is positioned on a partial rowset that consists of only those rows that are prior to the current position of the cursor starting with the first row of the result table, and a warning is returned. Values are not assigned to the host-variable-arrays for the rows in the rowset for which the warning is returned.
Although the rowset is logically obtained by fetching backwards from before the current rowset, the data is returned to the application starting with the first row of the rowset, to the end of the rowset.
If a row of the rowset reflects a hole, the following actions occur:
If a hole is detected and at least one indicator variable is not provided, an error is returned.
If the cursor is not positioned because of a prior error, values are not assigned to the host-variable-array, and an error is returned.
Positions the cursor on the first rowset of the result table, and returns data if a target is specified.
If a row of the rowset reflects a hole, the following actions occur:
If a hole is detected and at least one indicator variable is not provided, an error is returned.
If the result table contains fewer rows than specified implicitly or explicitly in the FOR n ROWS clause, values are not assigned to host-variable-arrays after the last row of the result table, and a warning is returned.
Positions the cursor on the last rowset of the result table and returns data if a target is specified. The last rowset is logically obtained by fetching the last row of the result table and fetching prior rows until the number of rows in the rowset is obtained or the first row of the result table is reached. Although the rowset is logically obtained by fetching backwards from the bottom of the result table, the data is returned to the application starting with the first row of the rowset, to the end of the rowset, which is also the end of the result table.
If a row of the rowset reflects a hole, the following actions occur:
If a hole is detected and at least one indicator variable is not provided, an error is returned.
If the result table contains fewer rows than specified implicitly or explicitly in the FOR n ROWS clause, the last rowset is the same as the first rowset, values are not assigned to host-variable-arrays after the last row of the result table, and a warning is returned.
If the FOR n ROWS clause specifies a number different from the number of rows specified implicitly or explicitly in the FOR n ROWS clause on the most recent FETCH statement for this cursor, the cursor is repositioned on the specified number of rows, starting with the first row of the current rowset. Otherwise, the position of the cursor on the current rowset is unchanged. Data is returned if a target is specified.
With isolation level UR or a sensitive dynamic scrollable cursor, it is possible that different rows will be returned than the FETCH that established the most recent rowset cursor position. This can occur while refetching the first row of the rowset when it is determined to not be there anymore. In this case, fetching continues moving forward to get the first row of data for the rowset. This can also occur when changes have been made to other rows in the current rowset such that they no longer exist or have been logically moved within (or out of) the result table of the cursor.
If the cursor is not positioned because of a prior error, values are not assigned to the host-variable-array, and an error occurs.
If the current rowset contains fewer rows than specified implicitly or explicitly in the FOR n ROWS clause, values are not assigned to host-variable-arrays after the last row, and a warning is returned.
Positions the cursor on the rowset beginning at the row of the result table that is indicated by the ABSOLUTE or RELATIVE specification, and returns data if a target is specified.
host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be an exact numeric type with scale zero, and must not include an indicator variable. The possible data types for the host variable are DECIMAL(n,0) or integer, where the DECIMAL data type is limited to DECIMAL(18,0). If a constant is specified, the value must be an integer.
If a row of the result table would be after the last row or before the first row of the result table, values are not assigned to host-variable-arrays for that row and a warning is returned.
If k=0, an error occurs. If k>0, the first row of the rowset is row k. If k<0, the rowset is positioned on the ABS(k) rows from the bottom of the result table. Assume that ABS(k) is equal to the number of rows for the rowset and that there are enough row to return a complete rowset:
If k=0 and the FOR n ROWS clause does not specify a number different from the number most recently specified implicitly or explicitly for this cursor, then the position of the cursor does not change (that is, "RELATIVE ROWSET 0" is the same as "CURRENT ROWSET"). If k=0 and the FOR n ROWS clause specifies a number different from the number most recently specified implicitly or explicitly for this cursor, then the cursor is repositioned on the specified number of rows, starting with the first row of the current rowset. Otherwise, RELATIVE repositions the cursor so that the first row of the new rowset cursor position is on the row in the result table that is either k rows after the first row of the current rowset cursor position if k>0, or ABS(k) rows before the first row of the current rowset cursor position if k<0. Assume that ABS(k) is equal to the number of rows for the resulting rowset
When ROWSET STARTING AT RELATIVE -n is specified and there are not enough rows between the current position of the cursor and the beginning of the result table to return a complete rowset:
If a row of the rowset reflects a hole, If a row of the rowset reflects a hole, the following actions occur:
If a hole is detected and at least one indicator variable is not provided, an error is returned. If a row of the rowset is unknown, values are not assigned to host variable arrays for that row, and an error is returned. If a row of the rowset would be after the last row or before the first row of the result table, values are not assigned to host-variable-arrays for that row, and a warning is returned.
Identifies the cursor to be used in the fetch operation. The cursor name must identify a declared cursor, as explained in the description of the DECLARE CURSOR statement in "DECLARE CURSOR" in topic 5.42, or an allocated cursor, as explained in "ALLOCATE CURSOR" in topic 5.2. When the FETCH statement is executed, the cursor must be in the open state.
If a single-row-fetch or multiple-row-fetch clause is not specified, the cursor position is adjusted as specified, but no data is returned to the user.
When single-row-fetch is specified, SENSITIVE or INSENSITIVE can be specified though there is a default. The default depends on the sensitivity of the cursor. If the sensitivity of the cursor is INSENSITIVE, then the default is INSENSITIVE. If the effective sensitivity of the cursor is SENSITIVE DYNAMIC or SENSITIVE STATIC, then the default is SENSITIVE. The single-row-fetch or multiple-row-fetch clause must not be specified when the FETCH BEFORE or FETCH AFTER option is specified. They are required when FETCH BEFORE or FETCH AFTER is not specified. If an individual fetch operation causes the cursor to be positioned or to remain positioned on a row if there is a target specification, the values of the result table are assigned to host variables as specified by the single-fetch-clause.
Specifies a list of host variables. Each host-variable must identify a structure or variable that is described in the application program in accordance with the rules for declaring host structures and variables. A reference to a structure is replaced by a reference to each of its variables. The first value in the result row is assigned to the first host variable, the second value to the second host variable, and so on.
Identifies an SQLDA that contains a valid description of the host output variables. Result values from the associated SELECT statement are returned to the application program in the output host variables.
Before the FETCH statement is processed, you must set the following fields in the SQLDA:
A REXX SQLDA does not contain this field.
The SQLDA must have enough storage to contain all SQLVAR occurrences. Each SQLVAR occurrence describes a host variable or buffer into which a value in the result table is to be assigned. If LOBs are present in the results, there must be additional SQLVAR entries for each column of the result table. If the result table contains only base types and distinct types, multiple SQLVAR entries are not needed for each column. However, extra SQLVAR entries are needed for distinct types as well as for LOBs in DESCRIBE and PREPARE INTO statements.
SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN.
Retrieves multiple rows of data from the result table of a query. The FOR n ROWS clause of the FETCH statement controls how many rows are returned on a single FETCH statement. The fetch orientation determines whether the resulting cursor position (for example, on a single row, rowset, before, or after the result table). Fetching stops when an error is returned, all requested rows are fetched, or the end of data condition is reached.
Fetching multiple rows of data can be done with scrollable or non-scrollable cursors. The operations used to define, open, and close a cursor used for fetching multiple rows of data are the same as for those used for single row FETCH statements.
If the BEFORE or AFTER option is specified, neither single-row-fetch or multiple-row-fetch can be specified.
host-variable or integer-constant is assigned to an integral value k. If a host variable is specified, it must be an exact numeric type with a scale of zero and must not include an indicator variable. Furthermore, k must be in the range, 0<k<=32767.
This clause must not be specified if a row-positioned fetch-orientation clause was specified. This clause must also not be specified for a cursor that is defined without rowset access.
If a rowset fetch orientation is specified and this clause is not specified, the number of rows in the resulting rowset is determined as follows:
For result set cursors, the number of rows for a rowset cursor position, established in the procedure that defined the rowset, is not inherited by the caller when the rowset is returned. Use the FOR n ROWS clause on the first rowset FETCH statement for the result set in the calling program to establish the number of rows for the cursor. Otherwise, the rowset consists of a single row.
The cursor is positioned on the row or rowset that is specified by the orientation clause (for example, NEXT ROWSET), and those rows are fetched if a target is specified. After the cursor is positioned on the first row being fetched, the next k-1 rows are fetched. Fetching moves forward from the cursor position in the result table and continues until the end of data condition is returned, k-1 rows have been fetched, or an assignment error is returned.
The resulting cursor position depends on the fetch orientation that is specified:
The values from each individual fetch are placed in data areas that are described in the INTO or USING clause. If a target specification is provided for a rowset-positioned FETCH, the host variable arrays must be specified as the target specification, and the arrays must be defined with a dimension of 1 or greater. The target specification must be defined as an array for a rowset-positioned FETCH even if the number of rows that is specified implicitly or explicitly is one.
Identifies for each column of the result table a host-variable-array to receive the data that is retrieved with this FETCH statement. If the number of host-variable-arrays is less than the number of columns of the result table, the SQLWARN3 field of the SQLCA is set to 'W'. No warning is given if there are more host-variable-arrays than the number of columns in the result table.
Each host-variable-array must be defined in the application program in accordance with the rules for declaring an array. A host-variable-array is used to return the values for a column of the result table. The number of rows to be fetched must be less than or equal to the dimension of each of the host-variable-arrays.
An optional indicator array can be specified for a host-variable-array. It should be specified if the SQLTYPE of any SQLVAR occurrence indicates that the column of the result table is nullable. Additionally, if an operation may result in null values, such as an UPDATE operation that results in a hole, is performed in the application, an indicator array should be specified. Otherwise an error occurs if null values are encountered. The indicators are returned as small integers.
Identifies an SQLDA that must contain a valid description of zero or more host-variable-arrays or buffers into which the values for a column of the result table are to be returned.
Before the FETCH statement is processed, you must set the following fields in the SQLDA:
The SQLVAR entry for the number of rows must also contain a flag value. The number of rows to be fetched must be less than or equal to the dimension of each of the host variable arrays.
You set the SQLDATA and SQLIND pointers to the beginning of the corresponding arrays. The SQLDA must have enough storage to contain all SQLVAR occurrences. Each SQLVAR occurrence describes a host-variable-array or buffer into which the values for a column in the result table are to be returned. If any column of the result table is a LOB, two SQLVAR entries must be provided for each SQLVAR, and SQLN must be set to two times the number of SQLVARS. SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN.
The host-variables parameter identifies a list of COBOL variables defined in working storage. The following is an example of a FETCH statement. We assume that custbl is a cursor previously defined in a DECLARE CURSOR statement.
EXEC SQL
FETCH custbl
INTO :CNO, :CNAME
END-EXEC