Db2 Connect performance considerations

Performance is the way a computer system behaves given a particular workload. It is affected by the available resources and how they are used and shared. If you want to improve performance, you must first decide what you mean by performance.

You can choose many different performance metrics, including:

Response time: The interval between the time that the application sends the database request and the time that the application receives a response.
Transaction throughput: The number of units of work that can be completed per unit of time. The unit of work could be simple, like fetching and updating a row, or complicated, involving hundreds of SQL statements.
Data transfer rate: The number of bytes of data transferred between the Db2 Connect application and the IBM mainframe database per unit of time.

Performance will be limited by the available hardware and software resources. CPU, memory, and network adapters are examples of hardware resources. Communication subsystems, paging subsystems, mbuf for AIX®, is an example of a software resource.

Data Flows

Figure 1 shows the path of data flowing between the IBM mainframe database server and the workstation through Db2 Connect.

The IBM mainframe database and part of communication subsystem B are usually running on the same system. This system is made up of one or more CPUs, main storage, an I/O subsystem, DASD, and an operating system. Because other programs might share these components, resource contention could cause performance problems.
The network is composed of a combination of cables, hubs, communication lines, switches, and other communication controllers. For example, the network hardware interface B could be communication controllers such as 3745 or 3172 or a token ring adapter for an IBM® Power Systems server. There could be more than one transmission medium involved between network hardware interfaces A and B.
Network hardware interface A could be token ring, Ethernet**, other LAN adapter, or an adapter which supports the SDLC or X.25 protocols.
Db2 Connect and the communication subsystem A are usually located on the same system. For the scope of this discussion, it is assumed that the application is also on the same system.

Bottlenecks

Transaction throughput is dependent on the slowest component in the system. If you identify a performance bottleneck, you can often alleviate the problem by changing configuration parameters, allocating more resources to the problem component, upgrading the component, or adding a new component to offload some of the work.

You can use various tools to determine how much time a query spends in each component. This will give you an idea of which components should be tuned or upgraded to improve performance. For example, if you determine that a query spends 60% of its time in the Db2 Connect machine, you might want to tune Db2 Connect or (if you have remote clients) add another Db2 Connect machine to the network.

Benchmarking

Benchmarking compares performance in one environment with performance in another. Benchmarking can begin by running the test application in a normal environment. As a performance problem is narrowed down, specialized test cases can be developed to limit the scope of the function that is tested and observed.

Benchmarking does not need to be complex. Specialized test cases need not emulate an entire application in order to obtain valuable information. Start with simple measurements and increase the complexity only when warranted.

Characteristics of good benchmarks:

Each test is repeatable.
Each iteration of a test is started in the same system state.
The hardware and software used for benchmarking matches your production environment.
There are no functions or applications active in the system other than those being measured unless the scenario includes some other activity going on in the system.
Note: Applications that are started use memory even when they are minimized or idle. This could cause paging and skew the results of the benchmark.

Performance Tools

The following tables list some of the tools that can help you measure system performance. Because these tools themselves use system resources, you might not want to have them active all the time.

Table 1. Performance tools for CPU and memory usage
System	Tool	Description
AIX	vmstat, time, ps, tprof	Provides information about CPU or memory contention problems on the Db2 Connect workstation and remote clients.
Windows	Microsoft Performance Monitor	Runs as an add-in to the Microsoft Management Console.

Table 2. Performance tools for database activity
System	Tool	Description
All	Database monitor	Determines if the problem originates from the database.
System z®	IBM Tivoli® OMEGAMON® XE for Db2® Performance Monitor on z/OS®, ASG-TMON for Db2 (ASG), and CA Insight Performance Monitor for Db2 for z/OS (Computer Associates International, Inc.)	Monitors, analyzes and tunes the performance of IBM Db2 for z/OS and IBM Db2 applications to help detect problems so they can be isolated and resolved more quickly.
Windows	Microsoft Performance Monitor	Runs as an add-in to the Microsoft Management Console.

Table 3. Performance tools for network activity
System	Tool	Description
AIX	netpmon	Reports low level network statistics, including TCP/IP statistics such as the number of packet or frames received per second.
Network controller such as 3745	NetView Performance Monitor	Reports utilization of communication control and VTAM®.
Linux® and UNIX	netstat	Handles TCP/IP traffic.