Coding Methods for Distributed Data

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 10.  Accessing Distributed Data


There are different ways of accessing distributed data and there are also considerations for preparing and binding programs that will be executing statements against this data. We will take a look at connect options, program preparation, and bind options, as well as additional considerations for coordinating updates.

Three-Part Names

You can use three-part table names to access data at a remote location through DRDA access or DB2 private protocol access. When you use three-part table names, the way you code your application is the same, regardless of the access method you choose. You determine the access method when you bind the SQL statements into a package or plan. If you use DRDA access, you must bind the DBRMs for the SQL statements to be executed at the server into packages that reside at that server. Because platforms other than DB2 for OS/390 might not support the three-part name syntax, you should not code applications with three-part names if you plan to port those applications to other platforms.

In a three-part table name, the first part denotes the location. The local DB2 makes and breaks an implicit connection to a remote server as needed. An application uses a location name to construct a three-part table name in an SQL statement. It then prepares the statement and executes it dynamically. If it is an INSERT, the values to be inserted are transmitted to the remote location and substituted for the parameter markers in the INSERT statement. The following overview shows how the application uses three-part names:

 Read input values  Do for all locations Read location name Set up statement to prepare Prepare statement Execute statement End loop Commit 

After the application obtains a location name, for example SAN_JOSE, it creates the following character string:

 INSERT INTO SAN_JOSE.DSN8710.PROJ VA UES (?,?,?,?,?,?,?,?) 

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

To keep the data consistent at all locations, the application commits the work only when the loop has executed for all locations. Either every location has committed the INSERT or, if a failure has prevented any location from inserting, all locations have rolled back the INSERT. (If a failure occurs during the commit process, the entire unit of work can be in doubt.)

NOTE

graphics/note_icon.jpg

It might be more convenient to use aliases when creating character strings that become prepared statements, instead of using full three-part names.


Three-Part Names and Multiple Servers

If you use a three-part name or an alias that resolves to one in a statement executed at a remote server by DRDA access, and if the location name is not that of the server, then the method by which the remote server accesses data at the named location depends on the value of DBPROTOCOL. If the package at the first remote server is bound with DBPROTOCOL(PRIVATE), DB2 uses DB2 private protocol access to access the second remote server. If the package at the first remote server is bound with DBPROTOCOL(DRDA), DB2 uses DRDA access to access the second remote server. We recommend that you follow these steps so that access to the second remote server is by DRDA access:

  • Rebind the package at the first remote server with DBPROTOCOL(DRDA).

  • Bind the package that contains the three-part name at the second server.

Accessing Declared Temporary Tables

You can access a remote declared temporary table using a three-part name only if you use DRDA access. However, if you combine explicit CONNECT statements and three-part names in your application, a reference to a remote declared temporary table must be a forward reference. For example, you can perform the following series of actions, which includes a forward reference to a declared temporary table:

 EXEC SQL CONNECT TO CHICAGO; /* Connect to the remote site */  EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEMPPROD /*Define the temporary table*/  (CHARCO CHAR(6) NOT NULL); /* at the remote site */ EXEC SQL CONNECT RESET; /* Connect back to local site */ EXEC SQL INSERT INTO CHICAGO.SESSION.T1 (VALUES 'ABCDEF'); /* Access the temporary table*/ /* at the remote site (forward reference) */ 

However, you cannot perform the following series of actions, which includes a backward reference to the declared temporary table:

 EXEC SQL  DECLARE GLOBAL TEMPORARY TABLE TEMPPROD /*Define the temporary table*/ (CHARCO CHAR(6) NOT NULL); /* at the local site (ATLANTA)*/ EXEC SQL CONNECT TO CHICAGO; /* Connect to the remote site */ EXEC SQL INSERT INTO ATLANTA.SESSION.T1 (VALUES 'ABCDEF'); /* Cannot access temp table */ /* from the remote site (backward reference)*/ 

Using Explicit CONNECT Statements

With this method the application program explicitly connects to each new server. You must bind the DBRMs for the SQL statements to be executed at the server to packages that reside at that server. An application executes CONNECT for each server, and in turn the server can execute statements such as INSERTs . In this case the tables to be updated each have the same name, though each is defined at a different server. The application executes the statements in a loop, with one iteration for each server.

