b) Db2exfmt
I. 开启explain
Db2 set current explain mode yes
II. 执行db2exfmt,得到sql查询树 C:\\>db2exfmt DB2 Universal Database Version 9.0, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool
Enter Database Name ==> sample Connecting to the Database. Connect to Database Successful.
Binding package - Bind was Successful
Enter up to 26 character Explain timestamp (Default -1) ==> Enter up to 8 character source name (SOURCE_NAME, Default %%) ==>
Enter source schema (SOURCE_SCHEMA, Default %%) ==> Enter section number (0 for all, Default 0) ==> Enter outfile name. Default is to terminal ==>
DB2 Universal Database Version 9.0, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.00.0 SOURCE_NAME: SQLC2F0A SOURCE_SCHEMA: NULLID SOURCE_VERSION:
EXPLAIN_TIME: 2007-01-15-17.24.14.432001 EXPLAIN_REQUESTER: ADMINISTRATOR
Database Context: ----------------
Parallelism: None
CPU Speed: 4.251098e-007 Comm Speed: 100 Buffer Pool size: 250 Sort Heap size: 256 Database Heap size: 600 Lock List size: 50 Maximum Lock List: 22 Average Applications: 1 Locks Available: 935
Package Context: ---------------
SQL Type: Dynamic Optimization Level: 5
Blocking: Block All Cursors Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 1 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No
Query Degree: 1
Original Statement: ------------------ select *
from administrator.employee
Optimized Statement: -------------------
SELECT Q1.\Q1.\ \Q1.\AS \Q1.\
Q1.\AS \Q1.\AS \
\Q1.\
AS \AS \
Q1.\
FROM ADMINISTRATOR.EMPLOYEE AS Q1
Access Plan: -----------
Total Cost: 15.1785 Query Degree: 1
Rows RETURN ( 1) Cost I/O | 42 TBSCAN ( 2) 15.1785 2 | 42
TABLE: ADMINISTRATOR EMPLOYEE
Extended Diagnostic Information: --------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0058W The following MQT or statistical view was
not considered for rewrite matching because of one
or more of the following reasons: (1) MQT was in
SET INTEGRITY PENDING state, or (2) MQT would be
put to SET INTEGRITY PENDING state, or (3) MQT was
modified in the same statement, or (4) MQT or
statistical view was not enabled for optimization:
\
Plan Details: -------------
1) RETURN: (Return Result)
Cumulative Total Cost: 15.1785 Cumulative CPU Cost: 137639 Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.0314777 Cumulative Re-CPU Cost: 74046 Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 7.58224 Estimated Bufferpool Buffers: 2
Arguments: ---------
BLDLEVEL: (Build level)
DB2 v9.0.0.257 : s060328 STMTHEAP: (Statement heap size) 2048