The cursor types that the DB2ResultSet instance can use.
- Namespace:
- IBM.Data.DB2
- Assembly:
- IBM.Data.DB2 (in
IBM.Data.DB2.dll)
Syntax
[Visual Basic]
<Serializable>
Public Enum DB2CursorType
[C#]
[Serializable]
public enum DB2CursorType
[C++]
[Serializable]
__value public enum DB2CursorType
[JScript]
public
Serializable
enum DB2CursorType
Members
Member name |
Description |
ForwardOnly |
The DB2ResultSet to be created will use a forward-only,
non-scrollable, insensitive, and non-updatable cursor. |
Static |
The DB2ResultSet to be created will use a scrollable,
insensitive, and non-updatable cursor. |
Keyset |
The DB2ResultSet to be created will use a scrollable,
sensitive, and updatable cursor. Delete holes are visible to cursor. |
Dynamic |
The DB2ResultSet to be created will use a scrollable,
sensitive, dynamic, and updatable cursor. Delete holes are not visible
to the cursor. |
Data server restrictions
Informix® supports only the
Forward-only and Static cursor types. If the select does not have
either the primary-key, serial, or row-id the cursor will be read-only.
Remarks
The following table represents
the
DB2ResultSet option settings that are assigned
for particular
DB2CursorType values:
DB2CursorType value |
DB2ResultSet.Scrollable |
DB2ResultSet.Updatable |
DB2ResultSet.Sensitive |
DB2ResultSet.SkipDeleted |
Forward-only |
False |
False |
False |
False |
Static |
True |
False |
False |
False |
Keyset |
True |
True |
True |
False |
Dynamic |
True |
True |
True |
True |
When creating a DB2ResultSet instance
with the DB2Command.ExecuteResultSet method,
you can use either the DB2CursorType enumeration
or the DB2ResultSetOptions enumeration to define
the capabilities of the DB2ResultSet instance.
Following are details on the
four DB2ResultSet.CursorType modes supported
by the
DB2ResultSet class:
- ForwardOnly
- This cursor type is unidirectional and requires the least amount
of overhead processing of all the cursor types. A DB2ResultSet created
with this cursor type is functionally equivalent to a DB2DataReader.
- Static
- This is a read-only cursor. Once it is created, no rows can be
added or removed, and no values in any rows will change. The cursor
is not affected by other applications accessing the same data. The
isolation level of the statement 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 make changes
to the underlying data. Keyset cursors are based on row keys. When
a keyset-driven cursor is first opened, it stores the keys in a keyset
for the life of the entire result set. The keyset 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 keyset
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. Following are the various kinds of changes to the underlying
data, and corresponding keyset cursor behavior:
- Updated values in existing rows: Keyset cursors will reflect updates
made to existing rows. Because it fetches a row from the database
each time it is required, keyset-driven cursors always detect changes
made by themselves and other cursors.
- Deleted rows: Keyset cursors will reflect deleted rows. If a selected
row in the rowset is deleted after the keyset is generated, it will
appear 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
has been explicitly requested, then pessimistic locking will be used
and locks will be held as soon as the row is read. (This level of
locking is only supported for DB2 on Linux, UNIX and Windows servers.)
When an update or delete is attempted, the database server compares
the previous values 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 should query the values again and
re-issue the update or delete, if it is still applicable.
Rows
added to a keyset cursor-driven DB2ResultSet are inserted into the table
on the server, but are not added to the server's result set. Therefore,
these rows are not updatable nor are they sensitive to changes made
by other transactions. The inserted rows will appear, however, to
be part of the result set, since they are cached on the client. Any
triggers that apply to the inserted rows will appear to the application
as if they have not been applied. 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 instances
that use keyset cursors are only supported when accessing data servers
that support scrollable cursors. Currently this includes the following:
- DB2 UDB for z/OS Version 8
- DB2® for z/OS® Version
9 and later
- DB2 UDB for Linux, UNIX, and Windows Version 8
- DB2 for Linux, UNIX, and Windows Version
9 or later
- Dynamic
- DB2ResultSet instances can only 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 inserted by other cursors. Dynamic
cursors omit deleted rows from the result set, and as a result, cannot
position themselves on the delete holes, 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. This can be the primary key or any other unique
key.
DB2ResultSet instances using dynamic cursors
are only supported when accessing data servers that support dynamic
cursors. At this time, the dynamic cursors are supported only by the DB2 for z/OS servers.
Version information
- Last update
- This topic was last updated for: IBM DB2 Version 9.7
- .NET Framework version
- Supported in: 2.0, 3.0,
3.5, and 4.0
- IBM Data Server Client
- Supported in: IBM® DB2 Version
9 through Version 9.7