Introduction to distributed data access

This chapter assumes that you are requesting services from a remote DBMS. That DBMS is a server in that situation, and your local system is a client.

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). (Another access method, DB2 private protocol, is also available. Because this alternative method is not recommended, this book does not discuss it.) “Distributing data and providing Web access” on page 9 introduces you to DRDA; “Distributed data” on page 60 has additional information.

If your application performs updates against two or more DBMSs, each DBMS guarantees that units of work are consistently committed or rolled back. 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.

The examples that follow show statements that you can use to access distributed data. “Using explicit CONNECT statements” on page 378 and “Using three-part names” on page 379 discuss each type of statement in more detail.

Example: You can write statements like these to access data at a remote server:

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

You can also write a query like this to accomplish the same task:

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. You can read more about bind options in “Program preparation considerations” on page 381.

Example: 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 already exist at location ATLANTA.