IBM Support

How do we use Optimizer Profile in DB2 to overwrite access plan?

Question & Answer


Question

We have a query want to use table scan instead of index scan regardless how many rows in the table. But DB2 optimizer may choose different access plan after executing Runstats to the table. We heard DB2 has a feature called Optimization Profile to manually specify the access plan for queries, is there any step by step instruction how to use it?

Answer

Here is a simple instruction for how to use Optimization Profile.

The testing environment is on Windows, but the steps should be platform independent.
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".

1) Creating a database
D:\TEMP>db2 create db sampel2
DB20000I  The CREATE DATABASE command completed successfully.


D:\TEMP>db2 connect to sampel2

   Database Connection Information

Database server        = DB2/NT 9.1.0
SQL authorization ID   = DB2INST1
Local database alias   = SAMPEL2

2) Creating a profile table
D:\TEMP>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.

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

D:\TEMP\>db2 "create index IX1 on mytable (id)"
DB20000I The SQL command completed successfully.

4) Inserting some data for test
D:\TEMP>db2 "insert into mytable values ('Tom', 12345, 100, '123-456')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('Bob', 12346, 101, '123-457')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('Jeffery', 123, 102, '123-458')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('Jackie', 1255, 104, '123-459')"
DB20000I  The SQL command completed successfully.

5) Executing runstats
D:\TEMP>db2 "runstats on table db2inst1.mytable"
DB20000I  The RUNSTATS command completed successfully.

D:\TEMP>db2 "runstats on table db2inst1.mytable for indexes all"
DB20000I  The RUNSTATS command completed successfully.


6) Run the query and see the result
D:\TEMP>db2 "SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000"

NAME
                                                 ID          SALARY
      PHONE
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------ --------------------
Jeffery
                                                         123   +1.02000000000000E+002 123-458

  1 record(s) selected.

6) Creating explain table
D:\TEMP>cd D:\Program Files\IBM\SQLLIB\MISC

D:\Program Files\IBM\SQLLIB\MISC>db2 -tvf EXPLAIN.DDL

D:\Program Files\IBM\SQLLIB\MISC>cd D:\temp

7) Generating the current access plan
D:\TEMP>db2 set current explain mode explain
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604

D:\TEMP>db2 set current explain mode no
DB20000I  The SQL command completed successfully.

D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful
Output is in output.txt.
Executing Connect Reset -- Connect Reset was Successful.

8) In output.txt file, we can see optimizer picked up IXSCAN
Original Statement:
------------------
SELECT *
FROM DB2INST1.MYTABLE
WHERE ID < 1000


Optimized Statement:
-------------------
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS
        "PHONE"
FROM DB2INST1.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: DB2INST1
     (   3)        MYTABLE
   0.00630865
        0
       |
        4
INDEX: DB2INST1
       IX1

9) Next, we are going to create several files to enable Optimization Profile.

9.1) Creating a file called a1.xml, and copy the following lines into the file. Note the statement in the XML file must be exactly same as the query going to be used by application, and the version parameter in <OPTPROFILE> tag should be matching the version of DB2 server.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
<STMTPROFILE ID="Use Table Scan instead of Index Scan">
<STMTKEY SCHEMA="DB2INST1">
<![CDATA[SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000]]>
</STMTKEY>
<OPTGUIDELINES>
<TBSCAN TABLE="DB2INST1.MYTABLE"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

9.2) Create a file called insert.del with following line:
"DB2INST1", "PROF1", "a1.xml"

9.3) Import the xml file into profile table
D:\TEMP>db2 import from insert.del of del modified by lobsinfile insert into systools.opt_profile
SQL3109N  The utility is beginning to load data from file "insert.del".

SQL3110N  The utility has completed processing.  "1" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "1" rows were processed from the input file.  "1" rows were successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 1
Number of rows skipped      = 0
Number of rows inserted     = 1
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 1

10) Once the XML file is imported into SYSTOOLS.OPT_PROFILE table, we are going to enable a registry variables so that DB2 is able to pickup the profile:
D:\TEMP>db2set DB2_OPTPROFILE=YES

11) Recycling db2 instance to activate the registry variable:
D:\TEMP>db2stop force
12/27/2007 08:54:45     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

D:\TEMP>db2start
12/27/2007 08:54:48     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

12) Enabling profile for PROF1 and collect access plan again:
D:\TEMP>db2 connect to SAMPEL2

   Database Connection Information

Database server        = DB2/NT 9.1.0
SQL authorization ID   = DB2INST1
Local database alias   = SAMPEL2


D:\TEMP>db2 set current explain mode explain
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 set current schema db2inst1
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 set current optimization profile='PROF1'
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604

D:\TEMP>db2 set current explain mode no
DB20000I  The SQL command completed successfully.

D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output2.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in output2.txt.
Executing Connect Reset -- Connect Reset was Successful.

13) In the new output2.txt file, the following lines indicate Optimization Profile was enabled and found a matching SQL:
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
        DB2INST1.PROF1
STMTPROF: (Statement Profile Name)
        Use Table Scan instead of Index Scan

Original Statement:
------------------
SELECT *
FROM DB2INST1.MYTABLE
WHERE ID < 1000


Optimized Statement:
-------------------
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS
        "PHONE"
FROM DB2INST1.MYTABLE AS Q1
WHERE (Q1.ID < 1000)

Access Plan:
-----------
        Total Cost:                 7.56912
        Query Degree:                1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        1
     TBSCAN
     (   2)
     7.56912
        1
       |
        4
TABLE: DB2INST1
     MYTABLE


Based on the above access plan, IXSCAN was replaced by TBSCAN after enabling profile.
Note this approach only work for CLP applications. If you want to use Optimization Profile feature for CLI/JDBC applications, please refer DB2 Infocenter.

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Ft0024556.html

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Compiler - Optimizer","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.8;9.7;9.5;10.1;10.5","Edition":"Enterprise Server;Express;Personal;Personal Developer's;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21430976