Bookmark and Share

Recent Posts

Using Autonomous Procedures

September 22, 2015

DB2 11 introduced autonomous procedures, which give a called stored procedure the capability to COMMIT its work without committing the work in the calling program. Why would you need this? Let's consider one scenario where this feature would be useful.

Say you have a business requirement to record information in DB2 for every execution of a transaction -- even if the transaction fails and is rolled back by DB2. Traditionally, all of the work done by the transaction and any called stored procedure would be rolled back. Now with DB2 11 new function mode (NFM), you can call an autonomous procedure to record the information in DB2, and then return. Autonomous procedures execute as separate units of work that are independent from the calling of application programs.

From the DBA's perspective, the difference is very slight: Rather than use COMMIT ON RETURN YES/NO, you'd use AUTONOMOUSE. With AUTOMOUSE, the SQL procedure uses the rules of COMMIT ON RETURN(YES), but doesn't commit the work performed by the calling program.

In this scenario, separate tables are being used, and they're not being updated by the calling program. Remember that the locks in the calling program are independent of the locks in the autonomous SQL procedure. If you need to update the tables used in both the calling and called programs, you should reconsider the locking granularity or physical table design to avoid deadlock timeouts.

Autonomous procedures can be called by normal application programs, other stored procedures, user defined functions or triggers. Autonomous procedures can execute SQL statements, invoke other procedures (max 64 levels and not autonomous) and issue COMMIT/ROLLEBACK statements that apply to the SQL statements executed by nested processes within the autonomous procedure.

Read the DB2 11 for z/OS Application Programming and SQL Guide to learn more.

Have you found other uses for autonomous SQL procedures? Please share your experiences in comments.

Posted September 22, 2015 | Permalink

comments powered by Disqus