Cursor management

The LN MSQL database driver has one parameter that influences the cursor handling: msql_retained_cursors. The cursor type is fast-forward-only with auto-fetch enabled. You cannot change the cursor type.

With the msql_retained_cursors resource the number of inactive cursors and thus the number of open cursors can be modified.

When a query has fetched all rows, a close is issued on the corresponding cursor. This means that SQL Server is notified that no additional fetches are done. This gives SQL Server the chance to free certain query resources. After the cancel, the query can be re-executed without re-opening (parsing, binding) the cursor. The driver does not know if a cursor in cancel state is re-used later. In the worst case it is not re-used, and the cursor continues to be reserved for the query. After all rows are fetched, the driver has a facility to place inactive cursors (in cancel state) in a cancel list. These inactive cursors become candidates for being assigned to a different query.

A number of inactive cursors in this list that is not available for this purpose are defined by the resource msql_retained_cursors, which defaults to 20. If the number of cursors in the cancel list is less than 20, a cursor from the cancel list cannot be assigned to a different query. If more than 20 cursors are in the cancel list, and a request for a new cursor is issued, the least recently inactivated cursor is used for this new cursor. This cursor is disassociated from the original query and assigned to a new query, which does parsing and binding on this cursor.

When re-executing the original query, the driver detects that the cursor is associated with another query gets a new cursor and re-parse and bind the query again. Increasing the value of msql_retained_cursors leads to less re-parsing and re-binding of queries, which reduces CPU resources. The number of open cursors is increased which requires more memory on the application server.