Reverting to ACCESSTYPE

Although Db2 might plan to use direct row access, circumstances can cause Db2 to not use direct row access at run time.

Begin program-specific programming interface information. Db2 remembers the location of the row as of the time it is accessed. However, that row can change locations (such as after a REORG) between the first and second time it is accessed, which means that Db2 cannot use direct row access to find the row on the second access attempt. Instead of using direct row access, Db2 uses the access path that is shown in the ACCESSTYPE column of PLAN_TABLE.

If the predicate you are using to do direct row access is not indexable and if Db2 is unable to use direct row access, then Db2 uses a table space scan to find the row. This can have a profound impact on the performance of applications that rely on direct row access. Write your applications to handle the possibility that direct row access might not be used. Some options are to:

Ensure that your application does not try to remember ROWID columns across reorganizations of the table space.
When your application commits, it releases its claim on the table space; it is possible that a REORG can run and move the row, which disables direct row access. Plan your commit processing accordingly; use the returned row ID value before committing, or re-select the row ID value after a commit is issued.

If you are storing ROWID columns from another table, update those values after the table with the ROWID column is reorganized.
Create an index on the ROWID column, so that Db2 can use the index if direct row access is disabled.
Supplement the ROWID column predicate with another predicate that enables Db2 to use an existing index on the table. For example, after reading a row, an application might perform the following update:
```
EXEC SQL UPDATE EMP
SET SALARY = :hv_salary + 1200
  WHERE EMP_ROWID = :hv_emp_rowid
  AND EMPNO = :hv_empno;
```
If an index exists on EMPNO, Db2 can use index access if direct access fails. The additional predicate ensures Db2 does not revert to a table space scan.

End program-specific programming interface information.