Coordinator and participant servers in a distributed query

Queries that access more than one database server are called cross-server queries. To support distributed DML operations across multiple database servers, HCL® OneDB® servers maintain hierarchical relationships between a coordinator server and one or more participant servers.

Here coordinator and participant are defined as follows:

The coordinator directs the resolution of the query. It also decides whether the query should be committed or cancelled.
The participant (also called the subordinate server) directs the execution of the distributed query on one branch. The branch is the part of the distributed query involving only that participant database server.

More generally, the server of the local database of a session that initiates any cross-server distributed DML operation is called the coordinator, and the subordinate servers that execute individual branches of the same distributed transaction are called participant servers. The term distributed query is sometimes applied to any cross-server DML operation, including DELETE, INSERT, MERGE and UPDATE statements.

Cross-server distributed operations can include multiple database server instances as subordinate participants but exactly one coordinator. The coordinator must establish a connection with each subordinate. Within a single distributed operation, however, if a subordinate server attempts to connect to another subordinate, or to establish a new connection with the coordinator, the distributed operation fails with an error.

The distributed query example that follows refers to objects in a multi-server environment,

where db is the local database,
db2 is another database located on the same server,
and master_db is an external database on the remote server new_york.

The following example shows a cross-server distributed query that accesses data on remote server new_york, using the local server of database db as the transaction coordinator.

DATABASE db;
SELECT col2 FROM db2:tab1, master_db@newyork:tab2;

At a given time, a session can have only one local database, but can open multiple external databases. Distributed queries must always originate on the database server instance that acts as the coordinator.

Calling remote routines in cross-server operations

This restriction of the topology of cross-server connections to exactly two levels, the coordinator and the participants, limits the calling context for remote UDRs within cross-server operations to the coordinator.

For example, in the multi-server environment of the previous example, suppose that your local database included a UDR called bad_example1() that the following CREATE PROCEDURE statement had defined:

CREATE PROCEDURE bad_example1()
   EXECUTE PROCEDURE master_db@new_york:bad_example2()
END PROCEDURE;

Suppose also that the remote UDR called bad_example2() in the master_db database of the new_york database server instance had the following definition that invokes a remote UDR called bad_example3() on the new_buffalo database server instance:

CREATE PROCEDURE bad_example2()
   EXECUTE PROCEDURE examples_db@new_buffalo:bad_example3()
END PROCEDURE;

Suppose also that the remote UDR bad_example3() exists in the examples_db database of the new_buffalo database server, and that you hold all the required access privileges to invoke the local instance, the new_york instance, and the new_buffalo instance of the bad_example3() routine.

In this environment, when you issue the following statement from the local database:

EXECUTE PROCEDURE bad_example1();

your local database server, as the coordinator of this cross-server distributed operation, invokes the remote UDR bad_example2() on the new_york database server instance. Error -556 is returned, however, when the remote bad_example2() routine attempts to establish a connection between the new_york and the new_buffalo database servers. That attempt to connect fails, because it violates the rule that only the coordinator can connect to participant servers in the distributed transaction.

An invocation of the same remote UDR would have succeeded, however, if your session had not invoked bad_example1(), but a session on a local database of the new_york database server instance had instead initiated the cross-server distributed operation by issuing the

EXECUTE PROCEDURE bad_example2();

statement. In this case, the new_york database server is the coordinator of a cross-server operation in which the new_buffalo database server is a subordinate participant on the branch of the distributed operation that executes bad_example3(), and there is no third level of cross-server connections.

Remember:

Cross-server operations require exactly one coordinator to connect to one or more database server instances as subordinate participants. If a participant attempts to access any remote object, or to call a remote UDR in the database of any other server instance, that invocation fails with error -556, because only the coordinator can establish a connection with another database server.