allocated vs. used pages (large extents) -Reply LONG

>>> Glenn Travis <gtravis@richmond.infi.net> 9/May/1996 07:46am >>> said >Has anyone written a report showing the pages >allocated vs.pages used by a table/index? Like >Oracle, I want to keep my unused space to a >minimum and re-build when necessary (into one >extent). Glenn There are probably a number of ways. I did it by reading the output of oncheck into a table for manipulation. Following are the cron script, and the load program. Also an sql statement for extents counting that works in 7.12 HTH (Note physical_db is a metadata table containing database names and instances.) -- -- Peter Tashkoff <tashkop@kiwi.co.nz> Project Consultant/DBA NZ Kiwifruit Marketing Board Usual Disclaimers Apply Use of this posting by any commercial organisation for the purposes of vilifying another is explicitly prohibited --------------------------------------------------------------------------- checkspace.sh #!/bin/ksh# A script to read off the extent sizes# for tables in the database# and load them for manipulation.# Report gets written out to user informix.# PBT Mon Apr 22 12:51:36 NZST 1996#First do for live system. /home/informix/bin/setenv. /home/informix/bin/setolcd /tmp>./oncheck.out#Oncheck every database in the instance.for a in `echo select name from sysdatabases | dbaccess sysmaster`do oncheck -pt $a >> ./oncheck.outdone#Manipulate the outfile so that it can be loaded.cat oncheck.out | awk '/Report/{TAB = $4 }; /allocated/{printf (" %s | %s | %d\\n", TAB, $4, $5) }; /used/{printf (" %s | %s | %d \\n", TAB, $4, $5) ;}' > ./tabdata.unlcat ./tabdata.unl | sed -e 's/\\:/|/' -e 's/\\./|/' > ./tabdata1.unl#Now load the data into the database . /home/informix/bin/setolfglgo /home/informix/common/checkspace/loadmetrics /tmp/tabdata1.unl kmb_online#Now do test. /home/informix/bin/settestcd /tmp>./oncheck.out#Oncheck every database in the instance. This takes a while.for a in `echo select name from sysdatabases | dbaccess sysmaster`do oncheck -pt $a >>./oncheck.outdone#Manipulatethe file so it can be loaded.cat oncheck.out | awk '/Report/{TAB = $4 }; /allocated/{printf (" %s | %s | %d\\n", TAB, $4, $5) }; /used/{printf (" %s | %s | %d \\n", TAB, $4, $5) ;}' > ./tabdata2.unlcat ./tabdata2.unl | sed -e 's/\\:/|/' -e 's/\\./|/' > ./tabdata3.unl#Now load the data. - Need to reset to be pointing at the live system.. /home/informix/bin/setolfglgo /home/informix/common/checkspace/loadmetrics /tmp/tabdata3.unl kmb_on_test#Now report the data.fglgo /home/informix/common/checkspace/checkspace kmb_online 80 | mailx -s "Live" informixfglgo /home/informix/common/checkspace/checkspace kmb_on_test 80 | mailx -s "Test" informix#This file gets pretty big - tidy up. >/tmp/oncheck.out ---------------------------------------------------------- Loadmetrics.4gl # A program to load space utilisation metrics# PBT Wed Apr 3 16:44:05 NZST 1996-------------------------------------------------- Takes two args-- First is the name of the load file-- 2nd is the instance name. Must correspond-- to that used in physical_db E.G 'kmb_online'-- This allows the db_id to be catted together as-- EG maindb@kmb_online. nzkmb@kmb_on_test---------------------------------------------------- Once loaded, run reports using checkspace.4gl----------------------------------------------------database nzkmbdefine gv_load_file char(25)define gv_instance char(25)main let gv_load_file = arg_val(1) let gv_instance = arg_val(2) call load_records() call flush_out_table() --cleans out nzkmb table call reload_table() --loads it with new valuesend main############################################ load the data into temp and then parse# it into the nzkmb file.###########################################function load_records() create temp table record_load ( database_name char(25), owner_name char(18), table_name char(18), status_ind char(25), pages_qty integer ) load from gv_load_file insert into record_loadendfunction --load_records()############################################ Cleans out the old dataset###########################################function flush_out_table() --cleans out nzkmb table define fv_database_name char(25) define fv_db_id char(50) --remove all records for this instance let fv_db_id = "*@", gv_instance clipped delete from disk_use where db_id matches fv_db_id end function --flush_out_table() --cleans out nzkmb table###################################################### Replaces the deleted rows with more current data#####################################################function reload_table() --loads it with new values define fr_disk_use record like disk_use.* --Table has already been purged of entries --so this is a strightforward add declare icurs_disk_use cursor for select trim(a.database_name) , a.table_name, a.pages_qty , b.pages_qty from record_load a, record_load b where a.database_name = b.database_name and a.table_name = b.table_name and a.status_ind matches "*allocated*" and b.status_ind matches "*used*" foreach icurs_disk_use into fr_disk_use.* let fr_disk_use.db_id = fr_disk_use.db_id clipped, "@", gv_instance clipped insert into disk_use values (fr_disk_use.*) end foreach end function ---reload_table() --loads it with new values --------------------------------------------------------- #Extents checking sql "checkextent.sql" 17/405 --drop table pt; --First get the sizes select dbsname, tabname, sum(size) NumPages,count(*) Numextents from sysextents where dbsname in ("nzkmb", "maindb") group by 1,2 order by 1,2 into temp pt; --delete the small tables, only large are of interest delete from pt where NumPages < 100 ; --Find out just how much space the biggies are using select " check_size " || dbsname || " " || tabname from pt