SQLCODE -925: COMMIT not valid in IMS, CICS or RRSAF
SQLCODE -925 (SQLSTATE 2D521) means the program issued an SQL COMMIT in an IMS or CICS transaction, or under RRSAF when Db2 isn't the only resource manager. In those environments the transaction manager owns commits, so Db2 doesn't run the COMMIT.
Other names for this code
Also written as SQL -925, SQLCODE=-925, SQLCODE = -925, db2 -925, sqlstate 2D521, SQL0925N, commit not valid in ims cics or rrsaf environment.
What it means
Under CICS or IMS, a unit of work can touch Db2, VSAM files, IMS databases and message queues at once. The transaction manager coordinates the commit for all of them, so one resource can't commit on its own. If a program issues SQL COMMIT there, Db2 returns -925 and commits nothing.
RRSAF works the same way when z/OS RRS is coordinating more than one resource manager. In that case the commit has to go through RRS.
The work isn't lost when this happens. It stays uncommitted until the program commits through the right interface or the transaction ends. A program that ignores -925 often ends up holding locks far longer than its author expected.
Stored procedures can cause it too. A procedure defined with COMMIT ON RETURN YES, called from IMS or CICS, tries to commit when it returns.
Db2 for LUW uses SQL0925N with the same SQLSTATE. There it covers environments such as CICS and some CONNECT type 2 or stored procedure cases.
Common causes
- A program written for batch, with EXEC SQL COMMIT in it, was moved into CICS or IMS without changes.
- A shared subroutine issues COMMIT and is called from both batch and online programs.
- A stored procedure defined with COMMIT ON RETURN YES is called from a CICS or IMS transaction.
- An RRSAF program issues SQL COMMIT while RRS is coordinating more than one resource manager.
How to find the problem
- Search the program and any subroutines it calls for
EXEC SQL COMMIT. - Check how the program runs, from the CICS transaction, the IMS region type or the RRSAF connection.
- For a CALL to a stored procedure, check its definition in SYSIBM.
SYSROUTINES for COMMIT ON RETURN.
How to fix it
- In CICS, replace SQL COMMIT with
EXEC CICS SYNCPOINT. - In IMS, commit through IMS, for example with a checkpoint call, or let the transaction end normally.
- Under RRSAF with several resource managers, commit through RRS with SRRCMIT.
- Alter the stored procedure to COMMIT ON RETURN NO when it's called from CICS or IMS.
- Give shared routines a flag or a separate entry point so they only issue SQL COMMIT in batch.
What it looks like
DSNT408I SQLCODE = -925, ERROR: COMMIT NOT VALID IN IMS, CICS OR RRSAF ENVIRONMENT DSNT418I SQLSTATE = 2D521 SQLSTATE RETURN CODE
IBM documentation
IBM documentation: -925. Use the documentation for your installed release.
Related codes
Questions
Is my data committed after -925?
No. Db2 does nothing for that COMMIT. The changes stay pending until the transaction manager commits or the transaction ends.
Why does the same program work in batch?
In plain batch, Db2 is the only resource manager, so SQL COMMIT is allowed. Under CICS or IMS the transaction manager owns the commit.
What's the CICS equivalent of COMMIT?
EXEC CICS SYNCPOINT. It commits Db2 changes together with any other recoverable resources in the task.