Problems with ambiguous cursors

A cursor is considered ambiguous if Db2 cannot tell whether it is used for update or read-only purposes.

If the cursor appears to be used only for read-only, but dynamic SQL could modify data through the cursor, then the cursor is ambiguous. If you use CURRENTDATA to indicate an ambiguous cursor is read-only when it is actually targeted by dynamic SQL for modification, you'll get an error. Ambiguous cursors can sometimes prevent Db2 from using lock avoidance techniques. However, misuse of an ambiguous cursor can cause your program to receive a -510 SQLCODE, meaning:

• The plan or package is bound with CURRENTDATA(NO)
• An OPEN CURSOR statement is performed before a dynamic DELETE WHERE CURRENT OF statement against that cursor is prepared
• One of the following conditions is true for the open cursor:
  • Lock avoidance is successfully used on that statement.
  • Query parallelism is used.
  • The cursor is distributed, and block fetching is used.

In all cases, it is a good programming technique to eliminate the ambiguity by declaring the cursor with either the FOR FETCH ONLY or the FOR UPDATE clause.