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.
| PRTSQLINF | STRDBG or CHGQRYA | File-based monitor (STRDBMON) | Memory-Based Monitor | Visual Explain |
|---|---|---|---|---|
| Available without running query (after access plan has been created) | Only available when the query is run | Only available when the query is run | Only available when the query is run | Only available when the query is explained |
| Displayed for all queries in SQL program, whether executed or not | Displayed only for those queries which are executed | Displayed only for those queries which are executed | Displayed only for those queries which are executed | Displayed only for those queries that are explained |
| Information about host variable implementation | Limited information about the implementation of host variables | All information about host variables, implementation, and values | All information about host variables, implementation, and values | All information about host variables, implementation, and values |
| Available only to SQL users with programs, packages, or service programs | Available to all query users (OPNQRYF, SQL, QUERY/400) | Available to all query users (OPNQRYF, SQL, QUERY/400) | Available only to SQL interfaces | Available through System i® Navigator Database and API interface |
| Messages are printed to spool file | Messages are displayed in job log | Performance rows are written to database table | Performance information is collected in memory and then written to database table | Information is displayed visually through System i Navigator |
| Easier to tie messages to query with subqueries or unions | Difficult to tie messages to query with subqueries or unions | Uniquely identifies every query, subquery, and materialized view | Repeated query requests are summarized | Easy to view implementation of the query and associated information |