DB2 force optimizer usage tips have to be viewed

The following articles mainly describe how to use the DB2 force optimizer in practice. Many developers and database managers complain about the optimizer problem. In many cases, optimizer problems can be solved by conventional means, but in some special cases.

Or in an emergency (no time for complete problem analysis), you can use the profile to temporarily force the optimizer to use some specific operations...

The following is an example of step by step, which briefly describes how DB2 forces the optimizer to use table scan.

 
 

  
  
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with   
  
  
level identifier "02010107".   
  
  
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".   
  
  
Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name   
  
  
"DB2COPY1".  
 
 

Create a database

 
 

  
  
D:\TEMP\db2service.perf1>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.   
  
  
D:\TEMP\db2service.perf1>db2 connect to sampel2   
  
  
Database Connection Information   
  
  
Database server = DB2/NT 9.1.0   
  
  
SQL authorization ID = TAOEWANG   
  
  
Local database alias = SAMPEL2  
 
 

Create optimizer system table

 
 

  
  
D:\TEMP\db2service.perf1>db2 "create table systools.opt_profile (schema VARCHAR(128) not null, name varchar(128) 
not null, profile blob (2M) not null, primary key (schema, name))"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP\db2service.perf1>cd ..  
 
 

Create user table

 
 

  
  
D:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"   
  
  
DB20000I The SQL command completed successfully.  
 
 

Insert some data

 
 

  
  
D:\TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"   
  
  
DB20000I The SQL command completed successfully.  
 
 

Use of DB2 force Optimizer

The following example shows how to force the optimizer to use table scan.

 
 

  
  
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with   
  
  
level identifier "02010107".   
  
  
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".   
  
  
Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name   
  
  
"DB2COPY1".  
 
 

Create a database

 
 

  
  
D:\TEMP\db2service.perf1>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.   
  
  
D:\TEMP\db2service.perf1>db2 connect to sampel2   
  
  
Database Connection Information   
  
  
Database server = DB2/NT 9.1.0   
  
  
SQL authorization ID = TAOEWANG   
  
  
Local database alias = SAMPEL2  
 
 

Create optimizer system table

 
 

  
  
D:\TEMP\db2service.perf1>db2 "create table systools.opt_profile (schema VARCHAR(128) not null, 
name varchar(128) not null, profile blob (2M) not null, primary key (schema, name))"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP\db2service.perf1>cd ..  
 
 

Create user table

 
 

  
  
D:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"   
  
  
DB20000I The SQL command completed successfully.  
 
 

Insert some data

 
 

  
  
D:\TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"   
  
  
DB20000I The SQL command completed successfully.   
  
  
D:\TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"   
  
  
DB20000I The SQL command completed successfully.  
 
 

Now the optimizer uses index scan
 

 
 

  
  
Original Statement:   
  
  
------------------   
  
  
SELECT *   
  
  
FROM TAOEWANG.MYTABLE   
  
  
WHERE ID < 1000   
  
  
Optimized Statement:   
  
  
-------------------   
  
  
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS   
  
  
"PHONE"   
  
  
FROM TAOEWANG.MYTABLE AS Q1   
  
  
WHERE (Q1.ID < 1000)   
  
  
Access Plan:   
  
  
-----------   
  
  
Total Cost: 7.56853   
  
  
Query Degree: 1   
  
  
Rows   
  
  
RETURN   
  
  
( 1)   
  
  
Cost   
  
  
I/O   
  
  
|   
  
  
1   
  
  
FETCH   
  
  
( 2)   
  
  
7.56853   
  
  
1   
  
  
/----+---\   
  
  
1 4   
  
  
IXSCAN TABLE: TAOEWANG   
  
  
( 3) MYTABLE   
  
  
0.00630865   
  
  
0   
  
  
|   
  
  
4   
  
  
INDEX: TAOEWANG   
  
  
IX1  
 
 

The above content is an introduction to the use technology of the DB2 force optimizer. I hope you will have some gains.