Miscellaneous Distributed Guidelines

Keep the following guidelines in mind as you implement distributed DB2 applications and databases.

Favor Type-2 Connections

Application-directed distribution is implemented using the CONNECT statement. DB2 supports two different types of CONNECT s:

• Type 1 CONNECT : Multiple CONNECT statements cannot be executed within a single unit of work.

• Type 2 CONNECT : Multiple CONNECT statements can be executed within a single unit of work.

Type 2 CONNECT s allow updates to be made to multiple locations within a single unit of work. If you connect to a system using a type 1 CONNECT , or if the system is at a level of DRDA that does not support two-phase commit, you can update at only one system within a single unit of work. Only one type 1 CONNECT statement is permitted within a single unit of work; however, multiple type 2 CONNECT statements can be executed within a single unit of work.

The type of CONNECT being utilized is determined by a precompiler option and the type of processing being performed by the program.

First, DB2 provides a precompiler option to set the type of connect: CONNECT . Specifying CONNECT(1) indicates that the program is to use type 1 CONNECT s; CONNECT(2) , which is the default, specifies type 2 CONNECT s are to be used.

Second, the type of connect to be used can be determined by the type of processing within your application. If the first CONNECT statement issued is a type 1 CONNECT , type 1 CONNECT rules apply for the duration of the program. If a type 2 CONNECT is executed first, type 2 CONNECT rules apply.

Choose Appropriate Distributed Bind Options

Several bind parameters affect the distributed environment. Ensuring that the proper parameters are used when binding plans and packages can greatly influence the performance of distributed applications. Refer to Table 44.2.

Table 44.2. Distributed Bind Parameter Recommendations

Parameter	Recommendation	Default	Applies [*]
CURRENTDATA	CURRENTDATA(NO)	CURRENTDATA(YES)	B
DEFER	DEFER(PREPARE)	NODEFER(PREPARE)	P
CURRENTSERVER	"it depends"	local DBMS	P
SQLRULES	"it depends"	SQLRULES(DB2)	P
DISCONNECT	DISCONNECT(EXPLICIT)	DISCONNECT(EXPLICIT)	P
SQLERROR	"it depends"	SQLERROR(NOPACKAGE)	K

^[*] The Applies column indicates whether the parameter applies to plans ( P ), packages ( K ), or both ( B ).

Review the information in Table 44.2. Block fetch is used as the default for ambiguous cursors if the package or plan was bound with the CURRENTDATA(NO) parameter. CURRENTDATA(YES) is not recommended because block fetch would be disabled.

When system-directed dynamic access is requested , specifying DEFER(PREPARE) causes only a single distributed message to be sent for the PREPARE , DESCRIBE , and EXECUTE statements. A plan bound specifying DEFER(PREPARE) generally outperforms one bound as NODEFER(PREPARE) . The default, of course, is NODEFER .

The CURRENTSERVER parameter specifies a connection to a location before the plan is executed. The server's CURRENTSERVER register is set to the location specified in the CURRENTSERVER option, and a type 1 CONNECT is issued. This way, the connection can be established prior to making a request. However, debugging an application without an explicit CONNECT is more difficult.

If adherence to the ANSI/ISO standards for remote connection is essential, you should bind using SQLRULES(STD) . The ANSI/ISO standard does not allow a CONNECT to be issued against an existing connection, whereas DB2 does. Always specify SQLRULES(DB2) if conformance to the ANSI/ISO standard is not required.

The DISCONNECT parameter determines when connections are to be released. Three options exist: EXPLICIT , AUTOMATIC , and CONDITIONAL . Refer to Chapter 42, "Distributed DB2," for a discussion of these parameters.

Finally, the SQLERROR option indicates what is to happen when SQL errors are encountered when binding a package. If SQLERROR(CONTINUE) is specified, a package is created even if some of the objects do not exist at the remote location. This way, the package can be bound before objects are migrated to a remote location. The default, SQLERROR(NOPACKAGE) , is the safer option.

Remove the Distributed Factor

A wise first step when investigating an error within a distributed environment is to remove the remote processing from the request and try again.

Trying to execute the request directly on the server instead of from a remote client eliminates potentially embarrassing problem scenarios. For example, consider an application in which two DB2 subsystems, DB2S and DB2R, are connected via DDF. An application executing from DB2R is unsuccessful in requesting data from DB2S . The recommended first step in resolving the problem is to ensure that the same request executes properly on DB2S as a local request.

Distributed problem determination should ensue only if the request is successful.

Maintain a Problem Resolution Log

Keep a written record of problems encountered in the distributed environment. You should establish and strictly maintain this problem resolution log. You should include every unique problem, along with its solution, in the log. A sample problem resolution log form is shown in Figure 44.7.

Figure 44.7. Distributed problem resolution log.

For optimum effectiveness, the log should be automated for ease of maintenance. Anyone involved in distributed problem determination should be permitted to access and update the log. The log should be readily available and stored in a central location. If you review past problems, you can more easily resolve current problems and avoid future problems.