了解 DB2 Version 9.5 中的全局变量(转)

转自：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0711zubiri/

简介

在关系数据库系统内部，应用程序和实际数据库之间的主要交互都是以会话或连接的 SQL 语句形式来实现的。过去，为了在相同会话中实现不同 SQL 语句之间的信息共享，发出 SQL 语句的应用程序必须将语句输出参数集（本地变量）的值复制到另一个语句的输入本地变量中。此外，数据库系统本身定义或包含的 SQL 语句，例如用来定义触发器和视图的 SQL 语句，根本不能够访问这些共享信息。

总之，种种约束限制了关系数据库系统的灵活性，并因此限制了系统用户在数据库内部实现复杂、交互式模型的能力。这类系统的用户不得不在自己的应用程序或 SQL 过程中添加支持逻辑，以便在关系数据库系统中访问和传递用户应用程序信息和内部数据库信息。用户还需对其应用程序逻辑进行增强，以确保被传递和访问的信息的安全性。

为克服这种约束并最大化实现关系数据库系统的灵活性，DB2 V9.5 引入了全局变量的概念。通过引入全局变量，用户现在可以在关系数据库内部轻松地构建复杂系统，同时可在同一会话的 SQL 语句之间共享信息，或者使用数据库系统定义或包含的 SQL 语句访问信息。实现这些任务无需任何应用程序逻辑，从而支持信息传递。本文的目的是介绍这种新特性并解释如何使用它才能发挥其潜力。

首先，了解什么是全局变量以及它涉及的基本操作。然后查看一些有趣的场景，了解全局变量的使用。最后，本文将更加深入地阐述一些与全局变量有关的主题。

回页首

什么是全局变量？

全局变量表示一个可以使用 SQL 语句访问和修改的可变值。在 DB2 中，这些变量被实现为一种新型数据库对象，其定义保存在数据库编目中。编目中保存的是全局变量的定义而非实际的值，了解这一点非常重要。这样做是因为全局变量具有一个会话（连接）范围。这意味着每个会话都可以使用编目中保存的全局变量，但是每个会话拥有自己的私有值来操作和使用。其他会话都不能访问这个值。

全局变量的另一重要方面是，当考虑到信息安全问题时，可以控制对信息的访问。需要有特定的权限才能创建或删除全局变量，以及读取或修改其内容。这条规则同样适用于全局变量的定义 —— 与全局变量相关的权限也在系统编目中进行了定义。

回页首

创建和删除全局变量

要创建一个全局变量，可以使用新的 CREATE VARIABLE SQL 语句。您需要对模式使用 SYSADM, DBADM 权限或 CREATE_IN。例如，要创建一个全局变量来表示默认的打印机，应该发出如下语句：

 CREATE VARIABLE mySchema.defaultPrinter VARCHAR(30)
      DEFAULT 'Printer001'

mySchema.defaultPrinter 表示变量的名称。请注意，全局变量的名称由两部分组成，即模式的名称和其中的变量的名称。如果没有具体指定模式，则默认为当前模式。

VARCHAR(30) 表示变量的数据类型。数据类型可以是 DB2 内置数据类型、独特的类型或引用类型。注意，不能为全局变量指定 CLOB、DBCLOB、BLOB、LONG VARCHAR、LONG VARGRAPHIC、XML、ARRAY 或结构化类型。

如果在第一次引用全局变量没有为全局变量指定其他值，那么使用 DEFAULT ‘Printer001’ 表示它的默认值。默认指定的子句可以为一个常量、一个特殊的寄存器、另一个全局变量、一个表达式或关键字 NULL。表达式可以是 SQL Reference Guide 的 “Expressions” 部分中定义的任何类型的表达式（请参见 参考资料）。如果没有指定默认值，则初始变量为 NULL。表达式的最大大小为 64K。在第一次引用时，默认表达式将为全局变量计算一个值。只要不修改 SQL 中的数据或不会在 DB2 引擎以外执行任何操作，则可以使用 DB2 允许的任何表达式。此外，表达式在分配时必须与变量数据类型兼容。

下面列出了其他一些全局变量示例：

要创建表示用户第一次连接时间的变量，使用以下语句：

CREATE VARIABLE loginTime TIMESTAMP DEFAULT CURRENT TIMESTAMP

要创建表示员工所属部门的变量，使用以下语句：

CREATE VARIABLE schema1.gvDeptno INTEGER DEFAULT
      ((select deptno from hr.employees where empUser = SESSION_USER));

如果希望创建一个全局变量表示最高级安全级别，使用下面的语句：

