Verifying query performance

You can use the db2batch Benchmark Tool in DB2® for Linux, UNIX, and Windows to compare your query performance results after you create the recommended summary tables against the benchmarked query performance.

Before you begin

You must complete the following tasks before you can verify your query performance improvement:

Note: To ensure an accurate comparison, make sure that you use the same input file of SQL queries to benchmark your query performance and to verify your query performance.

Procedure

Make sure that the DFT_REFRESH_AGE system parameter is set to 0 by completing the following steps:
1. Open the DB2 command line by selecting Start > Programs > IBM DB2 > Command Line Tools > Command Window.
2. Connect to the database. For example, enter the following command to connect to the GSDB database:
```
db2 connect to gsdb
```
3. Set the default refresh age to 0 by entering the following command:
```
db2 update db cfg for database-name using dft_refresh_age 0
```
  where database-name is the name of the database that you are connected to, such as GSDB.
4. Reset the database connection by entering the following command:
```
db2 connect reset
```
5. Stop and start DB2 by entering the following commands:
```
db2stop

db2start
```
To test the performance of your queries, enter the following command on a command line:
```
db2batch -d dbname -f input.txt -r output.txt
```
where dbname specifies the database to run the queries against, input.txt specifies the input file with your SQL queries, and output.txt specifies the file that the results of the db2batch command will be piped to. The db2batch tool summarizes performance results and provides both arithmetic and geometric means. For syntax and options, enter db2batch -h on a command line. See the DB2 Information Center for more information on the db2batch Benchmark Tool and creating benchmark tests.
Recommended: Pipe the output of the db2batch command to an output file using the -r output.txt
flag.

If you are satisfied with the performance results after creating the recommended summary tables, you do not need to do any additional performance analysis.

If your queries do not improve as much as you expected, you can check whether specific queries routed and troubleshoot any queries that did not route. Generally, you can run the Optimization Advisor wizard again and allow more disk space and time and enable data sampling if you did not enable it before. Allowing more disk space will most likely have the largest effect on improving performance. The more space that you provide for the summary tables, the more improvement you will see. If you allow the Optimization Advisor wizard to perform data sampling, it can make better recommendations. Likewise, the more time that you allow for the Optimization Advisor wizard to create the recommendations, the better the recommendations are likely to be.

If you are not satisfied with the performance results because your queries do not improve at all or only very little, or if your queries perform satisfactorily for a period of time and then decrease in performance, you can troubleshoot the cause of the unsatisfactory performance.

Feedback | Information roadmap