Contents


Improve DB2 for Linux, UNIX, and Windows query performance using the REOPT bind option

Comments

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.
  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.
  • 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        = 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.
  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
    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.  "1000000" rows were read from
    the 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/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 of rows read         = 1000000
    Number of rows skipped      = 0
    Number of rows loaded       = 1000000
    Number of rows rejected     = 0
    Number of rows deleted      = 0
    Number of rows committed    = 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
    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.  "1000000" rows were read from
    the 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/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 of rows read         = 1000000
    Number of rows skipped      = 0
    Number of rows loaded       = 1000000
    Number of rows rejected     = 0
    Number of rows deleted      = 0
    Number 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.
  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.

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:             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.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
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_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:             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.

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        = DB2/LINUXX8664 9.7.2
 SQL authorization ID   = DB2INST1
 Local database alias   = 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    MESSAGES FOR db2ueiwi.bnd
------  --------------------------------------------------------------------
 2239   SQL0204N  "SYSTEM.SYSUSERAUTH" is an undefined name.
                  SQLSTATE=42704
 2243   SQL0204N  "SYSTEM.SYSUSERAUTH" is an undefined name.
                  SQLSTATE=42704

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    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=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        = DB2/LINUXX8664 9.7.2
 SQL authorization ID   = DB2INST1
 Local database alias   = 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.
$ 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.
Package "SYSLN102": Bind succeeded.
Package "SYSLN202": Bind succeeded.
Package "SYSLN302": Bind succeeded.
Package "SYSLN402": Bind succeeded.
DB2Binder finished.

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          getdbmcfgparams.c  tbconstr.c
admincmd_contacts.c       dbmcon.c          getmessage.c       tbcreate.c
admincmd_describe.c       dbmconx1.h        ilinfo.c           tbinfo.c
admincmd_export.c         dbmconx1.sqc      ininfo.c           tbload.c
admincmd_import.c         dbmconx2.h        makefile           tbmod.c
admincmd_onlinebackup.c   dbmconx2.sqc      README             tbonlineinx.c
admincmd_quiesce.c        dbmconx.c         spcall.c           tbread.c
admincmd_updateconfig.c   dbnative.c        spcat              tbrunstats.c
bldapp                    dbuse.c           spclient.c         tbtemp.c
bldmc                     dbusemx.sqc       spclires.c         tbumqt.c
bldrtn                    dbxamon.c         spcreate.db2       trustedcontext.c
clihandl.c                dbxamon.ini       spdrop.db2         udfcli.c
cli_info.c                dtinfo.c          spserver.c         udfsrv.c
clisqlca.c                dtlob.c           spserver.exp       udfsrv.exp
db2cli                    dtudt.c           ssv_db_cfg.c       utilcli.c
dbcongui.c                embprep           tbast.c            utilcli.h
dbconn.c                  getdbcfgparams.c  tbcompress.c
$ cat test_reopt.c
#include <time.h>
#include <sys/time.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include <sqlca.h>
#include "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);
  
  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
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        = 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:

  • 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 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 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        = 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_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        = 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_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.

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. */
  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        = DB2/LINUXX8664 9.7.2
 SQL authorization ID   = DB2INST1
 Local database alias   = 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.
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        = 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.

$ ./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
 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        = 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_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
  --------------------------
  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;

   //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

 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_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 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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=580034
ArticleTitle=Improve DB2 for Linux, UNIX, and Windows query performance using the REOPT bind option
publish-date=11182010