Improve DB2 query performance using the REOPT bind option
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/]http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/
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.
IntroductionIn 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 backgroundBefore 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:
[list=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.
[*]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.
[*]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.
[*]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.
[*]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:
[list]
[*]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.
[*]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:
[list]
[*]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 environmentThis 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.
[list=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 force09/25/2010 12:47:38 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/25/2010 12:47:46 0 0 SQL1063N DB2START processing was successful.SQL1063N DB2START processing was successful.$ db2setDB2_HASH_JOIN=NODB2COMM=tcpip
[*]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 USDB20000I The CREATE DATABASE command completed successfully.$ db2 connect to sample Database Connection Information Database server = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = SAMPLE$ db2 "create table DB2INST1.T1 (ID integer not null, PENDING integer, DESC char(100))"DB20000I The SQL command completed successfully.$ 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.
[*]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.shecho "remove load data for T1"rm load.del 2>/dev/nullcount=0echo "start populating data for T1"while [ $count -lt 1000000 ]; doecho $count,1,description >> load.dellet "count = count + 1";doneecho "finish populating data for T1"echo "remove load data for T2"rm load1.del 2>/dev/nullcount=0echo "start populating data for T2"while [ $count -lt 1000000 ]; doecho $count,done >> load1.dellet "count = count + 1";doneecho "finish populating data for T2"$ chmod 755 datagen.sh$ ./datagen.shremove load data for T1start populating data for T1finish populating data for T1remove load data for T2start populating data for T2finish populating data for T2$ lsdatagen.sh load.del load1.del$ head load.del0,1,description1,1,description2,1,description3,1,description4,1,description5,1,description6,1,description7,1,description8,1,description9,1,description$ tail load.del999990,1,description999991,1,description999992,1,description999993,1,description999994,1,description999995,1,description999996,1,description999997,1,description999998,1,description999999,1,description$ head load1.del0,done1,done2,done3,done4,done5,done6,done7,done8,done9,done$ tail load1.del999990,done999991,done999992,done999993,done999994,done999995,done999996,done999997,done999998,done999999,done
Listing 4. Loading data into tables$ db2 load from load.del of del replace into DB2INST1.T1 NONRECOVERABLESQL3501W The table space(s) in which the table resides will not be placed inbackup 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/201008:48:16.518625".SQL3519W Begin Load Consistency Point. Input record count = "0".SQL3520W Load Consistency Point was successful.SQL3110N The utility has completed processing. "1000000" rows were read fromthe input file.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/201008:48:19.202411".SQL3500W The utility is beginning the "BUILD" phase at time "08/12/201008:48:19.202686".SQL3213I The indexing mode is "REBUILD".SQL3515W The utility has finished the "BUILD" phase at time "08/12/201008:48:22.349494".Number of rows read = 1000000Number of rows skipped = 0Number of rows loaded = 1000000Number of rows rejected = 0Number of rows deleted = 0Number of rows committed = 1000000$ db2 load from load1.del of del replace into DB2INST1.T2 NONRECOVERABLESQL3501W The table space(s) in which the table resides will not be placed inbackup 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/201008:48:28.794348".SQL3519W Begin Load Consistency Point. Input record count = "0".SQL3520W Load Consistency Point was successful.SQL3110N The utility has completed processing. "1000000" rows were read fromthe input file.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/201008:48:30.754238".SQL3500W The utility is beginning the "BUILD" phase at time "08/12/201008:48:30.754531".SQL3213I The indexing mode is "REBUILD".SQL3515W The utility has finished the "BUILD" phase at time "08/12/201008:48:33.191273".Number of rows read = 1000000Number of rows skipped = 0Number of rows loaded = 1000000Number of rows rejected = 0Number of rows deleted = 0Number of rows committed = 1000000
Listing 5. Inserting another row into each table$ db2 "insert into DB2INST1.T1 values (1000000,0,'description')"DB20000I The SQL command completed successfully.$ db2 "insert into DB2INST1.T2 values (1000000, 'pending')"DB20000I The SQL command completed successfully.
[*]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 allDB20000I The RUNSTATS command completed successfully.$ db2 runstats on table DB2INST1.T2 on all columns with distribution and indexes allDB20000I The RUNSTATS command completed successfully.
[*]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.
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#ibm-content]Back to topVerifying access plansUsing the actual valueWhen 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.sqlselect 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 explainDB20000I The SQL command completed successfully.$ db2 -tvf value.sqlselect status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0SQL0217W The statement was not executed as only Explain information requestsare being processed. SQLSTATE=01604$ db2 set current explain mode noDB20000I The SQL command completed successfully.$ db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o exfmt_value.txtDB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008Licensed Material - Program Property of IBMIBM DATABASE 2 Explain Table Format ToolConnecting to the Database.Connect to Database Successful.Binding package - Bind was SuccessfulOutput is in exfmt_value.txt.Executing Connect Reset -- Connect Reset was Successful.$ cat exfmt_value.txt | head -n 93 | tail -48Original Statement:------------------select statusfrom db2inst1.t1 as A, db2inst1.t2 as Bwhere A.id=B.id and A.pending=0Optimized Statement:-------------------SELECT Q1.STATUS AS "STATUS"FROM DB2INST1.T2 AS Q1, DB2INST1.T1 AS Q2WHERE (Q2.PENDING = 0) AND (Q2.ID = Q1.ID)Access Plan:----------- Total Cost: 30.2842 Query Degree: 1 Rows RETURN ( 1) Cost I/O | 1 ^NLJOIN ( 2) 30.2842 4 /------+-------\ 1 1 IXSCAN FETCH ( 3) ( 4) 15.1412 22.704 2 3 | /---+---\ 1e+06 1 1e+06 INDEX: DB2INST1 IXSCAN TABLE: DB2INST1 I1 ( 5) T2 Q2 15.14 Q1 2 | 1e+06 INDEX: SYSIBM SQL100925125141710 Q1 $ db2 -tvf value.sqlselect status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0STATUS -------------------------------------------------------------------------------------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 markersWhen 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.sqlselect 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 explainDB20000I The SQL command completed successfully.$ db2 -tvf pmarker.sqlselect status from t1, t2 where t1.id=t2.id and t1.pending=?SQL0217W The statement was not executed as only Explain information requestsare being processed. SQLSTATE=01604$ db2 set current explain mode noDB20000I The SQL command completed successfully.$ db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o exfmt_pmarker.txtDB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008Licensed Material - Program Property of IBMIBM DATABASE 2 Explain Table Format ToolConnecting 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 -48Original Statement:------------------select statusfrom db2inst1.t1 as A, db2inst1.t2 as Bwhere A.id=B.id and A.pending=?Optimized Statement:-------------------SELECT Q1.STATUS AS "STATUS"FROM DB2INST1.T2 AS Q1, DB2INST1.T1 AS Q2WHERE (Q2.PENDING = :?) AND (Q2.ID = Q1.ID)Access Plan:----------- Total Cost: 36963.3 Query Degree: 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.
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.
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#ibm-content]Back to topBinding packages with different REOPT levelsThe 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 = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = SAMPLE$ db2 bind ~/sqllib/bnd/@db2ubind.lst blocking all sqlerror continue grant publicLINE MESSAGES FOR db2ubind.lst------ -------------------------------------------------------------------- SQL0061W The binder is in progress.LINE MESSAGES FOR db2ueiwi.bnd------ -------------------------------------------------------------------- 2239 SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name. SQLSTATE=42704 2243 SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name. SQLSTATE=42704LINE MESSAGES FOR db2clpnc.bnd------ -------------------------------------------------------------------- SQL0595W Isolation level "NC" has been escalated to "UR". SQLSTATE=01526LINE MESSAGES FOR db2arxnc.bnd------ -------------------------------------------------------------------- SQL0595W Isolation level "NC" has been escalated to "UR". SQLSTATE=01526LINE MESSAGES FOR db2ats_sps.bnd------ -------------------------------------------------------------------- 1168 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1198 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1229 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1477 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1494 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1512 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1550 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1674 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1691 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1710 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1727 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1890 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name. SQLSTATE=01532 1945 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name. SQLSTATE=01532 1957 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name. SQLSTATE=01532 1974 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name. SQLSTATE=01532LINE MESSAGES FOR db2ubind.lst------ -------------------------------------------------------------------- SQL0091N Binding was ended with "0" errors and "19" warnings.$ db2 bind ~/sqllib/bnd/@db2cli.lst blocking all sqlerror continueLINE MESSAGES FOR db2cli.lst------ -------------------------------------------------------------------- SQL0061W The binder is in progress. SQL0091N Binding was ended with "0" errors and "0" warnings.$ db2 terminateDB20000I 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 = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = SAMPLE$ db2 bind ~/sqllib/bnd/db2clipk.bnd blocking all grant public collection NULLIDR1LINE 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 NULLIDRALINE MESSAGES FOR db2clipk.bnd------ -------------------------------------------------------------------- SQL0061W The binder is in progress. SQL0091N Binding was ended with "0" errors and "0" warnings.$ db2 terminateDB20000I 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 allBinder 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.Package "SYSLN102": Bind succeeded.Package "SYSLN202": Bind succeeded.Package "SYSLN302": Bind succeeded.Package "SYSLN402": Bind succeeded.DB2Binder finished.
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#ibm-content]Back to topSetting re-optimization (REOPT) level for dynamic SQL statementsNow 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 http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#listing10]Listing 10 and will compare the results.
CLI, ODBC, JDBC by legacy driver, OLE DB, .NET, and ADO applicationsDB2 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.
[list]
[*]Specify either REOPT keyword or CurrentPackageSet keyword in the db2cli.ini file.
[list]
[*]REOPT = 2 | 3 | 4
[list]
[*]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$ lsadmincmd_autoconfigure.c dbinfo.c getdbmcfgparams.c tbconstr.cadmincmd_contacts.c dbmcon.c getmessage.c tbcreate.cadmincmd_describe.c dbmconx1.h ilinfo.c tbinfo.cadmincmd_export.c dbmconx1.sqc ininfo.c tbload.cadmincmd_import.c dbmconx2.h makefile tbmod.cadmincmd_onlinebackup.c dbmconx2.sqc README tbonlineinx.cadmincmd_quiesce.c dbmconx.c spcall.c tbread.cadmincmd_updateconfig.c dbnative.c spcat tbrunstats.cbldapp dbuse.c spclient.c tbtemp.cbldmc dbusemx.sqc spclires.c tbumqt.cbldrtn dbxamon.c spcreate.db2 trustedcontext.cclihandl.c dbxamon.ini spdrop.db2 udfcli.ccli_info.c dtinfo.c spserver.c udfsrv.cclisqlca.c dtlob.c spserver.exp udfsrv.expdb2cli dtudt.c ssv_db_cfg.c utilcli.cdbcongui.c embprep tbast.c utilcli.hdbconn.c getdbcfgparams.c tbcompress.c$ cat test_reopt.c#include
Listing 16. Result from above application (NULLIDRA)$ db2stop force09/25/2010 13:40:22 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/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 force09/25/2010 13:45:56 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/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 = 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.13447781 microsec Disconnecting from sample... Disconnected from sample.
Table 1. When REOPT and CurrentPackageSet can be specified
Before ConnectionAfter ConnectionAfter statements allocated
SQL_ATTR_REOPTNoYesYes
SQL_ATTR_CURRENT_PACKAGE_SETYesYesNo
Note:
[list]
[*]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 DriverSpecify 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.javaimport java.sql.*;import java.io.*;import java.util.*;import com.ibm.db2.jcc.*;import javax.sql.*;class Test_Reopt{ public static void main(String argv[]) { //make jcc connection try { 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 force09/25/2010 14:12:46 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/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 = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = 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_Reoptconnected with JDBC type 4 Universal driverEclipse 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 force09/25/2010 14:16:28 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/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 = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = 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_Reoptconnected with JDBC type 4 Universal driverEclipse time (microseconds)=13310117
WebSphere® application by JDBC Universal DriverIf 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.
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#ibm-content]Back to topSpecifying the REOPT option for static SQL statementsThe 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 SQLProgram 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$ lsautostore.c dbmcon1.sqc getlogs.sqc ssv_db_cfg.c tbselinitbldapp dbmcon2.h getmessage.sqc tbast.sqc tbsel.sqcbldmc dbmcon2.sqc globvarsupport.sqc tbcompress.sqc tbtemp.sqcbldmt dbmcon.sqc inattach.c tbconstr.sqc tbtrig.sqcbldrtn dbmigrat.c inauth.sqc tbcreate.sqc tbumqt.sqccli_info.c dbpkg.sqc ininfo.c tbident.sqc tbunion.sqcclisnap.c dbrecov.sqc insnap.c tbinfo.sqc tscreate.sqcclisnapnew.c dbredirect.sqc insnapnew.c tbintrig.sqc tsinfo.sqcdb2uext2.cdisk dbrestore.sqc instart.c tbloadcursor.sqc udfcli.sqcdb2uext2.ctape dbrollfwd.sqc largerid.sqc tbload.sqc udfemcli.sqcdb2uext2.ctsm dbsample.sqc makefile tbmerge.sqc udfemsrv.expdbauth.sqc dbsnap.c README tbmod.sqc udfemsrv.sqcdbcfg.sqc dbsnapnew.c setintegrity.sqc tbmove.sqc udfsrv.cdbconn.sqc dbstat.c spcat tbonlineinx.sqc udfsrv.expdbcreate.c dbthrds.sqc spclient.sqc tbpriv.sqc utilapi.cdbhistfile.sqc dbuse.sqc spcreate.db2 tbread.sqc utilapi.hdbinfo.c dtformat.sqc spcreate_gv.db2 tbreorg.sqc utilemb.hdbinline.sqc dtlob.sqc spdrop.db2 tbrowcompress.sqc utilemb.sqcdbinspec.sqc dtudt.sqc spserver.exp tbrunstats.sqc utilrecov.cdblogconn.sqc embprep spserver.sqc tbsavept.sqc utilsnap.cdblognoconn.sqc evm.sqc ssv_backup_db.c tbselcreate.db2dbmcon1.h fnuse.sqc ssv_backup_tbsp.sqc tbseldrop.db2$ cat test_reopt.sqc#include
SQL stored procedureSQL 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.sqlCREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)SPECIFIC test_reoptLANGUAGE SQLINHERIT SPECIAL REGISTERSBEGIN 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 force09/25/2010 14:32:40 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/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 = DB2/LINUXX8664 9.7.2 SQL authorization ID = DB2INST1 Local database alias = 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_reoptDB20000I The SQL command completed successfully.$ db2 -td@ -vf test_reopt.sqlCREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)SPECIFIC test_reoptLANGUAGE SQLINHERIT SPECIAL REGISTERSBEGIN 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);ENDDB20000I The SQL command completed successfully.$ date; db2 "call test_reopt(0,?)"; dateSat Sep 25 14:42:51 EDT 2010 Value of output parameters -------------------------- Parameter Name : ELIPSE_TIME Parameter Value : 877315 Return Status = 0Sat Sep 25 14:43:05 EDT 2010$ db2 "CALL SET_ROUTINE_OPTS('REOPT ALWAYS')" Return Status = 0$ db2 drop procedure test_reoptDB20000I The SQL command completed successfully.$ db2 -td@ -vf test_reopt.sqlCREATE PROCEDURE test_reopt(in hId integer, out elapse_time bigint)SPECIFIC test_reoptLANGUAGE SQLINHERIT SPECIAL REGISTERSBEGIN 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);ENDDB20000I The SQL command completed successfully.$ date; db2 "call test_reopt(0,?)"; dateSat Sep 25 14:43:58 EDT 2010 Value of output parameters -------------------------- Parameter Name : ELAPSE_TIME Parameter Value : 4727 Return Status = 0Sat Sep 25 14:43:58 EDT 2010
SQLJ applicationThe 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.sqljimport 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; //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 mainstatic 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 force09/25/2010 15:32:46 0 0 SQL1064N DB2STOP processing was successful.SQL1064N DB2STOP processing was successful.$ db2start09/25/2010 15:32:54 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$ 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_Reopt1Connecting to SAMPLE successfully using JDBC driverAverage 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_Reopt1Connecting to SAMPLE successfully using JDBC driverAverage Server eclipse time (microseconds)=32586
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011reopt/#ibm-content]Back to topConclusionAs 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 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.
AcknowledgementSpecial thanks to Anthony Reina and Samir Kapoor who provided helpful advice during the writing of this article.
ResourcesLearn[list]
[*]The http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0020997.html]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 http://www.ibm.com/developerworks/data/library/techarticle/dm-0508kapoor/index.html]Recreate optimizer access plans using db2look (developerWorks, Aug 2005), get detailed instructions on how to generate access plans using the db2exfmt utility.
[*]The article http://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/index.html]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 http://www.ibm.com/developerworks/data/products/db2luw/index.html]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 http://www.ibm.com/developerworks/data/zones/dba/index.html]DBA Central.
Get products and technologies[list]
[*]Download a trial version of http://www.ibm.com/developerworks/downloads/im/udb/index.html]DB2 for Linux, UNIX, and Windows.
Discuss[list]
[*]http://www.ibm.com/developerworks/forums/forum.jspa?forumID=842]Participate in the discussion forum.
Comments