CREATE VARIABLE mySchema.SecurityLevel CHAR(5) DEFAULT NULL

请注意，一旦创建完毕，全局变量的 length 属性和数据类型将不能更改。此外，您不能更改全局变量定义，包括其默认值。

如果您不希望再保存全局变量的定义，则需要删除它。删除全局变量和删除其他数据库对象需要使用相同的权限（请参考 SQL Reference 中的完整权限列表）。例如，要删除我们前面创建的表示默认打印机的全局变量，应使用以下语句：

DROP VARIABLE mySchema.defaultPrinter

where:

mySchema.defaultPrinter 是变量的名称。如果没有指定模式，则遵守特定规则来确定模式名称。本文稍后的内容将讨论名称解析。

请注意，如果函数、方法、触发器或视图中引用了某个全局变量，则不能删除该变量。

回页首

使用全局变量

创建了全局变量之后，如果拥有相应的权限，数据库中的任何会话都可以使用它。READ 权限用于引用全局变量，而 WRITE 权限用于修改全局变量的值。GRANT 语句允许授权用户对这些权限进行授权，而 REVOKE 语句用于删除这些权限。全局变量的所有者被显式授予了有关该变量的所有权限。

任何表达式都可以引用全局变量，只要该表达式不需要保持确定性。以下这些情况需要确定性表达式，因此不能使用全局变量：

• 检查约束
• 生成的列的定义
• 刷新直接 Materialized Query Tables (MQTs)

如上文所述，每个会话对于给定的全局变量都具有自己的私有值。当在会话中第一次引用时，全局变量将进行实例化来获得其默认值。

要查询当前用户所在部门的全部员工，使用以下语句：

SELECT * FROM hr.employees WHERE> deptno = schema1.gvDeptno

要查询本地变量 hv_depnot 的用户部门编号，使用以下语句：

EXEC SQL VALUES schema1.gvDeptno INTO :hv_deptno

要查询用户的部门编号，使用以下语句：

VALUES schema1.gvDeptno

可以使用 SET, SELECT INTO, VALUES INTO 语句改变全局变量的值。也可以作为调用语句的 out 或 input 参数的实参进行修改。

例如，要将 mySchema.defaultPrinter 变量的值修改为 “Printer002”，使用以下语句：

SET mySchema.defaultPrinter = 'Printer002'

要修改 schema1.activeEmployees 的值，使用以下语句：

EXEC SQL SELECT count(*) INTO schema1.activeEmployees 
 FROM hr.employees WHERE active = ‘Y’

回页首

示例

现在您已经了解了如何使用全局变量执行简单的操作，本节将提供一些有趣的场景以展示如何使用它们。

使用全局变量关闭触发器以进行维护

全局变量的一项有趣应用就是控制触发器的行为。例如，如果您的表具有以下触发器：

CREATE TRIGGER validate_t
BEFORE INSERT ON T
REFERENCING NEW AS  N  FOR EACH ROW
WHEN  (N.ORDER_DATE <  CURRENT TIMESTAMP  – 7 DAYS)
SIGNAL SQLSTATE  ‘38000’
SET MESSAGE TEXT  = ‘ORDER DATE TOO OLD’;
            

如果 DBA 希望进行一些维护并向表插入一些较旧的数据，他必须阻止其他用户向表插入新的数据、删除触发器、执行维护、重新创建触发器，维护完之后才能允许用户插入数据。

通过创建可以控制触发器行为的全局变量，可以简化这个任务并提高并发行。要执行维护并向表插入较旧的数据，您将使用全局变量并完成以下步骤：

1. 创建一个全局变量：

CREATE VARIABLE schema1.disableTriggers CHAR(1) DEFAULT ‘N’

2. 向所有用户授予对变量的读权限：

GRANT READ on VARIABLE shema1.disableTriggers TO PUBLIC

3. 只向 DBA 用户 ID 授予对变量的写权限：

 GRANT WRITE ON VARIABLE shema1.disableTriggers TO dba_user

4. 创建触发器（在 WHEN 子句中添加对全局变量的检查）：

CREATE TRIGGER  validate_t
BEFORE INSERT ON  T 
REFERENCING NEW AS  N  FOR EACH ROW
WHEN  (schema1.disableTriggers = 'N'   AND  N.ORDER_DATE< CURRENT TIMESTAMP  - 7 DAYS)
SIGNAL SQLSTATE  '38000'
SET MESSAGE TEXT  = 'ORDER DATE TOO OLD':

之后，如果需要对表执行任何维护，您所需做的就是修改会话中全局变量的值：

