APPENDIX K Calculating Extent Sizes


You will find the most recent version of these scripts (and others) at http://www.smooth1.demon.co.uk/www.wilsontechnology.com. : A post was made a few weeks ago of a shellscript which calculated : extent sizes for each table. I can't give an tribute because I don't : remember whose work it was but the shell worked fine for me. It : worked by using tbcheck -pt and awk to get the number of pages used : for each table.

I wrote it and here it is. I wanted to post this again anyway because the architecture it was originally written for (Sequent Intel 486MP) had 4k per page, while other architectures (HP 9000) have 2k per page. The new version of dbsize has this as a required value to be added to the script. It represents the number of 1k bytes per Informix On-line page.

If your OS has the 'pagesize' command, this will probably equal the proper Informix value.

Here are some scripts I worked up to create the EXTENT lines in the database create scripts.

The first one, dbsize, reads the output of 'tbcheck -pT' and generates a file containing a line with the table name, and another line with the computed sizes and extents (i.e. + 20%). You will need to remove the sys* tables at the top of the file, as they do not appear in the database creation script. You run it as

dbsize tbcheck.out

The script dbadd reads the output of the first script and the database create script and inserts the extents in the proper places. You run it as:

dbadd sizefile son_db.sql

You could integrate the two scripts into one, but I think this is easier to follow.

It saved me alot of time, and possible typing errors. It gives extents to all files over 8 pages.

Also, I have gotten in the habit of testing the script before I import by creating the database by hand and running dbaccess and inserting the entire SQL script into the query workspace and running it quickly to check for syntax errors. Saves alot of worry if I have made edits by hand.


:
#
# Define this before using.
# This is the number of 1k bytes per page, usually 2 or 4
#
KPERPAGE=
#
#
[ "$KPERPAGE" = "" ] && echo "Define 1k byes per page" 2>&1 && exit 1
awk '   $1 == "TBLSpace" && $2 == "Report" {printf "%s  ",$4}
        $3 == "pages"    && $4 == "used" {printf "%s\n", $5} ' $* |\
cut -d'.' -f2 |\
awk '   $2 > 8 {printf "^create table.*\\\\.%s \n", $1;
                sz=$2 * '"$KPERPAGE"' * 1.2;
                new = sz * 0.2;
                sz = int((sz + 7)/8)*8
                new = int((new + 7)/8)*8
                if (new <32)
                        new = 32;
                printf "EXTENT SIZE %d NEXT SIZE %d\n", sz, new}'

---------------------------------------------------------------------------

:
#dbadd
#set -x
trap "rm -f /tmp/$$" 0 1 2 3 15 
while :
do
        read TABLE || break
        read EXTENT || break
        awk '   BEGIN {found="N"; done="N"}
                { if (done != "Y" && $0 ~ /'"$TABLE "'/)
                        found="Y";
                  if (done != "Y" && found == "Y" && $1 == ");")
                  {
                        printf ")\n%s;\n","'"$EXTENT"'";
                        done = "Y";
                  }
                  else  printf "%s\n", $0;
                }' $2 >/tmp/$$ 
                mv /tmp/$$ $2
done <$1
--------------------------------------------------------------------------
The following script displays the tables with more than one extent,
listing the most fragmented tables first.  The number of extents and
pages is listed for each table.

:
echo | awk '{printf "%-30s\t%5s\t%12s\n", "TABLE","EXTS.","PAGES";}'
oncheck -pe son_db | sort | grep '^ *[a-z]'|awk '
	BEGIN		{matchit = ""; count = 0;size = 0;}
	{if (matchit != $1)
	 {
		if (count > 1)
			printf "%-30s\t%5d\t%12d\n", matchit, count, size;
		matchit = $1;
		count = 1;
		size = $3;
	 }
	 else
	 {
		count++;
		size += $3;
	 }
	}
	END		{if (count > 1) printf "%-30s\t%5d\t%12d\n", matchit, count,size}'|
sort -t '	' -rn -k2
---------------------------------------------------------------------------
Bruce Momjian                          |  830 Blythe Avenue
root@candle.pha.pa.us                  |  Drexel Hill, Pennsylvania 19026