PDF

Improve DB2 for Linux, UNIX, and Windows query
performance using the REOPT bind option
Shan Chen ([email protected])
Common Client Technologies (CCT) Advanced Technical
Support for Data Servers
IBM
18 November 2010
Tao Wang ([email protected])
DB2 Advanced Technical Support
IBM
REOPT is a bind option for IBM® DB2® for Linux®, UNIX®, and Windows® that influences
the optimizer to re-optimize so that it can consider values passed to the application at runtime.
In this article, learn how to use REOPT to improve query performance in different types of
applications.
Introduction
In the DB2 for Linux, UNIX, and Windows environment, you may find that runtime query
performance is related to input variable values that are passed to statements at OPEN time. This
can be an expected behavior when the data distribution is highly skewed. However, you would still
like the optimizer to find the best access plan based on the values that users have passed into the
query.
REOPT is a bind option that you can specify when binding a package or executing a dynamic SQL
statement in order to influence the behavior of DB2 to re-optimize the query so that the optimizer
can take advantage of this late-arriving bit of information, the value of the input variable.
Query processing background
Before we go in the the details of REOPT, let's step back and review how query processing works.
Query processing can be summarized in the following steps:
1. When a query is issued by a user or application, it first needs to be parsed and transformed
to a format that DB2 recognizes. This internal representation is performed by the query graph
model (QGM) component. QGM is a graphical representation of the query. It is initialized after
the SQL statement is parsed.
© Copyright IBM Corporation 2010
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Trademarks
Page 1 of 29
developerWorks®
ibm.com/developerWorks/
2. After parsing the statement and generating the initial graphical representation, DB2 checks
constraints, foreign keys, triggers, and views, and then modifies QGM to include all those
objects.
3. The next step is query rewrite (QRW) optimization. At this point, DB2 modifies the original
user query based on predefined rules in order to make the statement run more efficiently.
Note in the all above steps, DB2 handles the query based on the SQL data definitions
language (DDL) of the database. At this point, data statistics are not included in the
calculations.
4. After QRW optimization, the DB2 optimizer gets involved. It estimates the execution cost
of each potential access plan based on the statistics data stored in catalog and statistics
tables, and selects the one with the lowest cost. At this step, DB2 uses various optimization
techniques in order to obtain the most accurate cost estimate, such as. column distribution,
column group statistics, statistical views, and materialized query tables. If the cost estimatd
does not correctly describe the real cost during SQL execution, the access plan picked by the
optimizer may not be optimal.
5. At each step, QGM is modified to reflect the decision of each component. After the DB2
optimizer selects an access plan, DB2 generates the executable code for running the query,
based on the QGM result.
As you've seen in the steps above, the optimizer selects the optimal access plan based on
available statistics. However, in order to get the best cost estimate, the optimizer needs the values
that will be used when the query executes.
Here is one example. Let's say that a table contains 100 rows, and 99 of them have value "0"
and only one has a value of "1". When a query is issued against the table, DB2 must know the
exact value provided for the column in order to correctly estimate whether the result set is 99
rows or one row. This is feasible when the query is dynamic and already contains the value in the
statement. However for a static query which is compiled at bind time, or for a dynamic query using
a parameter marker, the DB2 optimizer will not be able to estimate the exact rows to be returned
because the values are unknown. In this case, the optimizer uses a generic rule or a default value
to estimate an average cost by assuming normal data distribution. This can lead to a sub-optimal
plan compared with one selected when the SQL includes the exact values.
A cost estimation that assumes normal data distribution may not best reflect the real cost of a
query when different parameters are used. In that case, you can use the REOPT option at BIND
time or when or running dynamic queries in order to allow the DB2 optimizer to pick up the value
during execution and thus select the optimal access plan.
There are three different settings for REOPT:
• REOPT NONE: This is the default. No query optimization occurs at query execution time. The
default estimates chosen by the compiler are used for the special registers, global variables,
or parameter markers. The default NULLID package set is used to execute dynamic SQL
statements.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 2 of 29
ibm.com/developerWorks/
developerWorks®
• REOPT ONCE: Query optimization occurs once at query execution time, when the query
is executed for the first time. The NULLIDR1 package set, which is bound with the REOPT
ONCE bind option, is used.
• REOPT ALWAYS: Query optimization or reoptimization occurs at query execution time every
time the query is executed. The NULLIDRA package set, which is bound with the REOPT
ALWAYS bind option, is used.
This article introduces some typical uses of REOPT during SQL optimization. You'll have a chance
to examine several examples that show how to enable REOPT in different types of applications,
and how SQL performance is affected by this variable. After reading this article, you will be able to:
• Understand how REOPT affects the behavior of the DB2 optimizer and runtime performance
• Modify DB2 configurations, applications, and bind commands to enable or disable the REOPT
feature
Preparing the test environment
This section describes the test environment that we used for the examples discussed in this article.
In order to make the illustrations clear and not overly complicated, we used only two tables in the
tests, and did not change the sample data in the tests.
You can follow these steps to recreate the same environment that we used in this article if you
want to try these test for yourself or perform additional experiments on your own.
1. First, use the db2set command to set DB2_HASH_JOIN to NO, and stop and start DB2, as
shown in Listing 1.
Listing 1. Setting db2set variable
$ db2set DB2_HASH_JOIN=NO
$ db2stop force
09/25/2010 12:47:38
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 12:47:46
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2set
DB2_HASH_JOIN=NO
DB2COMM=tcpip
2. Next create a sample database and populate it with some tables.
Listing 2. Creating database and tables
$ db2 create database sample using codeset UTF-8 territory US
DB20000I The CREATE DATABASE command completed successfully.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "create table DB2INST1.T1 (ID integer not null, PENDING integer,
DESC char(100))"
DB20000I The SQL command completed successfully.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 3 of 29
developerWorks®
ibm.com/developerWorks/
$ db2 "alter table DB2INST1.T1 add primary key (ID)"
DB20000I The SQL command completed successfully.
$ db2 "create index DB2INST1.I1 on T1 (PENDING, ID)"
DB20000I The SQL command completed successfully.
$ db2 "create table DB2INST1.T2 (ID integer not null, STATUS char(100))"
DB20000I The SQL command completed successfully.
$ db2 "alter table DB2INST1.T2 add primary key (ID)"
DB20000I The SQL command completed successfully.
3. Now create some scripts that will create some sample data, and load your tables with the
sample data.
Listing 3. Creating script to populate data
$ cat datagen.sh
echo "remove load data for T1"
rm load.del 2>/dev/null
count=0
echo "start populating data for T1"
while [ $count -lt 1000000 ]; do
echo $count,1,description >> load.del
let "count = count + 1";
done
echo "finish populating data for T1"
echo "remove load data for T2"
rm load1.del 2>/dev/null
count=0
echo "start populating data for T2"
while [ $count -lt 1000000 ]; do
echo $count,done >> load1.del
let "count = count + 1";
done
echo "finish populating data for T2"
$ chmod 755 datagen.sh
$ ./datagen.sh
remove load data for T1
start populating data for T1
finish populating data for T1
remove load data for T2
start populating data for T2
finish populating data for T2
$ ls
datagen.sh load.del
load1.del
$ head load.del
0,1,description
1,1,description
2,1,description
3,1,description
4,1,description
5,1,description
6,1,description
7,1,description
8,1,description
9,1,description
$ tail load.del
999990,1,description
999991,1,description
999992,1,description
999993,1,description
999994,1,description
999995,1,description
999996,1,description
999997,1,description
999998,1,description
999999,1,description
$ head load1.del
0,done
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 4 of 29
ibm.com/developerWorks/
developerWorks®
1,done
2,done
3,done
4,done
5,done
6,done
7,done
8,done
9,done
$ tail load1.del
999990,done
999991,done
999992,done
999993,done
999994,done
999995,done
999996,done
999997,done
999998,done
999999,done
Listing 4. Loading data into tables
$ db2 load from load.del of del replace into DB2INST1.T1 NONRECOVERABLE
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/temp/load.del".
SQL3500W The utility is beginning the "LOAD" phase at time "08/12/2010
08:48:16.518625".
SQL3519W
Begin Load Consistency Point. Input record count = "0".
SQL3520W
Load Consistency Point was successful.
SQL3110N The utility has completed processing.
the input file.
"1000000" rows were read from
SQL3519W
Begin Load Consistency Point. Input record count = "1000000".
SQL3520W
Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "08/12/2010
08:48:19.202411".
SQL3500W The utility is beginning the "BUILD" phase at time "08/12/2010
08:48:19.202686".
SQL3213I
The indexing mode is "REBUILD".
SQL3515W The utility has finished the "BUILD" phase at time "08/12/2010
08:48:22.349494".
Number
Number
Number
Number
Number
Number
of
of
of
of
of
of
rows
rows
rows
rows
rows
rows
read
skipped
loaded
rejected
deleted
committed
=
=
=
=
=
=
1000000
0
1000000
0
0
1000000
$ db2 load from load1.del of del replace into DB2INST1.T2 NONRECOVERABLE
SQL3501W The table space(s) in which the table resides will not be placed in
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 5 of 29
developerWorks®
ibm.com/developerWorks/
backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/temp/load1.del".
SQL3500W The utility is beginning the "LOAD" phase at time "08/12/2010
08:48:28.794348".
SQL3519W
Begin Load Consistency Point. Input record count = "0".
SQL3520W
Load Consistency Point was successful.
SQL3110N The utility has completed processing.
the input file.
"1000000" rows were read from
SQL3519W
Begin Load Consistency Point. Input record count = "1000000".
SQL3520W
Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "08/12/2010
08:48:30.754238".
SQL3500W The utility is beginning the "BUILD" phase at time "08/12/2010
08:48:30.754531".
SQL3213I
The indexing mode is "REBUILD".
SQL3515W The utility has finished the "BUILD" phase at time "08/12/2010
08:48:33.191273".
Number
Number
Number
Number
Number
Number
of
of
of
of
of
of
rows
rows
rows
rows
rows
rows
read
skipped
loaded
rejected
deleted
committed
=
=
=
=
=
=
1000000
0
1000000
0
0
1000000
Listing 5. Inserting another row into each table
$ db2 "insert
DB20000I The
$ db2 "insert
DB20000I The
into DB2INST1.T1 values (1000000,0,'description')"
SQL command completed successfully.
into DB2INST1.T2 values (1000000, 'pending')"
SQL command completed successfully.
4. Next, collect statistics so that the system tables will be populated.
Listing 6. Collecting statistics
$ db2 runstats on table DB2INST1.T1 on all columns with distribution and indexes all
DB20000I The RUNSTATS command completed successfully.
$ db2 runstats on table DB2INST1.T2 on all columns with distribution and indexes all
DB20000I The RUNSTATS command completed successfully.
5. Finally, create explain tables so that you will be able to run explain to understand what the
optimizer is doing.
Listing 7. Creating explain tables
$ db2 -tvf ~/sqllib/misc/EXPLAIN.DDL >/dev/null
Now your test environment is ready.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 6 of 29
ibm.com/developerWorks/
developerWorks®
Verifying access plans
Using the actual value
When you use the actual value in the query, the optimizer will be able to calculate the estimation
based on the real value.
Listing 8. Query using value
$ cat value.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0;
For the above query, there is only one row in T1 that satisfies the predicate (t1.pending=0). The
best access plan should be fetching the ID column from T1 using index I1, and then performing a
nested-loop join with primary key for T2 to get the RowID, and finally fetching the status column for
the result.
Listing 9. Access plan for value.sql
$ db2 set current explain mode explain
DB20000I The SQL command completed successfully.
$ db2 -tvf value.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
$ db2 set current explain mode no
DB20000I The SQL command completed successfully.
$ db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o exfmt_value.txt
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
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 exfmt_value.txt.
Executing Connect Reset -- Connect Reset was Successful.
$ cat exfmt_value.txt | head -n 93 | tail -48
Original Statement:
-----------------select status
from db2inst1.t1 as A, db2inst1.t2 as B
where A.id=B.id and A.pending=0
Optimized Statement:
------------------SELECT Q1.STATUS AS "STATUS"
FROM DB2INST1.T2 AS Q1, DB2INST1.T1 AS Q2
WHERE (Q2.PENDING = 0) AND (Q2.ID = Q1.ID)
Access Plan:
----------Total Cost:
Query Degree:
30.2842
1
Rows
RETURN
(
1)
Cost
I/O
|
1
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 7 of 29
developerWorks®
ibm.com/developerWorks/
^NLJOIN
(
2)
30.2842
4
/------+-------\
1
IXSCAN
(
3)
15.1412
2
|
1e+06
INDEX: DB2INST1
I1
Q2
1
FETCH
(
4)
22.704
3
/---+---\
1
1e+06
IXSCAN TABLE: DB2INST1
(
5)
T2
15.14
Q1
2
|
1e+06
INDEX: SYSIBM
SQL100925125141710
Q1
$ db2 -tvf value.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0
STATUS
------------------------------------------------------------------------------------pending
1 record(s) selected.
From the access plan, IXSCAN(3) shows one row is expected to be returned from T1. For each
row returned from IXSCAN(3), one row (FETCH(4)) is estimated to be fetched from T2. Thus the
estimation for final the result set is one row only (NLJOIN(2)), which matches the real result.
Using parameter markers
When you use a parameter marker in the query, the optimizer doesn't know which value the
application is going to provide, so the access plan cannot be optimized based on the real value
being used in the query.
Listing 10. Query using parameter marker
$ cat pmarker.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=?;
For the above query, the predicate on t1.pending is unknown. The access plan previously used
may not be the best one if the value is changed from 0 to 1.
For example, if the user provides (t1.pending=1) as predicate, using a nested-loop join to go
through a million rows will take a long time. In this case, the optimizer creates a different access
plan, which may not be optimal if the user input is 0.
Listing 11. Access plan for pmarker.sql
$ db2 set current explain mode explain
DB20000I The SQL command completed successfully.
$ db2 -tvf pmarker.sql
select status from t1, t2 where t1.id=t2.id and t1.pending=?
SQL0217W The statement was not executed as only Explain information requests
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 8 of 29
ibm.com/developerWorks/
are being processed.
developerWorks®
SQLSTATE=01604
$ db2 set current explain mode no
DB20000I The SQL command completed successfully.
$ db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o exfmt_pmarker.txt
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_pmarker.txt.
Executing Connect Reset -- Connect Reset was Successful.
$ cat exfmt_pmarker.txt | head -n 93 | tail -48
Original Statement:
-----------------select status
from db2inst1.t1 as A, db2inst1.t2 as B
where A.id=B.id and A.pending=?
Optimized Statement:
------------------SELECT Q1.STATUS AS "STATUS"
FROM DB2INST1.T2 AS Q1, DB2INST1.T1 AS Q2
WHERE (Q2.PENDING = :?) AND (Q2.ID = Q1.ID)
Access Plan:
----------Total Cost:
Query Degree:
36963.3
1
Rows
RETURN
(
1)
Cost
I/O
|
500000
^MSJOIN
(
2)
36963.3
35704.7
/-------+-------\
1e+06
0.5
FETCH
FILTER
(
3)
(
5)
33628.9
3155.73
32839.9
2864.78
/---+----\
|
1e+06
1e+06
500000
IXSCAN
TABLE: DB2INST1 IXSCAN
(
4)
T2
(
6)
4772.88
Q1
3155.73
4252.94
2864.78
|
|
1e+06
1e+06
INDEX: SYSIBM
INDEX: DB2INST1
SQL100925125141710
I1
Q1
Q2
In the above access plan, IXSCAN(4) scans the entire primary key for one million rows. For
each of rows being scanned, FETCH(3) will read the status column from T2. The result set from
FETCH(3) is ordered by the ID column and joined using a merge join with index I1 for T1 to get
final result set.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 9 of 29
developerWorks®
ibm.com/developerWorks/
Depending on the value provided for T1.PENDING column, the above access plan may or may not
be optimal. If the user provides a value of 1, the above plan will be the best one. However, if 0 is
used in the predicate, a nested-loop join would be the optimal choice instead of merge join.
The SQL statement above is a good candidate for REOPT ALWAYS. With REOPT ALWAYS, the
query will be re-compiled and the access plan will be re-generated based on run-time value. This
brings little overhead for compiling, but gives an optimal access plan. We will illustrate how to set
REOPT level in the next section.
Binding packages with different REOPT levels
The access plan for a given SQL statement is generated at bind time. To enable re-optimization
for an SQL statement, bind the package with the REOPT bind option. By default, packages
with REOPT NONE option are generated, and they have COLLECTION name "NULLID". If you
discover that a different REOPT option should be used in application, generate packages with the
different REOPT option and configure the application to point to the desired packages.
DB2 CLI packages are shared by DB2 CLI, ODBC, JDBC, OLE DB, .NET, and ADO applications.
Any change you make to these packages will affect all the application of these types.
Listing 12. Bind packages with default option
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 bind ~/sqllib/bnd/@db2ubind.lst blocking all sqlerror continue grant public
LINE
------
MESSAGES FOR db2ubind.lst
-------------------------------------------------------------------SQL0061W The binder is in progress.
LINE
-----2239
MESSAGES FOR db2ueiwi.bnd
-------------------------------------------------------------------SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name.
SQLSTATE=42704
SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name.
SQLSTATE=42704
2243
LINE
------
MESSAGES FOR db2clpnc.bnd
-------------------------------------------------------------------SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE
------
MESSAGES FOR db2arxnc.bnd
-------------------------------------------------------------------SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE
-----1168
MESSAGES FOR db2ats_sps.bnd
-------------------------------------------------------------------SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1198
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 10 of 29
ibm.com/developerWorks/
1229
SQL0204N
1477
SQL0204N
1494
SQL0204N
1512
SQL0204N
1550
SQL0204N
1674
SQL0204N
1691
SQL0204N
1710
SQL0204N
1727
SQL0204N
1890
SQL0204N
1945
SQL0204N
1957
SQL0204N
1974
SQL0204N
developerWorks®
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
"SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
LINE
------
MESSAGES FOR db2ubind.lst
-------------------------------------------------------------------SQL0091N Binding was ended with "0" errors and "19"
warnings.
$ db2 bind ~/sqllib/bnd/@db2cli.lst blocking all sqlerror continue
LINE
------
MESSAGES FOR db2cli.lst
-------------------------------------------------------------------SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
The above commands create packages with REOPT NONE under collection NULLID. This is
normally done as a post-install task.
Listing 13. Bind packages with REOPT ONCE and REOPT ALWAYS
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 bind ~/sqllib/bnd/db2clipk.bnd blocking all grant public collection NULLIDR1
LINE
------
MESSAGES FOR db2clipk.bnd
-------------------------------------------------------------------SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
$ db2 bind ~/sqllib/bnd/db2clipk.bnd blocking all grant public collection NULLIDRA
LINE
------
MESSAGES FOR db2clipk.bnd
-------------------------------------------------------------------SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 11 of 29
developerWorks®
ibm.com/developerWorks/
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
These commands will generate two sets of packages with REOPT ONCE and REOPT ALWAYS
under collections NULLIDR1 and NULLIDRA respectively. Note, NULLID, NULLIDR1 and
NULLIDRA are reserved keywords, and cannot be used for other purposes.
In the case where you have only an IBM Data Server client for JDBC and SQLJ installed, where
no DB2 command line tools are available, a DB2Binder tool is provided to bind the package for the
standalone JDBC driver.
Listing 14. Bind from standalone JDBC driver
$ export PATH=$PATH:~/sqllib/java/jdk64/jre/bin
$ java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://myhost:60009/sample -user db2inst1 \
-password mypassword -collection mycol -reopt once -blocking all
Binder performing action "add" to "jdbc:db2://myhost:60009/sample"
under collection "mycol":
Package "SYSSTAT": Bind succeeded.
Package "SYSSH100": Bind succeeded.
Package "SYSSH200": Bind succeeded.
Package "SYSSH300": Bind succeeded.
Package "SYSSH400": Bind succeeded.
Package "SYSSN100": Bind succeeded.
Package "SYSSN200": Bind succeeded.
Package "SYSSN300": Bind succeeded.
Package "SYSSN400": Bind succeeded.
Package "SYSSH101": Bind succeeded.
Package "SYSSH201": Bind succeeded.
Package "SYSSH301": Bind succeeded.
Package "SYSSH401": Bind succeeded.
Package "SYSSN101": Bind succeeded.
Package "SYSSN201": Bind succeeded.
Package "SYSSN301": Bind succeeded.
Package "SYSSN401": Bind succeeded.
Package "SYSSH102": Bind succeeded.
Package "SYSSH202": Bind succeeded.
Package "SYSSH302": Bind succeeded.
Package "SYSSH402": Bind succeeded.
Package "SYSSN102": Bind succeeded.
Package "SYSSN202": Bind succeeded.
Package "SYSSN302": Bind succeeded.
Package "SYSSN402": Bind succeeded.
Package "SYSLH100": Bind succeeded.
Package "SYSLH200": Bind succeeded.
Package "SYSLH300": Bind succeeded.
Package "SYSLH400": Bind succeeded.
Package "SYSLN100": Bind succeeded.
Package "SYSLN200": Bind succeeded.
Package "SYSLN300": Bind succeeded.
Package "SYSLN400": Bind succeeded.
Package "SYSLH101": Bind succeeded.
Package "SYSLH201": Bind succeeded.
Package "SYSLH301": Bind succeeded.
Package "SYSLH401": Bind succeeded.
Package "SYSLN101": Bind succeeded.
Package "SYSLN201": Bind succeeded.
Package "SYSLN301": Bind succeeded.
Package "SYSLN401": Bind succeeded.
Package "SYSLH102": Bind succeeded.
Package "SYSLH202": Bind succeeded.
Package "SYSLH302": Bind succeeded.
Package "SYSLH402": Bind succeeded.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 12 of 29
ibm.com/developerWorks/
Package "SYSLN102":
Package "SYSLN202":
Package "SYSLN302":
Package "SYSLN402":
DB2Binder finished.
Bind
Bind
Bind
Bind
developerWorks®
succeeded.
succeeded.
succeeded.
succeeded.
Setting re-optimization (REOPT) level for dynamic SQL statements
Now that there are three sets of packages in the system, the application can use different REOPT
settings by pointing to the collection it needs without interfering with other applications. We will
illustrate specifying REOPT NONE (default) and REOPT ALWAYS in applications for the statement
in Listing 10 and will compare the results.
CLI, ODBC, JDBC by legacy driver, OLE DB, .NET, and ADO applications
DB2 CLI, ODBC, type2 JDBC, OLE DB, .NET and ADO applications share CLI packages and they
all go through the CLI driver.
There are two ways to select different REOPT level for applications going through CLI driver.
• Specify either REOPT keyword or CurrentPackageSet keyword in the db2cli.ini file.
• REOPT = 2 | 3 | 4
• 2---REOPT NONE (default value)
• 3---REOPT ONCE
• 4---REOPT ALWAYS
• CurrentPackageSet="NULLID" | "NULLIDR1" | "NULLIDRA"
Setting REOPT = 4 is equvelent to CurrentPackageSet = "NULLIDRA". If both REOPT and
CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.
• Specify connection attribute SQL_ATTR_CURRENT_PACKAGE_SET or SQL_ATTR_REOPT
in application.
Listing 15. Sample CLI application test_reopt.c
$ cp -r ~/sqllib/samples/cli .
$ cd cli
$ ls
admincmd_autoconfigure.c dbinfo.c
admincmd_contacts.c
dbmcon.c
admincmd_describe.c
dbmconx1.h
admincmd_export.c
dbmconx1.sqc
admincmd_import.c
dbmconx2.h
admincmd_onlinebackup.c
dbmconx2.sqc
admincmd_quiesce.c
dbmconx.c
admincmd_updateconfig.c
dbnative.c
bldapp
dbuse.c
bldmc
dbusemx.sqc
bldrtn
dbxamon.c
clihandl.c
dbxamon.ini
cli_info.c
dtinfo.c
clisqlca.c
dtlob.c
db2cli
dtudt.c
dbcongui.c
embprep
dbconn.c
getdbcfgparams.c
$ cat test_reopt.c
#include <time.h>
#include <sys/time.h>
#include <stdio.h>
getdbmcfgparams.c
getmessage.c
ilinfo.c
ininfo.c
makefile
README
spcall.c
spcat
spclient.c
spclires.c
spcreate.db2
spdrop.db2
spserver.c
spserver.exp
ssv_db_cfg.c
tbast.c
tbcompress.c
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
tbconstr.c
tbcreate.c
tbinfo.c
tbload.c
tbmod.c
tbonlineinx.c
tbread.c
tbrunstats.c
tbtemp.c
tbumqt.c
trustedcontext.c
udfcli.c
udfsrv.c
udfsrv.exp
utilcli.c
utilcli.h
Page 13 of 29
developerWorks®
#include
#include
#include
#include
#include
ibm.com/developerWorks/
<string.h>
<stdlib.h>
<sqlcli1.h>
<sqlca.h>
"utilcli.h" /* header file for utilcli.c under /sqllib/samples/cli */
int execSelectQuery(SQLHANDLE);
int main(int argc, char *argv[])
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1];
char user[MAX_UID_LENGTH + 1];
char pswd[MAX_PWD_LENGTH + 1];
struct timeval start, end;
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
rc = CLIAppInit(dbAlias,
user,
pswd,
&henv,
&hdbc,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF);
/*this is equvelent to SQL_ATTR_CURRENT_PACKAGE_SET below,
choose one or another*/
/*
SQLSetConnectAttr(hdbc,
SQL_ATTR_REOPT,
(SQLPOINTER) 4,
SQL_IS_UINTEGER );
DBC_HANDLE_CHECK(hdbc, cliRC);
*/
SQLSetConnectAttr(hdbc,
SQL_ATTR_CURRENT_PACKAGE_SET,
(SQLPOINTER) "NULLIDRA",
SQL_NTS);
DBC_HANDLE_CHECK(hdbc, cliRC);
if (rc != 0)
{
return rc;
}
gettimeofday(&start, NULL);
rc = execSelectQuery(hdbc);
DBC_HANDLE_CHECK(hdbc, cliRC);
gettimeofday(&end, NULL);
printf("%ld microsec\n",
(((end.tv_sec*1000000+ end.tv_usec)-(start.tv_sec*1000000 + start.tv_usec))));
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
DBC_HANDLE_CHECK(hdbc, cliRC);
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 14 of 29
ibm.com/developerWorks/
developerWorks®
rc = CLIAppTerm(&henv, &hdbc, dbAlias);
return rc;
} /* end main */
int execSelectQuery(SQLHANDLE hdbc)
{
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE hstmt; /* statement handle */
SQLCHAR *stmt = (SQLCHAR *)
"select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id \
and A.pending=?";
SQLSMALLINT parameter1 = 0;
struct
{
SQLINTEGER ind;
SQLCHAR val[100];
}
status; /* variable to be bound to the status column */
/* allocate a statement handle */
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* prepare the statement */
cliRC = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind the parameter to the statement */
cliRC = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_SHORT,
SQL_SMALLINT,
0,
0,
&parameter1,
0,
NULL);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* execute the statement */
cliRC = SQLExecute(hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* bind column status to variable */
cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, status.val, 100,
&status.ind);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
/* free the statement handle */
cliRC = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
return rc;
}
Listing 16. Result from above application (NULLIDRA)
$ db2stop force
09/25/2010 13:40:22
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 15 of 29
developerWorks®
ibm.com/developerWorks/
09/25/2010 13:40:30
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
= DB2/LINUXX8664 9.7.2
SQL authorization ID
= DB2INST1
Local database alias
= SAMPLE
$ ./bldapp test_reopt
$ ls test_reopt*
test_reopt test_reopt.c test_reopt.o
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ ./test_reopt
Connecting to sample...
Connected to sample.
67242 microsec
Disconnecting from sample...
Disconnected from sample..
Changing line 51 in the test_reopt.c to make it looks like the following:
(SQLPOINTER) "NULLID",
Listing 17. Result from above application (NULLID)
$ rm test_reopt
$ rm test_reopt.o
$ db2stop force
09/25/2010 13:45:56
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 13:46:05
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ ./bldapp test_reopt
$ ls test_reopt*
test_reopt test_reopt.c test_reopt.o
$ db2 "select count(*) from db2inst1.t1"
1
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 16 of 29
ibm.com/developerWorks/
developerWorks®
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ ./test_reopt
Connecting to sample...
Connected to sample.
13447781 microsec
Disconnecting from sample...
Disconnected from sample.
Table 1. When REOPT and CurrentPackageSet can be specified
Before Connection
SQL_ATTR_REOPT
No
SQL_ATTR_CURRENT_PACKAGE_SET
Yes
After Connection
After statements allocated
Yes
Yes
Yes
No
Note:
• If both SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are set after
connection is made, and if they are mutally exclusive, only the first one set will be valid.
• SQL_ATTR_CURRENT_PACKAGE_SET set after connection is made will only affect
subsequent allocated statements. This gives the granularity to set REOPT level for the
specific statement.
• It is not recommended to set SQL_ATTR_REOPT attribute after statement handle is
allocated.
Java application by JDBC Universal Driver
Specify jdbcCollection or currentPackageSet as a DB2BaseDatasource property. The default
value for jdbcCollection is NULLID. If currentPackageSet is set, its value overrides the value of
jdbcCollection.
Listing 18. Sample file Test_Reopt.java
import
import
import
import
import
java.sql.*;
java.io.*;
java.util.*;
com.ibm.db2.jcc.*;
javax.sql.*;
class Test_Reopt
{
public static void main(String argv[])
{
//make jcc connection
try
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 17 of 29
developerWorks®
ibm.com/developerWorks/
{
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
DB2SimpleDataSource dbds=new DB2SimpleDataSource();
dbds.setDatabaseName("SAMPLE");
dbds.setDriverType(4);
dbds.setServerName("myhost");
dbds.setPortNumber(60009);
dbds.setUser("db2inst1");
dbds.setPassword("mypassword");
/*setJdbcCollection is equvelent to setCurrentPackageSet. If both are set,
CurrentPackageSet override JdbcCollection. Switch between NULLID and NULLIDRA
for test*/
((com.ibm.db2.jcc.DB2BaseDataSource)dbds).setCurrentPackageSet("NULLIDRA") ;
Connection con=dbds.getConnection();
System.out.println(
"connected with JDBC type 4 Universal driver");
DB2SystemMonitor systemMonitor =
((DB2Connection)con).getDB2SystemMonitor();
systemMonitor.enable(true);
systemMonitor.start(DB2SystemMonitor.RESET_TIMES);
PreparedStatement pst = con.prepareStatement
("select status from db2inst1.t1 as A, db2inst1.t2 as " +
"B where A.id=B.id and A.pending=?");
pst.setInt (1, 0);
ResultSet rs = pst.executeQuery();
rs.close();
pst.close();
systemMonitor.stop();
System.out.println("Eclipse time (microseconds)="
+ systemMonitor.getServerTimeMicros());
con.close();
}
catch ( Exception e )
{
e.printStackTrace();
}
finally { }
} //end of main
}
Listing 19. Result from Test_Reopt.java
$ export PATH=$PATH:~/sqllib/java/jdk64/bin
$ javac Test_Reopt.java
$ db2stop force
09/25/2010 14:12:46
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 14:12:53
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 18 of 29
ibm.com/developerWorks/
developerWorks®
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ java Test_Reopt
connected with JDBC type 4 Universal driver
Eclipse time (microseconds)=65662
Changing line 26 in the Test_Reopt.java to make it looks like the following:
((com.ibm.db2.jcc.DB2BaseDataSource)dbds).setCurrentPackageSet("NULLID") ;
Listing 20. Result from above application (NULLID)
$ javac Test_Reopt.java
$ db2stop force
09/25/2010 14:16:28
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 14:16:35
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ java Test_Reopt
connected with JDBC type 4 Universal driver
Eclipse time (microseconds)=13310117
WebSphere® application by JDBC Universal Driver
If JDBC Universal driver is used by WebSphere application, these properties can be set from
WebSphere Application Server admin console. Select Resources > JDBC > JDBC providers >
JDBC_provider name > Data sources > data_source name > WebSphere Application Server
data source properties to specify the properties.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 19 of 29
developerWorks®
ibm.com/developerWorks/
Specifying the REOPT option for static SQL statements
The static SQL statement is pre-compiled and bound to a package before execution. In order to
use different REOPT levels for a package, the package needs to be rebound and generated with a
new level. Depending on the type of application, the way to bind the package can vary.
Embedded SQL
Program test_reopt.sqc is written with embedded SQL and is pre-compiled and bound using
default REOPT level. In order to bind the package with REOPT ALWAYS, run BIND with REOPT
ALWAYS option using test_reopt.bnd file generated from pre-compiling.
Listing 21. Bind with REOPT ALWAYS for test_reopt.sqc
$ cp -r ~/sqllib/samples/c .
$ cd c
$ ls
autostore.c
dbmcon1.sqc
getlogs.sqc
ssv_db_cfg.c
tbselinit
bldapp
dbmcon2.h
getmessage.sqc
tbast.sqc
tbsel.sqc
bldmc
dbmcon2.sqc
globvarsupport.sqc
tbcompress.sqc
tbtemp.sqc
bldmt
dbmcon.sqc
inattach.c
tbconstr.sqc
tbtrig.sqc
bldrtn
dbmigrat.c
inauth.sqc
tbcreate.sqc
tbumqt.sqc
cli_info.c
dbpkg.sqc
ininfo.c
tbident.sqc
tbunion.sqc
clisnap.c
dbrecov.sqc
insnap.c
tbinfo.sqc
tscreate.sqc
clisnapnew.c
dbredirect.sqc insnapnew.c
tbintrig.sqc
tsinfo.sqc
db2uext2.cdisk
dbrestore.sqc
instart.c
tbloadcursor.sqc
udfcli.sqc
db2uext2.ctape
dbrollfwd.sqc
largerid.sqc
tbload.sqc
udfemcli.sqc
db2uext2.ctsm
dbsample.sqc
makefile
tbmerge.sqc
udfemsrv.exp
dbauth.sqc
dbsnap.c
README
tbmod.sqc
udfemsrv.sqc
dbcfg.sqc
dbsnapnew.c
setintegrity.sqc
tbmove.sqc
udfsrv.c
dbconn.sqc
dbstat.c
spcat
tbonlineinx.sqc
udfsrv.exp
dbcreate.c
dbthrds.sqc
spclient.sqc
tbpriv.sqc
utilapi.c
dbhistfile.sqc
dbuse.sqc
spcreate.db2
tbread.sqc
utilapi.h
dbinfo.c
dtformat.sqc
spcreate_gv.db2
tbreorg.sqc
utilemb.h
dbinline.sqc
dtlob.sqc
spdrop.db2
tbrowcompress.sqc utilemb.sqc
dbinspec.sqc
dtudt.sqc
spserver.exp
tbrunstats.sqc
utilrecov.c
dblogconn.sqc
embprep
spserver.sqc
tbsavept.sqc
utilsnap.c
dblognoconn.sqc evm.sqc
ssv_backup_db.c
tbselcreate.db2
dbmcon1.h
fnuse.sqc
ssv_backup_tbsp.sqc tbseldrop.db2
$ cat test_reopt.sqc
#include <time.h>
#include <sys/time.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h" /* header file for utilcli.c under /sqllib/samples/c */
int execSelectQuery(void);
int main(int argc, char *argv[])
{
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
int rc = 0;
/* Check the command line arguments. */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
return rc;
/* Connect to database. */
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 20 of 29
ibm.com/developerWorks/
developerWorks®
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
return rc;
rc = execSelectQuery();
if (rc != 0)
return rc;
/* Disconnect from database. */
rc = DbDisconn(dbAlias);
if (rc != 0)
return rc;
return 0;
} /* Main */
int execSelectQuery(void)
{
struct timeval start, end;
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
short hId;
char hStatus[100];
EXEC SQL END DECLARE SECTION;
gettimeofday(&start, NULL);
hId = 0;
EXEC SQL SELECT STATUS into :hStatus
FROM DB2INST1.T1 AS A, DB2INST1.T2 AS B
WHERE A.ID = B.ID AND A.PENDING = :hId ;
gettimeofday(&end, NULL);
printf("%ld microsec\n",
((end.tv_sec*1000000+end.tv_usec)-(start.tv_sec*1000000+start.tv_usec)));
return 0;
}
$ ./bldapp test_reopt sample db2inst1 mypassword
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
LINE
------
MESSAGES FOR test_reopt.sqc
-------------------------------------------------------------------SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
LINE
------
MESSAGES FOR utilemb.sqc
-------------------------------------------------------------------SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
LINE
------
MESSAGES FOR test_reopt.bnd
-------------------------------------------------------------------SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
DB20000I The SQL command completed successfully.
DB20000I The TERMINATE command completed successfully.
$ db2stop force
09/25/2010 14:27:39
0
0
SQL1064N DB2STOP processing was successful.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 21 of 29
developerWorks®
ibm.com/developerWorks/
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 14:27:46
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ ./test_reopt sample db2inst1 mypassword
Connecting to 'sample' database...
Connected to 'sample' database.
14579955 microsec
Disconnecting from 'sample' database...
Disconnected from 'sample' database.
$ db2 bind test_reopt.bnd action replace reopt always blocking all grant public
LINE
------
MESSAGES FOR test_reopt.bnd
-------------------------------------------------------------------SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
$ ./test_reopt sample db2inst1 mypassword
Connecting to 'sample' database...
Connected to 'sample' database.
24683 microsec
Disconnecting from 'sample' database...
Disconnected from 'sample' database.
SQL stored procedure
SQL stored procedure is pre-compiled and bound to a package at creation time. To rebind the
package for a stored procedure, drop and recreate it with new REOPT level, or rebind the package
for this specific SQL stored procedure.
Listing 22. Drop and recreate stored procedure with REOPT ALWAYS
$ cat test_reopt.sql
CREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)
SPECIFIC test_reopt
LANGUAGE SQL
INHERIT SPECIAL REGISTERS
BEGIN
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 22 of 29
ibm.com/developerWorks/
developerWorks®
DECLARE v_status CHAR(100);
DECLARE start timestamp;
DECLARE end timestamp;
SELECT CURRENT TIMESTAMP INTO start from sysibm.sysdummy1;
SELECT STATUS into v_status FROM DB2INST1.T1 AS A, DB2INST1.T2 AS B
WHERE A.ID = B.ID AND A.PENDING = hId;
SELECT CURRENT TIMESTAMP INTO end from sysibm.sysdummy1;
set elapse_time = MICROSECOND(end - start);
END@
$ db2stop force
09/25/2010 14:32:40
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 14:32:47
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ db2 drop procedure test_reopt
DB20000I The SQL command completed successfully.
$ db2 -td@ -vf test_reopt.sql
CREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)
SPECIFIC test_reopt
LANGUAGE SQL
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE v_status CHAR(100);
DECLARE start timestamp;
DECLARE end timestamp;
SELECT CURRENT TIMESTAMP INTO start from sysibm.sysdummy1;
SELECT STATUS into v_status FROM DB2INST1.T1 AS A, DB2INST1.T2 AS B
WHERE A.ID = B.ID AND A.PENDING = hId;
SELECT CURRENT TIMESTAMP INTO end from sysibm.sysdummy1;
set elapse_time = MICROSECOND(end - start);
END
DB20000I The SQL command completed successfully.
$ date; db2 "call test_reopt(0,?)"; date
Sat Sep 25 14:42:51 EDT 2010
Value of output parameters
--------------------------
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 23 of 29
developerWorks®
ibm.com/developerWorks/
Parameter Name : ELIPSE_TIME
Parameter Value : 877315
Return Status = 0
Sat Sep 25 14:43:05 EDT 2010
$ db2 "CALL SET_ROUTINE_OPTS('REOPT ALWAYS')"
Return Status = 0
$ db2 drop procedure test_reopt
DB20000I The SQL command completed successfully.
$ db2 -td@ -vf test_reopt.sql
CREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)
SPECIFIC test_reopt
LANGUAGE SQL
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE v_status CHAR(100);
DECLARE start timestamp;
DECLARE end timestamp;
SELECT CURRENT TIMESTAMP INTO start from sysibm.sysdummy1;
SELECT STATUS into v_status FROM DB2INST1.T1 AS A, DB2INST1.T2 AS B
WHERE A.ID = B.ID AND A.PENDING = hId;
SELECT CURRENT TIMESTAMP INTO end from sysibm.sysdummy1;
set elapse_time = MICROSECOND(end - start);
END
DB20000I The SQL command completed successfully.
$ date; db2 "call test_reopt(0,?)"; date
Sat Sep 25 14:43:58 EDT 2010
Value of output parameters
-------------------------Parameter Name : ELAPSE_TIME
Parameter Value : 4727
Return Status = 0
Sat Sep 25 14:43:58 EDT 2010
SQLJ application
The package for SQLJ application is generated at the time db2sqljcustomize is executed.
When db2sqljcustomize runs, it creates a serialized profile. It also creates a DB2package, if
automaticbind value is YES.
Listing 23. Rebind SQLJ package
$ cat Test_Reopt1.sqlj
import java.lang.*;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.util.*;
import javax.naming.*;
import com.ibm.db2.jcc.*;
#sql iterator Named_Iterator(String status);
class Test_Reopt1
{
public static void main(String argv[])
{
Connection con = null;
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 24 of 29
ibm.com/developerWorks/
developerWorks®
//make jcc connection
try
{
//deploy a data source
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
com.ibm.db2.jcc.DB2SimpleDataSource db2datasource =
new com.ibm.db2.jcc.DB2SimpleDataSource();
db2datasource.setServerName("myhost");
db2datasource.setPortNumber(Integer.parseInt("60009"));
db2datasource.setDatabaseName("SAMPLE");
db2datasource.setDataSourceName ("SAMPLE");
db2datasource.setDriverType(4);
db2datasource.setUser("db2inst1");
db2datasource.setPassword("mypassword");
con = db2datasource.getConnection();
if (con != null)
System.out.println("Connecting to SAMPLE successfully using JDBC driver");
execSelectQuery(con);
}
catch ( Exception e )
{
System.out.println( e.toString() );
}
finally {
}
} //end of main
static void execSelectQuery(Connection con)
{
try
{
Named_Iterator namedIter = null;
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
DB2SystemMonitor systemMonitor =
((DB2Connection)con).getDB2SystemMonitor();
systemMonitor.enable(true);
systemMonitor.start(DB2SystemMonitor.RESET_TIMES);
int id = 0;
#sql namedIter = {select status from db2inst1.t1 as A, db2inst1.t2 as B
where A.id = B.id and A.pending = :id };
systemMonitor.stop();
System.out.println("Average Server eclipse time (microseconds)=
" + systemMonitor.getServerTimeMicros());
//close the cursor
namedIter.close();
}
catch (Exception e)
{
System.out.println( e.toString() );
}
}
}
$ db2stop force
09/25/2010 15:32:46
0
0
SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
09/25/2010 15:32:54
0
0
SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
$ db2 connect to sample
Database Connection Information
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 25 of 29
developerWorks®
Database server
SQL authorization ID
Local database alias
ibm.com/developerWorks/
= DB2/LINUXX8664 9.7.2
= DB2INST1
= SAMPLE
$ db2 "select count(*) from db2inst1.t1"
1
----------1000001
1 record(s) selected.
$ db2 "select count(*) from db2inst1.t2"
1
----------1000001
1 record(s) selected.
$ sqlj Test_Reopt1.sqlj
$ db2sqljcustomize -user db2inst1 -password mypassword -url \
jdbc:db2://myhost:60009/sample Test_Reopt1_SJProfile0.ser
[jcc][sqlj]
[jcc][sqlj] Begin Customization
[jcc][sqlj] Loading profile: Test_Reopt1_SJProfile0
[jcc][sqlj] Customization complete for profile Test_Reopt1_SJProfile0.ser
[jcc][sqlj] Begin Bind
[jcc][sqlj] Loading profile: Test_Reopt1_SJProfile0
[jcc][sqlj] Driver defaults(user may override): BLOCKING ALL VALIDATE BIND
STATICREADONLY YES
[jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND
[jcc][sqlj] Binding package TEST_R01 at isolation level UR
[jcc][sqlj] Binding package TEST_R02 at isolation level CS
[jcc][sqlj] Binding package TEST_R03 at isolation level RS
[jcc][sqlj] Binding package TEST_R04 at isolation level RR
[jcc][sqlj] Bind complete for Test_Reopt1_SJProfile0
$ java Test_Reopt1
Connecting to SAMPLE successfully using JDBC driver
Average Server eclipse time (microseconds)=13545671
$ db2sqljcustomize -user db2inst1 -password mypassword -url \
jdbc:db2://myhost:60009/sample -bindoptions "REOPT ALWAYS" \
Test_Reopt1_SJProfile0.ser
[jcc][sqlj]
[jcc][sqlj] Begin Customization
[jcc][sqlj] Loading profile: Test_Reopt1_SJProfile0
[jcc][sqlj] Customization complete for profile Test_Reopt1_SJProfile0.ser
[jcc][sqlj] Begin Bind
[jcc][sqlj] Loading profile: Test_Reopt1_SJProfile0
[jcc][sqlj] User bind options: reopt ALWAYS
[jcc][sqlj] Driver defaults(user may override): BLOCKING ALL VALIDATE
BIND STATICREADONLY YES
[jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND
[jcc][sqlj] Binding package TEST_R01 at isolation level UR
[jcc][sqlj] Binding package TEST_R02 at isolation level CS
[jcc][sqlj] Binding package TEST_R03 at isolation level RS
[jcc][sqlj] Binding package TEST_R04 at isolation level RR
[jcc][sqlj] Bind complete for Test_Reopt1_SJProfile0
$ java Test_Reopt1
Connecting to SAMPLE successfully using JDBC driver
Average Server eclipse time (microseconds)=32586
Conclusion
As we've discussed in this article, the DB2 optimizer may choose a sub-optimal access plan when
there are host variables or parameter markers in the SQL statement. But with the REOPT bind
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 26 of 29
ibm.com/developerWorks/
developerWorks®
option, the optimizer will generate an access plan against the value provided by the application
during runtime, instead of using the generic plan created during bind. Try out the examples in
this article to experience how you can improve runtime performance for queries that contain host
variables or parameter markers by using the REOPT option.
Acknowledgement
Special thanks to Anthony Reina and Samir Kapoor who provided helpful advice during the writing
of this article.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 27 of 29
developerWorks®
ibm.com/developerWorks/
Resources
Learn
• The Information Center description of the Reopt CLI/ODBC configuration keyword is a good
starting point to get more information about command syntax.
• In the article Recreate optimizer access plans using db2look (developerWorks, Aug 2005),
get detailed instructions on how to generate access plans using the db2exfmt utility.
• The article Influence query optimization with optimimzation profiles and statistical views
(developerWorks, Dec 2006), provides more information about how to influence query
performance in DB2.
• In the DB2 for Linux, UNIX, and Windows area on developerWorks, get the resources you
need to advance your DB2 skills.
• Read more articles about database performance on DBA Central.
Get products and technologies
• Download a trial version of DB2 for Linux, UNIX, and Windows.
Discuss
• Participate in the discussion forum for this content.
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 28 of 29
ibm.com/developerWorks/
developerWorks®
About the authors
Shan Chen
Shan Chen is a CCT Advanced Techical Support Specialist for Data Servers at IBM
Toronto Laboratory. She is a certified DB2 database administrator and a certified DB2
developer.
Tao Wang
Tao Wang is an IBM Certified Advanced Database Administrator - DB2 for Linux,
UNIX, and Windows. Tao currently works with the DB2 Advanced Support - Down
System Division (DSD) team and has in-depth knowledge in the engine area.
© Copyright IBM Corporation 2010
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)
Improve DB2 for Linux, UNIX, and Windows query performance
using the REOPT bind option
Page 29 of 29