Configuration Advisor

[ Team LiB ]

Configuration Advisor Now that we have discussed various database manager and database configuration parameters, it is time to introduce the Configuration Advisor. The Configuration Advisor is designed to configure database manager and database configuration parameters based on inputs from the DBA. It helps to get you started and helps you set a performance baseline for your database. After monitoring your database in a typical production environment, the parameter settings should be reviewed and adjusted as necessary. The Configuration Advisor consists of a 10-step process, as depicted in Figures 9.8�9.17. Figure 9.8. Configuration Advisor Wizard�Introduction. Figure 9.17. Configuration Advisor results. NOTE The Configuration Advisor is an example of IBM's SMART program. The SMART program is designed to improve DBA productivity by providing tools and wizards to perform self-tuning, monitoring, and analysis of DB2 databases. Introduction� Calculate new values or restore saved values. See Figure 9.8. Server� Set target values for database server real memory. See Figure 9.9. Figure 9.9. Set server values. If you have a dedicated database server, then set the slider bar to 80%. If other applications are running on the server, set this to a lower value. Set the type of workload. See Figure 9.10. Figure 9.10. Set the type of workload. By setting the type of workload, we are telling the Configuration Advisor to optimize DB2 memory for your particular workload. In a data warehousing environment, queries tend to run longer, use more sortheap, and differ in use of package cache and bufferpool efficiency. Queries in a data warehouse environment tend to be "ad-hoc" and dissimilar, which mean the package cache hit rate will usually be low and table objects too large to fit in the bufferpool, resulting in physical reads from disk for the data. However, bufferpools in this environment tend to support keeping all or part of index pages in the bufferpool, resulting in reading indexes from the bufferpool. Specify a typical database transaction. See Figure 9.11. Figure 9.11. Specify a typical database transaction. By specifying a typical database transaction number, the Configuration Advisor uses this number to calculate the size of locklist, maxlocks, and other configuration parameters. Specify a database administration priority. See Figure 9.12. Figure 9.12. Specify a database administration priority. By setting a database administration priority, the Configuration Advisor will determine whether or not to optimize the database configurations settings for performance or fast recovery, or both depending on the option selected. This option will calculate configuration settings such as num_iocleaners, min_commit, and other parameters that affect logging activity. Specify whether or not the database is populated. See Figure 9.13. Figure 9.13. Specify whether or not the database is populated. By specifying whether or not the database is populated, we are telling DB2 to size bufferpools and num_ioservers and other configuration parameters appropriately. Estimate the number of applications connected to the database. See Figure 9.14. Figure 9.14. Estimate the number of connections connected to the database. DB2 uses this information to help arrive at recommended configuration settings for rqrioblk, maxappls, aslheapsz, maxagents, and other configuration parameters. . Specify an isolation level. See Figure 9.15. Figure 9.15. Specify the isolation level. By specifying an isolation level, we are providing DB2 with information on how to size locklist, maxlocks, and other configuration parameters. Schedule. See Figure 9.16. Figure 9.16. Configuration advisor scheduling. The results of the Configuration Advisor session can be scheduled or executed immediately. Recommendations can be saved to the Task Center by checking the appropriate checkbox and run at a later date. Results. See Figure 9.17. Recommendations can be applied by selecting the Finish button. The Configuration Advisor was formerly known as the Configure Performance Wizard in previous releases. In a recent benchmark, a comparison of a database configured by an expert and one configured by the Configuration Advisor resulted in almost equivalent database performance, so the Configuration Advisor is definitely a good starting point for configuring your database. What it also pointed out was that the majority of default parameter values should only be used as a starting point and should not be used for production databases. NOTE The Configuration Advisor is not currently available for partitioned databases.

[ Team LiB ]