OS/2 Shareware BBS: 11 Util/11-Util.zip/TRACK.ZIP/RPTS.SQL

Text File | 1989-05-17 | 12.1 KB | 310 lines

/* */
/* This script creates stored procedures that provide reports for */
/* performance test runs */
/* */

use bench_stat
go

/*********************************************************************/
/*            PROCEDURES TO SHOW WORKSTATION RUN INFORMATION         */
/*********************************************************************/

/* */
/* Show run information */
/* */

drop procedure tp_workstation_rpt
go

create procedure tp_workstation_rpt @run_no int as
declare @outline varchar(255), @loopcnt int, @loopmax int, @sdate datetime,
@edate datetime, @secs int, @tran_time int, @avg_tran float, @tot_tran float,
@iterate int, @elapsed int, @tot_under1 int, @tot_under2 int
select @loopcnt = 1

if @run_no = 0           /* If the run_no = 0, then get all runs */
  select @loopmax = max(number) from runs
else
  select @loopmax = 1

while @loopcnt <= @loopmax
BEGIN
if @loopmax > 1            /* Assign next run number of getting all runs */
  select @run_no = @loopcnt
print ""
print "**********************************************************************"
select @outline = "Performance Test Run #"
select @outline = @outline+convert(varchar(10),@run_no)+" Information "
print @outline
print "**********************************************************************"
print ""

select @sdate = start_time from runs where number = @run_no
select @edate = end_time from runs where number = @run_no
select @outline = "Started: "
select @outline = @outline + convert(varchar(30),@sdate)
select @outline = @outline + "      Ended: "
select @outline = @outline + convert(varchar(30),@edate)
print @outline

print ""
select @outline = "WORKSTATION: "
select @outline = @outline + (select computer from runs where number = @run_no)
print @outline

print ""
print "*************************** RUN STATISTICS ***************************"
print ""
select @outline = "TOTAL ITERATIONS: "
select @iterate = iterations from runs where number = @run_no
select @outline = @outline + convert(varchar(10),@iterate)
select @outline = @outline + "         TOTAL ERRORS: "
select @outline = @outline + convert(varchar(10),(select errors from runs
                                     where number = @run_no))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "TOTAL ELAPSED TIME:    "
select @secs = datediff(second,@sdate,@edate)
select @elapsed = @secs
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
print @outline

print ""
select @outline = "TOTAL EXECUTION TIME:  "
select @tran_time = tot_exec from runs where number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "MIN EXECUTION: "
select @tran_time = min_exec from runs where number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "MAX EXECUTION: "
select @tran_time = max_exec from runs where number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "AVG EXECUTION: "
select @tran_time = avg_exec from runs where number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "TOTAL EXECUTION UNDER 1 SECOND: "
select @tot_under1 = under1_exec from runs where number = @run_no
select @outline = @outline + convert(varchar(10),@tot_under1)
print @outline

print ""
select @outline = "PERCENT OF EXECUTION UNDER 1 SECOND: "
select @outline = @outline + convert(varchar(10),(@tot_under1/convert(float,@iterate)))
print @outline

print ""
select @outline = "TOTAL EXECUTION UNDER 2 SECONDS: "
select @tot_under2 = under2_exec from runs where number = @run_no
select @outline = @outline + convert(varchar(10),@tot_under2)
print @outline

print ""
select @outline = "PERCENT OF EXECUTION UNDER 2 SECONDS: "
select @outline = @outline + convert(varchar(10),(@tot_under2/convert(float,@iterate)))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "TRANSACTIONS PER SECOND: "
select @outline = @outline + convert(varchar(10),(@iterate/convert(float,@elapsed)))
print @outline

select @loopcnt = @loopcnt + 1
END
go

/*********************************************************************/
/*        PROCEDURE TO SHOW MULTI_USER RUN INFORMATION               */
/*********************************************************************/

drop procedure tp_multiuser_rpt
go

create procedure tp_multiuser_rpt @run_no int as
declare @outline varchar(255), @loopcnt int, @loopmax int, @sdate datetime,
@edate datetime, @secs int, @tran_time int, @avg_tran float, @tot_tran float,
@iterate int, @elapsed int, @tot_under1 int, @tot_under2 int

