1. Database connection pooling.
The system maintains a pool of pre-allocated database connections. When an application component opens a database connection, the system locates a free connection in the pool and allocates it to the component. When the application closes a database connection the connection is returned to the pool. This makes opening and closing database connections faster and less expensive.

2. Automatic transaction enlistment.
When a transactional application component opens a database connection, Microsoft Transaction Server automatically enlists the database connection in the component’s current transaction. This makes application development easier. The application benefits from transactions with less application programming.

3. Transparent access to databases on NT, Unix, and other platforms.
An application can update databases residing on Windows NT, Unix, IBM AS/400, IBM MVS, Tandem, and other systems. The location of the database is transparent to the application developer. This is possible because the application opens a database connection by specifying an ODBC DSN. The DSN specifies the name and location of the database. The DSN permits the application located on the Windows NT system to transparently communicate with databases distributed across a wide variety of platforms.

4. Distributed transactions.
All of the databases that an application accesses can be updated under the control of a single atomic transaction. If the transaction commits, all of the application’s updates are made permanent in all of the databases. If the transaction aborts, all of the application’s updates are rolled back in all of the databases. This is true even when the databases are distributed across a collection of Windows NT, Unix, IBM AS/400, IBM MVS, Tandem, and other systems.

1.2 Which databases fully support Microsoft Transaction Server now?

The following databases fully support Microsoft Transaction Server. This includes the ability to participate in distributed transactions.

Database	Comments
Microsoft SQL Server	Microsoft SQL Server 6.5 fully supports Microsoft Transaction Server
IBM DB2	IBM DB2 is fully supported in the Microsoft Transaction Server 2.0 release. See Using an IBM DB2 Database with Microsoft Transaction Server for more details.
Informix	The Informix Client SDK 2.20 contains the ODBC driver for Informix that fully supports Microsoft Transaction Server. For more information see http://www.informix.com/informix/products/pa/sdk220.htm or contact Informix in Menlo Park, California.
Ingres II from Computer Associates	Computer Associates International, Inc. is enhancing Ingres II to work with Microsoft Transaction Server. This product is currently in Beta testing and no release date has yet been announced. For more information contact David Thole at Computer Associates at David.Thole@cai.com
Oracle	Oracle 7.3.3 and later releases and Oracle 8 are fully supported in the Microsoft Transaction Server 2.0 release. See Using an Oracle Database with Microsoft Transaction Server for more details.
Sybase	Sybase Adaptive Server Enterprise fully supports Microsoft Transaction Server. Beta testing is now underway at several sites. For more information contact Sybase in Emeryville, California.

1.3 Which databases partially support Microsoft Transaction Server?

The following databases partially support Microsoft Transaction Server. The notes indicate the restrictions that apply.

Database	Comments
StarQuest StarSQL Pro	StarQuest’s StarSQL Pro allows Microsoft Transaction Server components to access IBM DB/2 databases on NT, OS/2, AIX, AS/400, and MVS. StarSQL supports the following platforms and versions of DB2: · DB2/MVS (DB2 for OS/390) Version 2.3 and above on MVS. · DB2/400 Version 2.2 and above on AS/400. · SQL/DS (DB2 for VM and VSE) Version 3.3 and above. · DB2/CS (DB2/UDB) Version 2.1 and above on Windows NT, OS/2, and AIX. StarSQL uses DRDA and connect to IBM platforms over TCP/IP, Microsoft SNA Server, Novell NetWare for SAA, IBM APPC Networking Services for Windows, and a variety of other transports. StarQuest’s StarSQL Pro currently supports connection pooling. Support for distributed transactions is planned for the last quarter of 1998. For more information see the Starquest web site at www.starquest.com or contact Mark Rampel at mark.rampel@starquest.com
Microsoft Access	Currently supports Microsoft Transaction Server connection pooling. Restrictions: Distributed transactions are not supported because this database does not externalize distributed transactions.
Microsoft Fox Pro	Currently supports Microsoft Transaction Server connection pooling. Restrictions: Distributed transactions are not supported because this database does not externalize distributed transactions.

1.4 Which databases will support Microsoft Transaction Server in the future?

The following databases will fully support Microsoft Transaction Server in the future. This includes support for connection pooling and the ability to participate in distributed transactions. This will allow Microsoft Transaction Server applications to update databases on any of the platform that these database vendors support. All updates can be performed under distributed transaction protection. This will allow updates to these databases to be synchronized with updates to other transaction protected resources including Microsoft SQL Server, MSMQ, Oracle databases, IBM DB/2 databases, and the like.

Databases	Comments
Tandem NonStop SQL	Tandem NonStop SQL will fully support Microsoft Transaction Server. Beta or release dates have not been announced. For more information contact Tandem Computers in Cupertino, California.

1.5 Which databases do not support Microsoft Transaction Server?

The following databases do not support Microsoft Transaction Server.

Database	Comments
Lotus Notes	Lotus Notes has not committed to supporting Microsoft Transaction Server.
Oracle RDB	Oracle RDB currently has no plans to support Microsoft Transaction Server.
Sybase Adaptive Server Anywhere	Sybase Adaptive Server Anywhere does not support Microsoft Transaction Server.

1.6 What must an ODBC compliant database do to support Microsoft Transaction Server?

An ODBC compliant database must do the following to support Microsoft Transaction Server:

1. It must provide an ODBC driver on Windows NT or Windows 95.

2. The ODBC driver must be thread-safe and not require thread affinity.

3. The ODBC driver must support the SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) call that enlists the ODBC connection in the component’s current transaction.

4. The database must support either the XA or the OLE Transactions standard for transaction coordination.

For databases that do not support transactions, only the first two requirements apply.

1.7 What must other databases do to support Microsoft Transaction Server?

A database that provides an interface other than ODBC must do the following to support Microsoft Transaction Server:

1. The database should include a resources dispenser that pools database connections. This makes it much cheaper for Microsoft Transaction Server components to connect to and disconnect from the database.

2. The resource dispenser must be thread-safe and not require thread affinity.

3. The resource dispenser should automatically enlist database connections in the component’s current transaction.

4. The database must support either the XA or the OLE Transactions standard for transaction coordination.

For databases that do not support transactions, only the first two requirements apply.

1.8 What is Microsoft doing to help database vendors integrate their products with Microsoft Transaction Server?

We are doing a great deal to assist database and ODBC driver vendors to support Microsoft Transaction Server. We have contacted all of the leading database and ODBC driver vendors and offered the following assistance:

1. We provide technical briefings on Microsoft Transaction Server.

2. We provide pre-release versions of Microsoft Transaction Server at no charge.

3. We offer technical assistance from the Microsoft Transaction Server development team.

4. We provide the Microsoft Transaction Server SDK release.
The SDK includes the Microsoft Transaction Server software and documentation needed to integrate a database with Microsoft Transaction Server.

5. We provide product test suites.

1.9 Can I access databases on UNIX and other platforms and can these databases participate in transactions?

Yes, applications can access databases distributed across a mixture of Windows NT, UNIX, Digital VMS, IBM AS/400, IBM MVS, and other systems. This is possible because an ODBC driver on one system can communicate with a database server running on another system. This is a standard feature of most ODBC drivers.

If the ODBC driver and database support distributed transactions, all of the work done on behalf of the application component will be performed under the control of a single atomic distributed transaction. The Microsoft Distributed Transaction Coordinator running on the NT system will coordinate the transaction.

1.10 If I access databases on UNIX and other platforms using transactions, must Microsoft Distributed Transaction Coordinator be running on those platforms?

A Microsoft Transaction Server transaction can include databases on a mixture of NT systems, UNIX systems, IBM AS/400, IBM MVS systems, and the like. Those systems need not be running Microsoft Distributed Transaction Coordinator.

This is possible because the Distributed Transaction Coordinator running on the NT system acts as the transaction coordinator. The Microsoft Distributed Transaction Coordinator communicates with the ODBC driver running on the NT system to tell it the outcome of the transaction. The ODBC driver relays this information to the database on the remote Unix, Digital VMS, AS/400, MVS, or other system. The database then commits or aborts the transaction as necessary. There is no need to have a Distributed Transaction Coordinator on the system containing the database server.

1.11 If I access databases on UNIX and other platforms using transactions, must Tuxedo, Top End, or Encina be running on those platforms?

Tuxedo, TopEnd, or Encina is not needed on the Unix or other platform.

Distributed Transaction Coordinator on Windows NT is acting as the transaction manager. Distributed Transaction Coordinator on Windows NT communicates with the ODBC driver running on the NT system to tell it the outcome of the transaction. The ODBC driver communicates this information to the database on the Unix, Digital VMS, AS/400, MVS, or other system. The database then commits or aborts the transaction as necessary. No transaction manager is required on the system containing the database server.

1.12 Can an Access database be used with Microsoft Transaction Server?

Access databases can be read and updated from Microsoft Transaction Server components.

Access does not support distributed transactions; hence any Microsoft Transaction Server component that reads or updates an Access database must be marked “Does not support transactions”.

When using Access with Microsoft Transaction Server you must use the version 3.51 or later of the Access Driver. Earlier versions of the Access driver did not support connection pooling.

You may use the following data access methods with Access.

Data Access Method	Comments
ADO
RDO
ODBC
DAO 3.5 with ODBC Direct
MFC ODBC Database Classes	You must use CDatabase::OpenEx rather than CDatabase::Open and you must specify the noOdbcDialog option to open your database connection. .

