Dbspace space usage report ( 2 of 3 )
##############################################################################
function create_tem_table()
##############################################################################
create temp table tmp_spaceuse
(
dbname char(20),
tabname char(20),
partnum char(20),
frag_type char(1),
index_name char(20),
dbspace char(20),
dbnum int,
rowsize int,
nrows int,
fextsiz int,
nextsiz int,
nextns int,
nptotal int,
npused int,
npdata int,
percent decimal(9,5)
) with no log
if sqlca.sqlcode !=0
then
display "error While creating Temp table ",sqlca.sqlcode
end if
end function
#_create_tem_table
##############################################################################
function prep_cursor()
##############################################################################
if p_sortname = "dbspace"
then
let g_scratch = "select * from tmp_spaceuse ",
" order by 1,7,5 "
else
if p_sortname = "rows"
then
let g_scratch = "select * from tmp_spaceuse ",
" order by 1,7,9,5 "
else
if p_sortname = "extent"
then
let g_scratch = "select * from tmp_spaceuse ",
" order by 1,7,12,5 "
else
if p_sortname = "alloc"
then
let g_scratch = "select * from tmp_spaceuse ",
" order by 1,7,13,5 "
else
let g_scratch = "select * from tmp_spaceuse ",
" order by 1,7,5,9 "
end if
end if
end if
end if
prepare prep_rpt from g_scratch
end function
#prep_cursor
##############################################################################
function print_usage ()
##############################################################################
define
ans char(1)
options prompt line last
display "Usage : spaceuse -d database [-t tablename] [-s dbspace]
[-x rows] -[k[p]]" at 5,01 display " -d Database Name or 'all' for
all database " at 6,10 display " -t Table Name or 'all' for all table
" at 8,10 display " -s Dbspace Number " at 10,10 display " -x
sort by [ [r]rows, [d]dbspace, [a]alloc, [e]extent] " at 12,10 display
" -k[p] k-> in Kbytes, p-> in Pages" at 14,10 display " -f Full
Report(including Summary) " at 15,10 display " [ ] indicates default
options " at 16,10 prompt "Press any key to continuee" for char ans
end function
#end print_usage
##############################################################################
function chk_for_syntax()
##############################################################################
define
p_dbspace_str char(20)
let tot_arg = num_args()
let arg_count=1
while (true)
if tot_arg < arg_count
then
exit while
end if
let arg_str = arg_val(arg_count)
let arg_count=arg_count + 1
if arg_str[1,1] != "-"
then
call print_usage()
exit program
end if
if length(arg_str clipped) < 3
then
if arg_str[1,2] = "-d"
then
let arg_str = arg_val(arg_count)
let p_dbname =arg_str clipped
let p_dbname_flag = 1
let arg_count=arg_count + 1
continue while
end if
if arg_str[1,2] = "-t"
then
let arg_str = arg_val(arg_count)
let p_tablename =arg_str clipped
let p_tablename_flag = 1
let arg_count=arg_count + 1
continue while
end if
if arg_str[1,2] = "-s"
then
let arg_str = arg_val(arg_count)
let p_dbspace_str =arg_str clipped
if p_dbspace_str[1,1] not matches '[0-9]'
then
select dbsnum into p_dbspace
from sysdbspaces where name = p_dbspace_str
else
let p_dbspace=arg_str clipped
end if
let p_dbspace_flag = 1
let arg_count=arg_count + 1
continue while
end if
if arg_str[1,2] = "-x"
then
let arg_str = arg_val(arg_count)
let p_sortname =arg_str clipped
if arg_str[1,1]='r'
then
let p_sortname ="rows"
end if
if arg_str[1,1]='d'
then
let p_sortname ="dbspace"
end if
if arg_str[1,1]='e'
then
let p_sortname ="extent"
end if
if arg_str[1,1]='a'
then
let p_sortname ="alloc"
end if
let p_sortname_flag = 1
let arg_count=arg_count + 1
continue while
end if
if arg_str[1,2] = "-k"
then
let p_kbytes_flag = 1
continue while
end if
if arg_str[1,2] = "-f"
then
let p_full_rpt_flag = 1
continue while
end if
if arg_str[1,2] = "-p"
then
let p_pages_flag = 1
continue while
end if
else
let str_len=length(arg_str clipped)
if arg_str[1,2] = "-d"
then
let p_dbname =arg_str[3,str_len] clipped
let p_dbname_flag = 1
continue while
end if
if arg_str[1,2] = "-t"
then
let p_tablename =arg_str[3,str_len] clipped
let p_tablename_flag = 1
continue while
end if
if arg_str[1,2] = "-s"
then
#let arg_str = arg_val(arg_count)
let p_dbspace_str =arg_str[3,str_len] clipped@@N