Cubing — Benchmarking query performance

You can use the db2batch Benchmark Tool in DB2® to benchmark your query performance before you create the recommended summary tables. This is an optional, but recommended step. You can set the DFT_REFRESH_AGE system parameter to 0 so that you can measure query performance without DB2 using existing summary tables.

Before you begin

Create an input file with the queries that you want to test. Separate the queries by placing a semicolon at the end of each query.

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.

To make sure that summary tables are considered by the DB2 optimizer, make sure that you set the DFT_REFRESH_AGE parameter to any after you complete your benchmarking tests.