Three-part names and aliases in distributed data applications

Using three-part object names and aliases provides the application with location transparency; objects can move to a new location without requiring changes to the application. Instead, the DBMS manages the underlying connections.

You can use three-part names to access data at a remote location, including tables and views. Using a three-part name, or an alias, an application program implicitly connects to each server. With these access methods, the database server controls where the statement executes.

A three-part name consists of:

  • A LOCATION name that uniquely identifies the remote server that you want to access
  • An AUTHORIZATION ID that identifies the owner of the object (the table or view) at the location that you want to access
  • An OBJECT name that identifies the object at the location that you want to access

Example

Begin general-use programming interface information.

This example shows how an application uses a three-part name in INSERT, PREPARE, and EXECUTE statements. Assume that the application obtains a location name, 'SAN_JOSE'. Next, it creates the following character string:

INSERT INTO SAN_JOSE.IDP101.PROJ VALUES (?,?,?,?,?)

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, :MAJPROJ;

The host variables match the declaration for the PROJ table.

End general-use programming interface information.
Recommendation: If you plan to port your application from a z/OS server to another server, you should not use three-part names. For example, a client application might connect to a z/OS server and then issue a three part-name for an object that resides on a Linux® server. Db2 for z/OS is the only server that automatically forwards SQL requests that reference objects that do not reside on the connected server.

A convenient alternative approach is to use aliases when creating character strings that become prepared statements, instead of using full three-part names.

Deciding between three-part names and aliases

Three-part names and aliases each have different advantages.

You can always use three-part names to reference data at another remote server. The advantage of three-part names is that they allow application code to run at different Db2 locations without the additional overhead of maintaining aliases. However, if the table locations change, you must also change the affected applications.

The advantage of aliases is that they allow you to move data around without needing to modify application code or interactive queries. However, if you move a table or view, you must drop the aliases that refer to those tables or views. Then, you can re-create the aliases with the new location names.