select @loopcnt = 1

if @run_no = 0           /* If the run_no = 0, then get all runs */
  select @loopmax = max(number) from runs
else
  select @loopmax = 1

while @loopcnt <= @loopmax
BEGIN
if @loopmax > 1            /* Assign next run number of getting all runs */
  select @run_no = @loopcnt
print ""
print "**********************************************************************"
select @outline = "Multiuser Performance Test Run #"
select @outline = @outline+convert(varchar(10),@run_no)
print @outline
print "**********************************************************************"

print ""
select @sdate = start_time from multi_runs where multi_number = @run_no
select @edate = end_time from multi_runs where multi_number = @run_no
select @outline = "Started: "
select @outline = @outline + convert(varchar(30),@sdate)
select @outline = @outline + "      Ended: "
select @outline = @outline + convert(varchar(30),@edate)
print @outline

print ""
select @outline = "TOTAL USERS: "
select @outline = @outline + convert(varchar(10),(select users from multi_runs where multi_number = @run_no))
print @outline

print ""
select @outline = "WORKSTATION RUNS: "
select @outline = @outline + convert(varchar(10),(select startrun from multi_runs where multi_number = @run_no))
select @outline = @outline + " to "+ convert(varchar(10),(select endrun from multi_runs where multi_number = @run_no))
print @outline

print ""
select @outline = "TEST SERVER: "+ test_server from multi_runs where multi_number = @run_no
select @outline = @outline + "          TRACKING SERVER: "+ track_server from multi_runs where multi_number = @run_no
print @outline

print ""
print "*************************** RUN STATISTICS ***************************"
print ""
select @outline = "TOTAL ITERATIONS: "
select @iterate = iterations from multi_results where multi_number = @run_no
select @outline = @outline + convert(varchar(10),@iterate)
select @outline = @outline + "         TOTAL ERRORS: "
select @outline = @outline + convert(varchar(10),(select errors
                  from multi_results where multi_number = @run_no))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "TOTAL ELAPSED TIME:    "
select @secs = datediff(second,@sdate,@edate)
select @elapsed = @secs
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
print @outline

print ""
select @outline = "TOTAL EXECUTION TIME:  "
select @tran_time = tot_exec from multi_results where multi_number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "MIN EXECUTION: "
select @tran_time = min_exec from multi_results where multi_number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "MAX EXECUTION: "
select @tran_time = max_exec from multi_results where multi_number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "AVG EXECUTION: "
select @tran_time = avg_exec from multi_results where multi_number = @run_no
select @secs = @tran_time/1000
select @outline = @outline + "Hours: " + convert(varchar(10),((@secs/60)/60))
select @outline = @outline + "  Mins : " + convert(varchar(10),((@secs/60)%60))
select @outline = @outline + "  Secs : " + convert(varchar(10),(@secs%60))
select @outline = @outline + "  Milli: " + convert(varchar(10),(@tran_time%1000))
print @outline

print ""
select @outline = "TOTAL EXECUTION UNDER 1 SECOND: "
select @tot_under1 = under1_exec from multi_results where multi_number = @run_no
select @outline = @outline + convert(varchar(10),@tot_under1)
print @outline

print ""
select @outline = "PERCENT OF EXECUTION UNDER 1 SECOND: "
select @outline = @outline + convert(varchar(10),(@tot_under1/convert(float,@iterate)))
print @outline

print ""
select @outline = "TOTAL EXECUTION UNDER 2 SECONDS: "
select @tot_under2 = under2_exec from multi_results where multi_number = @run_no
select @outline = @outline + convert(varchar(10),@tot_under2)
print @outline

print ""
select @outline = "PERCENT OF EXECUTION UNDER 2 SECONDS: "
select @outline = @outline + convert(varchar(10),(@tot_under2/convert(float,@iterate)))
print @outline

print ""
print "**********************************************************************"

print ""
select @outline = "TRANSACTIONS PER SECOND: "
select @outline = @outline + convert(varchar(10),(@iterate/convert(float,@elapsed)))
print @outline

select @loopcnt = @loopcnt + 1
END
go