DB2 Advisors

DB2 Design Advisor

The Design Advisor is a handy tool that can help you to determine what database objects can improve the performance of a given workload. A workload is basically a set of SQL statements for which 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 indexes, new materialized query tables (MQT), conversion to multidimensional clustering (MDC) tables, redistribution of tables, deletion of indexes 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 (731). If you are interested in advanced database objects, please refer to the DB2 Information Center.

You can start the 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. Here the -g option tells the design advisor to get the SQL statements from the dynamic SQL snapshots. In addition, the -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. Here 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 -i option.

db2advis -d sample -i input.sql -o output.out

The Design Advisor can also be started from the Control Center. Select the database you want to work with, Select Design Advisor from its pop-up menu. You will get the Design Advisor as shown here.

As you step through the tool, you can import a SQL workload, make changes to the SQL statements, update statistics for certain tables, choose to evaluate the workload now, and etc.

9 of 12 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Data movement utilities
• DB2 EXPORT utility
• DB2 IMPORT utility
• DB2 LOAD utility
• DB2 data movement utilities
• DB2 maintenance utilities
• DB2 Control Center
• DB2 Advisors
• Conclusion
• Resources
• About the author

---

9 of 12 | Previous | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.