目的:
测试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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-702290/