You cannot use DAO with Microsoft Transaction Server because when DAO is given incomplete or incorrect information about an ODBC Data Source, Access displays a dialog box to prompt the user for the missing or incorrect information. This dialog box may cause the application to hang if it is running on an unattended server or if it does not have desktop access.

Access could avoid this problem by calling SQLDriverConnect with the SQL_DRIVER_NOPROMPT flag. This flag prevents dialog boxes from being displayed. However, Access does not currently set this flag

ODBC Connection Pooling only works when the SQL_DRIVER_NOPROMPT flag is set; therefore, Microsoft Transaction Server denies a connection to any component that does not use this flag.

1.13 Can a Visual Fox Pro database be used with Microsoft Transaction Server?

Visual Fox Pro databases can be read and updated from Microsoft Transaction Server components.

Visual Fox Pro does not support distributed transactions; hence any Microsoft Transaction Server component that reads or updates a Visual Fox Pro database must be marked “Does not support transactions”.

Result sets are not updateable in the Visual Fox Pro ODBC driver. That means that while you can get a scrollable cursor with the Visual Fox Pro ODBC driver, it will not be updateable. You can send SQL Update statements to the source tables, etc.

You may use the following data access methods with Visual Fox Pro.

Data Access Method	Comments
ADO
RDO
ODBC
DAO 3.5 with ODBC Direct
MFC ODBC Database Classes	You must use CDatabase::OpenEx rather than CDatabase::Open and you must specify the noOdbcDialog option to open your database connection. .

1.14 Can XA compliant databases by accessed from an MSCS cluster?

Yes, XA compliant databases, such as Oracle, can now be accessed from an MSCS cluster.

In the original MTS 2.0 releases, XA compliant databases could not be accessed from an MSCS cluster as the result of a security limitation in Microsoft Distributed Transaction Coordinator. This limitation was eliminated in the Windows NT SP 4 and later releases.

2 Using an Oracle Database with Microsoft Transaction Server

2.1 Can I use an Oracle database with MTS?

Yes, the Microsoft Transaction Server 2.0 release supports Oracle databases.

2.2 Can an Oracle database participate in Microsoft Transaction Server transactions?

Yes, Oracle databases can participate in Microsoft Transaction Server transactions. This is possible because Oracle 7.3.3 and later release of Oracle for Windows NT supports XA and we have enhanced the Microsoft Oracle ODBC driver to work with Microsoft Transaction Server.

2.3 What versions of Oracle work with Microsoft Transaction Server transactions?

Microsoft Transaction Server works with Oracle 7.3.3 or greater. Oracle 7.3.3 is the first release of Oracle that supports transactions with Microsoft Transaction Server because it is the first release of Oracle on Windows NT that supports XA.

Microsoft Transaction Server also works with Oracle 8 databases. You may access an Oracle 8 database using either Oracle 7.3 or Oracle 8 client software. Previously, we required that you access Oracle 8 via Oracle 7.3 client software; this restriction has been eliminated.

Microsoft Transaction Server works with Oracle Parallel Server.

2.4 Are there any restrictions when accessing an Oracle 8 database?

The following feature of Oracle 8 can't be used through the Oracle 7.3 client library:

· NCHAR data types (UniCode/National Language Character fields)

· The new Oracle object paradigm.

· Oracle connection/session pooling optimizations.

2.5 What ODBC drivers can I use with an Oracle database?

You must use the Microsoft Oracle ODBC Driver supplied with Microsoft Transaction Server release 2.0. It is currently the only Oracle ODBC driver that works with Microsoft Transaction Server.

2.6 Does ODBC connection pooling work with Oracle databases?

Yes, connection pooling works with Oracle databases. Connection pooling is built into the ODBC 3.0 and 3.5 Driver Managers; therefore, connection pooling works provided the Oracle ODBC Driver you use is thread-safe. Oracle database connections are pooled when you use the Microsoft Oracle ODBC Driver.

2.7 Can I access Oracle databases on UNIX and other platforms and can these databases participate in transactions?

Yes, you can access Oracle databases on Unix and other platforms and these Oracle databases can participate in transactions. For example, you can update a Microsoft SQL Server database on one Windows NT system, an Oracle database on another Windows NT system, and an Oracle database on a Unix system under a single atomic transaction. If the transaction commits, all three databases are updated. If the transaction aborts, all work done on all three databases is backed out. Microsoft Transaction Server is intended to interoperate with any Oracle platform accessible from Windows NT or Windows 95.

During the Microsoft Transaction Server 2.0 beta program, Microsoft Transaction Server was used with Oracle databases on the following platforms.

Platform

Digital Unix

HP/UX

IBM AIX

Windows NT

Sun Solaris

2.8 If I access Oracle databases on UNIX and other platforms using transactions, must Microsoft Distributed Transaction Coordinator be running on those platforms?

A Microsoft Transaction Server component can update Oracle databases on both Windows NT and other systems including Unix. All of the updates made by the component can be performed under the control of a single distributed atomic transaction. The other systems need not be running Microsoft Distributed Transaction Coordinator.

This is possible because the Distributed Transaction Coordinator running on the NT system acts as the transaction coordinator. The Microsoft Distributed Transaction Coordinator communicates with the Microsoft Oracle ODBC driver running on the NT system to tell it the transaction’s outcome. The ODBC driver relays this information to the Oracle database on the Unix or other system. The Oracle database then commits or aborts the transaction as necessary. There is no need to have a Distributed Transaction Coordinator on the system containing the Oracle database.

2.9 If I access Oracle databases on UNIX and other platforms using transactions, must Tuxedo, Top End, or Encina be running on those platforms?

No, Tuxedo, TopEnd, or Encina is not needed on the Unix or other platform.

Microsoft Distributed Transaction Coordinator on Windows NT is acting as the transaction manager. Distributed Transaction Coordinator on Windows NT communicates with the Microsoft Oracle ODBC driver running on the NT system to tell it the outcome of the transaction. The ODBC driver communicates this information to the Oracle database on the Unix or other system. The Oracle database then commits or aborts the transaction as necessary. No Tuxedo, TopEnd, or Encina transaction manager is required on the Unix or other system..

2.10 What data access methods work with Oracle?

The following data access methods work with Oracle:

Data access method	Comments
ADO Microsoft ActiveX™ Data Objects	ADO provides a convenient object-oriented programming interface for accessing OLE DB data sources. ADO permits a collection of records to be passed between clients and servers in the form of a recordset. A recordset can be used to pass a query result from the server to the client, and to pass updated records from the client to the server.
JDBC	JDBC allows Java components to invoke ODBC databases.
OLE DB	OLE DB provides a standard interface to any tabular data source. ADO/OLEDB currently only supports transactions through the “Kagera” ODBC to OLEDB provider. The Microsoft native OLE DB provider for Oracle will support transactions when it becomes available. You cannot call OLE DB interfaces directly from Microsoft Visual Basic® because OLE DB is a pointer-based interface. A Visual Basic client can access an OLE DB data source through ADO.
ODBC Open DataBase Connectivity	ODBC provides a standard interface to relational data sources.
RDO Remote Data Objects	RDO provides a convenient object-oriented programming interface for accessing ODBC data sources.
MFC ODBC Database Classes

2.11 Can I use Oracle’s OCI with Microsoft Transaction Server?

You can use Oracle’s OCI interfaces with Microsoft Transaction Server but Oracle does not provide a resource dispenser for OCI. As a result, OCI connections are not pooled and OCI connections cannot be enlisted in Microsoft Transaction Server transactions.

2.12 Why are three sessions opened to the Oracle 7.3 Database for each Oracle database Connection?

This is normal. The Oracle 7.3 client opens one session to the Oracle database when opening a non-transactional connection to the Oracle database. However, Oracle opens three sessions for each XA transactional connection to the Oracle database. The Oracle 7.3 client opens two sessions to the Oracle database server when xa_open is called to establish a transactional Oracle database connection. The Oracle 7.3 client opens and closes a third session for each XA transaction performed. The third session is opened when xa_start is called to initiate work on a new transaction. This third session is closed when the transaction completes. You can verify this using the TestOracleXaConfig program. Run the TestOracleXaConfig program and insert a break point after the xa_open call. Verify that there are two sessions open. Insert a breakpoint after the xa_start call and verify that a third session is open.

For the Oracle 8 release, only one Oracle session is opened for either transactional or non-transactional Oracle database connections.

3 Using an IBM DB2 Database with Microsoft Transaction Server

3.1 Can I use an IBM DB2 database with MTS?

Yes, the IBM DB2 Connect Enterprise Edition V5.release fully supports Microsoft Transaction Server.

You can obtain information about IBM DB2 Microsoft Transaction Server support from IBM’s web site at http://www.software.ibm.com/data/db2/db2tech/db2mts.htm

3.2 Can an IBM DB2 database participate in Microsoft Transaction Server transactions?

Yes, IBM DB2 supports Microsoft Transaction Server distributed transactions. This allows Microsoft Transaction Server applications to update IBM DB2 databases on a wide variety of platforms under distributed transaction protection.. Updates to DB2 databases can be synchronized with updates to other transaction protected resources including Microsoft SQL Server, Microsoft Message Queue Server, Oracle databases, and the like.

3.3 What version of IBM DB2 works with Microsoft Transaction Server transactions?

