Symptom
- DB2 reports fails to refresh.
- The same reports based off DB2 used to refresh successfully, before changing the DB2 configuration: CursorHold=0
- When attempting to refresh reports connecting to an IBM DB2 data source in Crystal Reports, it fails with the error:
Database Connector Error: '[IBM][CLI Driver]Invalid cursor state. [Database Vendor Code: -99999 ]'
Environment
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- IBM DB2
Reproducing the Issue
- Set the IBM DB2 option from CursorHold=1, to 0
- In Crystal Reports, create a report off IBM DB2 data source.
- Add at least one field on the report.
- When refreshing the report, it fails with the error:
Database Connector Error: '[IBM][CLI Driver]Invalid cursor state. [Database Vendor Code: -99999 ]'
Cause
- Crystal Reports require the cursor to be preserved, and it is failing because when CursorHold is set to zero, it does not preserve the cursor.
- In more details: The IBM IBM DB2 option: CursorHold, controls the effect of a transaction completion on open cursors, and accept the following values:
1 = SQL_CURSOR_HOLD_ON, the cursors are not destroyed when the transaction is committed (default).
0 = SQL_CURSOR_HOLD_OFF, the cursors are destroyed when the transaction is committed.
And as per IBM DB2 documentation, you can set the value to zero, only if the application used:
- Does not have behavior that is dependent on the SQL_CURSOR_COMMIT_BEHAVIOR or the SQL_CURSOR_ROLLBACK_BEHAVIOR information returned via SQLGetInfo(), and
- Does not require cursors to be preserved from one transaction to the next.
Note: For more details, see the IBM DB2 documentation on the option: CursorHold.
Resolution
- Set the IBM DB2 option: CursorHold to the default value of 1 ( CursorHold=1 )
Keywords
CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016