Distributed Performance Problems

Recall the definition of performance given in Part IV. Performance in a distributed environment also can be defined in terms of throughput and response time. The requester and the server each place a different degree of emphasis on these two aspects.

The server views performance primarily in terms of throughput. Remember that throughput is the amount of work that can be done in a unit of time.

The requester views performance more in terms of response time. Response time is more visible to the end user . Recall that response time is the amount of time required to accomplish a predefined set of work.

Analyzing Distributed Throughput

When analyzing the throughput of a given distributed DB2 implementation, you must examine each component of the implementation. Failure to analyze every component may result in an overall performance degradation caused by a single weak link.

The combination of all components used to process a transaction is called the throughput chain . A sample throughput chain can include a combination of the following components:

• Requester hardware

• Local/requester operating system (Linux, AIX, z/OS, and so on)

• Local DB2

• Network operating system

• Actual network (or LAN)

• Middleware (or gateway)

• Mainframe

• z/OS

• Server DB2

• DASD

Each link in the chain may be necessary to complete a given transaction. The best throughput that any given configuration can achieve is always confined by the slowest component on the chain.

To achieve optimal performance, you should spend more tuning and optimization effort on the weaker links in the throughput chain.

Factors Affecting Throughput

The three biggest factors affecting throughput in a distributed environment are hardware, contention , and availability.

The processing speed of the hardware used in the distributed environment has a big impact on throughput. Factors such as processor speed (MIPS), available memory, physical configuration, and DASD speed have an impact on the throughput component of performance.

When the demand for a particular resource is high, contention results. When two or more processes attempt to utilize a particular resource in a conflicting manner, contention degrades overall performance. In a distributed environment, the number of locations that can utilize a resource increases ; thus, contention problems usually increase.

The final factor is availability. In a distributed environment, multiple computing platforms are used. If one of these platforms breaks down or becomes otherwise unavailable (such as with a communication problem), throughput is affected. Depending on application design, throughput may

• Increase, if transactions continue to be processed . Work targeted for the unavailable component must be saved so that it can be applied later when the unavailable component becomes available.

• Decrease, if logic has not been coded to handle unavailable components, and transactions start to "hang."

• Become nonexistent, if all work is suspended until the unavailable component is made available again.

NOTE

Plan for periods of resource unavailability in a distributed environment and code distributed DB2 application programs accordingly .

Analyzing Distributed Response Time

Response time is typically easier to comprehend than throughput. Usually, a throughput problem comes to light as a result of a complaint about response time.

End users are the typical bearers of bad news about response-time problems. As the actual patrons of the system, they understand its basic performance patterns. When response time suffers, end users tend to voice their dissatisfaction quickly.

Online performance monitoring tools and performance reports are other means of gauging response-time problems.

General Distributed Performance Guidelines

When developing distributed DB2 applications, implement the following techniques to ensure optimal performance.

Standard DB2 Performance Tuning Techniques

Follow standard DB2 performance tuning techniques, as outlined in Part V, "DB2 Performance Tuning."

Minimize the SQL Result Set

Be sure to access only the data that is actually required by the application. Do not access more data than is necessary and filter it out in the application program. Although this tip is a standard SQL tuning rule of thumb, it is particularly applicable in a distributed environment. When fewer rows qualify, less data is sent over the communication lines. And remember, network- related problems tend to be a significant obstacle in distributed environments.

Use OPTIMIZE FOR n ROWS

Client programs can use the OPTIMIZE FOR n ROWS clause to optimize the retrieval of a large number of rows. To retrieve multiple query blocks on each network transmission, specify a large value for n , in the OPTIMIZE FOR n ROWS clause for queries that must return a large number of rows. Favor this technique if your application has the following qualities:

• A large number of rows are fetched from read-only queries

• The cursor is not closed before all of the result set is fetched

• No additional SQL statements are issued to the DB2 server while the cursor remains open

• Only one cursor at a time is open and being fetched from that is defined with the OPTIMIZE FOR n ROWS clause

This can result in a reduced number of network transmission, and therefore, enhanced performance.

Distributed Buffer Pool

The buffer pool that will hold the distributed data, after it has been sent from the server to the client, is the buffer pool in which the CDB is defined. Ensure that adequate space has been allocated to accommodate distributed data access in the aforementioned buffer pool.

NOTE

As of DB2 V5, the CDB tables were moved to the DB2 Catalog. This means that these tables must use BP0.

DDF Dispatching Priority

When DB2 is used as a database server in a distributed environment, the dispatching priority of the DDF address space should be reanalyzed.

The general recommendation made in Chapter 27, "Tuning DB2's Environment," (see Figure 27.3) is to code the dispatching priority of DSNDDF on a par with IMS MP regions (below short-running TSO requests but above medium-running TSO requests ). However, in a distributed environment with critical distributed transactions, consider changing the dispatching priority of DSNDDF to a higher position in the hierarchy. Refer to Figure 44.6.

Figure 44.6. Distributed dispatching priority hierarchy.

You should set the dispatching priority of DSNDDF so that it is not so high as to affect overall system performance but not so low as to degrade the performance of distributed DB2 requests.

In general, higher dispatching priorities should be reserved for I/O-bound applications. Because DSNDDF is a low CPU consumer, setting a higher DPRTY may prove to be advantageous.

CAUTION

Ensure that a higher DSNDDF dispatching priority does not cause excessive resource consumption. If you decide to experiment with the dispatching priority of DSNDDF , thoroughly test different priority hierarchies in your shop until you're satisfied that DDF is at an appropriate level.

Tuning VTAM Parameters

Before you implement distributed DB2 applications, buy your VTAM systems programmer lunch ! (Most system programmers have a ravenous appetite; buy them food, and they'll be your friends for life.)

The performance of DB2 in a distributed environment depends heavily on ensuring that the appropriate VTAM parameters are coded for the type of distributed applications to be implemented. The following VTAM parameters are important ”pacing rate, VTAM delay, and conversation queuing:

If the VTAM pacing rate is set high, and your application retrieves multiple rows, the communication channels can become flooded, consuming an inordinate amount of system resources.

Avoid the VTAM DELAY parameter when your application is coded to retrieve single rows. The DELAY parameter causes a planned wait that would impede performance.

Queuing of conversations can greatly increase response time. Consider increasing CONVLIMIT if the number of queued conversations is high. Likewise, if the number of queued conversations is very low or zero, consider decreasing CONVLIMIT . Start the DB2 global trace, IFCID 167 , to collect information on queued conversation requests.

The number of conversations that a remote DB2 subsystem can handle is controlled in the SYSIBM.LUMODES table. You use the CONVLIMIT column of LUMODES to set the limit of conversations per DB2 subsystem (in the LUNAME column) per VTAM logon mode (in the MODENAME column).

For a change to CONVLIMIT to take place, the DDF address space must be recycled. Whenever you're making these types of changes, be sure to keep your VTAM systems programmer in the loop, because setting these values overrides the VTAM DSESLIM parameter, and the VTAM systems programmer usually has a much better idea (than a DB2 DBA or analyst) of what these numbers should be.