SET disable_triggers = ‘Y’;

修改了全局变量的值之后就可以执行维护了，同时其他用户可以向表插入新的纪录。维护完成之后，将值改回来即可：

SET disable_triggers = ‘N’;

使用全局变量降低复杂性并提升性能

在使用全局变量之前，可以使用它们计算变量值，从而可提升其他 SQL 实体的性能并降低复杂性。例如，通过调用一个可以为 SESSION_USER 特殊寄存器提供值的函数来设置变量，使用它取回当前用户的部门编号。视图可以在谓词中使用该全局变量的值，从而只选择分配给用户所在部门的行。视图不需要包含子查询。因此，降低了复杂性并且视图操作更加高效和快捷。

1. 像以前一样，创建全局变量：

CREATE VARIABLE schema1.gv_deptno INTEGER 
      DEFAULT(get_deptno (SESSION_USER))

2. 或者，在变量创建定义中使用 fullselect 来代替函数调用：

CREATE VARIABLE schema1.gv_deptno INTEGER DEFAULT
 ((SELECT deptno FROM hr.employees WHERE empUser = SESSION_USER));

3. 创建视图：

CREATE VIEW schema1.emp_filtered AS SELECT * FROM employee
      WHERE deptno = schema1.gv_deptno;

4. 调整权限以使其他用户只能在视图中进行选择：

REVOKE ALL ON schema1.emp_filtered FROM PUBLIC
GRANT SELECT ON schema1.emp_filtered TO PUBLIC

因此，使用该视图的任何用户只能够查看视图中属于所在部门的行。

回页首

名称解析

全局变量名称由两部分构成。如果在没有使用模式名情况下引用全局变量，则使用 SQL 路径协助解析名称。对于静态 SQL 语句，使用 FUNCPATH 绑定选项指定 SQL 路径。对于动态 SQL 语句，SQL 路径为 CURRENT PATH 特殊寄存器的值。这与函数、过程和数据类型使用的是相同的算法。

只有确定变量名与列、SQL 变量、SQL 参数、转换变量或特殊寄存器的名称不匹配时，才能将它视为一个全局变量。虽然 CURRENT PATH 特殊寄存器可能包含多个模式，用户也可能不具备足够的权限访问一个或多个模式中的对象。函数和过程使用EXECUTE 权限执行例程解析 —— 例如，如果用户不具备 EXECUTE 权限访问最符合指定内容的例程，DB2 将移至列表中下一个最符合的例程。但不能对 VARIABLES 执行这个操作。如果用户不具备针对某个变量的 READ（或 WRITE）权限，DB2 不会移至下一个模式 —— 而仅仅是不能进行编辑。

在对函数和数据类型使用全局变量时，必要时可以使用保守的绑定语义。在很多实例中，在处理语句时将解析全局变量，并且数据库管理器必须能够重复这种解析。这条规则适用于

• 包中的静态 DML 语句
• 视图
• 触发器
• SQL 例程

对于包中的静态 DML 语句，将在进行绑定时解析全局变量。在视图、触发器和 SQL 例程中，将在创建数据库对象时进行解析。

在对全局变量进行解析时，如果同一个 SQL 路径中的不同模式新添了一个具有相同名称的全局变量，则可能会更改行为。为避免这个问题，数据库管理器将在必要时应用保守的绑定语义。这意味着，在 SQL 路径中，只有在最后一次显式绑定时间戳之前定义的全局变量才会进行名称解析。

如果 DML SQL 语句、触发器、视图或例程内引用了一个全局变量，将为语句或对象记录关于完全限定全局变量名的依赖关系。同样，如果可以的话，将对语句使用的授权 ID 进行检查，以查看是否拥有对全局变量的 READ 权限。

回页首

事务

如果全局变量是在会话内创建，则其他会话不能使用它，除非已经提交了这个工作单元。然而，在创建全局变量的会话的内部，在提交工作单元之前也可以使用新创建的变量。这种行为与其他创建对象（例如表）是一致的。

对全局变量值的设定是非事务性的。因此，应用程序无法回滚全局变量的值设置。注意以下事项：

• 如果回滚全局变量的创建，那么该变量将不再存在。
• 如果要回滚全局变量的删除，变量值将为删除前的值（如果在删除前会话引用了全局变量，则为旧值，如果之前没有发生引用，则为未初始化的值）。

XA 交互

全局变量的范围被规定为 XA 事务内的单一会话级别，并且不可用于其他加入事务的会话。

