Accessing distributed data by using three-part table names

You can use three-part table names to access data at a remote location through DRDA access.

When you use three-part table names, you must create copies of the package that you used at the local site at all possible remote locations that could be accessed by the three-part table name references. You must also explicitly or generically specify remote packages in the PKLIST of the PLAN that is used by the application.

Recommendation: Always use an alias, which resolves to a three-part table name, rather than specifying a specific three-part table name in an SQL statement. Using an alias will permit you to physically move the location of the table as needed. By using an alias, you can drop and re-create the alias by specifying the tables's new remote location and then rebind the packages of the application.

In a three-part table name, the first part denotes the location. The local Db2 makes and breaks an implicit connection to a remote server as needed.

When a three-part name is parsed and forwarded to a remote location, any special register settings are automatically propagated to remote server. This allows the SQL statements to process the same way no matter at what site a statement is run.

Example

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.

Spiffy's application uses a location name to construct a three-part table name in an INSERT statement. It then prepares the statement and executes it dynamically. The values to be inserted are transmitted to the remote location and substituted for the parameter markers in the INSERT statement.

The following overview shows how the application uses aliases for three-part names: 

Read in the alias values
Do for all locations
     Read location name
     Set up statement to prepare
     Prepare statement
a     Execute statement
End loop
Commit

After the application obtains the next alias of a remote table to be inserted, For example, REGION1PROJ (which is the DSN8D10.PROJ table at location SAN_JOSE), it creates the following character string:

INSERT INTO REGION1PROJ VALUES (?,?,?,?,?,?,?,?)

The alias is created as follows:

CREATE ALIAS REGION1PROJ FOR SAN_JOSE.DSN8D10.PROJ

The application assigns the character string to the variable INSERTX and then executes these statements: 

EXEC SQL
   PREPARE STMT1 FROM :INSERTX;
EXEC SQL
   EXECUTE STMT1 USING :PROJNO, :PROJNAME, :DEPTNO, :RESPEMP,
                       :PRSTAFF, :PRSTDATE, :PRENDATE, :MAJPROJ;

The host variables for Spiffy's project table match the declaration for the sample project table.

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.)

Three-part names and multiple servers

Recommendation: Always use an asterisk (*) for the location name in a pklist. Never use the explicit location name unless you are sure that no other location could ever be accessed.
The following steps are recommended:
  1. Bind the DBRM into a package at the local Db2.
  2. Bind package copy at the first target site of the alias.
  3. Bind package copy at the target site.