Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to Library

Using autonomous transactions 

Tue March 10, 2020 06:29 PM

Posted by: Serge Rielau

When I started leading the project to add Oracle's PL/SQL to DB2 it became quickly clear that perhaps amongst the top five features needed to claim a reasonable level of compatibility were autonomous transactions.

An autonomous transaction  ("#PRAGMA AUTONOMOUS" in PL/SQL speak) is a transaction which executes in total isolation from its invocation context.
While this feature has seen a lot of uptake with the Oracle to DB2  realm I have rarely seen it used in a traditional DB2 environment.
 
The reasons for that may be:
  • Poor advertising of the feature and hence a lack of awareness
  • Limited need within DB2 due to fundamental differences with Oracle.
  • The feature is more of a legacy thing that has no use in today's world.  
As so often I suspect the truth is somewhere in between.
 
In Oracle the vast majority of usages for autonomous transactions are:
  • Logging of events for either auditing or problem determination.
    I like to call this SQL based auditing  because it is implemented within the application in SQL.
    A more secure way of doing auditing provided by both DB2 and Oracle nowadays is through a separate infrastructure

  • Execution of DDL statements such as CREATE TABLE etc.
    This requires some explaining for a DB2 person. In Oracle every DDL statement performs an implicit commit.
    So if you want to execute some DDL in a transaction you are stuck.
    Thus by pushing the "EXECUTE IMMEDIATE 'CREATE TABLE ...'" statements into autonomous transactions the parent transaction is protected from the commit of the DDL.
    The user still has to deal with a logical rollback implementation in case of an exception I suppose, but at least the problem is now limited in scope.
    DB2, of course, has fully transactional DDL, so this scenario is pointless.
There are other ways to step outside a transaction using non-transactional objects. That is by using objects which do not rollback their states upon failure or ROLLBACK.
In DB2 such objects include:
  • Special registers
    E.g. a SET PATH statement is non transactional.
     
  • Global, module, PL/SQL package, or local variables, and routine parameters
    Variables are the realm of procedural logic. Any SET statement is unaffected by rollback.
    This includes ARRAYs of ROWs btw which make for a fine non transactional table.

  • Global temporary tables defined as ON ROLLBACK PRESERVE ROWS
    Global temps can be defined to not log any updates.
    Since logging is DB2's way to remember how to revert a transaction, not logging by definition makes the object non transactional.
     
  • IDENTITY and SEQUENCE values.
    Not being transactional ensures that sequences and identity achieve high levels of concurrency without sessions waiting on locks.
The objects above all have one property in common: All of  them have session private values.
That means while you may be able to step outside the current transaction none of the data can be shared past the lifetime of the session unless you find a way to back it up into a regular table.   
 
So, this is where the primary value of an autonomous transaction lies:
  • Full access to the global database objects
    You can record information for posterity.
     
  • Full transactional control within the transaction
    Instead of being non-transactional you can still perform save pointing and rollbacks within the bubble of the autonomous transaction.
But, enough of the talk. This wouldn't be a very useful BLOG without its examples.
Given that auditing seems to be the one obvious usage scenario we will implement auditing on the employee table. 
CREATE TABLE emp(empid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                                  name VARCHAR(30) NOT NULL,
 

#Db2

Statistics
0 Favorited
7 Views
0 Files
0 Shares
0 Downloads

Comments

Dhaval Singh
Fri March 04, 2022 03:48 AM

I believe triggers also work as an autonomous block of code.