Methods of Accessing Distributed Data

< Day Day Up >

You should note that the developer of a distributed application does not have to know the descriptions of remote request, RUW, and DUW. Ensuring that the application does not access multiple locations within a single request is sufficient. DB2 handles the distributed access based on the nature of the request(s).

Of course, an informed programmer is an efficient programmer. To enhance performance, application developers should be aware of the location at which the data to be accessed exists.

A DB2 application developer has two choices for the manner in which distributed data is accessed:

Application-directed access
System-directed access

In the following sections, you will examine these two methods of distributed data access.

Application-Directed Data Access

Application-directed data access is the more powerful of the two options. With this access, explicit connections are required. Furthermore, application-directed distributed access conforms to the DRDA standard.

Establishing Connections

When implementing application-directed distribution, the application must issue a CONNECT statement to the remote location, prior to accessing data from that location. Consider this example:

 CONNECT TO CHICAGO;

This statement connects the application to the location named CHICAGO . The connection must be a valid location, as defined in the SYSIBM.LOCATIONS (or SYSBM.SYSLOCATIONS ) table. Multiple locations can be connected at once. For example, an application can issue the following:

 CONNECT TO CHICAGO;         .         .         . CONNECT TO JACKSONVILLE;         .         .         . CONNECT TO PITTSBURGH;

In this scenario, three connections have been established ”one each to Chicago, Jacksonville, and Pittsburgh. The CONNECT statement causes a VTAM conversation to be allocated from the local site to the specified remote location. Therefore, if the preceding example were to be issued from Seattle, three VTAM conversations would be established:

One from Seattle to Chicago
One from Seattle to Jacksonville
One from Seattle to Pittsburgh

However, only one connection can be active at any one time. You use the SET CONNECTION statement to specify which connection should be active. Now look at this example:

 SET CONNECTION PITTSBURGH;

This statement sets the active connection to Pittsburgh. Additionally, the SET CONNECTION statement places the previously active connection into a dormant state.

In all the preceding examples (for both CONNECT and SET CONNECTION ), you could have used a host variable in place of the literal, as in this example:

 SET CONNECTION :HV;

This statement sets the active connection to be whatever location was stored in the host variable at the time the statement was executed.

Releasing Connections

After it is established, a connection is available for the duration of the program unless it is explicitly released or the DISCONNECT BIND option was not set to EXPLICIT (which is the default).

Connections are explicitly released using the RELEASE statement, as shown here:

 RELEASE PITTSBURGH;

This statement releases the connection to the Pittsburgh location. Valid options that can be specified on the RELEASE statement are

A valid location specified as a literal or a host variable
CURRENT , which releases the currently active connection
ALL , which releases all connections
ALL PRIVATE , which releases DB2 private connection and is discussed in the next section

The DISCONNECT BIND option also affects when connections are released. You can specify this option for plans only. It applies to all processes that use the plan and have remote connections of any type. The following DISCONNECT parameters are valid:

`EXPLICIT`	This option is the default. It indicates that only released connections will be destroyed at a `COMMIT` point.
`AUTOMATIC`	This option specifies that all remote connections are to be destroyed at a `COMMIT` point.
`CONDITIONAL`	This option specifies that all remote connections are to be destroyed at a `COMMIT` point unless a `WITH HOLD` cursor is associated with the conversation.

System-Directed Data Access

In addition to application-directed distribution, DB2 also provides system-directed access to distributed DB2 data. The system-directed access is less flexible than application-directed access because of it is viable for DB2-to-DB2 distribution only, and connections cannot be explicitly requested (instead connections are implicitly performed when distributed requests are initiated).

Although system-directed access does not conform to DRDA, it does provide the same levels of distributed support as application-directed access ”remote request, RUW, and DUW. System-directed access is requested using three-part table names , as shown in this example:

 SELECT  COL1, COL2, COL7 FROM    PITTSBURGH.OWNER.TABLE WHERE   KEY = :HV;

Issuing this request causes an implicit connection to be established to the Pittsburgh location. DB2 determines the location by using the high-level qualifier of the three-part name . This type of distribution is called system-directed because the system (DB2), not the application, determines to which location to connect.

Optionally , you can create an alias for the three-part table name. The alias enables users to access a remote table (or view) without knowing its location. Here's an example:

 CREATE ALIAS EMP FOR PITTSBURGH.OWNER.EMPLOYEE; SELECT COL1, COL2 FROM   EMP;

The first statement creates the alias EMP for the EMPLOYEE table located in Pittsburgh. The second statement requests the data from the Pittsburgh EMPLOYEE table using the alias, EMP . Note that the three-part name is avoided.

Three-Part Name Support with DRDA

Applications (running DB2 V6 and later) can use three-part names to access distributed data and still use DRDA. Applications that used private protocol distribution can now use DRDA protocol with no program code or database naming changes.

To use DRDA protocol with three-part names, you must BIND a package at each location that is specified in a three-part name and then BIND a package or plan at the local location specifying the DBPROTOCOL(DRDA) BIND option. You do not need to re-code any logic, nor do you need to rename any database objects.

CAUTION

IBM will eventually phase out private protocol distribution in a subsequent release of DB2. IBM continues to support private protocol distribution to provide support for legacy applications written using the private protocol before DRDA support was provided. Now that DB2 provides the ability to use DRDA with three-part names, private protocol distribution will not likely be supported by IBM much longer. And even though IBM still supports private protocol, it is no longer being enhanced. Therefore, you should avoid implementing new applications using private protocol distribution.

Converting Private Protocol to DRDA

To convert an application that uses private protocol distribution to use DRDA instead, follow these steps:

First you must determine the locations that are accessed by the application. To do this, you can look for SQL statements in the application that access three-part names. The first component of the three-part name is the location name. If the application uses aliases, you can query the DB2 Catalog to determine the location of the alias using the following SQL SELECT statement:
```
 
```
```
 SELECT LOCATION, CREATOR, NAME, TBCREATOR, TBNAME FROM   SYSIBM.SYSTABLES WHERE  NAME = 'alias name' AND    TYPE = 'A'; 
```
If the application uses dynamic SQL instead of static SQL, simply BIND packages at all remote locations that users access using three part names.
Using the list of locations obtained in step 1, BIND a package at each of the locations. You can also BIND a package locally (optionally, you can just use the DBRM).

NOTE

If the application combines application-directed and system-directed access by using a CONNECT to get to a remote location, and then three-part names to get yet another location, you must BIND a package specifying DBPROTOCOL(DRDA) at the first remote location and another package at the third location.
BIND all remote packages into a plan with the local package or DBRM. Use the DBPROTOCOL(DRDA) option when issuing the BIND for this plan.
Ensure that all aliases are accurate. When using private protocol distribution, aliases are resolved at the location that issues the request. However, for DRDA distribution, aliases are resolved at the location where the package is executed. So, you will need to create additional aliases at remote locations when switching from private protocol to DRDA.
If you use the resource limit facility (RLF) to control distributed requests, you will need to ensure that the RLF settings are applied correctly. When using private protocol, distribution plan names are provided to the RLF to govern SQL access. When using DRDA, you must specify package names instead of plan names.

Refer to Chapter 29, "DB2 Resource Governing," for additional information on the RLF.

< Day Day Up >