Stored Procedures

Stored procedures are schema elements containing procedures, which can be invoked from an application program and executed on the database server. These procedures can access the database using embedded SQL more efficiently than programs on the client since client/server communication is avoided. Furthermore, the entire procedure can be prepared in advance. The purpose of this experiment is to evaluate the performance gained by executing a procedure on the server as compared to executing the procedure on the client.

The task is the same as in described in Assignment 2. You are to implement it in three ways, all of which use savepoints to roll back when the budget exceeds the maximum.

1. Use a client side Java/JDBC program that opens an updatable result set over the Invest table, fetches the rows, and updates each row individually as specified for the subtask. After the updates, the program evaluates the percentage and either goes through another iteration or rolls back to the last savepoint and terminates. This does not use stored procedures and has the maximum communication overhead, since each row is handled by the client separately.
2. Same as in the first way except that the program does the subtask using a few SELECT and UPDATE statements, not processing each row individually. This also does not use stored procedures, but communication is reduced over the first way, because rows are not transferred to the client for updating, although successive SELECT and UPDATE statements must still be executed from the client.
3. Use a stored procedure for executing the entire task. All the transaction, which was part of the Java/JDBC code till now, is to be embedded in the stored procedure. Communication is now minimized since the task is reduced to a single invocation of the procedure.

What to do

1. Write, test, and debug your Java/JDBC programs.
2. Create the table Invest as in Assignment 2, but initialize it to contain 1000 rows, with distinct ids and names, with half of them being stocks and having an amount of 3000 each, and the other half of them being bonds and having an amount of 1000 each.
3. Prepare to run TPPT, which can be found in Start menu -> All Programs -> TPPT. First read its manual. Then get an example, so that you only need to put your stored procedure in schema.txt, and put your Java/JDBC code for the task in ClientJavaClass.java. Note, you need a different ClientJavaClass.java for each of the three ways, so you can put them in three different source directory, with one copy of ClearClass.java in each directory. Try to understand the example, as you may need to write them yourself next time.
4. Do experiments for each of the three ways and get the report. Execution must be done in the transaction lab to connect to the server. Run an experiment with a single terminal for 20 seconds. First, run your programs in TPPT on UnderMultiUser server using your assigned Sybase account name and password; this is a debugging phase to make sure your program runs correctly in TPPT. Then, run your programs in TPPT on SingleUser server; the user name and password are contained within the tool; and the reports generated are your final 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 and Java/JDBC code for each of the three ways,
2. timing results produced by the tool and the final state of the Invest table, and
3. an analysis of the results; do the numbers make sense in terms of the amount of communications you anticipate?

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.