A cursor declared using the WITH HOLD option is closed after a COMMIT or ROLLBACK instead of remaining open.

Document ID : KB000022456
Last Modified Date : 14/02/2018
Show Technical Document Details

Symptoms: 

A SQL cursor is declared using the WITH HOLD option, e.g. EXEC SQL DECLARE C1 CURSOR WITH HOLD FOR SELECT....

After doing a COMMIT or ROLLBACK the cursor is closed instead of remaining open.

Any subsequent SQL commands against the cursor fail with SQLCODE -135 - INVALID CURSOR STATE.

 

Resolution:

The WITH HOLD cursor option is a CA Datacom SQL extension. It needs to be activated by adding the following DIAGOPTION card to the MUF startup parameters:

DIAGOPTION 0,128,ON

In addition the program must run with SQLMODE=DATACOM. This can be specified as a pre-compiler option or specified in the third option of the CA Datacom Multi-User (MUF)  SQLOPTION startup parameter:

SQLOPTION YES,0017,DATACOM,120,NO,YES

Additional Information:

 Database and System Administrator Guide section Diagnostic Startup Option (DIAGOPTION).