CSE 515: Database Transaction Processing Systems,  Fall 2008

[Announcements] [Experiments]   [Hints]    [Home]

                                        

Experiment 2 - Stored Procedures

                             

Goal:

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. This experiment uses TPPT.

 

Description:

         The task to be executed is the same as that of Experiment 1:  from a given starting state, raise all faculty salaries by the same percentage - an integral number of (compounded) 1% raises -- so as to keep the total budget less than or equal to $40000.   You are to implement this 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 faculty table and fetches and updates each row individually with a 1% raise.  After giving all faculties the raise, the program evaluates the budget and either goes through another iteration or rolls back to the last savepoint and terminates. This does not involve stored procedures and requires the maximum communication overhead, since each row is handled by the client separately. 

2.        Same as (1) except that the program gives all faculties a 1% raise with a single UPDATE statement.  This also does not involve stored procedures, but communication is now reduced over (1) since rows are not transferred to the client for updating, although successive UPDATE statements must still be executed from the client.

3.        Use a stored procedure for executing the entire task.  Create the procedure outside your java code. All the transaction logic, which was part of the JAVA code till now, is now embedded in the stored procedure. Communication is now minimized since it is reduced to a single invocation of the procedure.

Steps:

  1. Write, debug and run your programs in JDK or JBuilder.
  2. The data are different from in experiment 1. The table Faculty is created by:
  3. 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)

)

 

To initialize the Faculty table, use

                        INSERT INTO Faculty SELECT * FROM tppt.tppt.initFaculty

Note that you will need to re-initialize the salaries in the Faculty table each time you run an experiment.

 

  1.  TPPT is installed in GradLab. You can find it in start menu-> programs-> databases-> TPPT. Read the tppt manual to get an idea about how to run experiments using TPPT.
  2. Here is a downloadable example for TPPT for you so that you only need to
    1. Add/ replace the stored procedure in schema.txt with your stored procedure.
    2. Put your codes which execute the tasks into ClientJavaClass.java. You will need a different ClientJavaClass.java for each way, so try to put them in 3 different source directory, with one copy of ClearClass.java in each directory. (Try to understand the example, you may need to write them by yourself next time.)
  3. Do experiments for each way and get the report. The execution can be done in gradlab.  Run an experiment with a single terminal for 20 seconds.
    1. Run your programs in Tppt on GradMultiUser server using your assigned Sybase account name and password. This is a debugging phase to make sure your program runs correctly in Tppt.
    2. Run your programs in Tppt on SingleUser server. The User Name and password are contained within the tool. The reports generated are your final results. Please don't use SingleUser until you are ready to generate a report. Once you finished GradMultiUser debugging, you only need a few minutes more to run it again on SingleUser to get the final results. So finish your job in GradMultiUser first.

 

Hints:

1.      When you run your program in Tppt, it will manage the connection to the database, so you don't need the connection code. However, when you debug     your program in JDK or JBuilder, your code must establish the connection to the database.

2.      Before calling a stored procedure or executing an SQL statement in your program, make sure it's correct using Jisql.

3.      Because only one connection is allowed on SingleUser server at a time, if you cannot connect to the server, please wait for some time and try again.

4.      Useful materials for TPPT can be found on the Hints page.

 

 

Hand In:

1.        The Java source code and/or stored procedures for the three cases

2.        Timing results produced by the tool and the final state of the Faculty table.

3.        Your analysis of the results.  Do the numbers make sense in terms of the amount of I/O that you anticipate?

 

 


Last updated on October 15, 2008