DB2_全域性變數

目的：
測試DB2使用全域性變數(是獨立的資料庫物件，支援read/write許可權控制，資料會話間獨立，類似於臨時表)，
本指令碼摘錄自DB2安裝目錄admin_scripts/globvarsupport.db2。
版本：Windows DB2 Express-C V9.7

操作步驟：
使用"db2cmd db2 -t"進入互動模式,執行後續操作。

1.基本操作
CONNECT TO SAMPLE;

-- ****************************************************************************
-- The code below shows how users can perform. different operations on
-- global variables.
-- ****************************************************************************
-- Create a session global variable.
CREATE VARIABLE myjob_current varchar (10) DEFAULT ('soft-engg');

-- Obtain information of the global variable created.
SELECT substr (varschema, 1, 10) as varschema,
       substr (varname, 1, 10) AS varname,
       varid, substr(owner,1,10) AS owner,
       ownertype, create_time,
       substr(typeschema,1,10) AS typeschema,
       substr(typename,1,10) AS typename, length
  FROM syscat.variables
  WHERE varname = 'MYJOB_CURRENT';

-- Give read and write permissions to users 'bob' and 'pat'.
GRANT READ, WRITE ON VARIABLE myjob_current TO USER bob, USER pat;

-- Check the privileges for users 'praveen' and 'sanjay'.
SELECT substr (varschema, 1, 10) AS schema,
       substr (varname, 1, 10) AS name,
       substr(grantor,1,10) AS grantor, grantortype AS Rtype,
       substr(grantee,1,10) AS grantee, granteetype AS Etype,
       readauth, writeauth
  FROM syscat.variableauth
  WHERE varname ='MYJOB_CURRENT';

-- Revoke write permission from user 'sanjay'
REVOKE WRITE ON VARIABLE myjob_current FROM USER pat;

-- Check the privilege for user 'sanjay' to verify write
-- permission was revoked.
SELECT substr (varschema, 1, 10) AS schema,
       substr (varname, 1, 10) AS name,
       substr(grantor,1,10) AS grantor, grantortype AS Rtype,
       substr(grantee,1,10) AS grantee, granteetype AS Etype,
       readauth, writeauth
  FROM syscat.variableauth
  WHERE varname ='MYJOB_CURRENT' AND grantee = 'PAT';

-- Assign value 'MGR' to global variable 'myjob_current'.
SET myjob_current = 'MGR';

-- Query the value of global variable 'myjob_current'.
VALUES myjob_current;
--&gtMGR

--在第2個會話中：
connect to sample;

-- Query the value of global variable 'myjob_current'.
VALUES myjob_current;
--&gtsoft-engg

-- Add a comment to the  global variable 'myjob_current'.
COMMENT ON VARIABLE myjob_current IS 'Manager';

-- Check comment added to the global variable 'myjob_current'.
SELECT substr (varschema, 1, 10) AS varschema,
       substr (varname, 1, 10) AS varname,
       substr (remarks, 1, 50) AS comment
  FROM syscat.variables
  WHERE varname = 'MYJOB_CURRENT';

-- Count the number of global variables created in the catalog
SELECT count (*) FROM syscat.variables;
--&gt77

-- Drop the global variable.
DROP VARIABLE myjob_current;

-- Count the number of global variables created in the catalog
SELECT count (*) FROM syscat.variables;
--&gt76

-- ****************************************************************************
-- The code below shows users how ownership of a global variable
-- can be transferred to another user.
-- ****************************************************************************

-- Create a session global variable.
CREATE VARIABLE myvar_transfer int;

-- Obtain information of the global variable created.
SELECT substr (varschema, 1, 10) AS varschema,
       substr (varname, 1, 10) AS varname,
       substr (owner, 1, 10) AS owner, ownertype, create_time
  FROM syscat.variables
  WHERE varname = 'MYVAR_TRANSFER';

SELECT substr (varschema, 1, 10) AS varschema,
       substr (varname, 1, 10) AS varname,
       substr (grantor, 1, 10) AS grantor, grantortype,
       substr (grantee, 1, 10) AS grantee, granteetype,
       readauth, writeauth
  FROM syscat.variableauth
  WHERE varname = 'MYVAR_TRANSFER';

-- Transfer ownership of the global variable to another user.
TRANSFER OWNERSHIP OF VARIABLE myvar_transfer
  TO USER mohan PRESERVE PRIVILEGES;

-- Obtain information of the global variable after TRANSFER.
SELECT substr (varschema, 1, 10) AS varschema,
       substr (varname, 1, 10) AS varname,
       substr (owner, 1, 10) AS owner, ownertype, create_time
  FROM syscat.variables
  WHERE varname = 'MYVAR_TRANSFER';

