--/****************************************************************************
-- (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: autonomous_transaction.db2
--
-- PURPOSE         : The purpose of this sample is to demonstrate the use of 
--                   the AUTONOMOUS keyword in the CREATE PROCEDURE statement.
--
-- USAGE SCENARIO  : In an enterprise, each employee has some privileges on 
--                   certain tables and procedures. The employees use stored 
--                   procedures to perform operations on the table. The employees 
--                   cannot access those tables on which they do not have access 
--                   privileges. If any employee tries to access any restricted 
--                   data, an autonomous procedure will log the complete event and 
--                   store the details in a log table. At the end of day, the 
--                   administrator can check all the events.
--
-- PREREQUISITE    : The following users should exist in the operating system.
--
--                   john with password "john12345" in SYSADM group
--                   bob  with password "bob12345"
--                   pat  with password "pat12345"
--	
-- EXECUTION       : db2 -td@ -vf autonomous_transaction.db2
--                   
-- INPUTS          : NONE
--
-- OUTPUT          : Successful execution of autonomous procedure.
--
--                                     
--
-- DEPENDENCIES    : NONE
--
-- SQL STATEMENTS USED:
--                CREATE TABLE
--                CREATE PROCEDURE
--                DROP TABLE
--                DROP PROCEDURE
--                INSERT 
--                SELECT
--                UPDATE TABLE
--
-- *************************************************************************
--
-- For more information about the command line processor (CLP) scripts,
-- see the README file.
--
-- For information on using SQL statements, see the SQL Reference.
--
-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--
-- http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp/ 
--
-- *************************************************************************
--
--  SAMPLE DESCRIPTION
--  
-- 1. The data is stored in the following tables:

-- a) TEMP_EMPLOYEE   : Contains employee details.
-- b) TEMP_PAYROLL    : Contains the employees' salary details.
-- c) EVENT_LOG       : Contains the details of all the events performed
--                      by the user.

-- 2. The application is processed using the following procedures:
--
-- a) UPDATE_SALARY   : Procedure to update the salary. The user passes the 
--                      employee's experience, work department, and new salary 
--                      as the parameters.
--
-- b) REPORT_GENERATE : Procedure to generate the employee's salary report.
--
-- c) EVENT_LOG       : An autonomous procedure to store the events performed 
--                      by the user.
--
--  SAMPLE DETAILS
--
--  (1) The user JOHN is the administrator with SYSADM authority. JOHN 
--      creates two stored procedures; UPDATE_SALARY, REPORT_GENERATE, 
--      and an autonomous procedure EVENT_LOG. A table EVENT_LOG is 
--      also created by JOHN to store all the events.
--
--  (2) BOB and PAT are non-administrator users. BOB is from the Payroll 
--      department and has access to the TEMP_PAYROLL table and TEMP_EMPLOYEE table. 
--      BOB can also update the employees' salary with the help of a stored 
--      procedure UPDATE_SALARY.
--
--  (3) The user PAT is also from the Payroll department and his job is to generate the 
--      reports. This is done with the help of a stored procedure REPORT_GENERATE.
--
--  (4) BOB invokes the procedure UPDATE_SALARY to update the salary and PAT 
--      invokes the procedure REPORT_GENERATE to generate the report.
--
--  (5) When PAT tries to invoke the procedure UPDATE_SALARY to access the 
--      TEMP_PAYROLL and TEMP_EMPLOYEE tables, an autonomous procedure EVENT_LOG is 
--      automatically invoked as PAT does not have sufficient privileges to 
--      perform this operation. Hence, the transaction is rolled back and the event 
--      is logged in the EVENT_TABLE.
--
--  (6) Later, the administrator (JOHN) will invoke the table EVENT_LOG to track 
--      all the events in the EVENT_LOG table.
-- 
-- *************************************************************************

-- *************************************************************************/
-- SET UP                                                                  */
-- *************************************************************************/

--  /******************************************************/
--  /* User JOHN creates the tables                       */
--  /******************************************************/

-- Connect to sample database
CONNECT TO sample USER john USING john12345@

echo@
echo **************************@
echo CREATE TABLES             @
echo **************************@
echo@

-- Create table temp_employee under 'JOHN' schema

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 under 'JOHN' schema

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

-- Create table event_log under 'JOHN' schema.
-- The event_log table stores the session user, the event performed by the user, 
-- the event time, and the event date.

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


