The default for a cursor is update-capable [updateable] *if* the
query that is defined is capable and a specified CLAUSE [or something
else; anything in compile\set options?] does not prevent that query from
being delete-capable.
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Statements
_i DECLARE CURSOR i_
"The DECLARE CURSOR statement defines a cursor.
...
_i Notes i_
...
_i Result table of a cursor: i_
A cursor in the open state designates a result table and a position
relative to the rows of that table. The table is the result table
specified by the SELECT statement of the cursor.
A cursor is deletable if all of the following are true:
...
# The select-statement does not contain an ORDER BY clause and does not
contain the UPDATE clause and SENSITIVE is not specified in the DECLARE
CURSOR statement
# The select-statement does not include a FOR READ ONLY clause.
# The select-statement does not include a FETCH FIRST n ROWS ONLY clause.
# The result of the outer fullselect does not make use of a temporary table.
# The select-statement does not include the SCROLL keyword, or the
SENSITIVE keyword or UPDATE clause is also specified.
# The select list does not include a DATALINK column unless a UPDATE
clause is specified.
A result column in the select list of the outer fullselect associated
with a cursor is updatable if all of the following are true:
* The cursor is deletable.
* The result column is derived solely from a column of a table or
an updatable column of a view. That is, at least one result column must
not be derived from an expression that contains an operator, scalar
function, constant, or a column that itself is derived from such
expressions.
A cursor is read-only if it is not deletable.
If the UPDATE clause is omitted, only the columns in the SELECT clause
of the subselect that can be updated can be changed.
If UPDATE is specified without a list of column names, then the list of
columns that can appear as targets in the assignment clause of
subsequent positioned UPDATE statements identifying this cursor is
determined as follows:
* If WITH EXTENDED INDICATORS is specified, all the columns of the
table or view identified in the first FROM clause of the fullselect.
* Otherwise, only the updatable columns of the table or view
identified in the first FROM clause of the fullselect.
If UPDATE is specified with a list of column names, only the columns
specified in the list of column names can be appear as targets in the
assignment clause in subsequent positioned UPDATE statements identifying
this cursor.
...
If a cursor is not going to be used in a Positioned UPDATE or DELETE
statement, it should be declared as FOR READ ONLY. ..."
And FWiW:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/gotips.htm
IBM i 7.1 Information Center -> Database -> Performance and query
optimization -> Processing queries: Overview
_i General query optimization tips i_
"... Also, specify FOR FETCH ONLY if the columns do not need to be
updated. ..."
Regards, Chuck
On 05 Apr 2013 13:21, Thomas Garvey wrote:
The program is replete with selects against the physical file, but
none have FOR READ ONLY. Isn't Read Only the default when not
specified? Any spec that fetched or tried to update where current
would fail, no?
Mark S Waterbury on Friday, April 05, 2013 2:11 PM wrote:
Are you doing SELECTs against any of those files? If so, perhaps you
need something like this:
C /EXEC SQL
C DECLARE empcsr CURSOR FOR
C SELECT empno, empnam, empsal
C FROM employee
C WHERE empdept = :dept
C FOR READ ONLY
C /END-EXEC
midrange.com
