What are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

Question

I am converting an Informix SE to an IDS database. The programs I have use ODBC System DSNs to communicate with the database.

For SE, we use the sesoctcp protocol. The program code, would open a Recordset from the DSN, loop through each record and save each record to a local AccessDB (execute INSERT INTO), and then perform a MoveNext on the Recordset.

For IDS, we use the onsoctcp protocol. With the same code, the program throws an error on MoveNext.

I believe the Recordset is being closed... I added code to perform a MoveLast/MoveFirst on the Recordset before the loop and the MoveNext does not throw an error. This change will require me to update potentially 400 programs.

On the other hand, I can forego the code changes and instead change the DSN Cursor Behavior to "1 - Preserve" and the MoveNext does not throw an error. My limited understanding is that if I leave it on "0 - close", this tells the driver to decide when to close the recordset instead of letting the program decide.

The only documentation I could find for Cursor Behavior doesn't explain the values: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

So... what are the pitfalls of using "Preserve" for Cursor Behavior in Informix ODBC DSN?

Answer 1

Based on the research below, I feel confident that I can recommend to change the Cursor Behavior to Preserve, though at a potential performance hit. I believe the performance loss with Preserve is minimal compared to the potential performance hit on a large recordset with code that performs a .MoveLast/.MoveFirst to populate the recordset.

---

Useless info From IBM: Flag for cursor behavior when a commit or rollback transaction is called Possible values are: • 0=close cursor • 1=preserve cursor https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.odbc.doc/ids_odbc_069.htm

Performance risks From DataDirect for ODBC: Purpose Determines whether cursors will be preserved or closed at the end of transactions. Valid Values 0 | 1 Behavior If set to 1 (Enabled), cursors are held at their current position when transactions end. This value may slow the performance of your database operations. If set to set to 0 (Disabled), cursors are closed at the end of transactions.

http://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/cursor-behavior.html

Performance risks From Pace University for connecting to Informix for administering ColdFusion Server: Preserve cursors at the end of each transaction -- Determines whether cursors will be preserved or closed at the end of each transaction. Enable this attribute if you want cursors to be held at the current position when the transaction ends. Enabling this option may impact the performance of your database operations. http://csis.pace.edu/~ctappert/cs615-02/support/Help/Administering_ColdFusion_Server/admin0416.htm

Required Preserve For ArcGIS (popular software): Cursor Behavior must be set to 1 - Preserve because ArcGIS expects Informix cursors to remain open and active following a database COMMIT operation. Setting the Cursor Behavior to 0 - Close will have undesirable effects during data insertions and updates. http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-informix/set-odbc-informix.htm

Required Preserve For TOTVS DBACCESS (unknown software): … determines the behavior of the database in relation to the automatic closing of cursors when instructions COMMIT WORK or ROLLBACK WORK are submitted. If the configuration is not proper [during Fetch operations], Cursors may return EOF before the end of data recovery

http://tdn.totvs.com/display/tecen/DBAccess+-+Informix+-+ODBC+-+Cursor+Behavior