echo@
echo **************************@
echo INSERT DATA INTO TABLES   @
echo **************************@
echo@

-- 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)@


echo@
echo **************************@
echo FETCH DATA FROM TABLES    @
echo **************************@
echo@

-- Fetch data from temp_employee
SELECT * FROM temp_employee@

-- Fetch data from temp_payroll
SELECT * FROM temp_payroll@


echo@
echo@
echo **************************@
echo CREATE PROCEDURE event_log@
echo **************************@
echo@
echo@

-- Create autonomous procedure "event_log" to log the event. Each procedure 
-- will call this procedure before any operation. While calling 
-- event_log procedure, each procedure will pass the event name as an argument.
-- "event_log" procedure inserts the event in "event_log" table.

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@


echo@
echo@
echo *******************************@
echo CREATE PROCEDURE update_salary @
echo *******************************@
echo@
echo@

-- Create procedure "update_salary" to perform the salary update.
-- Caller user passes the employees' total experience, 
-- work department, and new salary as arguments. The "update_salary"         
-- procedure will update the salary of employees' whose work department 
-- and total experience is equal to the passed arguments. Only the user 
-- BOB can update the salary.

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@


echo@
echo@
echo *********************************@
echo CREATE PROCEDURE report_generate @
echo *********************************@
echo@
echo@

-- Create procedure "report_generate". This procedure
-- will generate the report of employee salary details. Only the user 
-- PAT can generate the report.

CREATE PROCEDURE report_generate()
   LANGUAGE SQL
   BEGIN
     DECLARE v_empfn CHAR(10);
     DECLARE v_empln CHAR(10);
     DECLARE v_empsal DECIMAL(9,2);
     DECLARE c_report_gen CURSOR;
     SET c_report_gen= CURSOR FOR SELECT empname, lastname, salary 
     FROM temp_employee t1, temp_payroll t2 
     WHERE t1.empno = t2.empno;
      CALL event_log('S');
       IF (USER <> 'PAT')
        THEN
        ROLLBACK;
       ELSE
        OPEN c_report_gen;
        
        CALL DBMS_OUTPUT.NEW_LINE;
        CALL DBMS_OUTPUT.PUT_LINE('---------------');
        CALL DBMS_OUTPUT.PUT_LINE('EMPLOYEE REPORT');
        CALL DBMS_OUTPUT.PUT_LINE('---------------');
        CALL DBMS_OUTPUT.NEW_LINE;
        CALL DBMS_OUTPUT.PUT_LINE('');
        CALL DBMS_OUTPUT.PUT
          ('EMP NAME  '||'   '||'LASTNAME '||'    '||'SALARY');
        CALL DBMS_OUTPUT.NEW_LINE;
        CALL DBMS_OUTPUT.PUT
          ('-----------'||'  '||'-----------'||'  '||'-----------');
        CALL DBMS_OUTPUT.NEW_LINE;

       fetch_loop:
       LOOP
         FETCH FROM c_report_gen INTO v_empfn, v_empln, v_empsal;

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

        CALL DBMS_OUTPUT.PUT(v_empfn);
        CALL DBMS_OUTPUT.PUT('   ');
        CALL DBMS_OUTPUT.PUT(v_empln);
        CALL DBMS_OUTPUT.PUT('   ');
        CALL DBMS_OUTPUT.PUT(v_empsal);
        CALL DBMS_OUTPUT.NEW_LINE;
       
       END LOOP fetch_loop;
       CLOSE c_report_gen;      
       END IF;
   END@


