The Design Advisor can help you determine what database objects can improve the performance of a given workload. A workload is basically a set of SQL statements that the Design Advisor evaluates based on the characteristics of the workload, the characteristics of the database, and the hardware resources. It uses the DB2 optimizer, the database statistics, and the Explain mechanism to generate recommendations for new indices, new materialized query tables (MQT), conversion to multidimensional clustering (MDC) tables, redistribution of tables, deletion of indices, and MQTs unused by the specified workload. You might be familiar with MQTs and MDCs; they are advanced database objects, which are not covered in the DBA exam (611). If you are interested in advanced database objects, please refer to the DB2 Information Center.
You can start the DB2 Advisor from the command line with the db2advis along with the necessary inputs. There are few ways to specify a workload using the db2advis command.
You can specify a single SQL statement when issuing the db2advis command. The following example evaluates the given SQL statements and makes recommendations accordingly.
db2advis -d sample -s "select * from employee where workdept='A00' and salary > 40000" -o output.out
You can use a set of dynamic SQL statements captured in a DB2 snapshot.
To do so, you need to reset the database monitor with the command
db2 reset monitor for database database-name.
Let your application run for a desired period of time and allow DB2
snapshots to capture the dynamic SQL statements. Issue the following
db2advis command to evaluate the workload and make recommendations.
-g option tells the Design Advisor
to get the SQL statements from the dynamic SQL snapshots. In addition,
-p option causes the captured SQL
statements to be stored in the ADVISE_WORKLOAD system tables.
db2advis -d sample -g -p -o output.out
Alternatively, you can create a workload file containing a set of SQL statements. Set the frequency of the statements in the workload. Following is a sample workload file.
--#SET FREQUENCY 100 SELECT COUNT(*) FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS'; --#SET FREQUENCY 1 SELECT * FROM EMPLOYEE WHERE WORKDEPT='A00' AND SALARY > 40000;
Then, simply run the db2advis command with the
db2advis -d sample -i input.sql -o output.out