内容


使用 REOPT 绑定选项提高 DB2 for Linux, UNIX, and Windows 的查询性能

Comments

简介

在 DB2 for Linux, UNIX, and Windows 环境中,您可能会发现运行时查询性能与 OPEN 时传递给语句的输入变量值有关。这种可能性在数据分布跨度很大的时候是确实存在的。然而,您可能仍然希望优化器基于用户传递给查询的值来查找最佳的存取计划。

REOPT 是一个绑定选项,您可以在绑定一个包或执行一个动态 SQL 语句以影响 DB2 重新优化查询的行为时指定这个选项,这样优化器就能够利用这个后面到达的信息,即输入变量的值。

查询处理背景知识

在开始详细介绍 REOPT 之前,让我们先了解一下查询处理是如何进行的。查询处理可以总结为以下步骤:

  1. 当用户或应用程序发起一个查询时,首先需要将其解析并转换成 DB2 所能识别的格式。这个内部表现形式是由查询图形模型(QGM)组件所执行的。QGM 是查询的图形化表现方式。它是在 SQL 语句被解析之后初始化的。
  2. 在解析语句并生成初始化图形表示之后,DB2 会检查约束、外键、触发器和视图,然后修改 QGM 以包含所有这些对象。
  3. 下一步是查询重写(QRW)优化。在这一步,DB2 会基于预定义规则修改原始的用户查询,以提高语句的运行效率。

    注意在上面的所有步骤中,DB2 会基于数据库的 SQL 数据定义语言(DDL)来处理查询。此时,计算过程中并不包含数据统计信息。

  4. 在 QRW 优化之后,DB2 优化器就会介入。它会基于存储在目录和统计表中的统计数据而评估每一个可能的存取计划的执行开销,然后选择最低开销的存取计划。在这一步,DB2 会使用各种优化技术,以获得最精确的开销评估值,如列分布、列组统计、统计视图和物化查询表。如果评估得到的开销不能够正确反映 SQL 执行中的实际开销,那么优化器所选择的存取计划可能不是最优的。
  5. 在每一步中,QGM 都会修改以反映每一个组件的决策。在 DB2 优化器选择一个存取计划之后,DB2 会基于 QGM 结果生成用于运行查询的可执行代码。

您可以从以上步骤看到,这个优化器基于可用的统计信息选择了最优的存取计划。然而,为了获得最佳的开销评估值,优化器需要获得查询执行时使用的值。

举个例子,假如一个表有 100 行记录,其中 99 行记录的值为 “0”,而只有一行记录值为 “1”。当查询一个表时,DB2 必须知道列的确切值才能够正确评估结果集是 99 行还是 1 行。这在动态且已经在语句中包含了值的查询中是可行的。然而,对于一个在绑定时已经编译的静态查询而言,或者对于一个使用参数标记的动态查询而言,DB2 优化器将无法评估返回的确切记录行数,因为其值是未知的。在这种情况下,优化器会使用一个通用规则或者默认值以正常数据分布率来评估得到一个平均开销值。这相对于在 SQL 包含确切值的查询而言得到的值是次优化的。

假定正常数据分布的开销评估可能并不能最好地反映查询使用不同参数时的实际开销。在这种情况下,您可以在绑定时或在运行动态查询时使用 REOPT 选项,从而允许 DB2 优化器在执行中获得这个值,最终得到最优的存取计划。

REOPT 有三个不同的设置:

  • REOPT NONE:这是默认值。执行查询时不进行任何的查询优化。编译器选择的默认评估值是用在特殊注册、全局变量或参数标记的。这时使用默认的 NULLID 包集来执行动态 SQL 语句。
  • REOPT ONCE:查询执行中只发生一次查询优化,即在查询第一次执行时。这时使用的是 NULLIDR1 包集,它是使用 REOPT ONCE 绑定选项绑定的。
  • REOPT ALWAYS:每一次查询执行时都发生查询优化或重新优化。这时使用的是 NULLIDRA 包集,它是使用 REOPT ALWAYS 绑定选项绑定的。

本文将介绍 REOPT 在 SQL 优化过程中的一些典型用法。您将看到几个不同的示例,展示如何在不同类型应用程序中启用 REOPT,这个变量如何影响 SQL 性能。在阅读本文之后,您将能够:

  • 理解 REOPT 如何影响 DB2 优化器的行为及运行时性能
  • 修改 DB2 配置、应用和绑定命令,以启用或禁用 REOPT 特性。

准备测试环境

这一节将介绍我们在本文示例中所使用的测试环境。为了使演示更清晰和简单,我们在测试中只使用两个表,并且不会修改测试中的样例数据。