Microsoft Transaction Server works with IBM DB2 Connect Enterprise Edition V5 or greater.

3.4 Are there any restrictions when accessing an IBM DB2 database?

Using MTS with DB2 Connect Enterprise Edition for Windows NT requires that IBM Communication Server for Windows NT be used for establishing SNA connectivity to host and AS/400 systems.

DB2 Connect Enterprise Edition for Windows NT does not currently work with Microsoft SNA Server Version 4, but IBM is in the process of eliminating this restriction.

3.5 What ODBC drivers can I use with an IBM DB2 database?

If you wish to use Microsoft Transaction Server transactions, you must use IBM’s DB2 ODBC Driver. It is currently the only DB2 ODBC driver that supports distributed transactions with Microsoft Transaction Server.

3.6 Does ODBC connection pooling work with IBM DB2 databases?

Yes, connection pooling works with IBM DB2 databases. Connection pooling is built into the ODBC 3.0 and 3.5 Driver Managers. The IBM DB2 ODBC driver supports connection pooling.

3.7 Can I access IBM DB2 databases on other platforms and can these databases participate in transactions?

Yes, you can access IBM DB2 databases on other platforms and these IBM DB2 databases can participate in transactions. For example, you can update a Microsoft SQL Server database on one Windows NT system, an IBM DB2 database on another Windows NT system, and an IBM DB2 database on an MVS system under a single atomic transaction. If the transaction commits, all three databases are updated. If the transaction aborts, all work done on all three databases is backed out.

Microsoft Transaction Server applications can access DB2 databases using transaction protection on the following platforms:

· DB2 Universal DB Version 5 on Windows NT, OS/2, AIX, HP/UX, and Solaris.
A transactional Microsoft Transaction Server application on Windows NT can connect to an IBM DB2 database on these platforms over TCP/IP, SNA, NETBIOS (NT and OS/2 only), or SPX/IPX (NT, OS/2, and AIX only).

· MVS, OS/390, VM, VSE, and AS/400 via DRDA.
A transactional Microsoft Transaction Server application on Windows NT that wishes to connect to an IBM DB2 database on AS/400, MVS, OS/390, VM and VSE requires DB2 Connect Enterprise Edition V5 (DB2 Connect EE) and the IBM Communication Server on NT, OS/2, or AIX.

In the DB2 Connect V5.2 release targeted for September 26 1998, IBM will make it possible for a Microsoft Transaction Server application running on Windows NT to connect directly to an AS/400 or MVS system over TCP/IP.

3.8 If I access an IBM DB2 database on another platforms using transactions, must Microsoft Distributed Transaction Coordinator be running on that platform?

A Microsoft Transaction Server component can update IBM DB2 databases on both Windows NT and other systems. All of the updates made by the component can be performed under the control of a single distributed atomic transaction. The other systems need not be running Microsoft Distributed Transaction Coordinator.

This is possible because the Distributed Transaction Coordinator running on the NT system acts as the transaction coordinator. The Microsoft Distributed Transaction Coordinator communicates with the IBM ODBC driver running on the NT system to tell it the transaction’s outcome. The IBM ODBC driver relays this information to the IBM DB2 database on the other system. The IBM DB2 database then commits or aborts the transaction as necessary. There is no need to have a Distributed Transaction Coordinator on the system containing the IBM DB2 database.

3.9 If I access IBM DB2 databases on another platforms using transactions, must CICS, Tuxedo, Top End, or Encina be running on those platforms?

No, CICS, Tuxedo, TopEnd, or Encina is not needed on the other platform.

Microsoft Distributed Transaction Coordinator on Windows NT is acting as the transaction manager. Distributed Transaction Coordinator on Windows NT communicates with the ODBC driver running on the NT system to tell it the outcome of the transaction. The IBM ODBC driver communicates this information to the IBM DB2 database on the other system. The IBM DB2 database then commits or aborts the transaction as necessary. No CICS, Tuxedo, TopEnd, or Encina transaction manager is required on the other system.

3.10 Can you explain the difference between DB2 Connect Enterprise Edition and DB2 Connect Personal Edition?

IBM offers two DB2 Connect products: “DB2 Connect Enterprise Edition” and “DB2 Connect Personal Edition”.

“DB2 Connect Enterprise Edition” is a database gateway product that runs on a Windows NT, UNIX or OS/2. It allows applications running on desktop PCs to access DB2 databases on IBM hosts and AS/400 systems when no direct TCP/IP connectivity to the IBM host or AS/400 system is available.

DB2 Connect Enterprise Edition is also used with IIS and Microsoft Transaction Server based applications. In these environments DB2 Connect Enterprise Edition runs on the same machine as the IIS or Microsoft Transaction Server application and provides direct connectivity from that system to the IBM system containing the DB2 database.

“DB2 Connect Personal Edition” is licensed for use by a single person. It directly connects a single client application to an IBM DB2 database over TCP/IP or SNA. DB2 Connect Personal Edition is typically used by applications that rely upon the 2-tier application programming model. For example, Excel spreadsheets with host data connections or VB client applications that directly issue SQL calls rather than invoking Microsoft Transaction Server application components DB2 Connect Personal Edition is not appropriate for use with IIS and Microsoft Transaction Server.

3.11 Which DB2 Connect Product is appropriate for use with Microsoft Transaction Server?

You should use DB2 Connect Enterprise Edition with Microsoft Transaction Server because it supports Syncpoint Manager (SPM). SPM permits DB2 to participate in Microsoft Transaction Server distributed transactions.

DB2 Connect Personal Edition does not support Syncpoint Manager.

3.12 Does DB2 Connect Enterprise Edition require a separate gateway machine to get to DB2?

No separate gateway machine is required when using DB2 Connect Enterprise Edition. You may run DB2 Connect Enterprise Edition on the same system as your Microsoft Transaction Server application components.

3.13 Can my application connect to DB2 using TCP/IP or is SNA required?

OS/390 V5 customers can use either TCP/IP or SNA to connect their Microsoft Transaction Server applications to DB2.

MVS V3.1, V4.1 and DB2/400 customers must use SNA.

3.14 Does IBM recommend using SNA or TCP/IP with Microsoft Transaction Server when accessing DB2 on OS/390 V5?

IBM recommends TCP/IP because it is easier to configure. It also costs less because no SNA software is required.

3.15 What version of DB2 Connect Enterprise Edition supports TCP/IP connectivity to host data from MTS applications?

V5.2 of DB2 Connect Enterprise Edition is required for TCP/IP connectivity from Microsoft Transaction Server applications to DB2 on OS/390 V5.1. DB2 Connect Enterprise Edition V5.2 will become generally available on September 26, 1998. Existing customers of DB2 Connect Enterprise EditionV5 can upgrade to version 5.2 at no extra charge by applying IBM FixPack 6.

3.16 What data access methods work with IBM DB2?

The following data access methods work with IBM DB2:

Data access method	Comments
ADO Microsoft ActiveX™ Data Objects	ADO provides a convenient object-oriented programming interface for accessing OLE DB data sources. ADO permits a collection of records to be passed between clients and servers in the form of a record set. A record set can be used to pass a query result from the server to the client, and to pass updated records from the client to the server.
JDBC	JDBC allows Java components to invoke ODBC databases.
OLE DB	OLE DB provides a standard interface to any tabular data source. ADO/OLEDB currently only supports transactions through the “Kagera” ODBC to OLEDB provider. There is currently no native OLE DB provider for IBM DB2. You cannot call OLE DB interfaces directly from Microsoft Visual Basic® because OLE DB is a pointer-based interface. A Visual Basic client can access an OLE DB data source through ADO.
ODBC Open DataBase Connectivity	ODBC provides a standard interface to relational data sources.
RDO Remote Data Objects	RDO provides a convenient object-oriented programming interface for accessing ODBC data sources.
MFC ODBC Database Classes

4 Transactions

4.1 Under what circumstances does Microsoft Transaction Server initiate a transaction?

Microsoft Transaction Server initiates a transaction when an application component marked as “Requires a transaction” is invoked provided the component’s caller is not already part of a transaction. If the caller is already part of a transaction, then the called component will join the caller’s transaction.

Microsoft Transaction Server initiates a new transaction when an application component is invoked that is marked as “Requires a new transaction”. This happens whether or not the caller is already part of a transaction

When Microsoft Transaction Server initiates a transaction on behalf of a component, it stores the transaction in the component’s object context.

4.2 Exactly when is the transaction initiated?

Microsoft Transaction Server 2.0 initiates the transaction when the first method on the component is invoked. However, some method calls do not cause Microsoft Transaction Server to initiate a transaction including: QI, AddRef, Release, IDispatch::GetIdsOfNames, ISupportErrorInfo methods, etc.

COM+ will do just-in-time transaction initiation. COM+ only initiates the transaction when the component does something that requires a transaction, such as, opening an ODBC or OLE DB connection to a transactional database or invoking another transactional component. If the component never does anything that requires a transaction, COM+ never initiates one.

For either release, the following invariant holds: The transaction will be there by the time your component needs it.

4.3 How does the transaction get propagated to the database?

The ODBC Driver Manager and the ODBC Driver are responsible for propagating the transaction to the database. When an application component opens a database connection, the ODBC Driver Manager is invoked. The ODBC Driver Manager checks the component’s object context to see whether the component is participating in a transaction. If so, the ODBC Driver Manager calls the ODBC Driver’s SQLSetConnectionAttr(SQL_ATTR_ENLIST_IN_DTC) procedure passing it the transaction. The ODBC Driver passes the transaction to the database server. If the database supports OLE Transactions, then the OLE transaction is passed directly to the database server.

