Programming techniques for accessing remote servers

As the chapter introduction explains, you can connect to a remote server in different ways. You can code an application that uses DRDA to access data at a remote location by using these methods:

• CONNECT statements

• Three-part names and aliases

Using CONNECT statements provides application portability across all IBM clients and requires that the application manage connections. 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.

Using either method, you must bind the DBRMs for the SQL statements that are to execute at the server to packages that reside at the server.

• At the local DB2, use the BIND PLAN command to build an application plan.

• At the remote location, use the BIND PACKAGE command to build an application package that uses the local application plan.

Using explicit CONNECT statements

With the CONNECT statement, an application program explicitly connects to each server. You must bind the DBRMs for the SQL statements that are to execute at the server to packages that reside at that server.

The application connects to each server based on the location name in the CONNECT statement. You can explicitly specify a location name, or you can specify a location name in a host variable. Issuing the CONNECT statement changes the special register CURRENT SERVER to show the location of the new server.

Example: Assume that an application includes a program loop that reads a location name, connects to the location, and executes an INSERT statement. The application inserts a new location name into a host variable, :LOCATION_NAME, and executes the following statements:

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

The host variables match the declaration for the PROJ table.

DB2 guarantees the consistency of data across a distributed transaction. To keep the data consistent at all locations, the application commits the work only after the program loop executes for all locations. Either every location commits the INSERT or, if a failure prevents any location from inserting, all other locations roll back the INSERT.

Using three-part names

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 will execute.

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 you want to access

• An OBJECT name that identifies the object at the location you want to access

Example: 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.

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

Using aliases

Suppose that data is occasionally moved from one DB2 subsystem to another. Users who query that data should not be affected when this activity occurs. They always want to log on to the same system and access the same table or view, regardless of where the data resides. You can ensure this result by using an alias for an object name.

An alias is a substitute for the three-part name of a table or view. The alias can be a maximum of 128 characters, qualified by an owner ID. You use the CREATE ALIAS and DROP ALIAS statements to manage aliases.

Example: Assume that you create an alias as follows:

CREATE ALIAS TESTTAB FOR USIBMSTODB22.IDEMP01.EMP;

If a user with the ID JONES dynamically creates the alias, JONES owns the alias and you query the table like this:

SELECT SUM(SALARY), SUM(BONUS), SUM(COMM)
      FROM JONES.TESTTAB;

The object for which you are defining an alias does not need to exist when you execute the CREATE ALIAS statement. However, the object must exist when a statement that refers to the alias executes.

When you want an application to access a server other than the server that is specified by a location name, you do not need to change the location name. Instead, you can use a location alias to override the location name that an application uses to access a server. As a result, a DB2 UDB for z/OS requester can access multiple DB2 databases that have the same name but different network addresses. Location aliases allow easier migration to a DB2 server and minimize application changes.

After you create an alias, anyone who has authority over the object that the alias is referencing can use that alias. A user does not need a separate privilege to use the alias.

Comparing three-part names and aliases

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.

Coding considerations

This section explains some coding considerations for using DRDA access.

• Stored procedures

  If you use DRDA access, your program can call stored procedures. Stored procedures behave like subroutines that can contain SQL statements and other operations. “Using an application program as a stored procedure” on page 202 has detailed information about stored procedures.

• Three-part names and multiple servers

  Assume that a statement runs at a remote server (server 1). That statement uses a three-part name or an alias that resolves to a three-part name. The statement includes a location name of a different server (server 2). The package at the first remote server (server 1) is bound with DBPROTOCOL(DRDA). This bind option tells DB2 to use DRDA access to access the second remote server (server 2).

  Recommendation: Follow these steps to ensure that access to the second remote server is by DRDA access:

   

  1.	Rebind the package at the first remote server with the option DBPROTOCOL(DRDA).
  2.	Bind the package that contains the three-part name at the second server.

• SQL differences at servers other than DB2 UDB for z/OS

  With explicit connections, a program that uses DRDA access can use SQL statements that a remote server supports, even if the local server does not support them. A program that uses three-part object names cannot execute non-z/OS SQL.

Program preparation considerations

This section gives you an overview of some unique considerations about the precompile and bind options that are used for DRDA access and package resolution. (“Preparing an application program to run” on page 182 has general instructions about program preparation.)

Table 11.1 lists the z/OS precompiler options that are relevant to preparing a package to be run using DRDA access.

For the most part, binding a package to run at a remote location is like binding a package to run at your local DB2 subsystem. Binding a plan to run the package is like binding any other plan. Table 11.2 gives you guidelines for which z/OS bind options to choose when binding a package and plan to run using DRDA access.

JDBC, SQLJ, and ODBC use different methods for binding packages that involve less preparation for accessing a z/OS server.

You read about the CURRENT PACKAGE PATH special register in “Preparing an application program to run” on page 182. This special register provides a benefit for applications that use DRDA from a z/OS requester. The package collection ID is resolved at the server. Applications on the server can take advantage of the list of collections, and network traffic is minimal.

Example: Assume that five packages exist and that you want to invoke the first package at the server. The package names are SCHEMA1.PKG1, SCHEMA2.PKG2, SCHEMA3.PKG3, SCHEMA4.PKG4, and SCHEMA5.PKG5. Rather than issuing a SET CURRENT PACKAGESET statement to invoke each package, you can use a single SET statement if the server supports the CURRENT PACKAGE PATH special register:

SET CURRENT PACKAGE PATH = SCHEMA1, SCHEMA2, SCHEMA3,
SCHEMA4, SCHEMA5;

Planning considerations

When you work in a distributed environment, you need to consider how authorization works and the cost of running SQL statements.

The appropriate authorization ID must have authorization at a remote server to connect to and to use resources at that server.

You can use the resource limit facility at the server to govern distributed dynamic SQL statements. Using this facility, a server can govern how much of its resources a given package can consume by using DRDA access.