Developers next step is Tuning SQL. Before yo do it learn the tools required. Below post gives an idea on how to fix performance issues in DB2.
After you ran an application you determine that it does not meet your performance objectives, you need to determine the origin of the problem.
To identify a performance problem, you should begin by looking at the overall system before you decide that you have a problem in DB2. In general, look closely to see why application processes are progressing slowly or why a given resource is being heavily used.Tweet
Within DB2, the performance problem is either poor response time or an unexpected and unexplained high use of resources. Check factors such as total processor usage, disk activity, and paging.
First, get a picture of task activity, from classes 1, 2, and 3 of the accounting trace. DB2 provides a trace facility that lets you monitor and collect detailed information about DB2, including performance and statistical information.
Then, focus on specific activities, such as specific application processes or a specific time interval. You might see problems such as these: Slow response time.
These two ways limit the number of rows return from an SQL query. These are helpful for Oracle and other SQL developers.
You can collect detailed information about one slow task, a problem that can be explained by several reasons. For example, users might be trying to do too much work with certain applications, and the system simply cannot do all the work that they want done.
Real storage constraints. Applications progress more slowly than expected because of paging interrupts. The constraints show as delays between successive requests that are recorded in the DB2 trace. If you identify a performance problem in DB2, you can look at specific reports. Reports give you information about the following factors:
- Whether applications are able to read from buffer pools rather than from disk
- Whether and how long applications must wait to write to disk or wait for a lock for multiple users
- Whether applications are using more than the usual amount of resources
DB2 also provides several tools that help you analyze performance.
- DB2 Visual Explain
- DB2 Performance Expert