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 explains 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
2-6. Modification to optimization profile
================================================================
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 force the NLJOIN. For table contents, the SQL output and original access plan, check ref 1.
<OPTGUIDELINES>
<NLJOIN>
<ACCESS TABLE='TBLA'/>
<ACCESS TABLE='TBLB'/>
</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 TABLE='TBLA'/>
<ACCESS TABLE='TBLB'/>
</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 TABLE='TBLA'/>
<ACCESS TABLE='TBLB'/>
</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.
* If you want to apply this to every database connection, please check the following link.
https://www.ibm.com/support/pages/node/6470247
- 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
2-6. Modification to optimization profile
If you have some changes in optimization profile, please run the followings.
db2 "delete from systools.opt_profile where schema='DBUSER' and name='PROFILE1'"
db2 import from profiledata of del modified by lobsinfile insert into systools.opt_profile
db2 flush optimization profile cache DBUSER.PROFILE1
For more documentations with detail, 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":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13285759