Distributed data access

Distributed computing environments typically involve requests from users at one DBMS client that are processed by a DBMS server. The server DBMS is typically remote to the client. Certain programming techniques and performance implications apply to distributed computing environments.

The Db2 distributed environment supports both a two-tier and a multitier architecture.

A DBMS, whether local or remote, is known to your Db2 subsystem by its location name. Remote systems use the location name, or an alias location name, to access a Db2 subsystem. You can define a maximum of eight location names for a Db2 subsystem.

The location name of the Db2 subsystem is defined in the BSDS during Db2 installation. The communications database (CDB) records the location name and the network address of a remote DBMS. The CDB is a set of tables in the Db2 catalog.

The primary method that Db2 uses for accessing data at a remote server is based on Distributed Relational Database Architecture™ (DRDA).

If your application performs updates to two or more DBMSs, a transaction manager coordinates the two-phase commit process and guarantees that units of work are consistently committed or rolled back. IfDb2 requests updates to two or more DBMSs, Db2 acts as the transaction manager. The distributed commit protocols that are used on the network connection dictate whether both DBMSs can perform updates or whether updates are restricted to a single DBMS.

Examples

The following examples show statements that you can use to access distributed data.

Begin general-use programming interface information.

Example 1

To access data at a remote server, write statements like the following example:

EXEC SQL
  CONNECT TO CHICAGO;
SELECT * FROM IDEMP01.EMP
  WHERE EMPNO = '000030';

You can also accomplish the same task by writing the query like the following example:

SELECT * FROM CHICAGO.IDEMP01.EMP
  WHERE EMPNO = '000030';

Before you can execute either query at location CHICAGO, you must bind a package at the CHICAGO server.

Example 2

You can call a stored procedure to access data at a remote server. Your program executes these statements:

EXEC SQL
  CONNECT TO ATLANTA;
EXEC SQL
  CALL procedure_name (parameter_list);

The parameter list is a list of host variables that is passed to the stored procedure and into which it returns the results of its execution. The stored procedure must exist at location ATLANTA.

End general-use programming interface information.