ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_全局变量

DB2_全局变量

原创 Linux操作系统 作者:redhouser 时间:2011-07-14 14:14:38 0 删除 编辑

目的:
测试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;
-->MGR

--在第2个会话中:
connect to sample;

-- Query the value of global variable 'myjob_current'.
VALUES myjob_current;
-->soft-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;
-->77

-- Drop the global variable.
DROP VARIABLE myjob_current;

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

-- ****************************************************************************
-- 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
-->SQL0723N  触发器 "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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702184/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DB2_获取诊断日志
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    812176