The application connects to each new server by means of a host variable in the CONNECT statement. CONNECT changes the special register CURRENT SERVER to show the location of the new server. The values to insert in the table are transmitted to a location as input host variables . The following overview shows how the application uses explicit CONNECTs:

 Read input values  Do for all locations Read location name Connect to location Execute insert statement End loop Release all Commit 

The application inserts a new location name into the variable LOCATION_NAME, and executes the following statements:

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

To keep the data consistent at all locations, the application commits the work only when the loop has executed for all locations. Either every location has committed the INSERT or, if a failure has prevented any location from inserting, all other locations have rolled back the INSERT. (If a failure occurs during the commit process, the entire unit of work can be in doubt.)

Releasing Connections

When you connect to remote locations explicitly, you must also break those connections explicitly. You have considerable flexibility in determining how long connections remain open, so the RELEASE statement differs significantly from CONNECT.

Differences Between CONNECT and RELEASE
  • CONNECT makes an immediate connection to exactly one remote system.

  • CONNECT (Type 2) does not release any current connection.

  • RELEASE does not immediately break a connection. The RELEASE statement labels connections for release at the next commit point. A connection so labeled is in the release-pending state and can still be used before the next commit point.

  • RELEASE can specify a single connection or a set of connections for release at the next commit point.

Preparing Programs for 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. Binding a plan to run the package is like binding any other plan.

Precompiler Options

The following precompiler options are relevant to preparing a package to be run using DRDA access:

  • CONNECT: Use CONNECT(2) explicitly or by default. CONNECT(1) causes your CONNECT statements to allow only the restricted function known as remote unit of work. Be particularly careful to avoid CONNECT(1) if your application updates more than one DBMS in a single unit of work.

  • SQL: Use SQL(ALL) explicitly for a package that runs on a server that is not DB2 for OS/390. The precompiler then accepts any statement that obeys DRDA rules. Use SQL(DB2) explicitly or by default if the server is DB2 for OS/390 only. The precompiler then rejects any statement that does not obey the rules of DB2 for OS/390.

BIND PACKAGE Options

The following options of BIND PACKAGE are relevant to binding a package to be run using DRDA access:

  • location-name: Name the location of the server at which the package runs. The privileges needed to run the package must be granted to the owner of the package at the server. If you are not the owner, you must also have SYSCTRL authority or the BINDAGENT privilege granted locally.

  • SQLERROR: Use SQLERROR(CONTINUE) if you used SQL(ALL) when precompiling. That creates a package even if the bind process finds SQL errors, such as statements that are valid on the remote server but that the precompiler did not recognize. Otherwise, use SQLERROR(NOPACKAGE) explicitly or by default.

  • CURRENTDATA: Use CURRENTDATA(NO) to force block fetch for ambiguous cursors .

  • OPTIONS: When you make a remote copy of a package using BIND PACKAGE with the COPY option, use this option to control the default bind options that DB2 uses. Specify COMPOSITE to cause DB2 to use any options you specify in the BIND PACKAGE command. For all other options, DB2 uses the options of the copied package. This is the default command to cause DB2 to use the options you specify in the BIND PACKAGE command. For all other options, DB2 uses the defaults for the server on which the package is bound. This helps ensure that the server supports the options with which the package is bound.

  • DBPROTOCOL: Use DBPROTOCOL(PRIVATE) if you want DB2 to use DB2 private protocol access for accessing remote data that is specified with three-part names. Use DBPROTOCOL(DRDA) if you want DB2 to use DRDA access to access remote data that is specified with three-part names. You must bind a package at all locations whose names are specified in three-part names. These values override the value of DATABASE PROTOCOL on installation panel DSNTIP5. Therefore, if the setting of DATABASE PROTOCOL at the requester site specifies the type of remote access you want to use for three-part names, you do not need to specify the DBPROTOCOL bind option.

BIND PLAN Options

