Savepoints, Nested Transactions, and Chained Transactions

Savepoints, nesting, chaining are all mechanisms that can be used to divide a task into parts that may be committed or aborted separately. The purpose of this experiment is to investigate how these mechanisms work in Sybase.

The task is to implement a transaction that balances investment allocations between stocks and bonds. Suppose a person invests his money in a number of stocks and bonds, and his desired allocation for the two kinds is that 60% of his money is in stocks and 40% is in bonds. At the end of the year, he moves money from the kind above its desired percentage to the kind below its desired percentage, but the latter kind must not exceed its desired percentage.

The moving is done by iterating a subtask that reduces 1% of the money from each one in the former kind and adds the money to each one in the latter kind at a same percentage (this percentage, for increase over the money in each of the latter kind, needs a little calculation: a divided by b, where a is the sum of reductions from the former kind, and b is the sum of the latter kinds), and then checks to see if the latter kind exceeds its desired percentage. If so, the last iteration is undone. If not, another iteration is started.

What to do

1. Create and initialize a table Invest. This can be done using the Sybase Jisql tool. Use the following script

   CREATE TABLE Invest (
   	Id		char(3)    NOT NULL,
   	Name		char(4)    NOT NULL,
   	BondOrStock	char(1)    NOT NULL,
   	Amount	        float      NULL,
   	PRIMARY KEY (Id)
   )
   Insert into Invest values ('001', 'Adin', 'S', 1000.0)
   Insert into Invest values ('002', 'Bluk', 'S', 2000.0)
   Insert into Invest values ('003', 'Coti', 'B', 3000.0)
   Insert into Invest values ('004', 'Duma', 'B', 1000.0)
   Insert into Invest values ('005', 'Esan', 'S', 2000.0)
   

2. Write a stored procedure for the subtask described. 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).
3. Write your Java/JDBC programs Savepoint.java, Nested.java, and Chained.java, respectively, for each of the three cases for the task. Each program invokes the stored procedure in a different way. All transaction control should be done in your Java/JDBC programs.
4. Run your Java/JDBC code and get results.

Handins

Hand in your description and code electronically, using Blackboard, by midnight on the due date. Your handin should include

1. source code of the stored procedure,
2. source code of the Java program for each of the three cases,
3. final state of the Invest table in each of the three cases, and
4. an analysis of what happened in each of the three cases; for nested transactions, you will have trouble with Sybase's implementation, so explain not only what Sybase does, but also what it should do, and how you can perform the task using the current implementation.

Grading

This homework will be graded based on 100 points, allocated in proportion to the required amount of work for each part. You may do this assignment in a team of two people; the two people will receive the same points.