ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_自治事务

DB2_自治事务

原创 Linux操作系统 作者:redhouser 时间:2011-07-13 10:48:22 0 删除 编辑

目的:
测试DB2自治事务,本脚本摘录自DB2安装目录admin_scripts/autonomous_transaction.db2。
版本:Windows DB2 Express-C V9.7

用户要求:
操作系统用户mh有DBADM,SECADM权限,mh1234;操作系统用户bob,密码bob1234;操作系统用户pat,密码pat1234。

说明:
1,为了在CLP交互模式下支持多行输入,可以使用"db2cmd db2 -td@"进入交互模式。
2,由于Express-C V9.7版本不支持DBMS_OUTPUT,调用时报错,没有测试report_generate()过程:
db2 => set serveroutput on;
SQL8004N  找不到所请求功能的有效许可证密钥。


操作步骤:
1,设置测试环境
2,测试
3,清理测试环境


1,设置测试环境
CONNECT TO sample user mh using mh1234@

CREATE TABLE temp_employee(
empno CHAR(6),
empname VARCHAR(10),
lastname VARCHAR(10),
workdept CHAR(3),
bonus DECIMAL(9,2),
hiredate DATE)@

CREATE TABLE temp_payroll(
empno CHAR(6),
salary DECIMAL(9,2))@

CREATE TABLE event_log(
user_name VARCHAR(10),
event VARCHAR(65),
event_time TIME,
event_date DATE)@

-- Insert data into temp_employee table from employee table
INSERT INTO temp_employee VALUES
('000010', 'CHRISTINE', 'HAAS', 'A00', 1000.00, '01/01/1995')@
INSERT INTO temp_employee VALUES
('000020', 'MICHAEL', 'THOMPSON', 'B01', 800.00, '10/10/2003')@
INSERT INTO temp_employee VALUES
('000030', 'SALLY', 'KWAN', 'C01', 800.00, '04/05/2005')@
INSERT INTO temp_employee VALUES
('000050', 'JACK', 'GEYER', 'E01', 800.00, '08/17/1979')@
INSERT INTO temp_employee VALUES
('000060', 'IRVING', 'STERN', 'D11', 500.00, '09/14/2003')@
INSERT INTO temp_employee VALUES
('000070', 'EVA', 'PULASKI', 'D21', 700.00, '09/30/2005')@
INSERT INTO temp_employee VALUES
('000090', 'EILEEN', 'HENDERSON', 'E11', 600.00, '08/15/2000')@
INSERT INTO temp_employee VALUES
('000100', 'THEODORE', 'SPENSER', 'E21', 500.00, '06/19/2000')@
INSERT INTO temp_employee VALUES
('000110', 'VINCENZO', 'LUCCHESSI', 'A00', 900.00, '05/16/1988')@


-- Insert data into temp_payroll table
INSERT INTO temp_payroll VALUES
('000010', 10000.500)@
INSERT INTO temp_payroll VALUES
('000020', 12000.430)@
INSERT INTO temp_payroll VALUES
('000030', 11600.600)@
INSERT INTO temp_payroll VALUES
('000050', 10560.450)@
INSERT INTO temp_payroll VALUES
('000060', 13000.500)@
INSERT INTO temp_payroll VALUES
('000070', 11640.600)@
INSERT INTO temp_payroll VALUES
('000090', 12560.450)@
INSERT INTO temp_payroll VALUES
('000100', 13894.556)@
commit@

-- Fetch data from temp_employee
SELECT * FROM temp_employee@

-- Fetch data from temp_payroll
SELECT * FROM temp_payroll@

CREATE PROCEDURE event_log(IN event CHAR(1))
   AUTONOMOUS
   LANGUAGE SQL
   BEGIN
     CASE event
       WHEN 'U'
       THEN INSERT INTO event_log
           VALUES(SESSION_USER,
           'CALLING salary_update PROCEDURE TO UPDATE THE SALARY',
           CURRENT TIME,
           CURRENT DATE);

       WHEN 'S'
       THEN INSERT INTO event_log
           VALUES(SESSION_USER,
           'CALLING report_generate PROCEDURE TO VIEW EMPLOYEES SALARY',
           CURRENT TIME,
           CURRENT DATE);

     END CASE;
   END@


CREATE PROCEDURE update_salary
 (IN exp INTEGER,
  IN workdpt CHAR(3),
  IN new_salary INTEGER)
   LANGUAGE SQL
   BEGIN
    CALL event_log('U');
     UPDATE temp_payroll
        SET salary = salary + new_salary
      WHERE empno =
        (SELECT empno FROM temp_employee WHERE workdept = workdpt
         AND (CURRENT DATE - hiredate) > exp);
 
      IF (USER <> 'BOB')
          THEN
          ROLLBACK;
      END IF;
   END@

-- Grant execute privilege to user BOB on procedure update_salary
GRANT EXECUTE ON PROCEDURE update_salary TO USER bob@

-- Grant execute privilege to user PAT on procedure update_salary
GRANT EXECUTE ON PROCEDURE update_salary TO USER pat@

2,测试:
2.1初始数据
CONNECT RESET@
CONNECT TO sample USER mh USING mh1234@
 
SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   13000.50

  1 条记录已选择。

2.2 BOB调用更新操作
CONNECT RESET@
CONNECT TO sample user bob using bob1234@
CALL mh.update_salary(5, 'D11', 2000)@

CONNECT RESET@
CONNECT TO sample user mh using mh1234@

SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   15000.50

  1 条记录已选择。

2.3 PAT调用更新操作
CONNECT RESET@
CONNECT TO sample user pat using pat1234@

CALL mh.update_salary(5, 'D11', 2000)@

CONNECT RESET@
CONNECT TO sample user mh using mh1234@

SELECT salary FROM temp_payroll
 WHERE empno = (SELECT empno FROM temp_employee
   WHERE workdept = 'D11'
    AND (CURRENT DATE - hiredate) > 5)@
SALARY
-----------
   15000.50

  1 条记录已选择。


select * from event_log@
USER_NAME  EVENT                                                 EVENT_TIME EVENT_DATE
---------- ----------------------------------------------------- ---------- ----------
BOB        CALLING salary_update PROCEDURE TO UPDATE THE SALARY  09:48:05   2011-07-13
PAT        CALLING salary_update PROCEDURE TO UPDATE THE SALARY  09:52:41   2011-07-13

  2 条记录已选择。


3,清理测试环境
CONNECT RESET@
CONNECT TO sample USER mh USING mh1234@

DROP TABLE temp_employee@
DROP TABLE temp_payroll@
DROP TABLE event_log@
DROP PROCEDURE update_salary@
DROP PROCEDURE event_log@

CONNECT RESET@


 

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

上一篇: DB2_审计
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810214