静态和动态SQL的区别

静态和动态SQL分别适用于不同的情况。在确定静态SQL或动态SQL是否最适合您的应用程序时，您应该考虑两者之间的差异。

静态SQL与主机变量的灵活性

入门概念

使用静态 SQL 时，除非对程序进行更改，否则无法更改 SQL 语句的形式。不过，您可以通过使用宿主变量来提高静态语句的灵活性。

示例：在以下示例中，UPDATE语句可以更新任何员工的工资。在绑定时，您知道必须更新工资，但直到运行时才知道哪些人的工资需要更新，以及需要更新多少。

01  IOAREA.
    02  EMPID              PIC X(06).
    02  NEW-SALARY         PIC S9(7)V9(2) COMP-3.
⋮ (Other declarations)
READ CARDIN RECORD INTO IOAREA
  AT END MOVE 'N' TO INPUT-SWITCH.
⋮ (Other COBOL statements)
EXEC SQL
  UPDATE DSN8C10.EMP
    SET SALARY = :NEW-SALARY
    WHERE EMPNO = :EMPID
END-EXEC.

UPDATE语句本身（UPDATE）不会改变，其基本结构也不会改变，但输入可以改变UPDATE语句的结果。

动态SQL的灵活性

如果程序必须使用不同类型和结构的SQL语句，该怎么办？如果类型和结构太多，无法容纳每种类型的模型，那么您的程序可能需要动态SQL。

您可以使用以下程序之一来执行动态 SQL：

Db2 Query Management Facility (QMF): 为 Db2 提供了一种替代接口，该接口几乎可以接受任何SQL语句
SPUFI: 接受输入数据集中的SQL语句，然后动态处理并执行
Db2 command line processor: 接受来自UNIX系统服务环境的SQL语句。

动态SQL的局限性

您不能动态使用某些SQL语句。

动态SQL处理

一个提供动态SQL的程序接受或生成字符串形式的SQL语句作为输入。如果计划不使用SELECT语句，或者只使用那些返回已知数量已知类型值的语句，则可以简化编程。在大多数情况下，如果您事先不知道要执行的SQL语句，程序通常会执行以下步骤：

将输入数据（包括任何参数标记）转换为SQL语句
准备要执行的SQL语句，并获取结果表的描述
为SELECT语句获取足够的主存储空间来容纳检索到的数据
执行语句或获取数据行
处理返回的信息
处理SQL返回代码。

静态和动态SQL的性能

要访问 Db2 数据，SQL语句需要一个访问路径。 SQL语句性能的两个主要因素是 Db2 在运行时确定访问路径所用的时间以及访问路径是否有效。 Db2 在以下任一时刻确定语句的访问路径：

当您绑定包含 SQL 语句的计划或包时
当SQL语句执行时

Db2 确定访问路径的时间取决于以下因素：

无论语句是静态执行还是动态执行
语句中是否包含输入的主变量
语句是否包含已声明的全局临时表。

不带输入主机变量的静态SQL语句

对于不包含输入主机变量的静态 SQL 语句，当您绑定计划或包时， Db2 会确定访问路径。这种组合能够产生最佳性能，因为当程序执行时，访问路径已经确定。

带有输入主机变量的静态SQL语句

对于包含输入主机变量的静态 SQL 语句， Db2 确定访问路径的时间取决于您指定的 REOPT 绑定选项：REOPT(NONE) 或 REOPT(ALWAYS)。 REOPT(NONE)为默认值。不要指定REOPT(AUTO)或REOPT(ONCE)；这些选项仅适用于动态语句。 Db2 忽略静态 SQL 语句的 REOPT(ONCE) 和 REOPT(AUTO)，因为只缓存动态 SQL 语句。 Db2

如果您指定 REOPT(NONE)， Db2 将在绑定时确定访问路径，就像没有输入变量时一样。

如果您指定了 REOPT(ALWAYS)， Db2 将在绑定时和运行时使用以下输入变量的值确定访问路径：

主变量
参数标记
专用寄存器

Db2 必须在运行时花费额外的时间来确定语句的访问路径。然而，如果 Db2 确定使用变量值可以获得显著更好的访问路径，那么整体性能可能会得到提升。使用REOPT(ALWAYS)， Db2 使用已知的文字值优化语句。当列包含倾斜数据时，了解字面值可以帮助 Db2 选择更有效的访问路径。 Db2 如果分区表空间的限制键上有带宿主变量的搜索条件，则还可以识别哪些分区符合条件。

