DB2 Version 10.1 for Linux, UNIX, and Windows

DB2ResultSetOptions Enumeration

The DB2ResultSetOptions enumeration is used to specify options for the DB2ResultSet class. The DB2ResultSetOptions Enumeration consists of bit flags, which can be combined with a bitwise operation.

Namespace:: IBM.Data.DB2
Assembly:: IBM.Data.DB2 (in IBM.Data.DB2.dll)

Syntax

[Visual Basic]
<Serializable>
Public Enum DB2ResultSetOptions
[C#]
[Serializable]
public enum DB2ResultSetOptions
[C++]
[Serializable]
__value public enum DB2ResultSetOptions
[JScript]
public
   Serializable
enum DB2ResultSetOptions

Members

Member name	Bit value	Description
None	0	No options. A forward-only cursor is specified for the DB2ResultSet object.
Scrollable	1	A scrollable cursor is specified for the DB2ResultSet object.
Sensitive	2	A sensitive, scrollable cursor that can see updates and inserts that are made by the application or other cursors is specified for the DB2ResultSet object.
SkipDeleted	8	A sensitive, scrollable cursor that skips rows that were deleted by itself or other cursors is specified for the DB2ResultSet object. With the SkipDeleted option, it is not possible to assume a position on a row that is marked as deleted (for example, when IsDeleted() returns true.)
Updatable	4	A cursor that can be updated is specified for the DB2ResultSet object.

Data server restrictions

Informix® databases support only the ForwardOnly and Static cursor types. If the SELECT statement does not have the primary key, serial, or row ID, the cursor is read-only.

Remarks

The following table represents all valid combinations of DB2ResultSetOptions enumeration values, and the resulting DB2ResultSet.CursorType:

Combination	None	Scrollable	Updatable	Sensitive	SkipDeleted	DB2ResultSet.CursorType
Combination 1	X					ForwardOnly
Combination 2		X				Static
Combination 3		X		X		Keyset
Combination 4		X	X	X		Keyset
Combination 5		X		X	X	Keyset
Combination 6		X	X	X	X	Keyset

When you create a DB2ResultSet object with the DB2Command.ExecuteResultSet method, you can use either the DB2ResultSetOptions enumeration or the DB2CursorType enumeration to define the capabilities of the DB2ResultSet instance. The previous table shows the DB2ResultSet.CursorType values that correspond to each acceptable combination of DB2ResultSetOptions values. If your application requires a DB2ResultSet object that uses a Dynamic cursor, you must run the DB2Command.ExecuteResultSet method with the DB2CursorType.Dynamic enumeration value.

Following information are details on the DB2ResultSet.CursorType modes that are supported by the DB2ResultSet class:

ForwardOnly

The ForwardOnly cursor type is unidirectional and requires the least amount of processing use among all the cursor types. A DB2ResultSet object that is created with this cursor type is functionally equivalent to a DB2DataReader object.

Static

The Static cursor is a read-only cursor. After the Static cursor is created, no rows can be added or removed, and no values in any rows change. The cursor is not affected by other applications that are accessing the same data. The isolation level of the statement that is used to create the cursor determines how the rows of the cursor are locked, if at all.

Keyset

A keyset-driven scrollable cursor can detect and change the underlying data. Keyset cursors are based on row keys. When a keyset-driven cursor is first opened, it stores the keys in a key set for the life of the entire result set. The key set is used to determine the order and set of rows that are included in the cursor. As the cursor scrolls through the result set, it uses the keys in this key set to retrieve the most recent values in the database, which are not necessarily the values that existed when the cursor was first opened. For this reason, changes are not reflected until the application scrolls to the row. Effect of various changes to the underlying data on the keyset cursor is described in the following list:

Updated values in existing rows: Keyset cursors reflects updates that are made to existing rows. Because it fetches a row from the database each time it is required, keyset-driven cursors always detect changes that are made by themselves and other cursors.
Deleted rows: Keyset cursors reflects deleted rows. If a selected row in the rowset is deleted after the keyset cursor is generated, the deleted rows are returned as a hole (an empty record) in the cursor.
Inserted rows: Keyset cursors do not reflect inserted rows. The set of rows is determined once, when the cursor is first opened. To see the inserted rows, the application must re-execute the query.

The default keyset-driven cursor is a values concurrency cursor. A values concurrency cursor results in optimistic locking, where locks are not held until an update or delete is attempted. If lock concurrency was explicitly requested, then pessimistic locking is used and locks are held as soon as the row is read. (This level of locking is only supported for DB2® for Linux, UNIX, and Windows servers). When an update or delete is attempted, the database server compares the previous values that the application retrieved to the current values in the underlying table. If the values match, then the update or delete succeeds. If the values do not match, then the operation fails. If failure occurs, the application can query the values again and reissue the update or delete, if it is still applicable.

Rows added to a keyset cursor-driven DB2ResultSet object are inserted into the table on the server, but are not added to the server result set. Therefore, these rows are not updatable nor are they sensitive to changes made by other transactions. The inserted rows are part of the result set, since they are cached on the client. The application result set does not reflect any trigger actions that take place on the server side as the result set is cached before any server side initiated change. To make the inserted rows updatable, sensitive, and to see the result of applicable triggers, the application must issue the query again to regenerate the result set.

DB2ResultSet objects that use keyset cursors are supported only when you are accessing data servers that support scrollable cursors.

Dynamic

DB2ResultSet class objects can be created with the Dynamic cursor type, by passing the DB2CursorType.Dynamic value in the DB2Command.ExecuteResultSet method. Dynamic scrollable cursors can detect all changes (inserted rows, deleted rows, and updated rows) to the result set, and can make insertions, deletions, and updates to the result set. Unlike keyset-driven cursors, dynamic cursors can detect rows that are inserted by other cursors. Dynamic cursors omit deleted rows from the result set, and as a result, cannot position themselves on the deleted rows, like keyset cursors can.

To insert, update, or delete rows in a dynamic scrollable cursor's result set, the result set must include all the columns of at least one unique key in the base table. The unique key can be the primary key or any other unique key.

DB2ResultSet objects with dynamic cursors are only supported when you are accessing data servers that support dynamic cursors. Currently, the dynamic cursors are supported only by the DB2 for z/OS® servers.