If the database supports XA, then the ODBC Driver converts the OLE transaction identifier into an XA Transaction Identifier (XID) with help from Microsoft Distributed Transaction Coordinator. The XID is then passed to the database server using the XA protocol.

4.4 How does the transaction get propagated to other application components?

When a transactional component invokes another component that is marked “Requires a transaction” or “Supports transactions”, Microsoft Transaction Server automatically propagates the transaction from the calling component’s object context to the called component’s object context. This is true whether the called component is in the same package as its caller, in a different package on the same machine, or is in a package on a remote machine.

4.5 When does Microsoft Transaction Server automatically commit or abort a transaction?

Microsoft Transaction Server commits or aborts the transaction when the component that triggered creation of the transaction completes its work. The component signals that its work is complete by calling either SetComplete or SetAbort. SetComplete causes the transaction to commit; SetAbort causes the transaction to abort.

Microsoft Transaction Server will also commit or abort the transaction if the client releases its last reference to the component.

The following rules summarize when a transaction is committed or aborted.

· If the component calls SetComplete, the transaction commits as soon as the method call returns to the client, even if the client maintains a reference to the component.

· If the component calls SetAbort, the transaction aborts as soon as the method call returns to the client, even if the client maintains a reference to the component.

· If the component calls DisableCommit, the transaction aborts when the client releases its last reference to the component.

· If the component calls EnableCommit, the transaction commits when the client releases its last reference to the component.

If the component never calls SetAbort, SetComplete, DisableCommit, or EnableCommit, then the transaction commits when the client releases its last reference to the MTS component.

4.6 Can my application access the transaction identifier located in the object context?

No, the application component cannot access the transaction identifier in the object context.

4.7 Why does Microsoft Transaction Server support automatic transactions?

Automatic transactions make it much easier to develop reliable component-based applications.

When using Microsoft Transaction Server, the programmer writes very little code that manages transactions. MTS automatically begins a transaction on behalf of an application component based upon the components' transaction property. Application programmers use the data access methods they are familiar with including ADO, RDO, OLE DB, JDBC, and ODBC. The underlying resource dispensers, such as the ODBC Driver Manager, transparently enlist the resources they manage in the component’s transaction. When each component completes its work, it informs Microsoft Transaction Server whether it wishes to commit or abort the transaction. When the final component completes its work, Microsoft Transaction Server determines the outcome of the transaction. If all of the components that participated in the transaction signified that the transaction should commit, then Microsoft Transaction Server will commit the transaction. If any component signified that the transaction should abort, Microsoft Transaction Server aborts the entire transaction.

Automatic Transactions make it easy to compose applications from independently authored components. Each component is marked to indicate its transaction requirements. Microsoft Transaction Server automatically initiates transactions and propagates transactions from component to component to satisfy these requirements. If any component that is participating in the transaction cannot commit the transaction, Microsoft Transaction Server ensures that the entire transaction is aborted. This makes it possible to build an application from components while allowing each component to enforce its portion of the business rules.

4.8 Can an application client explicitly initiate and control transactions?

Microsoft Transaction Server supports both client-controlled transactions and automatic transactions. A client may explicitly control transactions by creating a Transaction Context Object. The client may then call the transaction context object’s CreateInstance method to invoke subordinate components. Microsoft Transaction Server will start a transaction, associate it with the transaction context object, and propagate the transaction to each of the called components that is marked either “Requires a transaction” or “Supports transactions”. When the client is ready to commit the transaction, it calls the transaction context object’s Commit method. Alternatively, the client can abort the transaction by calling the transaction context object’s Abort method.

Although Microsoft Transaction Server supports client-controlled transactions, we encourage component developers to allow Microsoft Transaction Server to automatically initiate and manage transactions. There are two reasons for allowing Microsoft Transaction Server to manage transactions automatically.

Applications are usually easier to write if Microsoft Transaction Server controls transactions automatically. Each component is marked to indicate whether it requires a transaction. When a component completes its work, it signifies whether the transaction should commit or abort. Microsoft Transaction Server determines the transaction outcome based upon the responses it receives from all of the components participating in the transaction. The application developer writes very little code for managing transactions.

Another benefit of automatic transactions is that the transactions are controlled at the server rather than at the client. Clients are often running on unreliable systems. Servers are normally running on more reliable and carefully managed systems. It is better to initiate and control the transaction from these more reliable server systems. Controlling the transaction from the server may also be more efficient because it reduces the number of network messages.

4.9 Can Microsoft Distributed Transaction Coordinator manage transactions involving UNIX, IBM MVS, IBM AS/400, and other systems?

Microsoft DTC can coordinate transactions distributed across a wide variety of heterogeneous systems. These systems may be running databases, message queuing systems, and transaction processing monitors.

In the case of databases, an application can access databases distributed across a mixture of NT, UNIX, IBM AS/400, IBM MVS, Tandem, and other systems. This is possible because an ODBC driver on one system can communicate with a database server running on another system. This is a standard feature of most ODBC drivers.

If the ODBC driver and database support distributed transactions, all of the work done on behalf of the application is performed under the control of a single atomic distributed transaction. The Microsoft Distributed Transaction Coordinator running on the NT system coordinates the transaction.

Microsoft Message Queue (MSMQ) supports transactional message queuing.

Microsoft’s COM Transaction Integrator for CICS & IMS (COMTI), code named “Cedar”, provides transactional access to IBM’s CICS over the SNA LU 6.2 sync level 2 protocol. CICS can fully participate in a distributed transaction controlled by Microsoft Transaction Server.

4.10 If a transaction aborts, what happens to the updates an application makes to databases on NT, UNIX, AS/400, MVS, or other systems?

If the transaction aborts, its effects will be backed out on every system participating in the transaction.

4.11 Can SQL Server transactions be used with Microsoft Transaction Server transactions?

Components can call BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION inside a transactional MTS component, although this is not recommended.

Calls to BEGIN TRANSACTION are ignored, as are calls to COMMIT TRANSACTION.

Calls to ROLLBACK TRANSACTION will abort the component's transaction even if the component calls SetComplete.

Components must not call BEGIN DISTRIBUTED TRANSACTION, PREPARE TRANSACTION, or SAVE TRANSACTION inside a transactional MTS component.

4.12 Can I use explicit ODBC transactions with Microsoft Transaction Server transactions?

Calls to SQLTransact are disabled while in a Microsoft Transaction Server transaction. Microsoft Transaction Server will report the error "Cannot use manual transactions while in a component transaction." if a transactional component attempts to call SQLTransact.

Similarly, calls to SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) are disallowed.

4.13 Can I obtain MTS Transaction statistics programmatically?

At present there is no easy way to obtain transactions statistics programmatically.

You can monitor the state of individual transactions using the Microsoft Transaction Server Event Sinks facility described in the Microsoft Transaction Server SDK. The IMTSTxEvents interface’s OnTransactionStart, OnTransactionPrepare, and OnTransactionAbort methods are called when the state of a transaction changes. The Microsoft Transaction Server Spy sample application illustrates how Microsoft Transaction Server Events Sinks can be used.

4.14 Does Microsoft DTC support nested transactions?

First, let’s describe how nested transactions work.

Assume that your application initiates transaction “A”. It does transactional updates under the protection of transaction “A” and then invokes a subordinate or “nested” transaction “B”. The application could do work on “B” and then either commit or abort “B”. If nested transaction “B” is aborted, all work done under the auspices of “B” is immediately undone. Note that aborting “B” does not cause transaction “A” to abort. If nested transaction “B” commits, any work done under the auspices of “B” is inherited by transaction “A”. This means that “A” inherits any locks held by “B”. If “A” commits, all work done by both “A” and “B” commits. If “A” aborts, all work done by both “A” and “B” is backed out.

We have only described a two level transaction, but the same principle applies if nested transaction “B” calls nested transaction “C”. You could also have more than one transaction at the same level. For example, “A” could call two nested transactions “B1” and “B2” which are both at level 2. In its full generality you could have a multilevel tree of nested transactions.

Arguably nested transactions are valuable when constructing applications using distributed components. Nested transactions allow an outer level component to invoke a subordinate component that updates one or more transaction-protected resource. If the subordinate component encounters problems, it can abort its nested transaction thereby undoing any work the subordinate component has done. The subordinate component can then return an error to its caller saying that it could not do what was requested of it. The outer component can proceed to call other subordinate components secure in the knowledge that any work done by the first subordinate component has been undone. When used in this way, nested transactions complement the encapsulation rules upon which component systems are often based.

This sounds ideal, so why doesn’t Microsoft Transaction Server support nested transaction?

To make nested transactions work, both the transaction manager and the resource managers (the relational database, queuing system, etc.) must support nested transactions. Supporting nested transactions in the transaction manager is a significant amount of work. Supporting nested transactions in the resource manager is even more difficult. When a nested transaction commits, the resource manager must transfer control of all locked records from the subordinate transaction to the parent transaction. When a nested transaction aborts, the resource manager must back out only the aborting sub-transaction but any subordinate nested transactions within it. Nested transactions can be hard for the system administrator to manage. If things go wrong and human operator intervention is required, it can be very hard to sort out the state of a nested transaction. Finally, nested transactions involve more record keeping and message passing on behalf of the system. As a result, nested transactions are fairly expensive in system resources.