如果您希望自己尝试这些测试或者根据自己需要进行更多实验,您可以按照以下步骤重新创建与本文完全相同的环境。

  1. 首先,要使用 db2set 命令将 DB2_HASH_JOIN 设置为 NO,然后停止后再启动 DB2,如清单 1 所示。
    清单 1. 设置 db2set 变量
    $ 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. 接下来,创建一个样例数据库,并在其中创建以下的表。
    清单 2. 创建数据库与表
    $ 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. 现在创建一些生成样例数据的脚本,然后在表中插入一些样例数据。
    清单 3. 创建脚本以生成数据
    $ 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
    清单 4. 将数据加载到表中
    $ 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
    清单 5. 往每个表中插入另一行记录
    $ 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. 接下来,收集统计信息,这样系统表中会出现一些数据。
    清单 6. 收集统计信息
    $ 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. 最后,创建解释表,这样您就能够执行解释以了解优化器的操作。
    清单 7. 创建解释表
    $ db2 -tvf ~/sqllib/misc/EXPLAIN.DDL >/dev/null

现在您的测试环境已经创建好了。

验证存取计划

使用实际值

如果您在查询中使用实际值,那么优化器将能够基于真实值来计算评估值。

清单 8. 使用值进行查询
$ cat value.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=0;

在上面的查询中,T1 中仅仅有一行记录符合断言(t1.pending=0)结果。最佳的存取计划应该是使用索引 I1 从 T1 查询 ID 字段,然后在 T2 主键上执行内嵌循环联接获得 RowID,最后获取结果中的 status 字段。

清单 9. 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.

在上面的存取计划中,IXSCAN(3) 显示 T1 预期会返回一行记录。对于 IXSCAN(3) 返回的每一行记录,我们都能够从 T2 获得一个记录值(FETCH(4))。因此结果集中最终的评估值只有一个(NLJOIN(2)),这是符合实际结果的。

使用参数标记

当在查询中使用参数标记时,优化器并不知道应用程序将会输入什么值,所以这个存取计划无法基于查询使用的实际值进行评估。

清单 10. 使用参数标记进行查询
$ cat pmarker.sql
select status from db2inst1.t1 as A, db2inst1.t2 as B where A.id=B.id and A.pending=?;

在上面的查询,t1.pending 断言的值是未知的。如果值从 0 变成 1,那么之前所使用的存取计划可能并不是最好的。

例如,如果用户输入的断言值为(t1.pending=1),那么使用内嵌循环联接遍历一百万行记录需要的时间会很长。在这种情况下,优化器会创建的存取计划都是不一样的,这在用户输入为 0 时可能不是最优的。

清单 11. 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

在上面的存取计划中,IXSCAN(4) 会扫描一百万行记录的所有主键。对于所扫描的每一行记录,FETCH(3) 都会读取 T2 的 status 字段。FETCH(3) 的结果集是根据 ID 字段排序的,并且使用 T1 的索引 I1 的合并联接而得到最终的结果集。

根据 T1.PENDING 字段的值,上面的存取计划可能是最优的,也可能不是。如果用户输入值 1,那么上面的存取计划就是最优的。然而,如果断言中使用的值为 0,那么内嵌循环联接可能是最优选择,而不是合并联接。

上面的 SQL 语句很适合使用 REOPT ALWAYS。通过使用 REOPT ALWAYS,这个查询将会重新编译,而这个存取计划将基于运行时值重新生成。这会增加一些编译负载,但却能够产生一个最优的存取计划。我们将在下一节介绍如何设置 REOPT 级别。

绑定不同 REOPT 级别的包

一个特定 SQL 语句的存取计划是在绑定时生成的。要重新优化一个 SQL 语句,我们需要在这个包上绑定 REOPT 绑定选项。默认情况下,生成的包都使用 REOPT NONE 选项,并且它们的 COLLECTION 名称均为 “NULLID”。如果您发现应用程序应当使用一个不同的 REOPT 选项,那么要使用不同的 REOPT 选项生成包,并配置应用程序,使之指向希望使用的包。

DB2 CLI 包是 DB2 CLI、ODBC、JDBC、OLE DB、.NET 和 ADO 应用程序共享的。您对这些包所做的任何修改都会影响到这些所有类型的应用程序。

清单 12. 在包中绑定默认选项
$ 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.

上面的命令创建了集合为 NULLID 且绑定了 REOPT NONE 的包。这一般是以一个安装后任务实现的。

清单 13. 在包中绑定 REOPT ONCE 和 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.

这些命令将生成两组分别绑定了 REOPT ONCE 和 REOPT ALWAYS 的包,它们的集合分别为 NULLIDR1 和 NULLIDRA。注意,NULLID、NULLIDR1 和 NULLIDRA 都是保留关键词,它们不能够用作其他用途。

在只安装了一个 JDBC 和 SQLJ 的 IBM Data Server 客户端,并且没有任何的 DB2 命令行工具时,DB2Binder 工具将可用于绑定独立的 JDBC 驱动程序包。

清单 14. 绑定独立的 JDBC 驱动程序
$ 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.

为动态 SQL 语句设置重新优化(REOPT)级别

现在系统中有三种不同的包,所以通过指向应用程序所需要的集合,它们可以使用不同的 REOPT 设置,而且不会影响到其他应用程序。我们将在 清单 10 介绍如何为应用程序语句指定 REOPT NONE(默认)和 REOPT ALWAYS,并比较结果。

传统驱动程序、OLE DB、.NET 和 ADO 应用程序中使用的 CLI、ODBC、JDBC

