Distributed data is accessed in various ways, and various considerations must be made for preparing and binding programs that will be executing statements against this data. This section looks at connect options, program preparation, and bind options, as well as additional considerations for coordinating updates.
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 a 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 z/OS 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 and 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 it obtains a location namefor example, SAN_JOSEthe application 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.)
Instead of using full three-part names, it might be more convenient to use aliases when creating character strings that become prepared statements.
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, 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. To ensure that access to the second remote server is by DRDA access, we recommend that you (1) rebind the package at the first remote server with DBPROTOCOL(DRDA), and then (2) 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 by 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 include 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 include 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)*/
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 into packages that reside at that server. An application executes a CONNECT statement for each server; in turn, it can execute statements, such as INSERTs, at each server. In this case, the tables to be updated each have the same name, although 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. When looping through locations to which the application is already connected, the SET CONNECTION statement is used. The following overview shows how the application uses explicit CONNECTs:
Do for all locations (connect loop) Connect to location End connect loop Do for all input (read loop) Read input values Do for all locations (insert loop) Set Connection to location Execute insert statement End insert loop Commit End read loop Release all connections Commit
In connecting to each location, the application places the location name into the variable LOCATION_NAME and executes the following statement:
EXEC SQL CONNECT TO :LOCATION_NAME; EXEC SQL
For each insert to be done, the application inserts a new location name into the variable LOCATION_NAME and executes the following statements:
EXEC SQL SET CONNECTION 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.)
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 in the following ways.
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.
The following precompiler options are relevant to preparing a package to be run using DRDA access.
BIND PACKAGE Options
The following options of BIND PACKAGE are relevant to binding a package to be run using DRDA access.
BIND PLAN Options
The following options of BIND PLAN are particularly relevant to binding a plan that uses DRDA access.
Two or more updates are coordinated if they must all commit or all roll back 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 or 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, the two programs restore the two systems to a consistent point when activity resumes.
You cannot 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, no automatic provision brings the two systems back to a consistent point. Your program must assume that task.
For CICS and IMS, you cannot update at servers that do not support two-phase commit. For TSO and batch, you can update if and only if no other connections exist or if all existing connections are to servers that are restricted to read-only operations. If these conditions are not met, 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 no connections or only read-only connections exist, 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.
Rely on DB2 to prevent updates to two systems in the same unit of work if either of them is a restricted system.
If you are accessing a mixture of systems, some of which might be restricted, you can