To my knowledge, the only commercial transaction manager that supports nested transactions is IBM’s Encina. Encina was the outgrowth of research work done at Carnegie Mellon University in the 1980’s. The Encina transaction manager included nested transactions because the founders of Encina, who came from CMU, though nested transactions would be useful in commercial transaction processing. Thus far, at least, almost no one has made use of nested transactions. One of the biggest impediments to the use of nested transactions is that none of the leading relational databases support nested transactions. For now at least, nested transactions is an interesting footnote but not a practical feature. At some point this may change.

For a complete description of nested transactions see Jim Gray’s book “Transaction Processing: Concepts and Techniques” published by Morgan Kaufmann.

In Microsoft Transaction Server and Microsoft DTC we do not support nested transactions. Every transaction is a separate atomic transaction. Each transaction commits or aborts independently. We allow component “X” operating under transaction “A” to invoke component “Y” operating under transaction “B”. In this case transactions “A” and “B” are two completely independent transactions. “A” and “B” commit or abort independently. “B” is not nested under “A”.

5 Resource Dispensers and Resource Managers

5.1 What is a resource dispenser?

A Microsoft Transaction Server resource dispenser does two things. It manages a pool of connections to the resource manager and it automatically enlists the resource manager in the component’s current transaction.

The ODBC Driver Manager is a typical resource dispenser. It manages a pool of ODBC database connections and enlists the database connections in the component’s current transaction. Application components use standard ODBC API calls to use the ODBC resource dispenser.

Connection Pool Management

Connection pooling makes it cheaper for short-lived components to connect and disconnect to resource managers.

Application components call the resource dispenser when they need a resource manager connection. The resource dispenser searches its pool of already open resource manager connections looking for a connection that meets the component’s needs. If it finds a satisfactory connection, the resource dispenser assigns the connection to the component. If the resource dispenser cannot find a satisfactory existing connection, it opens a new one. When the component releases the connection, the resource dispenser returns the connection to the pool for use by another component. If a connection goes unused for a period of time, the resource dispenser closes the connection and removes it from the pool. The system administrator can configure this connection timeout period.

Resource dispensers often provide administrative interfaces for controlling the connection pool. For example, the resource dispenser may allow the administrator to control the minimum and maximum connection pool size and the timeout for closing unused connections.

Automatic Transaction Enlistment

The resource dispenser automatically enlists the resource manager connection in the component’s current transaction. The resource dispenser does this by obtaining the current transaction from the component's object context and sending it to the resource manager. By doing this, the resource dispenser makes transaction enlistment automatic for the application.

5.2 What is a resource manager?

A Resource Manager is a database, file system, message queuing system, or the like that provides access to shared resources. The prototypical resource manager is a relational database.

A Transactional Resource Manager allows the data it manages to be updated under transaction control.

5.3 How does a resource dispenser relate to a resource manager?

A resource dispenser manages the connections to a resource manager.

5.4 What resource dispensers does Microsoft provide?

Microsoft provides the following resource dispensers.

1. The ODBC Driver Manager is a resource dispenser for managing ODBC connections.

2. OLE DB includes a resource dispenser for managing database connections.

3. MSMQ includes a resource dispenser for enlisting MSMQ queues in Microsoft Transaction Server transactions.

4. COM Transaction Integrator for CICS & IMS (COMTI) includes a resource dispenser for managing SNA connections to IBM CICS and IMS.

5.5 What is the Microsoft ODBC resource dispenser?

The ODBC resource dispenser is a very thin layer implemented in the ODBC Driver Manager. When your application component makes what looks like a standard ODBC call to connect to a database, the ODBC Driver Manager does two things.

First, it looks in the pool of existing ODBC connections and hands out an existing ODBC connection, if one is present that meets the application component’s requirements. This makes it very cheap for components that come and go relatively rapidly to connect to the database. It also reduces the number of required ODBC connections because the connections are shared and reused.

Second, it automatically enlists the ODBC connection in the component’s current transaction. The ODBC Driver Manager first obtains the current transaction from the component's object context. MTS automatically maintains this object context on behalf of the component. The ODBC Driver Manager then calls the ODBC driver passing it the component’s current transaction. The ODBC driver enlists the ODBC connection in the transaction. By doing this, the ODBC Driver Manager makes transaction enlistment automatic for the application component.

5.6 Can I build a resource dispenser?

Yes, you can build a resource dispenser. You may wish to do so if you manage a resource that requires either connection pooling or automatic transaction enlistment.

Note that building a resource dispenser is more difficult than building a Microsoft Transaction Server component that uses a resource dispenser because a resource dispenser must be multi-threaded and thread safe.

The interfaces for building a resource dispenser are not yet fully documented and some of the necessary header files are not provided in the SDK. If you wish to build a resources dispenser you must contact Microsoft for assistance.

5.7 How can I learn more about resource dispensers?

The Microsoft Transaction Server SDK includes a “Resource Dispenser Guide” that contains more information about resource dispensers. To obtain the “Resource Dispenser Guide”, install the Microsoft Transaction Server SDK and open the file C:\WINNT40\system32\SDK\Docs\mtxsdk.hlp

5.8 How can I obtain the Microsoft Transaction Server SDK?

You can obtain the Microsoft Transaction Server SDK from http://msdn.microsoft.com/developer/sdk/platform.htm. You will need to download the Platform SDK build environment before installing the Microsoft Transaction Server SDK.

You can obtain the MTS SDK documentation from http://msdn.microsoft.com/library Select “Platform SDK”, then “COM and ActiveX Object Services”, then “Microsoft Transaction Server SDK” or “Microsoft Transaction Server 2.0 Service Pack 1”

6 Data Access Methods

6.1 What data access methods fully support Microsoft Transaction Server?

The following data access methods fully support Microsoft Transaction Server. They support both connection pooling and automatic transaction enlistments.

Data access method	Comments
ADO Microsoft ActiveX™ Data Objects	ADO provides a convenient object-oriented programming interface for accessing OLE DB data sources. ADO permits a collection of records to be passed between clients and servers in the form of a Recordset. A recordset can be used to pass a query result from the server to the client, and to pass updated records from the client to the server.
DAO with ODBC Direct	DAO can be used in conjunction with ODBC Direct.
JDBC	JDBC-ODBC bridges allow Java components to invoke ODBC databases.
OLE DB	OLE DB provides a standard interface to any tabular data source. ADO/OLEDB currently only supports transactions through the “Kagera” ODBC to OLEDB provider. In future, some native OLEDB providers may support transactions while others may not. You cannot call OLE DB interfaces directly from Microsoft Visual Basic® because OLE DB is a pointer-based interface. A Visual Basic client can access an OLE DB data source through ADO.
ODBC Open DataBase Connectivity	ODBC provides a standard interface to relational data sources.
RDO Remote Data Objects	RDO provides a convenient object-oriented programming interface for accessing ODBC data sources.
MFC ODBC Database Classes

6.2 What data access methods do not fully support Microsoft Transaction Server?

The following data access methods either do not support Microsoft Transaction Server or support it with limitations.

Interface	Comments
DAO with Access Data Access Objects	DAO with Access cannot be used with Microsoft Transaction Server.
SQL Server DB Library	DB Library can be used with Microsoft Transaction Server but it does not support connection pooling or automatic transaction enlistment. You should use ODBC rather than DB Library.
SQL Server Embedded SQL	Embedded SQL cannot be used with Microsoft Transaction Server.
Oracle OCI	OCI can be used with Microsoft Transaction Server but it does not support connection pooling or automatic transaction enlistment.

6.3 Can I invoke stored procedures from Microsoft Transaction Server?

Yes, you can invoke stored procedures from Microsoft Transaction Server. The work done by the stored procedure will be full transaction protected provided the calling component is transactional and the data access method and database support Microsoft Transaction Server transactions.

6.4 Does IBM DB2 Support OLE-DB and ADO?

Yes, IBM DB2 supports OLE DB and ADO via the Kajera OLE DB to ODBC mapping. IBM does not currently have a native OLE DB provider, but they do plan to provide one in the future.

7 ODBC Drivers

7.1 What must an ODBC driver do to support Microsoft Transaction Server?

ODBC drivers do not automatically work with Microsoft Transaction Server, but they can be enhanced to work with Microsoft Transaction Server. To fully support Microsoft Transaction Server:

1. The ODBC driver must run on Windows NT or Windows 95.

2. The ODBC driver must be as 32-bit driver. It must be fully thread-safe and it must not require thread affinity. That is, the driver must be able to handle an ODBC call from any thread at any time. For example, it must be possible to connect to the database from a call on one thread, to use the database connection from another thread, and to disconnect from the database from another thread. This is essential because the ODBC driver manager opens and closes database connections from a system thread, while application components may call the database from any of a number of application threads.

If you attempt to use a non-thread-safe ODBC driver with Microsoft Transaction Server 1.0 or 1.1, a memory access violation may occur. This is most likely to occur after a database connection has been inactive for 60 seconds and the driver manager attempts to close the database connection.

In the Microsoft Transaction Server 2.0 release, the Resources Dispenser Manager catches this exception and logs the message:

An exception occurred within a Resource Dispenser :Dispenser=[ODBC env...] Method=IDispenserDriver::<method name>

