Understanding the DB2 database environment is an important part of any database administrator's job. Various facilities can be used to gain this understanding. Database activity can be analyzed in several ways. Graphical monitoring tools, such as DB2PM and Visual Explain, can be used with DB2 Universal Database.
The DB2 optimizer is one of the most advanced in the relational database industry. The optimizer generates an access plan during query compilation. Access paths are stored in the system catalog tables for static SQL applications. Access paths for dynamic SQL statements are generated at query execution time and are stored in memory.
To gain an understanding of the access path, or strategy, chosen by the DB2 optimizer, the Explain utility can be used. Explain populates a PLAN_TABLE and optional DSN tables with detailed information for the SQL statements. These tables can then be queried to determine the plan information about index use and other database resources. This graphical version of the access path can be examined by using Visual Explain. The ability to determine whether an SQL access path is appropriate is critical to the best performance for DB2 queries.