DB2 CLI、ODBC、type2 JDBC、OLE DB、.NET 和 ADO 应用程序均使用 CLI 包,它们都会通过 CLI 驱动程序。

为通过 CLI 驱动程序的应用程序选择不同 REOPT 级别的方法有两种。

  • 在 db2cli.ini 文件中指定 REOPT 关键词或 CurrentPackageSet 关键词。
    • REOPT = 2 | 3 | 4
      • 2---REOPT NONE(默认值)
      • 3---REOPT ONCE
      • 4---REOPT ALWAYS
    • CurrentPackageSet="NULLID" | "NULLIDR1" | "NULLIDRA"

    REOPT = 4 和 CurrentPackageSet = "NULLIDRA" 的设置效果是一样的。如果同时指定 REOPT 和 CurrentPackageSet 关键词,那么 CurrentPackageSet 优先级更高。

  • 在应用程序中指定连接属性 SQL_ATTR_CURRENT_PACKAGE_SET 或 SQL_ATTR_REOPT。
清单 15. CLI 示例应用程序 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;
}
清单 16. 上面应用程序的结果(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..

修改 test_reopt.c 的第 51 行,使它变成:

                   (SQLPOINTER) "NULLID",
清单 17. 上面应用程序的结果(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.
表 1. 何时能够指定 REOPT 和 CurrentPackageSet
连接之前连接之后语句分配之后
SQL_ATTR_REOPT不可以可以可以
SQL_ATTR_CURRENT_PACKAGE_SET可以可以不可以

注意:

  • 如果建立连接之后同时设置了 SQL_ATTR_REOPT 和 SQL_ATTR_CURRENT_PACKAGE_SET,并且如果它们互相排斥,那么只有第一个是有效的。
  • 建立连接之后设置的 SQL_ATTR_CURRENT_PACKAGE_SET 只会影响到后续分配的语句。这使得我们能够精确控制不同语句的 REOPT 级别。
  • 不推荐在分配语句句柄之后设置 SQL_ATTR_REOPT 属性。

使用 JDBC Universal Driver 的 Java 应用程序

将 DB2BaseDatasource 属性设置为 jdbcCollection 或 currentPackageSet。jdbcCollection 的默认值为 NULLID。如果设置了 currentPackageSet,那么它的值会覆盖 jdbcCollection 的值。

清单 18. 示例文件 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
}
清单 19. 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

将 Test_Reopt.java 的第 26 行修改为:

         ((com.ibm.db2.jcc.DB2BaseDataSource)dbds).setCurrentPackageSet("NULLID") ;
清单 20. 上面应用程序的结果(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

使用 JDBC Universal Driver 的 WebSphere® 应用程序

如果 WebSphere 应用程序中使用的是 JDBC Universal Driver,那么这些属性可以在 WebSphere Application Server 的管理控制台设置。选择 Resources > JDBC > JDBC providers > JDBC_provider name > Data sources > data_source name > WebSphere Application Server data source properties,就可以指定这些属性。

指定静态 SQL 语句的 REOPT 选项

静态 SQL 语句是在执行之前预编译并绑定到一个包上的。为了在一个包上使用不同的 REOPT 级别,这个包需要重新绑定到一个新的级别,并重新生成。绑定包的方法根据应用程序的类型不同而有所差别。

嵌入式 SQL

程序 test_reopt.sqc 是使用嵌入式 SQL 编写,并使用默认 REOPT 预编译和绑定的。为了将包绑定到 REOPT ALWAYS,我们需要使用预编译生成的 test_reopt.bnd 文件执行 REOPT ALWAYS 绑定。

清单 21. 为 test_reopt.sqc 绑定 REOPT ALWAYS
$ 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 存储过程

SQL 存储过程是预编译的,并且是在创建时绑定到一个包上的。要重新绑定存储过程的包,我们需要先删除它,然后再重新创建绑定新 REOPT 级别的存储过程,或者为这个特定的 SQL 存储过程重新绑定一个包。

清单 22. 删除和重新创建绑定 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 应用程序

SQLJ 应用程序的包是在 db2sqljcustomize 执行时生成的。当 db2sqljcustomize 运行时,它会创建一个序列化的配置文件。如果它的 automaticbind 值是 YES,它也会创建一个 DB2package。

清单 23. 重新绑定 SQLJ 包
$ 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

结束语

正如我们在本文讨论的,如果 SQL 语句中存在主机变量或参数标记,那么 DB2 优化器可能会选择一个次优化的存取计划。但是通过使用 REOPT 绑定选项,优化器将会根据应用程序在运行时所提供的值来生成存取计划,而不是使用绑定过程中创建的一般计划。通过尝试本文所给出的示例,您就能够体验到如何使用 REOPT 选项来提高那些包含主机变量或参数标记的查询的运行时性能。

致谢

特别感谢 Anthony Reina 和 Samir Kapoor,他们给本文提出了宝贵的建议。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=630905
ArticleTitle=使用 REOPT 绑定选项提高 DB2 for Linux, UNIX, and Windows 的查询性能
publish-date=03072011