ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_使用别名

DB2_使用别名

原创 Linux操作系统 作者:redhouser 时间:2011-07-15 13:34:09 0 删除 编辑

目的:
测试DB2使用别名,本脚本摘录自DB2安装目录admin_scripts/public_alias.db2。
版本:Windows DB2 Express-C V9.7
说明:由于该版本不支持DBMS_OUTPUT,部分操作报:SQL8004N  找不到所请求功能的有效许可证密钥。

操作步骤:
使用"db2cmd db2 -td@"进入交互模式,执行后续操作。

-- SAMPLE DETAILS
--
--  (1) Admin user creates PUBLIC alias "app", "tbsp" for table
--      SYSIBMADM.APPLICATIONS and SYSIBMADM.TBSP_UTILIZATION respectively.
--
--  (2) Admin creates a module database_monitoring in dba_object schema so
--      that DBAs can use it.
--
--  (3) Admin user creates public alias dbms_monit for module database_monitoring.
--
--  (4) Admin user alters the module by adding procedures.
--
--  (5) User bob and pat use the module database_monitoring for monitoring the database,
--      but they use dbms_monit alias for monitoring.
--
--  (6) User pat creates one more module of same name dbms_monit in the schema "pat".
--
--  (7) User pat calls the procedure in different ways.
--
--  (8) User bob calls the procedure in different ways.
--
-- ***************************************************/
-- SET UP                                            */
-- ***************************************************/

-- Connect to Sample
CONNECT TO sample@

echo@
echo ********************************@
echo USE OF PUBLIC ALIASES FOR TABLE @
echo ********************************@
echo@

-- Create schema to store objects used by DBA
CREATE SCHEMA dba_object@
SET CURRENT SCHEMA = dba_object@
SET CURRENT PATH = CURRENT PATH, dba_object@

-- Create public alias for table SYSIBMADM.APPLICATIONS
CREATE PUBLIC ALIAS app FOR TABLE SYSIBMADM.APPLICATIONS@

-- Create public alias for table SYSIBMADM.TBSP_UTILIZATION
CREATE PUBLIC ALIAS tbsp FOR TABLE SYSIBMADM.TBSP_UTILIZATION@


-- Create module database_monitoring
CREATE MODULE database_monitoring@

-- Grant execute privilege to user bob
GRANT EXECUTE ON MODULE database_monitoring TO USER bob@

-- Reset connection
CONNECT RESET@


-- Connect to sample
CONNECT TO sample@

-- Alter module database_monitoring to publish procedure
-- tbsp_detail. Users will use full module name to alter 
-- the module.
ALTER MODULE dba_object.database_monitoring PUBLISH
PROCEDURE tbsp_detail()@

-- Alter module database_monitoring to publish procedure
-- app_detail. Users will use full module name to alter 
-- the module.
ALTER MODULE dba_object.database_monitoring PUBLISH
PROCEDURE app_detail()@


-- Alter module database_monitoring to add procedure
-- tbsp_detail. Users will use full module name to alter 
-- the module.
ALTER MODULE dba_object.database_monitoring ADD
PROCEDURE tbsp_detail()
   BEGIN
   -- Declare variables
   DECLARE v_tbsp_id INTEGER;
   DECLARE v_tbsp_name CHAR(15);
   DECLARE v_tbsp_type CHAR(5);
   DECLARE v_tbsp_content_type CHAR(10);
   DECLARE v_tbsp_util_prcntg DECIMAL(5,2);
   DECLARE v_dbpgname CHAR(20);
   DECLARE c_tbsp_statistics CURSOR;

   SET c_tbsp_statistics = CURSOR FOR SELECT TBSP_ID, TBSP_NAME,
   TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_UTILIZATION_PERCENT,
   DBPGNAME
   FROM tbsp;

   -- Open cursor c_tbsp_statistics
   OPEN c_tbsp_statistics;

      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT_LINE('---------------------');
      CALL DBMS_OUTPUT.PUT_LINE('TABLESPACE STATISTICS');
      CALL DBMS_OUTPUT.PUT_LINE('---------------------');
      CALL DBMS_OUTPUT.NEW_LINE;

      CALL DBMS_OUTPUT.PUT ('TBSPACE ID'||'   '|| 'TBSPACE NAME'||'      '||
      'TYPE'||'    '||'CONTENT TYPE'||'  '|| '% USED'||'    '||'DBPAGENAME');

      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT_LINE('----------'||'   '||'------------'||'      '||
      '----'||'    '||'------------'||'  '||'------'||'    '||'----------');

   fetch_loop:
   LOOP

  -- Fetch values from cursor
     FETCH FROM c_tbsp_statistics INTO
     v_tbsp_id, v_tbsp_name, v_tbsp_type, v_tbsp_content_type,
     v_tbsp_util_prcntg, v_dbpgname;


      IF c_tbsp_statistics IS NOT FOUND
       THEN LEAVE fetch_loop;
      END IF;

     CALL DBMS_OUTPUT.NEW_LINE;
     CALL DBMS_OUTPUT.PUT(v_tbsp_id);
     CALL DBMS_OUTPUT.PUT('       ');
     CALL DBMS_OUTPUT.PUT('     ');
     CALL DBMS_OUTPUT.PUT(v_tbsp_name);
     CALL DBMS_OUTPUT.PUT('   ');
     CALL DBMS_OUTPUT.PUT(v_tbsp_type);
     CALL DBMS_OUTPUT.PUT('   ');
     CALL DBMS_OUTPUT.PUT(v_tbsp_content_type);
     CALL DBMS_OUTPUT.PUT('    ');
     CALL DBMS_OUTPUT.PUT(v_tbsp_util_prcntg);
     CALL DBMS_OUTPUT.PUT('     ');
     CALL DBMS_OUTPUT.PUT(v_dbpgname);
     CALL DBMS_OUTPUT.NEW_LINE;
   END LOOP fetch_loop;

   -- Close cursor c_tbsp_statistics
   CLOSE c_tbsp_statistics;
