Configuring Oracle data sources

There are several requirements and restrictions for using Oracle data sources for federated two-phase commit.

Before you begin

Before you begin

Restrictions:
  • Pass-through DDL and transparent DDL directed to Oracle both fail with SQL30090 reason code 21 (ORA-2089) Regular SQL submitted in pass-through sessions works.
Requirements:
  • You must give the following privileges to all users that run two-phase commit transactions from the federated server:
    • grant select on dba_pending_transactions to USERID;
    • grant select on dba_2pc_pending to USERID;
    • grant force transaction to USERID;
  • Optionally, you can also give the following privilege to users that run two-phase commit transactions from the federated server:
    • grant force any transaction to USERID;
  • If you intend to run more than 10 two-phase commit transactions simultaneously, consider increasing the distributed_transactions parameter of your Oracle server found in the init.ora file.

About this task

Procedure

To configure an Oracle data source:

Procedure

  1. Run the CREATE SERVER, ALTER SERVER, or SET SERVER statement with the DB2_TWO_PHASE_COMMIT option set to Y.

    The Oracle wrapper automatically creates the following XA OPEN string for Oracle data sources:

    Oracle_XA=Acc=Puid/password+SesTm=0+DB=dbname+SqlNet=dblink+Threads=true

    For example:

    XA_OPEN_STRING_OPTIONS '+LogDir=/home/user/directory+DbgFl=0x7'
  2. Optional: Specify additional XA options by using the XA_OPEN_STRING_OPTIONS server option.