XA datasources with #WebSphere and #Oracle

When using XA data sources with Websphere and Oracle we must grant to the Oracle user rights to perform XA recovery.

STEP 1: Create an XA datasource

AdminTask.createJDBCProvider('[-scope Node='+ node +',Server='+serverName
+' -databaseType Oracle -providerType "Oracle JDBC Driver" -implementationType "XA data source" -name "'+jdbcXAProviderName
+'" -description "Oracle JDBC Driver (XA)" -classpath [${ORACLE_JDBC_DRIVER_PATH}/ojdbc7.jar ] -nativePath "" ]')

STEP 2: Grant right to the oracle user (MY_USER).

As sysdba execute the following:

Grant access to pending transactions.

grant select on pending_trans$ to MY_USER;

Grant access rights to distributed transactions awaiting recovery.

grant select on dba_2pc_pending to MY_USER;

Grant access to unresolved transactions (either due to failure or if the coordinator has not sent a commit/rollback).

grant select on dba_pending_transactions to MY_USER;

Grant execute rights to be able to execute transaction rool-back.

grant execute on dbms_xa to MY_USER;

If you are using an Oracle 10.2.0.3 or lower JDBC driver, execute instead the following statement:

grant execute on dbms_system to MY_USER;