END@

echo @
echo ********************************@
echo USE OF PUBLIC ALIAS FOR MODULE @
echo ********************************@
echo @


-- Now admin user creates public alias for object dba_object.database_monitoring
-- which can be accessed by all the users who have privilege to use this
-- object, without using the full object name.
CREATE PUBLIC ALIAS dbms_monit FOR MODULE dba_object.database_monitoring@

-- Reset connection
CONNECT RESET@


echo @
echo ********************************@
echo USE OF PRIVATE ALIAS FOR MODULE @
echo ********************************@
echo @

-- User bob calls the procedure in different ways
CONNECT TO SAMPLE USER bob USING bob1234@
SET SERVEROUTPUT ON@

-- Call module by using full object name
CALL dba_object.database_monitoring.tbsp_detail()@

-- Create private alias of object dba_object.database_monitoring
-- to avoid use of full object name
CREATE ALIAS db_monitoring FOR MODULE dba_object.database_monitoring@

-- Call module by using private alias
CALL db_monitoring.tbsp_detail()@

-- Call module by using public alias
CALL dbms_monit.tbsp_detail()@

-- Connect to sample
CONNECT TO sample@

-- Alter module database_monitoring to add procedure
-- app_detail. Users will use full module name to alter 
-- the module.

ALTER MODULE dba_object.database_monitoring ADD
PROCEDURE app_detail()
   BEGIN
   -- Declare variables
   DECLARE v_agent_id INTEGER ;
   DECLARE v_app_name CHAR(20);
   DECLARE v_auth_id CHAR(15);
   DECLARE v_app_id CHAR(26);
   DECLARE v_app_status CHAR(15);
   DECLARE c_app_detail CURSOR;

   SET c_app_detail = CURSOR FOR SELECT AGENT_ID, APPL_NAME,
   AUTHID, APPL_ID, APPL_STATUS
   FROM app
   ORDER BY AGENT_ID ASC;
 
   -- Open cursor c_app_detail
   OPEN c_app_detail;

      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT_LINE('------------------');
      CALL DBMS_OUTPUT.PUT_LINE('APPLICATION STATUS');
      CALL DBMS_OUTPUT.PUT_LINE('------------------');
      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT
       ('AGENT ID '||' '|| 'APPLICATION NAME '||'        '||
        'AUTHORIZATION ID '||'      '|| 'APPLICATION ID '||'                '||
        'STATUS');
 CALL DBMS_OUTPUT.NEW_LINE;
 CALL DBMS_OUTPUT.PUT_LINE('--------'||'  '||
 '----------------'||'  '||'      -----------------'||'      '||
 '-------------------------'||'      '|| '-----------');

     fetch_loop:
     LOOP

   -- Fetch values from cursor
      FETCH FROM c_app_detail INTO
      v_agent_id, v_app_name, v_auth_id, v_app_id,
      v_app_status;

      IF c_app_detail IS NOT FOUND
       THEN LEAVE fetch_loop;
      END IF;

 CALL DBMS_OUTPUT.NEW_LINE;
 CALL DBMS_OUTPUT.PUT(v_agent_id);
 CALL DBMS_OUTPUT.PUT('       ');
 CALL DBMS_OUTPUT.PUT(v_app_name);
 CALL DBMS_OUTPUT.PUT('     ');
 CALL DBMS_OUTPUT.PUT(v_auth_id);
 CALL DBMS_OUTPUT.PUT('       ');
 CALL DBMS_OUTPUT.PUT(v_app_id);
 CALL DBMS_OUTPUT.PUT('     ');
 CALL DBMS_OUTPUT.PUT(v_app_status);
 CALL DBMS_OUTPUT.NEW_LINE;

     END LOOP fetch_loop;

   -- Close cursor c_app_detail
   CLOSE c_app_detail;
