Considerations of using DB2 pureScale with TXSeries for Multiplatforms

Answer

The prerequisites for using DB2 pureScale 10.5 with TXSeries are as follows:

• TXSeries 7.1.0.4 Interim Service Fix 12 ( IV46495 ) or higher
• DB2 APAR IC94589
• DB2 APAR IC95532

• Set DB2NOEXITLIST=ON on TXSeries region's environment file to avoid the exit handler installation by DB2 .
• Set the DB2COMM to TCPIP from DB2 command window on the 32-bit DB2 client side.

• Client Affinity and Automatic Client Reroute (ACR) :

TXSeries supports Client Affinity and ACR facility provided by DB2 pureScale.

For additional configuration details to use automatic client reroute, see
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/c0056195.html

The following restrictions apply when TXSeries region is configured with DB2 in Client Affinity environment as a File Manager.
- ACR becomes non-seamless
- Affinity Failback becomes invalid

• Workload Balancing (WLB):

The following restrictions apply when TXSeries region is configured with DB2 in WLB environment as a File Manager.

In case of WLB configuration:
- ACR becomes non-seamless
- Transaction level balancing becomes invalid.

The decision on when to perform a commit operation on DB2 database is governed by the DB2 attribute SQL_ATTR_AUTOCOMMIT. This has the following possible values.

SQL_AUTOCOMMIT_ON: When this value is set to the attribute SQL_ATTR_AUTOCOMMIT, each SQL statement is implicitly committed after the execution. If set during the middle of any transaction, the commit will be sent immediately.

SQL_AUTOCOMMIT_DEFERRED: This value causes the CLI driver to chain a COMMIT statement to the next SQL statement that is issued by the application. Therefore, the COMMIT statement is not sent separately to the database server and the negative impact on application performance is avoided. (This has been introduced with DB2 v10.5.4).

SQL_AUTOCOMMIT_OFF: For database connections based on XA, this is the default value.

As you know, TXSeries applications can have their logical transactions spanned across multiple databases. XA connection is required to co-ordinate these databases. In such situations it is mandatory that TXSeries should be in sync with all databases for committing data individually on different databases. A transaction involving multiple databases can either PASS or FAIL. If the transaction is failing, it has to be ROLLED BACK on different databases. For this reason, TXSeries does not support any unit of work to be committed directly by the program or by the databases without being in synchronization with TXSeries.

For this reason, SQL_AUTOCOMMIT_OFF is the only supported value by TXSeries for the attribute SQL_ATTR_AUTOCOMMIT. Other two values SQL_AUTOCOMMIT_ON and SQL_AUTOCOMMIT_DEFERRED are not supported.