Similar Ideas

Links

Tags

Recent tweets

Using autonomous transactions

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 registersE.g. a SET PATH statement is non transactional.

Global, module, PL/SQL package, or local variables, and routine parametersVariables 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 ROWSGlobal 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 objectsYou can record information for posterity.

Full transactional control within the transactionInstead 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 OR REPLACE TRIGGER audit_emp AFTER UPDATE OR DELETE OR INSERT ON emp REFERENCING NEW AS n OLD AS o FOR EACH ROWBEGIN CALL audit(CASE WHEN UPDATING THEN 'Update' WHEN INSERTING THEN 'Insert' WHEN DELETING THEN 'Delete' END || ': ' || COALESCE(n.empid, o.empid));END;

/

COMMIT;

In DB2 AUTONOMOUS is a procedure property.
So that's why I have encapsulated the transaction into a procedure and then called that from the trigger.Also note that there is no need to COMMIT. If the procedure does not exit with an exception the autonomous transaction will be implicitly committed.An autonomous procedure can be composed of any size and include any number of transactions within its body.

Now, let's try if it works. The interesting parts, of course are the rollbacks.