我们先来简单看一下DB2的优化器的工作流程：　

1. DB2的优化器，在接收到SQL语句后，会首先校验SQL的语法，确保是正确的SQL 　　

2. 根据当前的系统环境信息，生成最优的执行计划来优化SQL语句　　

3. 把SQL翻译成计算机指令语言，并执行这个优化后的SQL 　　

4. 返回结果，或者存储它们，以便将来的执行 DB2 系统目录中统计信息是让DB2优化器正确工作的一个非常重要的依据。

这些统计信息向优化器提供了与正在被优化的 SQL 语句将要访问的表状态相关的信息。这些信息主要包括：

db2exfmt o Table Operators 

db2exfmt     TBSCAN - Table Scan     

db2exfmt     IXSCAN - Index Scan  

db2exfmt     FETCH  - Fetch from Table                                 

db2exfmt o Joins                                                       

db2exfmt     MSJOIN - Merge Scan Join          

db2exfmt     NLJOIN - Nested Loop Join 

db2exfmt     HSJOIN - Hash Join                                                          

db2exfmt o Aggregation  

db2exfmt     GRPBY  - Group By                                         

db2exfmt     SUM    - Sum                                              

db2exfmt     AVG    - Average                                          

db2exfmt   MIN    - Minimum       

db2exfmt     MAX    - Maximun                                          

db2exfmt       

db2exfmt o Temp / Sort    

db2exfmt     TEMP   - Insert into temp table     

db2exfmt     SORT   - Sort                                             

db2exfmt o Special Operations                                          

db2exfmt     IXAND  - Index ANDing                                  

db2exfmt     RIDSCA - Index ORing or List Prefetch         

db2exfmt     IXA    - Star Schema Bitmap Indexing                 

db2exfmt     BTQ    - Broadcast Table Queue                     

db2exfmt     DTQ    - Directed Table Queue                             

db2exfmt     MBTQ   - Merge Broadcast Table Queue        

db2exfmt     MDTQ   - Merge Directed Table Queue                       

 db2exfmt     LTQ    - Local Table Queue, for Intra-partition parallelism

附:  BTQ   Broadcast Table Queue broadcasts data to several partitions.    

DELETE   Deletes rows from a table.   

DTQ   Directed Table Queue transfers data to a specific partition.   

EISCAN   Scans a user-defined index to produce a reduced stream of rows. 

 FETCH   Fetches columns from a table using spe-cific record identifier.   

FILTER   Represents the application of residual predicates.   

GRPBY   Groups rows by common values of desig-nated columns or functions.   

HSJOIN   Represents a hash join, where two or more tables are hashed on join columns.   INSERT   Inserts rows into a table. 

 IXAND   ANDs together the row identifiers (RIDs) from two or more index scans.   

IXSCAN   Scans an index of a table with optional start/stop conditions, producing an ordered stream of rows.   

LTQ   Local Table Queue. Transfers data between local agents.   

LMTQ   Local Merge Table Queue. Merges data transferred between local agents. 

 MBTQ   Merging Broadcast Table Queue. 

 MDTQ   Merging Directed Table queue. 

 MSJOIN   Represents a merge join, where both outer and inner tables must be in join-predicate order. 

 NLJOIN   Represents a nested loop join that accesses an inner table once for each row of the outer table.   

RETURN   Represents the return of data from the query to the user.   

RIDSCAN   Scans a list of row identifiers (RIDs) obtained from one or more indexes.   

RPD   For nonrelational wrappers, it shows the simulated

SQL operation that the nonrela-tional wrapper will be asked to perform. 

 SHIP   Retrieves data from a remote database source. Used in federated systems.   

SORT   Sorts rows in the order of specified col-umns, and optionally eliminates duplicate entries.   

TBSCAN   Retrieves rows by reading all required data directly from the data pages.   

TEMP   Stores data in a temporary table to be read back out (possibly multiple times).   

TQUEUE   Transfers table data between agents.   

UNION   Concatenates streams of rows from multi-ple tables.   

UNIQUE   Eliminates rows with duplicate values, for specified columns.   

UPDATE   Updates rows in a table.