Once you declare or allocate a cursor and open it with an OPEN statement, you can extract data and perform other operations such as referencing and updating. However, after the cursor is opened, whether or not operations that do not use a cursor can be performed depends on the specification of the FOR READ ONLY or FOR UPDATE clause and whether or not an operation that uses a cursor (updating or deletion) is performed. The FOR READ ONLY and FOR UPDATE clauses are specified in the cursor declaration and in the SELECT statement identified by either the SQL statement identifier specified in the cursor declaration or the extended statement name specified in the cursor allocation.

Table 3-23 shows the relationships between cursor updatability and operations that do not use a cursor. When the SQL optimization option for suppressing creation of update-SQL work tables is specified, the restrictions on operations that do not use a cursor are relaxed.

Table 3-23 Relationships between cursor updatability and operations that do not use a cursor

Condition				Operation that does not use a cursor
Specification of cursor updatability		Process that uses a cursor		SQL optimization option for suppressing creation of update-SQL work tables not applied				SQL optimization option for suppressing creation of update-SQL work tables applied and index key value no-lock facility used
		UD	Del	Ret	UD	Del	Add	Ret	UD	Del	Add
Static SQL	FOR READ ONLY clause specified	NP	NP	Y	Y	Y	Y	Y	Y	Y	Y
	FOR UPDATE OF column name specified	NP	NP	Y	CU	N	N	Y	Y2	Y2	Y2
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	CU	N	N	Y	Y2	Y2	Y2
		P	P	Y	Y	Y	Y	Y	Y	Y	Y
	FOR UPDATE clause specified	NP	NP	Y	Y	Y	Y	Y	Y	Y	Y
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	Y	Y	Y	Y	Y	Y	Y
		P	P	Y	Y	Y	Y	Y	Y	Y	Y
	None of the above1	NP	NP	Y	N	N	N	Y	Y2	Y2	Y2
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	Y	Y	Y	Y	Y	Y	Y
		P	P	Y	Y	Y	Y	Y	Y	Y	Y
Dynamic SQL	FOR READ ONLY clause specified	NP	NP	Y	Y	Y	Y	Y	Y	Y	Y
	FOR UPDATE OF column name specified	NP	NP	Y	CU	N	N	Y	Y2	Y2	Y2
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	CU	N	N	Y	Y2	Y2	Y2
		P	P	Y	Y	Y	Y	Y	Y	Y	Y
	FOR UPDATE clause specified	NP	NP	Y	Y	Y	Y	Y	Y	Y	Y
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	Y	Y	Y	Y	Y	Y	Y
		P	P	Y	Y	Y	Y	Y	Y	Y	Y
	None of the above	NP	NP	Y	N	N	N	Y	Y2	Y2	Y2
		NP	P	Y	Y	Y	Y	Y	Y	Y	Y
		P	NP	Y	Y	Y	Y	Y	Y	Y	Y
		P	P	Y	Y	Y	Y	Y	Y	Y	Y

The abbreviations in the column headers denote the following:

UD: Updating

Del: Deletion

Ret: Retrieval

Add: Addition

Legend:

P: Performed

NP: Not performed

Y: Can be performed.

CU: Specified column can be updated.

N: Cannot be performed.

¹ Specification of the FOR UPDATE clause is assumed when the same post source contains an update or deletion in which the CURRENT OF cursor name is specified.

² If the index being used in the retrieval that uses the cursor is updated, the retrieval results that were obtained with the cursor are not guaranteed. An example of this case and countermeasures are shown as follows.

Example:

 
CREATE INDEX X1 ON T1(C1);
DECLARE CR1 CURSOR FOR SELECT C1 FROM T1 WHERE C1>0;
 

The cursor that was declared is used to execute the following FETCH and UPDATE statements repeatedly:

 
FETCH CR1 INTO :XX;
UPDATE T1 SET C1=10;
 

The line that was updated to C1=10 is retrieved again.

Countermeasure:

Implement one of the following countermeasures:

• Change the search conditions so that the update value in the UPDATE statement does not satisfy the search condition.
  Example: WHERE C1>0 AND C1 <>10
  
• Delete the problem-causing column from the configuration columns of the index used in the retrieval. However, note that when a configuration column of the index is deleted, the performance may drop if the column was one that significantly narrowed the search described by the search conditions. Also note that deleting a configuration column of the index increases the number of index key duplications and may increase the incidence of lock-release waiting and deadlock. Therefore, check the potential effects of this countermeasure thoroughly before applying it.

To use multiple cursors for updating the same table simultaneously, you must specify all columns to be updated in the FOR UPDATE clause of the individual cursor declarations or dynamic SELECT statements. For example, to use cursor 1 to update column 1 and cursor 2 to update column 2, specify both column 1 and column 2 in the FOR UPDATE clause when you declare cursor 1 and cursor 2. If only column 1 is specified for cursor 1 or only column 2 is specified for cursor 2, an error occurs during updating.