Where <method name> is “CreateResource”, “RateResource”, “EnlistResource”, or “DestroyResource”.

3. The ODBC driver must support the new ODBC 3.0 SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) call. The ODBC driver manager uses this call to pass the component’s current transaction to the ODBC driver. The ODBC driver then passes the transaction to the database server.

Microsoft Transaction Server, the ODBC Driver Manager, and the ODBC driver work together to make transactions automatic and transparent to the application. This automatic transaction capability is an important feature of Microsoft Transaction Server. It makes applications more robust while making application development easier.

4. The database server must support either X/Open XA or the OLE Transactions two-phase commit protocol.

If the database server does not support the OLE Transaction two-phase commit protocol, the ODBC driver must translate OLE Transaction calls into the corresponding XA two-phase commit messages. The ODBC driver sends these XA two-phase commit messages to the database server. This permits the Microsoft Distributed Transaction Coordinator to perform the two-phase commit protocol with the database server.

7.2 What versions of the ODBC Driver Manager support Microsoft Transaction Server?

ODBC 3.0 and higher Driver Managers support Microsoft Transaction Server.

7.3 Which ODBC drivers currently support Microsoft Transaction Server?

The following ODBC drivers currently support Microsoft Transaction Server..

ODBC Driver	Comments
Microsoft SQL Server ODBC Driver	Microsoft’s SQL Server ODBC Driver (sqlsrv32.dll) version 2.65.0235 and later supports Microsoft Transaction Server.
IBM DB/2 ODBC Driver	IBM’s DB/2 ODBC Driver supports Microsoft Transaction Server. You can obtain information about IBM’s DB2 ODBC driver from IBM’s web site at http://www.software.ibm.com/data/db2/db2tech/db2mts.htm
Informix	The Informix ODBC Driver supports Microsoft Transaction Server. You can obtain information about the Informix ODBC Driver from Informix in Menlo Park, California.
Microsoft Oracle ODBC Driver	The Microsoft Oracle ODBC Driver (msorcl32.dll) version 02.73.7283.1 or later supports Microsoft Transaction Server. It is included in the NT 4.0 Option Pack release.
Ingres II from Computer Associates	Computer Associates International, Inc. is enhancing Ingres II to work with Microsoft Transaction Server. This product is currently in Beta testing and no release date has yet been announced. For more information contact David Thole at Computer Associates at David.Thole@cai.com
Sybase	Sybase has enhanced their ODBC driver for Adapter Server Enterprise to work with Microsoft Transaction Server. Beta testing is under way. For more information contact Sybase in Emeryville, California.

7.4 Which ODBC drivers will support Microsoft Transaction Server?

The following ODBC driver providers will support Microsoft Transaction Server in the future.

ODBC Driver	Comments
Tandem NonStop SQL	Tandem NonStop SQL will fully support Microsoft Transaction Server. Beta or release dates have not been announced. For more information contact Tandem Computers in Cupertino, California.

7.5 What ODBC driver vendors do not support Microsoft Transaction Server?

The following ODBC drivers do not currently support Microsoft Transaction Server:

ODBC Driver Vendor	Comments
Visigenic	Visigenic has not made any commitment to support Microsoft Transaction Server in their ODBC driver.

8 ODBC Connection Pooling

8.1 What is ODBC connection pooling?

ODBC connection pooling is a feature that is implemented by the ODBC Driver Manager. Connection pooling makes it much cheaper for an application to open and close a database connection. Once a connection has been created and placed in a connection pool, an application can reuse that connection without paying the cost of opening a new database connection.

When connection pooling is enabled, the ODBC driver manager maintains a “pool” of available ODBC connections. When an application opens a database connection, the driver manager searches the pool to locate an available connection that meets the application’s requirements. If it finds a suitable connection, the driver manager assigns it to the application. By reusing an existing database connection, the driver manager avoids the cost of establishing a new connection. If no suitable database connection is found, the driver manager opens a new database connection. When the application releases the database connection, the driver manager returns the connection to the pool.

Using a pooled connection can result in significant performance gains, because applications can save the overhead involved in making a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect, such as Internet applications or Microsoft Transaction Server application components.

The connection pooling architecture enables an environment and its associated connections to be used by multiple components in a single process. This means that standalone components in the same process can share database connections without being aware of each other. A connection in a connection pool can be used repeatedly by many components.

The Driver Manager maintains the connection pool. Connections are drawn from the pool when the application calls SQLConnect or SQLDriverConnect, and are returned to the pool when the application calls SQLDisconnect. The size of the pool grows dynamically based upon the requested resource allocations. It shrinks based on the inactivity timeout. If a connection is not reused before the timeout period expires, the connection is closed and removed from the pool. The size of the pool is limited only by the amount of memory available on the server.

8.2 Is connection pooling supported for all ODBC drivers?

Connection pooling is a standard feature of the ODBC 3.0 and 3.5 Driver Managers and it can be used with any 32-bit ODBC driver that is thread-safe.

To support connection pooling, an ODBC driver must be thread-safe and must not require thread affinity. That is, the driver must be able to handle an ODBC call from any thread at any time. For example, it must be possible to connect to the database from a call on one thread, to use the database connection from another thread, and to disconnect from the database from another thread. This is essential because the ODBC driver manager opens and closes database connections from one thread, while application components may call the database from any of a number of other threads.

If you attempt to use a non-thread-safe ODBC driver with the ODBC 3.0 driver manager, a memory access violation may occur. This is most likely to occur after a database connection has been inactive for 60 seconds and the driver manager attempts to close the database connection.

The ODBC 3.5 driver manager catches this exception and logs the message:

"Exception occurred within ODBC driver <driver name> during disconnect operation, likely not thread-safe ..."

8.3 Can applications use connection pooling if they do not use Microsoft Transaction Server?

Yes, connection pooling is a standard feature of the ODBC 3.0 and 3.5 Driver Managers. It can be used whether or not your application uses Microsoft Transaction Server. You must do two things to use connection pooling without using Microsoft Transaction Server. You must enable connection pooling when configuring the driver and your application program must make the appropriate ODBC calls.

Configuring the ODBC Driver to Support Connection Pooling

For the ODBC 3.5 Driver Manager, connection pooling is controlled on a driver-by-driver basis through the CPTimeout registry setting. If this registry entry is not present, connection pooling is disabled. To enable connection pooling for a specific driver, create the following registry entry:

HKLM\Software\ODBC\ODBCINST.INI\<driver name>\[CPTimeout]

Where CPTimeout is a string-value that specifies the timeout in seconds.

For the ODBC 3.0 Driver Manager, connection pooling is enabled by default. It cannot be disabled through the registry.

Enabling Connection Pooling in the Application

Non-Microsoft Transaction Server applications must perform the following steps to use connection pooling:

1. The application enables connection pooling by calling SQLSetEnvAttr to set the SQL_ATTR_CONNECTION_POOLING environment attribute to SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV. This call must be made before the application allocates the shared environment for which connection pooling is to be enabled. The environment handle in the call to SQLSetEnvAttr should be set to null, which makes SQL_ATTR_CONNECTION_POOLING a process-level attribute.

If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_ONE_PER_DRIVER, a single connection pool is supported for each driver, and it is not possible to share connections between environments. If an application works with many drivers and few environments, this may be more efficient because fewer comparisons may be required.

If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_ONE_PER_HENV, a single connection pool is supported for each environment, and it is possible to share connections between environments. If an application works with many environments and few drivers, this may be more efficient because fewer comparisons may be required.

If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_OFF connection pooling is disabled.

2. The application allocates an environment by calling SQLAllocHandle with the HandleType argument set to SQL_HANDLE_ENV. The environment allocated by this call will be an implicit shared environment because connection pooling has been enabled. The environment to be used is not determined, however, until SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called on this environment.

3. The application allocates a connection by calling SQLAllocHandle with InputHandle set to SQL_HANDLE_DBC, and the InputHandle set to the environment handle allocated for connection pooling. The Driver Manager attempts to find an existing environment that matches the environment attributes set by the application. If no such environment exists, one is created, with a reference count (maintained by the Driver Manager) of 1. If a matching shared environment is found, the environment is returned to the application, and its reference count is incremented.

The actual connection to be used is not determined by the Driver Manager until SQLConnect or SQLDriverConnect is called.

4. The application calls SQLConnect or SQLDriverConnect to make the connection. The Driver Manager uses the connection options in the call to SQLConnect (or the connection keywords in the call to SQLDriverConnect) and the connection attributes set after connection allocation to determine which connection in the pool should be used.

How a requested connection is matched to a pooled connection is determined by the SQL_ATTR_CP_MATCH environment attribute.

5. The application calls SQLDisconnect when it is done with the connection. The connection is returned to the connection pool, and it becomes available for reuse.

8.4 Can connection pooling be disabled when using Microsoft Transaction Server?

Microsoft Transaction Server always makes the necessary ODBC calls to enable connection pooling. It does this by calling SQLSetEnvAttr and setting the SQL_ATTR_CONNECTION_POOLING environment attribute.

For the ODBC 3.5 Driver Manager, you control whether connection pooling is used on a driver-by-driver basis through the CPTimeout registry setting. If this registry entry is not present, connection pooling is disabled. To enable connection pooling for a specific driver, create the following registry entry:

HKLM\Software\ODBC\ODBCINST.INI\<driver name>\[CPTimeout]