使用REOPT（ALWAYS）时， Db2 不会从头开始优化。例如， Db2 不会根据字面值进行查询转换。因此，使用REOPT(ALWAYS)优化过的宿主变量的静态SQL语句和使用显式文字值的类似SQL语句可能会导致不同的访问路径。

动态 SQL 语句

对于动态 SQL 语句， Db2 在运行时确定访问路径，此时语句已准备就绪。动态语句的重复成本可能会导致性能低于静态SQL语句。但是，如果您经常执行相同的SQL语句，您可以使用动态语句缓存来减少必须准备这些动态语句的次数。

带有输入主机变量的动态 SQL 语句

当您绑定包含动态 SQL 语句的应用程序与输入主机变量时，请考虑使用 REOPT(ALWAYS)、REOPT(ONCE) 或 REOPT(AUTO) 绑定选项，而不是 REOPT(NONE) 选项。

当您不使用动态语句缓存时，请使用REOPT(ALWAYS)。 Db2 在语句的每次执行或打开时确定语句的访问路径。此选项可确保语句的最佳访问路径，但使用REOPT(ALWAYS)可能会增加常用动态SQL语句的成本。

因此，对于大量亚秒级查询，REOPT（始终）选项并不是一个很好的选择。对于大量快速运行的查询，prepare的重复成本可能会超过语句的执行成本。在REOPT（ALWAYS）选项下处理的语句即使动态语句缓存已启用，也不会被动态语句缓存所包含，因为当指定REOPT（ALWAYS）时， Db2 无法重复使用访问路径。

当您使用动态语句缓存时，请使用REOPT(ONCE)或REOPT(AUTO)：

如果指定 REOPT(ONCE)，则 Db2 仅在语句的第一次 EXECUTE 或 OPEN 时确定语句的访问路径。它会将访问路径保存在动态语句缓存中，并在语句失效或被从缓存中移除之前一直使用该路径。这种访问路径的重复使用降低了包含输入主机变量的常用动态SQL语句的准备成本；然而，它并不考虑动态语句的参数标记值的更改。
REOPT(ONCE)选项非常适合临时查询应用，例如SPUFI、 DSNTEP2、 DSNTEP4、DSNTIAUL和 QMF Db2 ，它们可以更好地优化语句，因为它们知道特殊寄存器的字面值，例如CURRENT DATE和CURRENT TIMESTAMP，而不是使用默认的过滤因子估计值。
如果您指定了REOPT(AUTO)， Db2 将在运行时确定访问路径。对于每次带有参数标记的语句执行，如果 Db2 确定新的访问路径可能会提高性能，则它会生成一个新的访问路径。

编写PREPARE语句，实现高效优化

您应该对PREPARE语句进行编码，以减少开销。使用REOPT(AUTO)、REOPT(ALWAYS)和REOPT(ONCE)， Db2 在处理语句的OPEN或EXECUTE时同时准备SQL语句。也就是说， Db2 会像您指定了DEFER(PREPARE)一样处理语句。然而，在以下情况下， Db2 会准备两份声明：

您的程序在OPEN语句之前发出DESCRIBE语句
您使用INTO参数发出PREPARE语句

对于第一个准备， Db2 在不使用输入变量值的情况下确定访问路径。对于第二个准备， Db2 使用输入的变量值来确定访问路径。额外的准备可能会降低性能。

如果您指定 REOPT(ALWAYS)， Db2 会在每次运行时准备两次语句。

如果您指定了REOPT(ONCE)，则只有在语句从未保存在缓存中时， Db2 才会准备两次语句。如果语句已编写并保存在缓存中，则 Db2 将使用保存的语句版本来完成DESCRIBE语句。

如果您指定 REOPT(AUTO)， Db2 最初会不使用输入变量值来准备语句。如果语句已保存在缓存中，则对于后续的OPEN或EXECUTE， Db2 会根据输入的变量值确定是否需要新的访问路径。

对于使用游标的语句，您可以在程序中将DESCRIBE语句放在OPEN语句之后，从而避免重复准备。

如果使用预测性管理，并且与 REOPT(ALWAYS) 或 REOPT(ONCE) 绑定在一起的动态 SQL 语句超过了预测性管理警告阈值，则您的应用程序不会收到警告 SQLCODE。但是，它将从OPEN或EXECUTE语句中收到一个错误SQLCODE。