SELECT substr (varschema, 1, 10) AS varschema,
       substr (varname, 1, 10) AS varname,
       substr (grantor, 1, 10) AS grantor, grantortype,
       substr (grantee, 1, 10) AS grantee, granteetype,
       readauth, writeauth
  FROM syscat.variableauth
  WHERE varname = 'MYVAR_TRANSFER';

-- Drop the  global variable.
DROP VARIABLE myvar_transfer;

2,在觸發器中使用全域性變數
-- Create a global variable whose default value is set to 'N'. We will use
-- this global variable to enable or disable the firing of the trigger. Its
-- default will be 'N' since we want the trigger to be active by default.
CREATE VARIABLE disable_trigger char (1) DEFAULT ('N');

-- Grant write privilege only to the DBA User ID. We only want the DBA user to
-- be able to change the value of the global variable. This is because we want
-- to prevent regular users from being able to disable the trigger.
GRANT WRITE ON VARIABLE disable_trigger TO dba_user;

-- Create a trigger that depends on the global variable. The trigger will only fire
-- if the 'disable_trigger' global variable is set to 'N'.
CREATE TRIGGER validate_t BEFORE INSERT ON EMPLOYEE
  REFERENCING NEW AS n FOR EACH ROW
  WHEN (disable_trigger = 'N' AND n.empno > '10000')
  SIGNAL SQLSTATE '38000'
  SET message_text = 'EMPLOYEE NUMBER TOO BIG and INVALID';

-- To diable the trigger the DBA will set the global variable to 'Y'.
SET disable_trigger = 'Y';

--insert into t values();--ok

-- The DBA can perform. table maintenance operations like for example importing older
-- records since the trigger will not fire. After completing the table operations,
-- the DBA can set the global variable again to 'N'.
SET disable_trigger = 'N';

--insert into t values();--ok
--&gtSQL0723N  觸發器 "MH.VALIDATE_T" 中觸發 SQL
語句出錯。對該錯誤返回的資訊包括 SQLCODE "-435"、SQLSTATE "428B3" 和訊息標記
"38000"。  SQLSTATE=09000

-- Drop the trigger.
DROP TRIGGER validate_t;

-- Drop the variable.
DROP VARIABLE disable_trigger;

3,在儲存過程中使用全域性變數
-- Create the table 'security.users'.
CREATE TABLE security.users (userid varchar (10) NOT NULL PRIMARY KEY,
                             firstname varchar(10), lastname varchar(10),
                             authlevel int);

-- Populate table with the following data.
INSERT INTO security.users VALUES ('praveen', 'sanjay', 'mohan', 1);
INSERT INTO security.users VALUES ('PRAVEEN', 'SANJAY', 'MOHAN', 1);
INSERT INTO security.users VALUES ('padma', 'gaurav', 'PADMA', 3);

-- Create a global variable.
CREATE VARIABLE security.gv_user VARCHAR (30) DEFAULT (SESSION_USER);

-- Create procedure 'get_authorization' that is dependent on the
-- global variable 'security.gv_user'.
CREATE PROCEDURE get_authorization (OUT authorization INT)
RESULT SETS 1
LANGUAGE SQL
  SELECT authlevel INTO authorization
    FROM security.users
    WHERE userid = security.gv_user;

-- Assign 'praveen' to variable 'security.gv_user'.
SET security.gv_user = 'praveen';

-- Call stored procedure 'get_authorization'.
-- The authorization level returned will be 1
call get_authorization(?);

-- Assign 'padma' to variable 'security.gv_user'.
SET security.gv_user = 'padma';

-- Call stored procedure 'get_authorization'.
-- The authorization level returned will be 3
call get_authorization(?);

-- Drop a procedure.
DROP PROCEDURE get_authorization;

-- Drop a variable.
DROP VARIABLE security.gv_user;

-- Drop a table.
DROP TABLE security.users;

4,在檢視中使用全域性變數
-- ****************************************************************************
-- The code below shows how global variables along with views can be used to
-- improve security, reduce complexity and improve performance. 
-- ****************************************************************************
-- Create the global variable using a SELECT statement in the defination.
CREATE VARIABLE schema1.gv_workdept CHAR
         DEFAULT ((SELECT workdept FROM employee
  WHERE firstnme = SESSION_USER));

-- Create the view which depends on the global variable
CREATE VIEW schema1.emp_filtered AS
  SELECT * FROM employee
  WHERE workdept = schema1.gv_workdept;

-- Adjust permissions so that other users can only select from the view.
-- Any user using this view will only be able to see his department rows.
GRANT SELECT on schema1.emp_filtered TO PUBLIC;

-- Drop a view.
DROP VIEW schema1.emp_filtered;

-- Drop a variable.
DROP VARIABLE schema1.gv_workdept;

-- Disconnect from the database.
CONNECT RESET;

TERMINATE;