Where CPTimeout is a string-value that specifies the timeout in seconds. ODBC connections are handed out in LIFO order. If a connection goes unused for the CPTimeout period, the database connection is closed and dropped from the pool.

For the ODBC 3.0 Driver Manager, connection pooling is always enabled and cannot be disabled. If the CPTimeout registry entry is present, its value controls the connection timeout value. If no registry entry is present for a driver, a CPTimeout value of 60 seconds is assumed.

You may need to disable connection pooling if your ODBC driver experiences access violations when used with Microsoft Transaction Server. This can occur if your ODBC driver is not fully thread-safe. The access violation occurs when the ODBC driver manager attempts to release a database connection from the pool. The ODBC driver manager may not call on the same thread that originally opened the database connection; this can cause an access violation in the ODBC driver.

8.5 What are the consequences of disabling connection pooling?

Microsoft Transaction Server performance suffers when connection pooling is disabled. Whenever a Microsoft Transaction Server component opens a database connection, the system must establish an entirely new database connection. When the component finishes its work and is destroyed, the database connection must be closed. This can be quite expensive because Microsoft Transaction Server components are typically short lived. They only persist for the life of the transaction. Connection pooling is usually essential for good performance.

8.6 Can I control the number of ODBC database connections in the connection pool?

There is currently no way to control the number of ODBC database connections in the connection pool. You cannot limit the number of connections in the pool and you cannot pre-allocate connections.

The connection pool is initially empty. If your application component asks for a database connection and there is no suitable free connection in the pool, the ODBC driver manager will open a new ODBC connection. When your application component closes a database connection, the connection is returned to the pool.

At present, the only mechanism for influencing the number of ODBC connections is the CPTimeout value. It specifies how long an idle database connection remains in the pool before it is closed and removed from the pool.

8.7 Can two or more processes share a connection pool?

No, database connections are only pooled within a single process.

8.8 How are database connections selected from the connection pool?

When an application component opens a database connection, the ODBC Driver Manager attempts to find an existing connection in the connection pool that matches the criteria requested by the application. These criteria include the connection options requested in the call to SQLConnect (the values of the ServerName, UserName, and Authentication keywords) and any connection attributes set since SQLAllocHandle was called with a HandleType of SQL_HANDLE_DBC. The Driver Manager checks these criteria against the corresponding connection keywords and attributes of the connections in the pool. If a match is found, the connection in the pool is used; otherwise, a new connection is created.

If you wish to learn more about ODBC connection pool management, consult the ODBC 3.0 SDK documentation.

8.9 How does implementing security through client impersonation effect connection pooling?

Client impersonation works with connection pooling but it makes connection pooling much less effective. When you use client impersonation, the system must provide a unique connection for each user id. As a result, the connections in the pool are far less interchangeable.

8.10 What does the ODBC error message "Failed to enlist on calling object's transaction" mean?

The ODBC driver manager reports this error when it cannot enlist the database connection in the component’s current transaction. This occurs when the ODBC Driver Manager’s call to the ODBC driver’s SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) procedure fails.

For more information regarding this error obtain the “Failed to Enlist” document from ftp://ftp.microsoft.com/bussys/viper/docs

9 Transaction Processing Monitors and Microsoft Transaction Server

9.1 Which Transaction Processing Monitors interoperate with Microsoft Transaction Server?

The following transaction processing monitors interoperate with Microsoft Transaction Server.

TP Monitors	Comments
IBM CICS	Microsoft Transaction Server applications can invoke CICS applications running on MVS via COM Transaction Integrator for CICS & IMS (COMTI). COMTI supports IBM’s LU 6.2 sync level 2 protocol for distributed transactions. This permits the CICS system to participate in a distributed transaction with Microsoft Transaction Server. (see note 1) IBM CICS programs cannot invoke Microsoft Transaction Server components.
IBM IMS	Microsoft Transaction Server applications can invoke IMS applications running on MVS via COM Transaction Integrator for CICS & IMS. COMTI does not yet permit IMS programs to participate in distributed transactions with Microsoft Transaction Server. (see note 2) IBM IMS programs cannot invoke Microsoft Transaction Server components.
Tuxedo	Unisys Open Transaction Integrator (OpenTI) allows MTS components to invoke BEA TUXEDO and Unisys TransIT Open/OLTP applications. OpenTI makes it easy for application developers to create COM components that invoke BEA TUXEDO and Unisys TransIT Open/OLTP transaction programs. Once created, these components can be called from Visual Basic, Visual C, Active Server Pages, and Java applications running on NT. OpenTI supports MTS distributed transactions so MTS components can now be part of a single atomic transaction that includes BEA Tuxedo and Unisys TransIT Open/OLTP transaction programs You can obtain a free evaluation copy of the OpenTI product by simply ccompleting the registration form and downloading the product from http://www.marketplace.unisys.com/clearpath/sw_downloads/openti/index.html The evaluation copy is good through the end of April 1999. OpenTI 1.1 will be able to be purchased in May 1999 by calling Unisys Software Order Services at 1-800-829-1313 (US only) or contacting your Unisys sales representative. If you do not know your sales representative, the local sales office can be located through http://corp2.unisys.com/AboutUnisys//global_operations.html For additional information, please contact Bob Carnahan at robert.carnahan@unisys.com.

Note 1:

When using distributed transactions you must use CICS v3.3 or later. Although we haven’t tested it, it may be possible to use earlier versions of CICS when distributed transactions are not performed.

Note 2:

IBM announced support for sync-level 2 in IMS/DC for their IMS V6.0 release in December 1997. IMS Sync level 2 support requires the Resource Recovery Service (RRS/MVS) that ships with OS390 1.3. This product is documented in IBM GC28-1739.

We plan to support transactional access to IMS in the next COMTI service pack release. This service pack release is currently targeted for release in the first quarter of 1999.

9.2 Can Microsoft Transaction Server interoperate with Unix TP Monitors such as TopEnd, Encina, and OpenUTM?

No, Microsoft Transaction Server cannot currently interoperate with NCR TopEnd, IBM Encina, or SNI OpenUTM. We have talked with these companies about providing interoperability with Microsoft Transaction Server but none has yet announced plans to support such interoperability.

9.3 Can Microsoft Transaction Server use XA to interoperate with other TP Monitors such as Tuxedo?

No, XA is not a TP Monitor to TP Monitor interoperability standard. XA is a standard that defines how transaction managers communicate with resource managers. For example, it defines how Tuxedo (a TM) talks with Oracle (a RM). More specifically, it describes how the TM and RM perform the two-phase transaction commit protocol and do transactional recovery.

XA does not define how transaction managers coordinate distributed transactions with other transaction managers. For example, XA does not define how Tuxedo would coordinate distributed transactions with Microsoft Transaction Server, or how TopEnd would coordinate distributed transactions with Encina.

XA does not define how application programs communicate with other application programs. For example, XA does not define how a Tuxedo application would communicate with a Microsoft Transaction Server application, or how a TopEnd application would communicate with an Encina application.

People who are not extremely familiar with the X/Open DTP standard are often confused on this point. They believe that XA defines a standard for interoperability between one transaction processing monitor and another. XA is simply a standard that allows a transaction manager to perform two-phase commit with a resource manager. It allows Tuxedo, TopEnd, and Encina to coordinate transactions with Oracle, Informix, DB2, and Sybase.

9.4 Does COMTI work with both SNA and TCP/IP?

COM Transaction Integrator for CICS and IMS (COMTI) currently requires that SNA be used between the server running MTS (and SNA Server) and the mainframe. The customer's LAN and WAN can be pure TCP/IP. By using an ESCON attachment between the server running MTS and SNA Server. all SNA traffic is technically isolated to the mainframe.

There are several reasons why we do not support a TCP/IP connection between COMTI and the mainframe today. First, IBM currently restricts the DPL protocol (which link-model CICS applications use to talk to one another) to LU6.2. When IBM supports DPL over TCP/IP to the mainframe, we can support TCP/IP. Second, LU6.2 gives us built-in security by allowing the transactions that flow to the mainframe to be integrated with RACF, Top Secret or ACF/2 security profiles. Achieving the same level of security via TCP/IP would require an extra component to be developed on the mainframe. Third, LU6.2 provides integrated two-phase commit support for link-model CICS and IMS version 6 applications. This is not currently available via TCP/IP.

10 Message Queuing and Workflow Systems and Microsoft Transaction Server

10.1 Which message queuing systems support Microsoft Transaction Server?

The following message queuing systems support Microsoft Transaction Server.

Message Queuing Systems	Comments
Microsoft Message Queue Server	MSMQ version 1.0 supports Microsoft Transaction Server

10.2 Can IBM’s MQSeries be Accessed from MSMQ?

The FalconMQ(TM) Bridge 1.0 product from Level Eight System Inc provides a transactional bridge between Microsoft Message Queue Server (MSMQ) and IBM MQSeries Version 2 and Version 5 on Microsoft Windows NT, IBM MVS, AIX and AS/400, Hewlett-Packard HP-UX, Digital VMS, and Sun Solaris.

10.3 Which Message Queuing Systems do not support Microsoft Transaction Server?

The following message queuing systems do not support Microsoft Transaction Server.

Message Queuing Systems	Comments
IBM MQSeries	IBM is considering providing direct transactional interoperability between Microsoft Transaction Server and IBM’s MQSeries. We are cooperating with IBM in this effort.

10.4 Which workflow systems support Microsoft Transaction Server?

The following workflow systems support Microsoft Transaction Server.

Work Flow Systems

Comments

Fabasoft Components

The coming release of Fabasoft Components combines an OO document database and workflow.

For more information see www.fabasoft.com or contact Leo Bauernfeind at Leopold.Bauernfeind@fabasoft.com

11 Object Request Brokers (ORBs) and Microsoft Transaction Server

11.1 Which ORBs interoperate with Microsoft Transaction Server?

The following ORBs interoperate with Microsoft Transaction Server:

ORB

Comments

Iona

OrbixCOMet Desktop provides bi-directional integration between COM and CORBA applications. COM developers using such tools as Visual Basic, PowerBuilder, Delphi or Active Server Pages on the desktop can easily access CORBA applications running on Windows, Unix or MVS.

OrbixCOMet Desktop delivers complete support for COM-CORBA mapping along with Automation-CORBA mapping. It provides a client side bridge, enabling COM/Automation clients to talk directly to CORBA servers with support for callbacks. OrbixCOMet is a completely dynamic bridge with no requirement for C++ code generation. So Visual Basic, PowerBuilder and Delphi developers can build the applications within their own environment, without the need for a C++ compiler.

For more information see http://www-usa.iona.com/index.html

VisualEdge

ObjectBridge for COM-CORBA from Visual Edge Software supports all the major CORBA ORBs including Orbix, VisiBroker, PowerBroker and NEO.

DCOM/COM/Automation client developers can access CORBA server objects as if they were COM objects. Conversely, CORBA server developers can access DCOM/COM/Automation client objects, allowing the server to "push" data and events to the client. This push approach delivers greater flexibility, integrity and performance than "polling." Bi-directionality also enables CORBA servers to pull data from desktop applications for aggregation and reporting.

For more information see http://www.visualedge.com

12 Transaction Internet Protocol (TIP)

12.1 What is the Transaction Internet protocol (TIP)?

TIP is an IETF Internet draft that describes a standard two-phase commit protocol. It enables heterogeneous transaction managers to coordinate distributed transactions.

TIP satisfies two key requirements:

1. The TIP two-phase commit protocol is independent of the application-to-application communications protocol, such that it may be used with any application protocol but especially HTTP.

2. The TIP two-phase commit protocol is simple to implement and it has a small working footprint. This will encourage ubiquitous implementation and offer wide applicability.

12.2 What is the status of the TIP standard?

TIP is an Internet Draft.

12.3 Who is participating in the TIP standardization effort?

Tandem Computers and Microsoft drafted the TIP proposal. Many vendors are refining and endorsing TIP.

12.4 Who is working on providing interoperability through TIP?

Company/Product	Comments
Digital ACMS
ICL
Iona OrbixCOMet	Iona plans to provide transactional interoperability with OrbixCOMet Desktop using TIP. Iona has announced their intention to provide transactional interoperability with COM+ through TIP and some initial design work has been done. No release date has been announced..
Tandem Pathway and SQL
Unisys

12.5 How can I learn more about TIP?

You can obtain more information about TIP from ftp://ftp.isi.edu/in-notes/rfc2371.txt

13 X/Open DTP and XA

13.1 What is the X/Open Distributed Transaction Processing Model?

The X/Open Distributed Transaction Processing (DTP) model is a software architecture that allows application programs to update databases and other transaction protected resources under transaction control. X/Open DTP originated in the UNIX world. It was standardized through the X/Open group.

The X/Open DTP model describes how an application can use transaction processing monitors (TP Monitors) like Tuxedo to update databases like Oracle under transaction control. X/Open DTP is supported by all of the leading UNIX TP Monitors including BEA’s Tuxedo, NCR’s TopEnd, IBM’s Encina, and SNI’s OpenUTM. It is also supported by all of the leading UNIX databases including Oracle, Informix, IBM DB2, and Sybase. As these TP Monitors and databases have been ported to Windows NT, the X/Open DTP model has become more important on NT also.

The original X/Open DTP model was composed of three functional components and three functional interfaces.

Application Program (AP)

The Application Program contains the business application logic created by the application developer. The application program defines transaction boundaries through calls it makes to the transaction manager. It controls the operations performed against the application data through the calls it makes to the resource managers.

Resource Managers (RMs)

Resource Managers are databases, files systems, message queuing systems, and the like that provide access to shared resources. The prototypical resource manager is a relational database.

Transaction Manager (TM)

The Transaction Manager creates transactions, assigns transaction identifiers to them, monitors their progress, and coordinates their outcome. The transaction manager is responsible for orchestrating the two-phase commit process.

RM Interface

The application program communicates with the resource manager using the RM interface. Typically the provider of the resource manager defines this interface. For example, an application would normally use SQL statements to communicate with a relational database.

TX Interface

The application program communicates with the transaction manager using the TX interface. The application program uses the TX interface to initiate and control transactions. For example, the application initiates a transaction by calling tx_begin(). It commits the transaction by calling tx_commit() or aborts the transaction by calling tx_rollback().

XA Interface

The transaction manager and the resource managers communicate using the XA interface. XA calls are used to enlist the resource manager in a transaction, to implement the two-phase commit protocol, and to perform recovery. For example, xa_start enlists an RM in a transaction, xa_prepare initiates phase-one processing, xa_commit initiates phase-two processing, and xa_rollback initiates transaction rollback.

Application programs never use XA interfaces directly. TMs and RMs use XA interfaces solely for TM to RM communication.

13.2 What is XA and why is it important?

The XA interface is part of the X/Open DTP standard. It defines the interface that transaction managers and resource managers use to communicate. The XA calls are used to enlist the resource manager in a transaction, to implement the two-phase commit protocol, and to perform recovery.

XA is important because it has been broadly adopted. It is supported by the leading UNIX transaction processing monitors including BEA’s Tuxedo, NCR’s TopEnd, IBM’s Encina, SNI’s OpenUTM, and the like. It is supported by the leading UNIX databases including Oracle, Informix, IBM’s DB/2, and Sybase.

13.3 Does Microsoft Transaction Server work with XA compliant resource managers?

Microsoft Transaction Server does not support the X/Open XA interface. However, an XA compliant resource manager can be enhanced to work with Microsoft Transaction Server.

The resource manager’s client library must accept OLE Transactions calls in place of XA calls. The client library must translate these OLE Transactions calls into the message formats and protocols that would have resulted had the corresponding XA interfaces been called.

The client library must translate the OLE Transaction Identifier (TRID) into a XA Transaction Identifier (XID). The client library may call a Microsoft provided DTC helper procedure to convert a TRID into a XID.

The resource manager server need not be changed to work with Microsoft Transaction Server. The client library completely hides OLE Transactions from the resource manager. The resource manager only receives XA compliant message flows from its client library. It only sees the XID when communicating with its client library.

Microsoft DTC includes an XA Transaction Manager (XATM) recovery manager. This XATM recovery manager uses standard XA calls to perform recovery.

If you wish to learn more about XA support in Microsoft Transaction Server, consult the Microsoft Transaction Server Software Developers Kit. The section on Resource Dispensers in this document explains how you can obtain the Microsoft Transaction Server SDK.

FAP: The message formats and the communication protocols used for communication.

OLE Tx: OLE Transactions

ODBC: Open Database Connectivity

DTC: Microsoft Distributed Transaction Coordinator

RM: Resource Manager

13.4 Can SQL Server and MSMQ be used with XA Transaction Managers like Tuxedo?

Yes, Microsoft SQL Server and MSMQ can be used with XA compliant transaction managers including BEA’s Tuxedo, NCR’s TopEnd, IBM’s Encina, SNI’s OpenUTM, and the like.

This is possible because Microsoft Distributed Transaction Coordinator translates each standard XA call into the corresponding OLE Transactions call that SQL Server and MSMQ expect. As a result, SQL Server and MSMQ act as fully XA compliant resource managers.

The following diagram illustrates how this is done. This capability was included in the Distributed Transaction Coordinator version 1.0 released first distributed with SQL Server 6.5.

14 OLE Transactions

14.1 What is OLE Transactions?

OLE Transactions is Microsoft’s object-oriented protocol for transaction management. OLE Transactions defines the interfaces that applications, resource managers, and transaction managers use to perform transactions. Applications use OLE Transaction interfaces to initiate, commit, abort, and inquire about transactions. Resource managers use OLE Transaction interfaces to enlist in transactions, to propagate transactions from process to process or from system to system, to participate in the two-phase commit protocol, and to perform recovery.

OLE Transactions takes the place of both the X/Open TX and XA interfaces.

14.2 Why did Microsoft define OLE Transactions?

We evaluated the X/Open DTP standard but decided that OLE Transactions was required for the following reasons:

1. Microsoft’s computing model is based upon distributed, transaction-protected, components that communicate using COM interfaces. Our transaction interfaces are object-based to better fit this model.

2. We intend to extend the transaction model to support a wide variety of transaction-protected resources. This includes documents, images, voice, queued messages, workflow and other non-database resources. To do this, we must extend the transaction model to provide richer transaction capabilities. OLE Transactions permits us to make these extensions.

14.3 Does Microsoft have a Transaction Manager?

Microsoft Distributed Transaction Coordinator (Microsoft DTC) is Microsoft’s transaction manager. It supports both the OLE Transactions standard and the XA standard for interoperability with resource managers.