Programming Languages on Power

 View Only

 SQL Stored procedure rollback issue

  • SQL
andres cervantes's profile image
andres cervantes posted Tue July 01, 2025 01:22 PM

We are rewriting our billing system from legacy RPG code into SQL Stored procedure. The main proc runs within an Atomic transaction. However, it calls several other procedures that are not marked as Atomic. As a result, when an error occurs within the main proc or the others, the updates and inserts that were performed within the sub-procedures do not rollback.


#SQL
Kent Milligan's profile image
Kent Milligan posted Tue July 01, 2025 04:19 PM

Sounds like the stored procedures that your Atomic SQL procedure is calling are created with the COMMIT(*NONE) setting.  As a result, the changes they make are not under transaction control - each change is essentially automatically committed.

These subprocedures should be created with the same COMMIT setting as the Atomic SQL procedure.  I believe the default is *CHG, but you'll want to review what level of commitment control that your applications require.  Once you've figured that out, the COMMIT setting can be added to the stored procedures with the SET OPTION clause.

SET OPTION COMMIT = *CHG, ... 


#SQL

Related Content