Performance in DB2 is caused by many reasons. As a Developer you need to focus on Tuning SQL. As a performance expert or DBA you need to focus on using of Tools and analyzing performance with tools. The below post gives an idea on how you can start your analysis in finding root causes for performance issues.
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.
Here are SQL queries to use the SUBSTRING function along with LOCATE function. LOCATE gives position, and SUBSTRING uses that for the start position.
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