了解 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 employeeWHERE 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 ASmin_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 ASPROCEDURE 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 ASPROCEDURE hire_employee (ename  VARCHAR2,job    VARCHAR2,mgr    NUMBER,sal    NUMBER,comm   NUMBER,deptno NUMBER) ISBEGININSERT 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 SQLBEGININSERT 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 IScStartDate  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 TIMESTAMPDEFAULT (TIMESTAMP('2006-01-07-00.00.00.000000'))!CREATE VARIABLE uw_constants.cInstructor VARCHAR(30) DEFAULT ‘A.Morgan’!

回頁首

結束語

在本文中，我介紹了 DB2 V9.5 中最新引入的全局變量支持。通過使用這些新的數據庫對象，用戶可以輕松構建復雜的系統，其中可以在各個 SQL 語句之間共享信息，同時不需要使用任何應用程序邏輯就可支持信息傳遞。創建全局變量之后，將其定義保存在編目中，并且任何會話都可以使用它們。并且由于每個全局變量以會話為范圍，所有會話都擁有自己的私有值進行操作和使用，而其他會話無法訪問這些私有值。全局變量的另一重要方面是，在考慮到信息安全性時，可以對訪問進行控制。總之，全局變量易于使用，同時還提供了強大的功能和靈活性。