JDBC Programmer Reference › Scrollable Result Sets › CA IDMS Result Sets

CA IDMS Result Sets

The CA IDMS Server r17 JDBC driver supports TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets. It supports the concurrency option CONCUR_READ_ONLY.

Although the CA IDMS JDBC driver does not directly support TYPE_SCROLL_SENSITIVE and CONCUR_ UPDATABLE result sets, these options are available when used with an appropriate javax.sql.RowSet implementation.

TYPE_SCROLL_INSENSITIVE

The driver implements TYPE_SCROLL_INSENSITIVE result sets with a memory based client-side cache. Values are added to the result set as the rows are fetched, and are not are not changed to reflect changes by other transactions or other statements within the same transaction when the application positions the cursor on a cached row.

The Reference Implementation of the javax.sql.JdbcRowSet interface, com.sun.rowset.JdbcRowSetImpl, is included in the Java 1.6 run time library. When used with an IdmsResultSet object it supports a row set that is TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY.

TYPE_SCROLL_SENSITIVE and CONCUR_UPDATABLE

The Reference Implementation of the javax.sql.CachedRowSet interface, com.sun.rowset.CachedRowSetImpl, is included in the Java 1.6 run time library. When used with an IdmsResultSet object it can support a row set that is TYPE_SCROLL_SENSITIVE and CONCUR_UPDATABLE (or any combination of type and concurrency).

There are some restrictions on the use of this feature.

The query statement used for a CONCUR_UPDATABLE row set must satisfy the CA IDMS criteria for an updateable cursor:

• Only one table can be specified in the FROM clause.
• The query cannot contain derived or aggregate columns.
• The query cannot contain UNION, ORDER BY, or GROUP BY.
• The query must select all NOT NULL columns that have no default.
• The query statement used for a TYPE_SCROLL_SENSITIVE result set must satisfy a subset of these criteria:
• The query cannot contain derived or aggregate columns.
• The query cannot contain UNION, ORDER BY, or GROUP BY.

Note that the driver does not detect that the query cannot be used for the requested type or concurrency. If the RowSet implementation detects this, it demotes the result set to a supported type or concurrency.

The query statement should not include a FOR UPDATE clause. This does not prevent deadlocks and can result in a less efficient access strategy when the clause does not specify specific columns.

A CA IDMS cursor can move forward only. An application can fetch multiple rows in a single request to improve performance. When an application uses this BULK FETCH feature, CA IDMS considers the last row in the buffer to be current of cursor, and only this row has a read lock. CA IDMS has no way to specify that the current row should have an update lock. The FOR UPDATE clause only affects the access path generated by the optimizer, not the locking strategy.

The application cannot use a scrollable or updateable row set for positioned updates.

Since the driver uses a memory-based cache, applications should not use a TYPE_SCROLL_INSENSITIVE result set or row set for large result sets. TYPE_SCROLL_SENSITIVE row sets can be used for fairly large result sets but may perform more slowly in applications that access a high percentage of the fetched rows.

When the fetch direction hint for a statement or result set is set to FETCH_REVERSE, the driver considers the result set holdability to be CLOSE_CURSORS_AT_COMMIT, no matter what has been set for other statements. The driver attempts to use COMMIT instead of COMMIT CONTINUE in order to reduce resource use and contention in CA IDMS.

See the CA IDMS javadoc, installed in the HTNL Bookshelf for detailed information on the CA IDMS JDBC method implementations.