IBM Support

Hands-on example for embedded optimization guidelines and optimization profile

Technical Blog Post


Abstract

Hands-on example for embedded optimization guidelines and optimization profile

Body

After valid optimization guidelines are created, you can apply them by the following 2 methods. This document will explain how to use those methods by CLP examples.
1. Embedded optimization guidelines
2. Optimization profile
 
The contents is like as follows.
================================================================
0. Test environment
1. Embedded optimization guidelines
2. Optimization profile
2-1. Create the optimization profile file "inventory_db.xml"
2-2. Create the optimization profile table "SYSTOOLS.OPT_PROFILE"
2-3. Create an input file "profiledata" for "SYSTOOLS.OPT_PROFILE"
2-4. Populate the "profiledata" to "SYSTOOLS.OPT_PROFILE"
2-5. Enable the optimization profile with the CURRENT OPTIMIZATION PROFILE special register.
================================================================
 
 
0. Test environment
tables: tbla and tblb
SQL:select a.c1,
       a.c2 as a_c2,
       b.c2 as b_c2
    from tbla a join tblb b
    on a.c1=b.c1
 
This select query uses HSJOIN but the following optimization guidelines will force the NLJOIN. For table contents, the SQL output and original access plan, please check ref 1.
 
<OPTGUIDELINES>
    <NLJOIN>
        <ACCESS  TABID='Q1'/>
        <ACCESS  TABID='Q2'/>
    </NLJOIN>
</OPTGUIDELINES>
 
 
1. Embedded optimization guidelines
You can specify optimization guidelines through embedded optimization guidelines at the end of SQL statements like as follows. You can check access plan is changed in db2exfmt.
 
$ cat select2.sql
select a.c1,
       a.c2 as a_c2,
       b.c2 as b_c2
from tbla a join tblb b
on a.c1=b.c1
/*<OPTGUIDELINES>
      <NLJOIN>
          <ACCESS  TABID='Q1'/>
          <ACCESS  TABID='Q2'/>
      </NLJOIN>
</OPTGUIDELINES>*/
;
 
 
Access Plan:
-----------
        Total Cost:             13.5609
        Query Degree:           1
 
 
              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
                5
             NLJOIN
             (   2)
             13.5609
                2
         /-----+------\
        5                1
     TBSCAN           TBSCAN
     (   3)           (   4)
     6.77579          6.77619
        1                1
       |                |
        5                5
TABLE: V111_331  TABLE: V111_331
      TBLB             TBLA
       Q1               Q2
 
 
2. Optimization profile
2-1. Create the optimization profile file "inventory_db.xml"
- By using the optimization guidelines above, we make a xml file.
 
$ cat inventory_db.xml
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
    <STMTPROFILE>
        <STMTKEY>
            <![CDATA[select a.c1, a.c2 as a_c2, b.c2 as b_c2 from tbla a join tblb b on a.c1=b.c1]]>
        </STMTKEY>
        <OPTGUIDELINES>
            <NLJOIN>
                <ACCESS  TABID='Q1'/>
                <ACCESS  TABID='Q2'/>
            </NLJOIN>
        </OPTGUIDELINES>
    </STMTPROFILE>
</OPTPROFILE>
 
 
2-2. Create the optimization profile table "SYSTOOLS.OPT_PROFILE"
$ db2 "call sysinstallobjects('opt_profiles','c','','') "
 
  Return Status = 0
 
 
2-3. Create an input file "profiledata" for "SYSTOOLS.OPT_PROFILE"
$ cat profiledata
DBUSER, PROFILE1, inventory_db.xml
 
 
2-4. Populate the "profiledata" to "SYSTOOLS.OPT_PROFILE"
$ db2 import from profiledata of del modified by lobsinfile insert into systools.opt_profile
SQL3109N  The utility is beginning to load data from file "profiledata".
 
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
 
 
2-5. Enable the optimization profile with the CURRENT OPTIMIZATION PROFILE special register.
- From CLP session, you can use following command to enable the optimization profile.
$ db2 "set current optimization profile = DBUSER.PROFILE1"
DB20000I  The SQL command completed successfully.
 
- For your application, you can use the following command.
stmt.execute( "set current optimization profile = DBUSER.PROFILE1");
 
- After this, you can check the "Profile Information:" in the db2exfmt like as follows and the changed access plan to NLJOIN.
 
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
        DBUSER.PROFILE1
 
 
For more documentations with detail, please check ref 2
------------------------------------
ref 1
 
1. Table contents
$ db2 "select * from tbla"
 
C1          C2        
----------- ----------
          1 a1        
          2 a2        
          3 a3        
          4 a4        
          5 a5        
 
  5 record(s) selected.
 
$ db2 "select * from tblb"
 
C1          C2        
----------- ----------
          1 b1        
          2 b2        
          7 b7        
          8 b8        
          9 b9        
 
  5 record(s) selected.
 
2. The SQL output
$ db2 -tvf select1.sql
select a.c1, a.c2 as a_c2, b.c2 as b_c2 from tbla a join tblb b on a.c1=b.c1
 
C1          A_C2       B_C2      
----------- ---------- ----------
          1 a1         b1        
          2 a2         b2        
 
  2 record(s) selected.
 
 
3. Original access plan
Original Statement:
------------------
select
  a.c1,
  a.c2 as a_c2,
  b.c2 as b_c2
from
  tbla a join tblb b
  on a.c1=b.c1
 
 
Optimized Statement:
-------------------
SELECT
  Q2.C1 AS "C1",
  Q2.C2 AS "A_C2",
  Q1.C2 AS "B_C2"
FROM
  V111_331.TBLB AS Q1,
  V111_331.TBLA AS Q2
WHERE
  (Q2.C1 = Q1.C1)
 
Access Plan:
-----------
        Total Cost:             13.552
        Query Degree:           1
 
 
              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
                5
             HSJOIN
             (   2)
             13.552
                2
         /-----+------\
        5                5
     TBSCAN           TBSCAN
     (   3)           (   4)
     6.77579          6.77579
        1                1
       |                |
        5                5
TABLE: V111_331  TABLE: V111_331
      TBLA             TBLB
       Q2               Q1
 
------------------------------------
ref 2
 
Optimization profiles and guidelines
 
Configuring the data server to use an optimization profile
------------------------------------

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285759