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, 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 host or System i™ 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 host or System i database server and the workstation through DB2 Connect.

• The host or System i 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 System i 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 off-load 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.

• 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.