Optimizing query performance using query optimization tools
Query optimization is an iterative process. You can gather performance information about your queries and control the processing of your queries.
- DB2 for IBM i – Health Center
Use the DB2® for IBM® i Health Center to capture information about your database. You can view the total number of objects, the size limits of selected objects, the design limits of selected objects, environmental limits, and activity level. - Monitoring your queries using the Database Monitor
Start Database Monitor (STRDBMON) command gathers information about a query in real time and stores this information in an output table. This information can help you determine whether your system and your queries are performing well, or whether they need fine-tuning. Database monitors can generate significant CPU and disk storage overhead when in use. - Using System i Navigator with detailed monitors
You can work with detailed monitors from the System i® Navigator interface. The detailed SQL performance monitor is the System i Navigator version of the STRDBMON database monitor, found on the native interface. - Index advisor
The query optimizer analyzes the row selection in the query and determines, based on default values, if creation of a permanent index improves performance. If the optimizer determines that a permanent index might be beneficial, it returns the key columns necessary to create the suggested index. - Viewing your queries with Visual Explain
You can use the Visual Explain tool with System i Navigator to create a query graph that graphically displays the implementation of an SQL statement. You can use this tool to see information about both static and dynamic SQL statements. Visual Explain supports the following types of SQL statements: SELECT, INSERT, UPDATE, and DELETE. - Optimizing performance using the Plan Cache
The SQL Plan Cache contains a wealth of information about the SQE queries being run through the database. Its contents are viewable through the System i Navigator GUI interface. Certain portions of the plan cache can also be modified. - Verifying the performance of SQL applications
You can verify the performance of an SQL application by using commands. - Examining query optimizer debug messages in the job log
Query optimizer debug messages issue informational messages to the job log about the implementation of a query. These messages explain what happened during the query optimization process. - Print SQL Information
The Print SQL Information (PRTSQLINF) command returns information about the embedded SQL statements in a program, SQL package (used to store the access plan for a remote query), or service program. This information is then stored in a spooled file. - Query optimization tools: Comparison
Use this table to find the information each tool can provide, when it analyzes your queries, and the tasks it can do to improve your queries. - Changing the attributes of your queries
You can modify different types of query attributes for a job with the Change Query Attributes (CHGQRYA) CL command. You can also use the System i Navigator Change Query Attributes interface. - Collecting statistics with the statistics manager
The collection of statistics is handled by a separate component called the statistics manager. Statistical information can be used by the query optimizer to determine the best access plan for a query. Since the query optimizer bases its choice of access plan on the statistical information found in the table, it is important that this information is current. - Displaying materialized query table columns
You can display materialized query tables associated with another table using System i Navigator. - Managing check pending constraints columns
You can view and change constraints that have been placed in a check pending state by the system. Check pending constraints refers to a state in which a mismatch exists between a parent and foreign key in a referential constraint. A mismatch can also occur between the column value and the check constraint definition in a check constraint.
Parent topic: Database performance and query optimization