END@

-- Reset connection
CONNECT RESET@

-- User bob calls the procedure in different ways
CONNECT TO SAMPLE USER bob using bob1234@
SET SERVEROUTPUT ON@

-- Call procedure by using full object name
CALL dba_object.database_monitoring.app_detail()@

-- Call procedure by using private alias
CALL db_monitoring.app_detail()@

-- Call procedure by using public alias
CALL dbms_monit.app_detail()@

-- Reset connection
CONNECT RESET@

echo@
echo ******************@
echo OBJECT RESOLUTION @
echo ******************@
echo@

-- Connect to sample
CONNECT TO sample USER pat USING pat1234@

-- User pat creates one more module of same name in "pat" schema
CREATE MODULE dbms_monit@

-- Alter module dbms_monit to publish same procedure app_detail
ALTER MODULE dbms_monit PUBLISH
PROCEDURE app_detail()@

ALTER MODULE dbms_monit ADD
PROCEDURE app_detail()
   BEGIN
   -- Declare variables
   DECLARE v_agent_id INTEGER ;
   DECLARE v_app_name CHAR(20);
   DECLARE v_app_status CHAR(15);
   DECLARE c_app_detail CURSOR;

   SET c_app_detail = CURSOR FOR SELECT AGENT_ID, APPL_NAME,
   APPL_STATUS
   FROM app
   ORDER BY AGENT_ID ASC;
 
   -- Open cursor c_app_detail
   OPEN c_app_detail;

      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT_LINE('------------------');
      CALL DBMS_OUTPUT.PUT_LINE('APPLICATION STATUS');
      CALL DBMS_OUTPUT.PUT_LINE('------------------');
      CALL DBMS_OUTPUT.NEW_LINE;
      CALL DBMS_OUTPUT.PUT
       ('AGENT ID '||'  '|| 'APPLICATION NAME '||'       '|| 'STATUS');
 CALL DBMS_OUTPUT.NEW_LINE;
 CALL DBMS_OUTPUT.PUT_LINE('--------'||'  '||
 '----------------'||'       '|| '-----------');

     fetch_loop:
     LOOP

   -- Fetch values from cursor
      FETCH FROM c_app_detail INTO
      v_agent_id, v_app_name, v_app_status;

      IF c_app_detail IS NOT FOUND
       THEN LEAVE fetch_loop;
      END IF;

 CALL DBMS_OUTPUT.NEW_LINE;
 CALL DBMS_OUTPUT.PUT(v_agent_id);
 CALL DBMS_OUTPUT.PUT('       ');
 CALL DBMS_OUTPUT.PUT(v_app_name);
 CALL DBMS_OUTPUT.PUT('     ');
 CALL DBMS_OUTPUT.PUT(v_app_status);
 CALL DBMS_OUTPUT.NEW_LINE;

     END LOOP fetch_loop;

   -- Close cursor c_app_detail
   CLOSE c_app_detail;
END@


-- User pat calls the procedure in different ways
SET SERVEROUTPUT ON@

-- Call procedure of schema pat
CALL dbms_monit.app_detail()@

echo "Above output is expected"@
echo@

-- Reset connection
CONNECT RESET@

-- User bob drops procedure from module
CONNECT TO sample user bob using bob1234@
SET SERVEROUTPUT ON@

-- Alter module by dropping procedure  
ALTER MODULE pat.dbms_monit
DROP PROCEDURE app_detail@

echo "Above output is expected"@
echo@

-- Call procedure
CALL dbms_monit.app_detail()@

 -- User pat drops procedure from module
CONNECT TO sample USER pat USING pat1234@
SET SERVEROUTPUT ON@

-- Alter module by dropping procedure
ALTER MODULE dbms_monit
DROP PROCEDURE app_detail@

-- Call procedure
CALL dbms_monit.app_detail()@

echo "Above output is expected"@
echo@

-- Drop module dbms_monit
DROP MODULE dbms_monit@

-- Call procedure after dropping module
CALL dbms_monit.app_detail()@

echo "Above output is expected"@
echo@

-- Reset connection
CONNECT RESET@

-- Drop modules and aliases
CONNECT TO sample@

DROP PUBLIC ALIAS app FOR TABLE@
DROP PUBLIC ALIAS tbsp FOR TABLE @
DROP ALIAS bob.db_monitoring FOR MODULE@
DROP MODULE dba_object.database_monitoring@
DROP PUBLIC ALIAS dbms_monit FOR MODULE@
DROP SCHEMA dba_object RESTRICT@

CONNECT RESET@

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

上一篇: DB2_在线装载数据
下一篇: DB2_安全
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809847