Configuring databases for global coordination of transactions

If your message flow interacts with a database, and you want to globally coordinate the updates made to the database with other actions within the message flow, configure your databases for globally coordinated transactions.

About this task

If you have configured an integration node that specifies a local MQ queue manager, you can set up globally coordinated transactions for the message flows that are managed by the integration node. The specified queue manager then performs the transaction manager role.

If you restart a database while the integration node is still running, you must also restart the integration node. The integration node cannot detect that the database has stopped, and IBM® MQ therefore retains its old connections to the database. When the database starts again, the integration node tries, and fails, to use these connections.

To configure databases for coordinated message flows, follow the instructions relevant to your database manager:

Configuring Db2 for global coordination of transactions

Before you begin

You must complete these steps for databases that you connect to with an ODBC or a JDBC connection.

You must have database administrator (DBA) privileges to perform the following tasks.

About this task

To configure Db2 database instances for global coordination of transactions:

Procedure

Windows systems only: for each 32-bit DB2 instance that is involved in the global coordination, run the following commands to set the Transaction Process Monitor name (TP_MON_NAME) to MQ:
```
db2 update dbm cfg using TP_MON_NAME MQ
db2stop
db2start
```
If you are connecting an integration node on a distributed platform to a Db2 instance on z/OS®, you must configure DB2 Connect to enable support for global coordination.
Ensure that you have already configured a Db2 alias to represent the database by using DB2 Connect.
Perform the following tasks on the system that hosts the integration node:
1. Turn on the Connection Concentrator by configuring the Db2 database manager configuration parameters so that the value of the MAX_CONNECTIONS parameter is greater than the value of the MAX_COORDAGENTS parameter:
```
db2 update dbm cfg using MAX_CONNECTIONS max_connections_value
```
  where max_connections_value is greater than the existing value of the MAX_COORDAGENTS parameter.
2. Define the SPM name as the name of the system that hosts the integration node:
```
db2 update dbm cfg using SPM_NAME host_name
```
  where host_name is the TCP/IP name of the system that hosts the integration node.
3. Stop, then restart Db2 on the system that hosts the integration node to apply the changes:
```
db2stop
db2start
```
  DB2 Connect is now configured to enable global coordination of message flows that are deployed to the integration node (on a distributed platform) and that access Db2 on z/OS.

Results

The Db2 database instances are now configured for global coordination.

What to do next

See Configuring ODBC connections for globally coordinated transactions.

Configuring Oracle for global coordination of transactions

Before you begin

You must complete these steps for databases that you connect to with an ODBC connection only.

You must have database administrator (DBA) privileges to perform the following tasks.

About this task

To configure Oracle databases for global coordination of transactions:

Procedure

Ensure that the JAVA_XA package is present on the Oracle database by using, for example, the following Oracle SQLPLUS command:
```
	describe JAVA_XA;
```
For more information, see the Oracle product documentation.
Ensure that the user ID that the integration node uses to access the database has the necessary Oracle privileges to access the DBA_PENDING_TRANSACTIONS view.
You can grant the required access by using, for example, the following Oracle SQLPLUS command:
```
	grant select on DBA_PENDING_TRANSACTIONS to userid;
```
If more than one user ID is involved (for example, if IBM App Connect Enterprise and IBM MQ run under different user IDs), you also need the privilege FORCE ANY TRANSACTION.

Results

The Oracle databases are now configured for global coordination.

What to do next

See Configuring ODBC connections for globally coordinated transactions.

Configuring Sybase for global coordination of transactions

Before you begin

You must complete these steps for databases that you connect to with an ODBC connection only.

You must have database administrator (DBA) privileges to perform the following tasks.

Procedure

To configure Sybase databases for global coordination of transactions, ensure that the user ID that the integration node uses to access the database has been granted the Sybase role of dtm_tm_role.

Results

The Sybase databases are now configured for global coordination.

What to do next

See Configuring ODBC connections for globally coordinated transactions.