Manually analyze the DB2 SQL Execution Plan

In DB2 database monitoring, the DB2 SQL Execution Plan is very important. The following describes the manual DB2 SQL Execution Plan in detail for you to learn by test.

Manual DB2 SQL Execution Plan:

The EXPLAIN table can be automatically created when you use Visual Explain for the first time. You can create them manually even if they are not created, as shown below:

% Cd db2 install path] \ sqllib \ misc
% Db2 connect to bank
% Db2-tvf EXPLAIN. DDL

Use db2exfmt to explain dynamic SQL statements. In the DB2 command window, follow these steps:

% Db2 connect to database_name]
% Db2 set current explain mode explain
% Db2-tvf please Input file with an SQL statement ended with a semicolon]
% Db2 set current explain mode no
% Db2exfmt-d dbname]-g TIC-w-1-n %-s %-#0-o output file]

The output of the db2exfmt tool is as follows:
Overview: DB2 UDB version and release level, as well as the date and time when the explain tool is running
Database environment: the configuration parameters that the optimizer considers to determine access plans with minimal resource costs, including concurrency, CPU speed, communication speed, buffer pool size, sorting heap size, database heap size, lock list size, maximum lock list, average application and available lock
Package environment: SQL type dynamic or static), optimization level, isolation level, and intra-zone concurrency used by the statement
Initial statement: the SQL statement called by the application
Optimization statement: the version of the SQL statement that the optimizer converts from the initial statement. These statements have the same query results, but allow optimal performance.
Access Plan: Allows DB2 UDB to access data to solve the minimum extension path of SQL statements.
Operator Description: it shows the operators of each stage in an access plan.
Objects used in an access plan: Tables and/or indexes used in the access plan.
 

Learn about DB2 snapshot monitoring

Implementation Process of batch execution of DB2 SQL scripts

DB2 Online Export Method

Common db2 stored procedure statements

Usage of the DB2 create server statement