The CURRENTDATA option for local access

For local access, the CURRENTDATA option specifies whether data under a cursor must remain current with the data in the local base table.

For cursors positioned on data in a work file, the CURRENTDATA option has no effect. This effect applies only to read-only or ambiguous cursors in plans or packages bound with the ISOLATION(CS) option.

CURRENTDATA (YES)

CURRENTDATA (YES) means that the data upon which the cursor is positioned cannot change while the cursor is positioned on it. If the cursor is positioned on data in a local base table or index, then the data returned with the cursor is current with the contents of that table or index. If the cursor is positioned on data in a work file, the data that is returned by cursor matches only the current contents of the work file. The data is not necessarily current with the contents of the underlying table or index.

The following figure shows locking with CURRENTDATA(YES).

Figure 1. How an application that uses CS isolation with CURRENTDATA (YES) acquires locks
. The figure shows access to the base table. The L2 and L4 locks are released after DB2® moves to the next row or page. When the application commits, the last lock is released.
Begin figure description. A timeline that diagrams the positions of interactions between and an application that uses CS isolation and the CURRENTDATA(YES) option. End figure description.

As with work files, if a cursor uses query parallelism, data might not be current with the contents of the table or index, regardless of whether a work file is used. Therefore, for work file access or for parallelism on read-only queries, the CURRENTDATA option has no effect.

If you are using parallelism but want to maintain currency with the data, you have the following options:

  • Disable parallelism by one of the following methods:
    • Issue the following SQL statement: SET CURRENT DEGREE = '1'
    • Bind the application with the DEGREE(1) bind option.
  • Use isolation RR or RS (parallelism can still be used).
  • Use a LOCK TABLE statement (parallelism can still be used).

CURRENTDATA(NO)

This option means that the data at the cursor position can change while the cursor is positioned on it.