IIUG Software Repository Files

Subject: Informix ReOrg/Scripts Programs
From: Sujit Pal <spal@scotch.den.csci.csc.com>
Date: Thu, 5 Jun 1997 11:25:21 -0600

The writeup for the various utilities follow:

sqlprof.pl (Perl script, 1 file)
SQL Profiler. Scans a sqexplain.out file and identifies the number of times 
that a particular SQL was called and the estimated cost of the SQL, in 
descending order of number of times the query was called and the descending 
order of estimated cost. The script takes as its first argument the name 
of the sqexplain.out file (default ./sqexplain.out) and the second argument 
is the pattern to be matched, eg tablename will return all SQLs where the 
table was called, ? will return all PREPARED statements, etc. Both arguments 
are optional.

calc_extent.ec (ESQL/C program, 1 file)
Extent Size Calculator. Uses Informix recommendations (in TFM) to calculate 
the tablespace size. The EXTENT SIZE is calculated as 1.2 times the 
tablespace size and the NEXT EXTENT is calculated as 0.6 times the 
tablespace size. Also returns the number of extents allowed. Can be run 
in interactive mode (the user enters the database and table name, the 
estimated number of rows and estimated uniqueness of index for each table), 
silent mode (the user only enters the database and table name and the 
program takes the rest of the information from what is in the tables) or 
database mode (the user enters all the information in a database table). 
The tablename accepts wildcard values.

distrib.4gl (4GL Program, 1 file).
Program to find distribution of data for duplicate indexes. Generates quite 
voluminous output showing data distribution by index bin. Also generates 
a histogram for a graphical view of the data distribution and calculates 
a dup_factor to indicate how duplicated the data is in the bin. Better 
picture of the data distribution (once you get used to it) than dbschema -hd.

grep_indx.pl (Perl script, 1 file)
Greps out the CREATE [UNIQUE] INDEX statements from the dbschema output file. 
Ordinary grep does not take care of multi-line CREATE [UNIQUE] INDEX 
statements. Creates two output files one with the CREATE [UNIQUE] INDEX 
statements and the other with the DROP INDEX statements. Good for 
recreating all the indexes in the database for reorganization purposes.

tabprof.4gl (4GL Program, 1 file)
Report program to display table usage profile from sysmaster:sysptprof table.

idxstat.4gl (4GL Program, 1 file)
Returns statistics of some or all indexes in the database. The program 
returns the indexes (both unique and duplicate) for a table and calculates 
the cardinality, and reports the number of leaves and levels on the index. 
It also computes the cardinality (number of rows/number of unique rows) for 
both the full index and its component columns. Good for checking if the 
index created is a "good" one.

The shar file for the utilities follow: