CREATE XADATASOURCE statement

Use the CREATE XADATASOURCE statement to create a new XA-compliant data source and create an entry for it in the sysxadatasources system catalog table. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

CREATE XADATASOURCE [IF NOT EXISTS] xa_source USINGxa_type
Element Description Restrictions Syntax
xa_source Name that you declare here for the new XA data source Must be unique among XA data source names in sysxadatasources Identifier
xa_type Name of an existing XA data source type Must already exist in the database in the sysxasourcetypes system catalog table Identifier

Usage

An XA-compliant data source is an external data source that complies with the X/Open DTP XA Standard for managing interactions between a transaction manager and a resource manager. To register XA-compliant data sources in the database requires two SQL statements:
  • First create one or more XA-compliant data source types by using the CREATE XADATASOURCE TYPE statement.
  • Then create one or more instances of XA-compliant data sources with the CREATE XADATASOURCE statement.
You can integrate transactions at the XA data source with the Informix® transaction, using a 2-phase commit protocol to ensure that transactions are uniformly committed or rolled back among multiple database servers, and manage multiple external XA data sources within the same global transaction.

The CREATE XADATASOURCE statement is not supported on secondary servers within a high-availability cluster.

Any user can create an XA data source, which follows standard owner-naming rules, according to the ANSI-compliance status of the database. Only a database that uses transaction logging can support the X/Open DTP XA Standard.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an XA data source of the specified name is already registered in the current database.

Both XA data source types and instances of XA data sources are specific to one database. To support distributed transactions, they must be created in each database that interacts with the external XA data source.

The following statement creates a new XA data source instance called informix.NewYork of type informix.MQSeries. 
CREATE XADATASOURCE informix.NewYork USING informix.MQSeries;
SQL statements that are not valid in XA environments
The Informix® database server issues error -701 when you attempt to execute any of the following statements within an X/Open distributed transaction-processing environment:
  • CLOSE DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • SET LOG
  • SAVEPOINT
  • RELEASE SAVEPOINT
  • ROLLBACK TO SAVEPOINT
Within an XA environment, you can execute a single DATABASE statement after an xa_open call to specify a current database. After this database is selected, however, no subsequent DATABASE statement can be executed in the same session. If you attempt to execute a second DATABASE statement, the DATABASE statement fails with error -701.