全局变量是非事务性状态对象。因此，在 XA 环境中（包括紧密耦合的事务和松散耦合的事务），全局变量对象（实体和值）不会针对 XAend 发生更改（对诸如 XAstart、XArollback 等其他 XA API 也是如此）。

注意：和事务性状态对象一样，DB2 实现了声明式的全局临时表。因此，在执行以下 XA API 时，将删除声明式全局临时表。

XArollback

XAend(SUCCESS)

XAstart(NOFLAGS)

XAstart(JOIN)

回页首

全局变量审计

您可以对全局变量的信息进行审计。将生成如下所示的审计记录：

• 在对变量执行创建或删除操作时生成 OBJMAINT 审计记录
• 当对全局变量授予或撤销权限时生成 SECMAINT 审计记录
• 当对全局变量检查权限时生成 CHECKING 审计记录

回页首

对照 Oracle 包变量

在将应用程序从 Oracle 迁移到 DB2 时，开发人员发现 DB2 的新全局变量支持非常有用，因为 Oracle 中经常会使用包变量。要迁移到 DB2，将包名映射为模式名并使用特定的模式创建变量。下面的示例演示了这一点：

示例 1. 为数值数据类型创建变量

Oracle

   CREATE PACKAGE trans_data AS
   min_balance CONSTANT REAL := 10.00;
   number_processed INT;
   END trans_data;
   /

DB2

   CREATE SCHEMA trans_data!

   CREATE VARIABLE trans_data.min_balance DOUBLE DEFAULT (10.00)!

   CREATE VARIABLE trans_data.number_processed INTEGER!

示例 2. 在存储过程中使用变量

Oracle

   CREATE OR REPLACE PACKAGE emp_actions AS
      PROCEDURE hire_employee (
         ename  VARCHAR2,
         job    VARCHAR2,
         mgr    NUMBER,
         sal    NUMBER,
         comm   NUMBER,
         deptno NUMBER);
      END emp_actions;
   Empname VARCHAR2;
   /
	
   CREATE OR REPLACE PACKAGE BODY emp_actions AS
      PROCEDURE hire_employee (
         ename  VARCHAR2,
         job    VARCHAR2,
         mgr    NUMBER,
         sal    NUMBER,
         comm   NUMBER,
         deptno NUMBER) IS
      BEGIN
         INSERT INTO emp VALUES (empno_seq.NEXTVAL, empname, job,
            mgr, SYSDATE, sal, comm, deptno);
      END hire_employee;
   END emp_actions;
   /

DB2

   CREATE SCHEMA emp_actions!

   CREATE VARIABLE emp_actions.empname VARCHAR(4000)!

   CREATE PROCEDURE emp_actions.hire_employee (ename VARCHAR(4000), 
                                            job VARCHAR(4000), 
                                            mgr FLOAT, 
                                            sal FLOAT, 
                                            comm FLOAT, 
                                            deptno FLOAT )
   LANGUAGE SQL
   BEGIN
      INSERT INTO emp VALUES (empno_seq.NEXTVAL, 
         emp_actions.empname, job,mgr, CURRENT TIMESTAMP , 
         sal,comm,deptno);
   END!

示例 3. 为更加复杂的数据类型创建变量

Oracle

   CREATE OR REPLACE PACKAGE uw_constants IS
   cStartDate  CONSTANT DATE := TO_DATE('07-JAN-2006');
   cInstructor CONSTANT VARCHAR2(30) := 'A. Morgan';
   cPi  CONSTANT NUMBER(8,7) := 3.1415926;
   END uw_constants;
   /

DB2

   CREATE SCHEMA uw_constants!

   CREATE VARIABLE uw_constants.cStartDate TIMESTAMP
      DEFAULT (TIMESTAMP('2006-01-07-00.00.00.000000'))!

   CREATE VARIABLE uw_constants.cInstructor VARCHAR(30) 
      DEFAULT ‘A.Morgan’!

回页首

结束语

在本文中，我介绍了 DB2 V9.5 中最新引入的全局变量支持。通过使用这些新的数据库对象，用户可以轻松构建复杂的系统，其中可以在各个 SQL 语句之间共享信息，同时不需要使用任何应用程序逻辑就可支持信息传递。创建全局变量之后，将其定义保存在编目中，并且任何会话都可以使用它们。并且由于每个全局变量以会话为范围，所有会话都拥有自己的私有值进行操作和使用，而其他会话无法访问这些私有值。全局变量的另一重要方面是，在考虑到信息安全性时，可以对访问进行控制。总之，全局变量易于使用，同时还提供了强大的功能和灵活性。