The following options of BIND PLAN are particularly relevant to binding a plan that uses DRDA access:

  • DISCONNECT: For the most flexibility, use DISCONNECT(EXPLICIT) explicitly or by default. That requires you to use RELEASE statements in your program to explicitly end connections. But the other values of the option are also useful. DISCONNECT(AUTOMATIC) ends all remote connections during a commit operation without the need for RELEASE statements in your program. DISCONNECT(CONDITIONAL) ends remote connections during a commit operation except when an open cursor defined as WITH HOLD is associated with the connection.

  • SQLRULES: Use SQLRULES(DB2) explicitly or by default. SQLRULES(STD) applies the rules of the SQL standard to your CONNECT statements, so that CONNECT TO x is an error if you are already connected to x . Use STD only if you want that statement to return an error code. If your program selects LOB data from a remote location, and you bind the plan for the program with SQLRULES(DB2), the format in which you retrieve the LOB data with a cursor is restricted. After you open the cursor to retrieve the LOB data, you must retrieve all of the data using an LOB variable or retrieve all of the data using an LOB locator variable. If the value of SQLRULES is STD, this restriction does not exist. If you intend to switch between LOB variables and LOB locators to retrieve data from a cursor, execute the SET SQLRULES=STD statement before you connect to the remote location.

  • CURRENTDATA: Use CURRENTDATA(NO) to force block fetch for ambiguous cursors.

  • DBPROTOCOL: Use DBPROTOCOL(PRIVATE) if you want DB2 to use DB2 private protocol access for accessing remote data that is specified with three-part names. Use DBPROTOCOL(DRDA) if you want DB2 to use DRDA access to access remote data that is specified with three-part names. You must bind a package at all locations whose names are specified in three-part names. The package value for the DBPROTOCOL option overrides the plan option. For example, if you specify DBPROTOCOL(DRDA) for a remote package and DBPROTOCOL(PRIVATE) for the plan, DB2 uses DRDA access when it accesses data at that location using a three-part name. If you do not specify any value for DBPROTOCOL, DB2 uses the value of DATABASE PROTOCOL on installation panel DSNTIP5.

Coordinating Updates

Two or more updates are coordinated if they must all commit or all rollback in the same unit of work. Updates to two or more DBMSs can be coordinated automatically if both systems implement a method called two-phase commit.

DB2 and IMS, and DB2 and CICS, jointly implement a two-phase commit process. You can update an IMS database and a DB2 table in the same unit of work. If a system or communication failure occurs between committing the work on IMS and on DB2, then the two programs restore the two systems to a consistent point when activity resumes.

You cannot really have coordinated updates with a DBMS that does not implement two-phase commit. In the description that follows , we call such a DBMS a restricted system. DB2 prevents you from updating both a restricted system and any other system in the same unit of work. In this context, update includes the statements INSERT, DELETE, UPDATE, CREATE, ALTER, DROP, GRANT, REVOKE, and RENAME.

To achieve the effect of coordinated updates with a restricted system, you must first update one system and commit that work, and then update the second system and commit its work. If a failure occurs after the first update is committed and before the second is committed, there is no automatic provision for bringing the two systems back to a consistent point. Your program must assume that task.

CICS and IMS

You cannot update at servers that do not support two-phase commit.

TSO and batch

You can update if and only if

  • No other connections exist, or

  • All existing connections are to servers that are restricted to read-only operations.

If these conditions are not met, then you are restricted to read-only operations.

If the first connection in a logical unit of work is to a server that supports two-phase commit, and there are no existing connections or only read-only connections, then that server and all servers that support two-phase commit can update. However, if the first connection is to a server that does not support two-phase commit, only that server is allowed to update.

NOTE

graphics/note_icon.jpg

Rely on DB2 to prevent updates to two systems in the same unit of work if either of them is a restricted system.


Without Two-Phase Commit

If you are accessing a mixture of systems, some of which might be restricted, you can

  • Read from any of the systems at any time.

  • Update any one system many times in one unit of work.

  • Update many systems, including CICS or IMS, in one unit of work, provided that none of them is a restricted system. If the first system you update in a unit of work is not restricted, any attempt to update a restricted system in that unit of work returns an error.

  • Update one restricted system in a unit of work, provided that you do not try to update any other system in the same unit of work. If the first system you update in a unit of work is restricted, any attempt to update any other system in that unit of work returns an error.


Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net