Accessing distributed data by using explicit CONNECT statements

You must bind the DBRMs for the SQL statements to be executed at the server to packages that reside at that server.

The following example assumes that all systems involved implement two-phase commit. This example suggests updating several systems in a loop and ending the unit of work by committing only when the loop is complete. Updates are coordinated across the entire set of systems.

In this example, Spiffy's application executes CONNECT for each server in turn, and the server executes INSERT. In this case, the tables to be updated each have the same name, although each table is defined at a different server. The application executes the statements in a loop, with one iteration for each server.

The application connects to each new server by means of a host variable in the CONNECT statement. CONNECT changes the special register CURRENT SERVER to show the location of the new server. The values to insert in the table are transmitted to a location as input host variables.

The following overview shows how the application uses explicit CONNECTs:

Read input values
Do for all locations
     Read location name
     Connect to location
     Execute insert statement
End loop
Commit
Release all

For example, the application inserts a new location name into the variable LOCATION_NAME and executes the following statements:

EXEC SQL
   CONNECT TO :LOCATION_NAME;
EXEC SQL
   INSERT INTO DSN8A10.PROJ VALUES (:PROJNO, :PROJNAME, :DEPTNO, :RESPEMP,
                                   :PRSTAFF, :PRSTDATE, :PRENDATE, :MAJPROJ);

To keep the data consistent at all locations, the application commits the work only when the loop has executed for all locations. Either every location has committed the INSERT or, if a failure has prevented any location from inserting, all other locations have rolled back the INSERT. (If a failure occurs during the commit process, the entire unit of work can be indoubt.)

The host variables for Spiffy's project table match the declaration for the sample project table. LOCATION_NAME is a character-string variable of length 16.