Using scrollable cursors efficiently

Scrollable cursors are a valuable tool for writing applications such as screen-based applications, in which the result table is small and you often move back and forth through the data.

To get the best performance from your scrollable cursors:

• Determine when scrollable cursors work best for you.

  Scrollable cursors require more Db2 processing than non-scrollable cursors. If your applications require large result tables or you only need to move sequentially forward through the data, use non-scrollable cursors.

• Declare scrollable cursors as SENSITIVE only if you need to see the latest data.

  If you do not need to see updates that are made by other cursors or application processes, using a cursor that you declare as INSENSITIVE requires less processing by Db2.

  If you need to see only some of the latest updates, and you do not need to see the results of insert operations, declare scrollable cursors as SENSITIVE STATIC.

  If you need to see all of the latest updates and inserts, declare scrollable cursors as SENSITIVE DYNAMIC.

• To ensure maximum concurrency when you use a scrollable cursor for positioned update and delete operations, specify ISOLATION(CS) and CURRENTDATA(NO) when you bind packages and plans that contain updatable scrollable cursors.
• Use the FETCH FIRST n ROWS ONLY clause with scrollable cursors when it is appropriate.
  In a distributed environment, when you need to retrieve a limited number of rows, FETCH FIRST n ROWS ONLY can improve your performance for distributed queries that use DRDA access by eliminating unneeded network traffic.

  In a local environment, if you need to scroll through a limited subset of rows in a table, you can use FETCH FIRST n ROWS ONLY to make the result table smaller.

• In a distributed environment, if you do not need to use your scrollable cursors to modify data, do your cursor processing in a stored procedure.
  Using stored procedures can decrease the amount of network traffic that your application requires.
• In a work file database, create table spaces that are large enough for processing your scrollable cursors.
  Db2 uses declared temporary tables for processing the following types of scrollable cursors:

  • SENSITIVE STATIC SCROLL
  • INSENSITIVE SCROLL
  • ASENSITIVE SCROLL, if the cursor sensitivity in INSENSITIVE. A cursor that meets the criteria for a read-only cursor has an effective sensitivity of INSENSITIVE.
• Commit changes often for the following reasons:
  • You frequently need to leave scrollable cursors open longer than non-scrollable cursors.
  • An increased chance of deadlocks with scrollable cursors occurs because scrollable cursors allow rows to be accessed and updated in any order. Frequent commits can decrease the chances of deadlocks.
  • To prevent cursors from closing after commit operations, declare your scrollable cursors WITH HOLD.
• Use the following methods to prevent false out-of-space indications:
  1. Check applications such that they commit frequently.
  2. Close sensitive scrollable cursors that as soon as they are no longer needed.
  3. Remove WITH HOLD option for the sensitive scrollable cursor, if possible.
  4. Isolate LOB table spaces in a dedicated buffer pool in the data sharing environment.
  While sensitive static scrollable cursors are open against a table, Db2 disallows reuse of space in that table space to prevent the scrollable cursor from fetching newly inserted rows that were not in the original result set. Although this is normal, it can result in a seemingly false out-of-space indication. The problem can be more noticeable in a data sharing environment with transactions that access LOBs.

  In addition to the space reuse issue, the use of a sensitive static scrollable cursor in a data sharing environment might also result in lock contention on INSERT statements if the inserted objects are in the same buffer pool. This situation applies regardless of whether the objects have sensitive static scrollable cursors, and regardless of whether the objects contain any LOB columns. You can minimize this problem by isolating objects that have a large volume of insert activity so that they are in a dedicated buffer pool within the data sharing environment.

• Do not specify a sensitive static scrollable cursor when index access is needed for an expression-based index.