Defining a workload for the Design Advisor

When the Design Advisor analyzes a specific workload, it considers factors such as the type of statements that are included in the workload, the frequency with which a particular statement occurs, and characteristics of your database to generate recommendations that minimize the total cost of running the workload.

About this task

A workload is a set of SQL statements that the database manager must process during a period of time. The Design Advisor can be run against:

A single SQL statement that you enter inline with the db2advis command
A set of dynamic SQL statements that were captured in a Db2® snapshot
A set of SQL statements that are contained in a workload file

You can create a workload file or modify a previously existing workload file. You can import statements into the file from several sources, including:

A delimited text file
An event monitor table
Explained statements in the EXPLAIN_STATEMENT table
Recent SQL statements that were captured with a Db2 snapshot
Workload manager activity tables
Workload manager event monitor tables by using the -wlm option from the command line

After you import the SQL statements into a workload file, you can add, change, modify, or remove statements and modify their frequency.

Procedure

To run the Design Advisor against dynamic SQL statements:
1. Reset the database monitor with the following command:
```
   db2 reset monitor for database database-name
```
2. Wait for an appropriate amount of time to allow for the execution of dynamic SQL statements against the database.
3. Invoke the db2advis command using the -g parameter. If you want to save the dynamic SQL statements in the ADVISE_WORKLOAD table for later reference, use the -p parameter as well.
To run the Design Advisor against a set of SQL statements in a workload file:
1. Create a workload file manually, separating each SQL statement with a semicolon, or import SQL statements from one or more of the sources listed previously.
2. Set the frequency of the statements in the workload. Every statement in a workload file is assigned a frequency of 1 by default. The frequency of an SQL statement represents the number of times that the statement occurs within a workload relative to the number of times that other statements occur. For example, a particular SELECT statement might occur 100 times in a workload, whereas another SELECT statement occurs 10 times. To represent the relative frequency of these two statements, you can assign the first SELECT statement a frequency of 10; the second SELECT statement has a frequency of 1. You can manually change the frequency or weight of a particular statement in the workload by inserting the following line after the statement: - - # SET FREQUENCY n, where n is the frequency value that you want to assign to the statement.
3. Invoke the db2advis command using the -i parameter followed by the name of the workload file.
To run the Design Advisor against a workload that is contained in the ADVISE_WORKLOAD table, invoke the db2advis command using the -w parameter followed by the name of the workload.