CSE515 – Fall 2008
 
 Experiment 1 – Chaining, Savepoints and Nesting  
Due date:  10/15/2008 (Wen)
Goal:
    Chaining, savepoints and nested transactions are all mechanisms that can be used
to divide a task into parts that can be committed or aborted separately.  The purpose of  
this experiment is to investigate how these mechanisms work in Sybase.  
 
Description:
         The task is to implement a transaction that raises the salary of faculty members in
three different ways: using chaining, savepoints and nesting.  The purpose of the
transaction is to give all faculty members the same percentage raise, but the total salary
after the raise has been given must not $100000.  The transaction iterates a basic subtask
that raises all faculty salaries by one percent (from their current value, hence compound
interest) and then checks to see if the total salary exceeds $100000.  If so, the last
iteration is undone.  If not, another iteration of the subtask is initiated.  Create an instance
of the Faculty table for this experiment using the script  
 
  CREATE  TABLE  Faculty (
   FacId  char(9)     NOT NULL,
   Name  char(50)   NOT NULL,
   Password char(20)   NOT NULL,
   Validity char(1)     NOT NULL,
   Town  char(20),
   salary float    NULL,
   PRIMARY KEY (FacId)
  )
 
Insert into Faculty values ('200000001','200000001' , '200000001',' V' ,'city41', 500.0)
Insert into Faculty values ('200000002','200000002',  '200000002', 'V' , 'city9', 500.0)
Insert into Faculty values ('200000003','200000003' , '200000003', 'V' , 'city14', 500.0)
Insert into Faculty values ('200000004','200000004' , '200000004', 'V' , 'city9', 500.0)
Insert into Faculty values ('200000005','200000005' , '200000005', 'V' , 'city38', 500.0)  
 
Steps:
1. Create and initialize the Faculty table.  This can be done using the Sybase Jisql
tool.
2. Write a stored procedure for the body of the subtask (a one percent increment to
all faculty).   This can be done using the Sybase Jisql tool.  Note that the stored
procedure should not contain any transaction keyword (begin transaction, commit
transaction, rollback transaction).  All transaction control should be done in your
java program.
3. Write the Java/JDBC programs SavepointClass.java, NestedClass.java,
ChainingClass.java for each of the three cases.  Each program invokes the stored
procedure in a different way.
4. Run the JDBC code and get results.
 
Hand In:
1. Source code of  the stored procedure.
2. Source code of  the java programs in the three cases.
3. Final state of the Faculty table in the three cases.
4. An analysis of what happened in the three cases. You will have trouble with
Sybase’s implementation of nested transactions.  Explain what Sybase does and
what it should do.