-- /***************************************************
-- /* GRANT EXECUTE privileges to users
-- /***************************************************

echo@
echo@
echo ************************************************@
echo GRANT EXECUTE PRIVILEGES ON PROCEDURES TO USERS @
echo ************************************************@
echo@
echo@

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

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

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

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

-- RESET CONNECTION
CONNECT RESET@

-- CALL PROCEDURES TO PERFORM DIFFERENT OPERATIONS@

echo@
echo@
echo ***************************************@
echo FETCH SALARY OF EMPLOYEES BEFORE       @
echo CALLING update_salary STORED PROECDURE @
echo ***************************************@
echo@
echo@


-- Fetch salary of employees before calling update_salary procedure 
-- by user BOB.

-- Connect to database
CONNECT TO sample USER john USING john12345@
 
SELECT salary FROM temp_payroll 
 WHERE empno = (SELECT empno FROM temp_employee 
   WHERE workdept = 'D11' 
    AND (CURRENT DATE - hiredate) > 5)@


-- CALL Procedure update_salary to update the salary

echo@
echo User BOB calls procedure update_salary @
echo TO UPDATE THE SALARY@ 
echo@

-- RESET CONNECTION
CONNECT RESET@

-- Connect to database
CONNECT TO sample user bob using bob12345@

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


echo@
echo@
echo ***************************************@
echo FETCH SALARY OF EMPLOYEES AFTER        @
echo CALLING update_salary STORED PROECDURE @
echo ***************************************@
echo@
echo@

-- Fetch salary of employees after calling update_salary procedure 
-- by user BOB.

-- RESET CONNECTION
CONNECT RESET@

-- Connect to database
CONNECT TO sample user john using john12345@

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


echo@
echo@
echo ****************************************@
echo USER PAT CALLS PROCEDURE report_generate@
echo TO GENERATE THE REPORTS                 @
echo ****************************************@
echo@
echo@


-- RESET CONNECTION
CONNECT RESET@

-- Connect to sample database
CONNECT TO sample user pat using pat12345@

-- CALL procedure to generate the report
SET SERVEROUTPUT ON@ 
CALL JOHN.report_generate()@


-- The only user who has the appropriate privilege to execute the procedure 
-- "update_salary" is BOB. So when the user PAT invokes the procedure 
-- "update_salary", the procedure will check if the user is BOB. If the user 
-- is not BOB, all the transactions will be rolled back but the event will be 
-- logged as the event is passed as argument to the autonomous procedure.

echo@
echo@
echo **************************************@
echo FETCH SALARY OF EMPLOYEES BEFORE      @
echo CALLING update_salary STORED PROECDURE@
echo **************************************@
echo@
echo@

-- RESET CONNECTION
CONNECT RESET@

-- Select salary of employees before calling update_salary procedure 
-- by user PAT.

-- Connect to database
CONNECT TO sample user john using john12345@

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


echo@
echo@
echo ***************************************@
echo USER PAT CALLS PROCEDURE update_salary @ 
echo TO UPDATE THE SALARY                   @
echo ***************************************@
echo@
echo@

-- RESET CONNECTION
CONNECT RESET@

-- Connect to database
CONNECT TO sample user pat using pat12345@

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

echo@
echo@
echo ***************************************@
echo FETCH SALARY OF EMPLOYEES AFTER        @
echo CALLING update_salary STORED PROECDURE @
echo ***************************************@
echo@
echo@

-- Select salary of employees after calling update_salary procedure 
-- by user PAT.

-- RESET CONNECTION
CONNECT RESET@

-- Connect to database
CONNECT TO sample user john using john12345@

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

echo@
echo The above output is expected as the session user is not BOB.@
echo So all the transactions will be rolled back and salary will @
echo remain same but the event will be logged in event_log table.@
echo@


-- RESET CONNECTION
CONNECT RESET@

-- Connect to database
CONNECT TO sample user bob using bob12345@

echo@
echo@
echo ****************************************@
echo USER bob CALLS PROCEDURE report_generate@ 
echo TO GENERATE THE REPORT                  @
echo ****************************************@
echo@
echo@

CALL JOHN.report_generate()@
SET SERVEROUTPUT ON@

echo@
echo The above output is expected as session user is not PAT.   @
echo So report will not generate but the event will be logged in@
echo event_log table.                                           @
echo@

-- RESET CONNECTION
CONNECT RESET@

-- Track all the events.
CONNECT TO sample USER john USING john12345@

echo@
echo@
echo ********************************@
echo FETCH DATA FROM event_log TABLE @
echo ********************************@
echo@
echo@

SELECT * FROM event_log@


-- Disconnect from sample database
CONNECT RESET@


-- *************************************************
-- CLEAN UP
-- *************************************************

echo@
echo@
echo *******************************@
echo DROP ALL TABLES AND PROCEDURES @
echo *******************************@
echo@
echo@

-- Drop tables and procedures
CONNECT TO sample USER john USING john12345@

-- Drop table temp_employee
DROP TABLE temp_employee@

-- Drop table temp_payroll
DROP TABLE temp_payroll@

-- Drop table event_log
DROP TABLE event_log@

-- Drop procedure update_salary
DROP PROCEDURE update_salary@

-- Drop procedure report_generate
DROP PROCEDURE report_generate@

-- Drop procedure event_log
DROP PROCEDURE event_log@

-- Connect reset
CONNECT RESET@