The effect of sorts on OPEN CURSOR

The type of sort processing required by the cursor affects the amount of time it can take for Db2 to process the OPEN CURSOR statement.

Begin program-specific programming interface information. This information outlines the effect of sorts and parallelism on OPEN CURSOR.

Without parallelism:
  • If no sorts are required, then OPEN CURSOR does not access any data. It is at the first fetch that data is returned.
  • If a sort is required, then the OPEN CURSOR causes the materialized result table to be produced. Control returns to the application after the result table is materialized. If a cursor that requires a sort is closed and reopened, the sort is performed again.
  • If a RID sort is performed, but no data sort, then it is not until the first row is fetched that the RID list is built from the index and the first data record is returned. Subsequent fetches access the RID pool to access the next data record.
With parallelism:
  • At OPEN CURSOR, parallelism is asynchronously started, regardless of whether a sort is required. Control returns to the application immediately after the parallelism work is started.
  • If a RID sort is performed, but no data sort, then parallelism is not started until the first fetch. This works the same way as with no parallelism. End program-specific programming interface information.