--/**************************************************************************** -- (c) Copyright IBM Corp. 2008 All rights reserved. -- -- The following sample of source code ("Sample") is owned by International -- Business Machines Corporation or one of its subsidiaries ("IBM") and is -- copyrighted and licensed, not sold. You may use, copy, modify, and -- distribute the Sample in any form without payment to IBM, for the purpose of -- assisting you in the development of your applications. -- -- The Sample code is provided to you on an "AS IS" basis, without warranty of -- any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR -- IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF -- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do -- not allow for the exclusion or limitation of implied warranties, so the above -- limitations or exclusions may not apply to you. IBM shall not be liable for -- any damages you suffer as a result of using, copying, modifying or -- distributing the Sample, even if IBM has been advised of the possibility of -- such damages. -- ****************************************************************************** -- -- SAMPLE FILE NAME: public_alias.db2 -- -- PURPOSE : The purpose of this sample is to demonstrate the use of -- public aliases for database objects such as tables and modules. -- This sample will demonstrate the following features: -- -- 1) Use of public aliases for tables -- 2) Use of private aliases for modules -- 3) Use of public aliases for modules -- 4) Object resolution -- -- USAGE SCENARIO : An enterprise database contains number of objects such as -- tables, views, modules and so on. Some objects are created by -- database administrators in a specific schema which can be used by other DBAs -- to perform certain operations. Some objects are created by users -- in their schema. Users use the fully qualified object name -- (<schema name>.<object name>) to use any object outside their -- schema. There are some objects which are frequently used -- by the DBA or the user. It is preferable to create public aliases -- for frequently used objects because those objects can be referenced independently -- of the current SQL path or CURRENT SCHEMA by using by its simpler, -- one-part name. -- -- -- PREREQUISITE : The following users should exist in the operating system. -- bob with password "bob12345" -- pat with password "pat12345" -- -- EXECUTION : db2 -td@ -vf public_alias.db2 -- -- INPUT : NONE -- -- OUTPUT : Successful creation of public alias. -- -- DEPENDENCIES : NONE -- -- SQL STATEMENTS : -- USED -- ALTER MODULE PUBLISH PROCEDURE -- ALTER MODULE ADD PROCEDURE -- ALTER MODULE DROP PROCEDURE -- CONNECT -- CREATE MODULE -- CREATE PUBLIC alias FOR TABLE -- CREATE PUBLIC alias FOR MODULE -- DROP MODULE -- DROP PUBLIC alias FOR TABLE -- DROP PUBLIC alias FOR MODULE -- SELECT -- ************************************************************************* -- -- SAMPLE DESCRIPTION -- -- A. Schema used in the sample -- -- 1) dba_object : Contains objects frequently used by DBAs. -- -- B. Tables used in the sample -- -- 1) SYSIBMADM.APPLICATIONS : Contains details of connected applications. -- 2) SYSIBMADM.TBSP_UTILIZATION : Contains details of tablespaces. -- -- C. Aliases used in the sample -- -- 1) app : Public alias for the table SYSIBMADM.APPLICATIONS -- 2) tbsp : Public alias for the table SYSIBMADM.TBSP_UTILIZATION -- 3) dbms_monit : Public alias for the module database_monitoring -- 4) db_monitoring : Private alias for the module database_monitoring -- -- D. The application processing is performed by the following routines: -- -- 1) database_monitoring : Contains various stored procedures. This module is -- used by DBAs to monitor the database. -- -- a) tbsp_details : Procedure to monitor table spaces. -- -- b) app_details : Procedure to monitor connected applications. -- -- -- 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 bob12345@ 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 bob12345@ 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 pat12345@ -- 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 bob12345@ 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 pat12345@ 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@