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:

----------------------------- Cut here -----------------------------------
# This is a shell archive.  Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by Sujit Pal <spal@scotch> on Tue Jun  3 15:40:10 1997
#
# This archive contains:
#	ifx_utils	
#

LANG=""; export LANG
PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH

echo mkdir - ifx_utils
mkdir ifx_utils

echo x - ifx_utils/sqlprof.pl
cat >ifx_utils/sqlprof.pl <<'@EOF'
#!/usr/local/bin/perl 
#
# sqlprof
#
# Perl script to scan through a sqexplain.out file and identify the number
# of times that a particular SQL was called and the Estimated cost of the
# query (includes INSERTs, UPDATEs and DELETEs), in descending order of the
# number of times the query was called. The first argument is the output of
# SET EXPLAIN ON that needs to be scanned. The default is sqexplain.out in 
# the current directory but it can be anything if specified. The second
# argument is the pattern to be matched and should be a quoted string. For
# instance if we need to find all PREPAREd statements then we would look for
# a ? character or if we wanted to find TEMP tables we could look for "TEMP".
# The default pattern is a null, ie it matches everything.
#
# Author: Sujit Pal
# Dated: 10/25/96
#
if (($#ARGV == 0) && ($ARGV[0] eq "--") || ($#ARGV > 1)) 
{
   die "Usage: sqlprof [sqexplain_out_file] [pattern]\n";
}
if ($#ARGV == -1)
{
   $sqexpl_fname = "sqexplain.out";
   $pattern = "#";
}
if ($#ARGV == 0)
{
   if (index($ARGV[0], "\"") < 0)          # This is a filename
   {
      $sqexpl_fname = $ARGV[0];
      $pattern = "#";
   }
   else                                    # This is a pattern
   {
      $sqexpl_fname = "sqexplain.out";
      $pattern = $ARGV[0];
   }
}
if ($#ARGV == 1)
{
   $sqexpl_fname = $ARGV[0];
   $pattern = $ARGV[1];
   $upattern =~ tr/A-Z/a-z/;
}
open(SQEXPL, $sqexpl_fname) || die "Cant stat $sqexpl_fname\n";
chop(@sqexplls = <SQEXPL>);
close(SQEXPL);
#
# Grep out the SQL statements from the file
#
@sqllns = grep(/select|update|insert|delete/i, @sqexplls);
#
# Count the number of occurences
#
foreach (@sqllns)
{
   if ($sqprofs{$_} eq "")
   {
      $sqprofs{$_} = 1;
   }
   else
   {
      $sqprofs{$_}++;
   }
}
#
# Make a mapping between the SQL and its estimated cost. Create an
# associative array of costs with the statement as key
#
$i = 0;
foreach (@sqllns)
{
   $sqlstmts{$i} = $_;
   $i++;
}
@festcosts = grep(/Estimated Cost/, @sqexplls);
$i = 0;
foreach (@festcosts)
{
   ($const, $cost) = split(/:/, $_);
   $estcosts{$i} = $cost;
   $i++;
}
foreach (keys(%sqlstmts))
{
   if ($costs{$sqlstmts{$_}} eq "")
   {
      $costs{$sqlstmts{$_}} = $estcosts{$_};
   }
}
#
# Sort the two arrays by descending order of repetitions and descending
# order of estimated costs and print
#
if ($pattern eq "#")
{
   $ppattern = "None";
}
else
{
   $ppattern = $pattern;
}
$= = 60;
foreach (sort count_plus_cost (keys(%sqprofs)))
{
   $sqlstmt = $_;
   $sqlstmt =~ s/^ //;
   if ($pattern ne "#")
   {
      if (index($sqlstmt, $pattern) < 0 || index($sqlstmt, $upattern) < 0)
      {
         next;
      }
   }
   $rptcnt = $sqprofs{$_};
   $cost = $costs{$_};
   write;
}

sub count_plus_cost
{
   if ($sqprofs{$a} < $sqprofs{$b})
   {
      1;
   }
   elsif ($sqprofs{$a} == $sqprofs{$b})
   {
      if ($costs{$a} < $costs{$b})
      {
	 1;
      }
      elsif ($costs{$a} == $costs{$b})
      {
	 0;
      }
      else
      {
	 -1;
      }
   }
   else
   {
      -1;
   }
}

format STDOUT = 
@<<<<< @<<<<<<<< ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$rptcnt, $cost, $sqlstmt
~~               ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                $sqlstmt

.

format STDOUT_TOP =

                    SQL Usage Profile for Pattern: @<<<<<<<<<<<<
                                                   $ppattern

Filename: @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  Page-#: @<<<
          $sqexpl_fname                                                  $%

Count  Est.Cost  Query
-----  --------  --------------------------------------------------------------
.
@EOF

chmod 755 ifx_utils/sqlprof.pl

echo x - ifx_utils/calc_extent.ec
cat >ifx_utils/calc_extent.ec <<'@EOF'
/*
 * calc_extent.ec
 *
 * Calculates table size for tables needed for allocation of intial EXTENT 
 * SIZE and NEXT EXTENT sizes. Also calculates the upper limit on the number 
 * of extents for the table. Outputs recommended EXTENT SIZE and NEXT EXTENT,
 * TBLSpace * 1.2 and TBLSpace * 0.6 respectively.
 *
 */
#include <stdio.h>
#include <math.h>

#define KPERPAGE 2


main(argc, argv)
int argc;
char *argv[];
{
   $char p_dbname[19], p_tabname[19], p_idxtype[2];
   $long int p_ncols, p_nindexes, p_nrows, p_tabid, p_rowsize;
   $long int p_part1, p_part2, p_part3, p_part4, p_part5, p_part6,
	     p_part7, p_part8, p_part9, p_part10, p_part11, p_part12,
	     p_part13, p_part14, p_part15, p_part16;
   $long int p_colno, p_collength, p_coltype, p_colmin, p_colmax,
	     p_estrows;
   $float p_pctuniq;
   $int p_varlen, i, j;
   long int p_pagesize, p_colspace, p_ixspace, p_ixparts, p_extspace,
	    p_numextents, p_pageuse, p_homerow, p_expages,
	    p_spcneeded, p_overpage, p_datrows, p_datpages, p_entsize,
	    p_pagents, p_leaves, p_branches, p_keysize, p_fextsize,
	    p_nextsize; 
   long int p_colarr[16];
   float p_defpct;

   printf("********************************************\n");
   printf("*                                          *\n");
   printf("* TBLSpace requirements calculator         *\n");
   printf("* version 1.0                              *\n");
   printf("*                                          *\n");
   printf("********************************************\n");
   if (argc != 2)
   {
      printf("Usage: calc_extent -i|-s|-d\n");
      printf("       -i - interactive mode\n");
      printf("       -s - silent mode using defaults\n");
      printf("       -d - get inputs from database table\n");
      printf("               tblspcdat\n");
      printf("                  tabname CHAR(18)\n");
      printf("                  estrows INTEGER\n");
      printf("                  idxuniqlvl FLOAT\n");
      exit(1);
   }
   printf("Enter database name: ");
   scanf("%s", p_dbname);
   $DATABASE $p_dbname;
   if (sqlca.sqlcode != 0)
   {
      printf("Cant stat database %s\n", p_dbname);
      exit(1);
   }
   p_pagesize = KPERPAGE * 1024;
   p_pageuse = p_pagesize - 28;
   printf("Enter table name (wildcards allowed): ");
   scanf("%s", p_tabname);
   $DECLARE q_tables CURSOR FOR
      SELECT tabid, tabname, ncols, nindexes, nrows, rowsize 
      FROM systables
      WHERE tabid >= 100
      AND tabtype = "T"
      AND tabname MATCHES $p_tabname;
   $OPEN q_tables;
   for (;;)
   {
      $FETCH q_tables INTO $p_tabid, $p_tabname, $p_ncols, 
	 $p_nindexes, $p_nrows, $p_rowsize;
      if (sqlca.sqlcode == 100) break;
      p_colspace = 4 * p_ncols;
      p_ixspace = 12 * p_nindexes;
      $DECLARE q_indexes CURSOR FOR 
	 SELECT idxtype, part1, part2, part3, part4, part5, part6, part7,
	    part8, part9, part10, part11, part12, part13, part14,
	    part15, part16
	    FROM sysindexes
	    WHERE tabid = $p_tabid;
      $OPEN q_indexes;
      p_ixparts = 0;
      p_keysize = 0;
      p_pctuniq = 0.0;
      j = 0;
      for (;;)
      {
	 $FETCH q_indexes INTO $p_idxtype, $p_part1, $p_part2, $p_part3, 
	    $p_part4, $p_part5, $p_part6, $p_part7, $p_part8, $p_part9, 
	    $p_part10, $p_part11, $p_part12, $p_part13, $p_part14, 
	    $p_part15, $p_part16;
	 if (sqlca.sqlcode == 100) break;
	 j++;
	 if (strncmp(p_idxtype, "U", 1) == 0) p_pctuniq += 1.0;
	 p_colarr[1] = p_part1;
	 p_colarr[2] = p_part2;
	 p_colarr[3] = p_part3;
	 p_colarr[4] = p_part4;
	 p_colarr[5] = p_part5;
	 p_colarr[6] = p_part6;
	 p_colarr[7] = p_part7;
	 p_colarr[8] = p_part8;
	 p_colarr[9] = p_part9;
	 p_colarr[10] = p_part10;
	 p_colarr[11] = p_part11;
	 p_colarr[12] = p_part12;
	 p_colarr[13] = p_part13;
	 p_colarr[14] = p_part14;
	 p_colarr[15] = p_part15;
	 p_colarr[16] = p_part16;
	 for (i = 1; i <= 16; i++)
	 {
	    if (p_colarr[i] > 0) 
	    {
	       p_ixparts++;
	       p_colno = p_colarr[i];
	       $SELECT coltype, collength, colmin, colmax
		  INTO $p_coltype, $p_collength, $p_colmin, $p_colmax
		  FROM syscolumns
		  WHERE colno = $p_colno
		  AND tabid = $p_tabid;
	       if (p_coltype == 0) p_keysize += p_collength;
	       if (p_coltype == 1) p_keysize += sizeof(short);
	       if (p_coltype == 2) p_keysize += sizeof(int);
	       if (p_coltype == 3) p_keysize += sizeof(double);
	       if (p_coltype == 4) p_keysize += sizeof(int);
	       if (p_coltype == 5) p_keysize += 1 + (p_collength/512);
	       if (p_coltype == 6) p_keysize += sizeof(int);
	       if (p_coltype == 7) p_keysize += 8;
	       if (p_coltype == 8) p_keysize += 1 + (p_collength/512);
	       if (p_coltype == 10) p_keysize += 56;
	       if (p_coltype == 11) p_keysize += 56;
	       if (p_coltype == 12) p_keysize += p_collength / 256;
	       if (p_coltype == 13) p_keysize += 256;
	       if (p_coltype == 14) p_keysize += 56;
	    }
	 }
      }
      $CLOSE q_indexes;
      if (j == 0) 
      {
	 p_pctuniq = 1.0;
      }
      else 
      {
	 p_pctuniq /= j;
      }
      p_ixparts = p_ixparts * 4;
      p_extspace = p_pagesize - (p_colspace + p_ixspace + p_ixparts + 84);
      p_numextents = p_extspace / 8;
      printf("\nTable: %s\n", p_tabname);
      printf("=========================\n");
      /*
       * Data pages calculation
       */
      if (strncmp(argv[1], "-i", 2) == 0)      /* Interactive mode */
      {
         printf("Enter estimated number of rows for table %s (%d): ", 
	    p_tabname, p_nrows);
         scanf("%ld", &p_estrows);
      }
      if (strncmp(argv[1], "-d", 2) == 0)      /* Table mode */
      {
	 $SELECT estrows INTO $p_estrows
	     FROM tblspcdat
	     WHERE tabname = $p_tabname;
	 if (sqlca.sqlcode == 100) continue;
      }
      if (strncmp(argv[1], "-s", 2) == 0)      /* Silent mode */
      {
	 p_estrows = p_nrows;
      }
      if (p_rowsize <= p_pageuse) 
      {
	 p_homerow = p_rowsize;
	 p_overpage = 0;
      }
      else 
      {
	 p_homerow = 4 + (p_rowsize % p_pageuse);
	 p_overpage = p_rowsize / p_pageuse;
      }
      p_datrows = p_pageuse / p_homerow;
      if (p_datrows > 255) p_datrows = 255;
      p_datpages = (p_estrows / p_datrows) + 1;
      p_expages = p_estrows * p_overpage;
      p_spcneeded = p_datpages + p_expages;
      /*
       * Index pages calculation
       */
      if (strncmp(argv[1], "-i", 2) == 0)       /* Interactive mode */
      {
         printf("Estimate uniqueness of index (0=dups to 1=uniq) default %3.1f: ",
	    p_pctuniq);   
         scanf("%f", &p_pctuniq);
      }
      if (strncmp(argv[1], "-d", 2) == 0)      /* Table mode */
      {
	 $SELECT idxuniqlvl INTO $p_pctuniq
	     FROM tblspcdat
	     WHERE tabname = $p_tabname;
	 if (sqlca.sqlcode == 100) continue;
      }
      p_entsize = (p_keysize * p_pctuniq) + 4;
      p_pagents = p_pageuse / p_entsize;
      p_leaves = (p_estrows / p_pagents) + 1;
      p_branches = (p_leaves / p_pagents) + 1;
      p_spcneeded += p_leaves + p_branches;
      p_fextsize = p_spcneeded * 1.2;
      p_nextsize = p_spcneeded * 0.6;

      printf(">> Estimated TBLSpace needed       = %d pages\n", p_spcneeded);
      printf(">> Recommended EXTENT SIZE         = %d pages\n", p_fextsize);
      printf(">> Recommended NEXT EXTENT         = %d pages\n", p_nextsize);
      printf(">> Total number of extents allowed = %d extents\n", p_numextents);
   }
   $CLOSE q_tables;
}
@EOF

chmod 755 ifx_utils/calc_extent.ec

echo x - ifx_utils/distrib.4gl
cat >ifx_utils/distrib.4gl <<'@EOF'
--
-- distrib.4gl
-- 
-- Program to find distribution of data for duplicate indexes. Shows the
-- distribution graphically and generates a dup.factor metric to indicate
-- duplicacy. Better picture of the data distribution (once you get used
-- to it) than dbschema -hd
--
-- Author: Sujit Pal                  Date: 10/03/96
--
MAIN
DEFINE
   cDBName CHAR(18),
   cTabName CHAR(18),
   nI SMALLINT,
   nJ SMALLINT,
   nCount INTEGER,
   nRowId INTEGER,
   nRelCount INTEGER,
   nTotCount INTEGER,
   nMaxCount INTEGER,
   cHist CHAR(40),
   nLenColl INTEGER,
   nHistLen INTEGER,
   cSql CHAR(512),
   cColList CHAR(500),
   aIndexes ARRAY[16] OF INTEGER,
   cIdxName CHAR(18),
   nTotRows INTEGER,
   nRes SMALLFLOAT,
   nCols INTEGER,
   nCurrCols INTEGER,
   nBins INTEGER,
   nOvfLmt INTEGER,
   nAvgDups INTEGER,
   rDistdat RECORD
      bin_no INTEGER,
      n_rows INTEGER,
      n_unique INTEGER,
      dup_indx DECIMAL(5,2),
      n_ovflow INTEGER
   END RECORD,
   rSystables RECORD
      tabid INTEGER,
      tabname CHAR(18)
   END RECORD,
   rSysindexes RECORD
      part1 SMALLINT,
      part2 SMALLINT,
      part3 SMALLINT,
      part4 SMALLINT,
      part5 SMALLINT,
      part6 SMALLINT,
      part7 SMALLINT,
      part8 SMALLINT,
      part9 SMALLINT,
      part10 SMALLINT,
      part11 SMALLINT,
      part12 SMALLINT,
      part13 SMALLINT,
      part14 SMALLINT,
      part15 SMALLINT,
      part16 SMALLINT
   END RECORD,
   rSyscolumns RECORD
      colname CHAR(18)
   END RECORD

   IF (NUM_ARGS() < 1) THEN
      DISPLAY "distrib: Usage:"
      DISPLAY "distrib database_name [table_name]"
      EXIT PROGRAM
   END IF
   LET cDBName = ARG_VAL(1)
   IF (NUM_ARGS() > 1) THEN
      LET cTabName = ARG_VAL(2)
   ELSE 
      LET cTabName = "*"
   END IF
   DATABASE cDBName

   SET ISOLATION TO DIRTY READ

   DECLARE qTables CURSOR FOR 
      SELECT tabid, tabname FROM systables
      WHERE tabid > 99
      AND tabtype = "T"
      AND tabname MATCHES cTabName
   FOREACH qTables INTO rSystables.tabid, rSystables.tabname
      DECLARE qIndexes CURSOR FOR
	 SELECT idxname, part1, part2, part3, part4, part5, part6, part7, part8,
	    part9, part10, part11, part12, part13, part14, part15, part16
	 FROM sysindexes
	 WHERE idxtype = "D"
	 AND tabid = rSystables.tabid
      FOREACH qIndexes INTO cIdxName, aIndexes[1], aIndexes[2], aIndexes[3],
         aIndexes[4], aIndexes[5], aIndexes[6], aIndexes[7],
	 aIndexes[8], aIndexes[9], aIndexes[10], aIndexes[11],
	 aIndexes[12], aIndexes[13], aIndexes[14], aIndexes[15], 
	 aIndexes[16]
	 LET cColList = NULL
	 FOR nI = 1 TO 16
	    IF aIndexes[nI] < 0 THEN
	       LET aIndexes[nI] = aIndexes[nI] * (-1)
	    END IF
	    IF aIndexes[nI] > 0 THEN
	       SELECT colname INTO rSyscolumns.colname
		  FROM syscolumns
	          WHERE tabid = rSystables.tabid
	          AND colno = aIndexes[nI]
	       LET cColList = cColList CLIPPED, " ", 
		  rSyscolumns.colname CLIPPED, ","
	    END IF
	 END FOR
	 LET nLenColl = LENGTH(cColList CLIPPED)
	 LET cColList = cColList[1, nLenColl-1]
	 LET cSql = "SELECT ", cColList CLIPPED, ", COUNT(*) count FROM ", 
	    rSystables.tabname CLIPPED, " GROUP BY ", cColList CLIPPED,
	    " ORDER BY ", cColList CLIPPED, " INTO TEMP temp_dist"
	 PREPARE pGroupBy FROM cSql
	 EXECUTE pGroupBy

	 SELECT nrows INTO nTotRows
	    FROM systables
	    WHERE tabid = rSystables.tabid
	 DECLARE qDistrib CURSOR FOR
	    SELECT UNIQUE resolution FROM sysdistrib
	    WHERE tabid = rSystables.tabid
	 OPEN qDistrib
	 FETCH qDistrib INTO nRes
	 CLOSE qDistrib
	 LET nCols = ( nRes * nTotRows ) / 100
	 LET nBins = nTotRows / nCols
	 LET nOvflmt = ( 0.25 * nRes * nTotRows ) / 100

	 CREATE TEMP TABLE temp_datadist (
	    bin_no INTEGER,
	    n_rows INTEGER,
	    n_unique INTEGER,
	    dup_indx DECIMAL(5,2),
	    n_ovflow INTEGER)
	 LET cSql = "SELECT rowid, count FROM temp_dist ORDER BY rowid"
	 PREPARE pTemp FROM cSql
	 DECLARE qTemp CURSOR FOR pTemp
	 OPEN qTemp
	 LET nTotCount = 0
	 FOR nI = 1 TO nBins
	    LET rDistdat.bin_no = nI
	    LET rDistdat.n_rows = 0
	    LET rDistdat.n_unique = 0
	    LET rDistdat.dup_indx = 0.0
	    LET rDistdat.n_ovflow = 0
	    LET nAvgDups = 0
	    LET nCurrCols = nCols
            WHILE (1)
	       IF nTotCount = 0 THEN
	          FETCH qTemp INTO nRowId, nCount
	          IF (sqlca.sqlcode = NOTFOUND) THEN
		     EXIT WHILE
	          END IF
	       ELSE
		  LET nCount = nTotCount
	       END IF
	       IF (nCount > nOvfLmt) THEN
		  LET rDistdat.n_ovflow = rDistdat.n_ovflow + (nCount - nOvfLmt)
                  LET nCount = nOvfLmt
	       END IF
	       LET nTotCount = nTotCount + nCount
	       IF (nTotCount >= nCurrCols) THEN
		  LET nTotCount = nTotCount - nCurrCols
                  LET nCount = nCount - nTotCount
		  LET rDistdat.n_rows = rDistdat.n_rows + nCount
		  LET nAvgDups = nAvgDups + nCount
		  LET rDistdat.n_unique = rDistdat.n_unique + 1
		  EXIT WHILE
	       ELSE
		  LET nTotCount = 0
		  LET rDistdat.n_rows = rDistdat.n_rows + nCount
		  LET nAvgDups = nAvgDups + nCount
		  LET rDistdat.n_unique = rDistdat.n_unique + 1
	       END IF
	       LET nCurrCols = nCurrCols - nCount
	    END WHILE
	    LET nAvgDups = nAvgDups / rDistdat.n_unique
	    LET rDistdat.dup_indx = 1 - ((rDistdat.n_unique + nAvgdups )
		  / rDistdat.n_rows)
	    LET cSql = "INSERT INTO temp_datadist VALUES (?, ?, ?, ?, ?)"
	    PREPARE pIns FROM cSql
	    EXECUTE pIns USING rDistdat.*
	 END FOR
	 CLOSE qTemp
	 DISPLAY " "
         DISPLAY "Table Name: ", rSystables.tabname, " Index Name: ",
	    cIdxName CLIPPED, " ( ", cColList CLIPPED, " )"
         DISPLAY "bin  #-rows #-uniq #-ovflow dup.factor=1-(#-uniq + ",
	    "avg_seqscans)/#-rows"
         DISPLAY "---  ------ ------ -------- -----------------------",
	    "-----------------------"
	 LET cSql = "SELECT * FROM temp_datadist"
	 PREPARE pSelect FROM cSql
	 DECLARE qSelect CURSOR FOR pSelect
	 OPEN qSelect
	 WHILE (1)
	    FETCH qSelect INTO rDistdat.*
	    IF (sqlca.sqlcode = NOTFOUND) THEN
	       EXIT WHILE
	    END IF
	    LET nHistLen = rDistdat.dup_indx * 40
	    IF (nHistLen > 40) THEN
	       LET nHistLen = 40
	    END IF
	    LET cHist = NULL
	    FOR nI = 1 TO nHistLen
	       LET cHist = cHist CLIPPED, "*"
	    END FOR
            DISPLAY rDistdat.bin_no USING "##&", ": ", 
	       rDistdat.n_rows USING "#####&", " ", 
	       rDistdat.n_unique USING "#####&", " ",
	       rDistdat.n_ovflow USING "#######&", " ", 
	       rDistdat.dup_indx USING "#&.&&", ":",
	       cHist
	 END WHILE
	 CLOSE qSelect
	 LET cSql = "DROP TABLE temp_dist"
	 PREPARE pDrop1 FROM cSql
	 EXECUTE pDrop1
	 LET cSql = "DROP TABLE temp_datadist"
	 PREPARE pDrop2 FROM cSql
	 EXECUTE pDrop2
      END FOREACH
      CLOSE qIndexes
   END FOREACH
   CLOSE qTables
END MAIN
@EOF

chmod 755 ifx_utils/distrib.4gl

echo x - ifx_utils/grep_indx.pl
cat >ifx_utils/grep_indx.pl <<'@EOF'
#! /usr/local/bin/perl
# 
# grep_indx.pl
#
# 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 2 output files one with all the CREATE [UNIQUE]
# INDEX statements and the other with DROP INDEX statements for all the 
# indexes in the dbschema output file.
#
# Author: Sujit Pal                     Date: 01/12/97
#
if ($#ARGV < 0)
{
   die "Usage: grep_indx dbschema_file_name\n";
}
open(SQLIN, $ARGV[0]) || die "Cant open dbschema output file $ARGV[0]\n";
open(SQLINDX, ">$ARGV[0]_indx.sql") || die "Can't open $ARGV[0]_indx.sql\n";
open(SQLDROP, ">$ARGV[0]_drop.sql") || die "Can't open $ARGV[0]_drop.sql\n";

while(<SQLIN>) 
{
   if (/\s*create.*index.*/)        #  index statment
   {
      $line = "";
      while (! /;$/)         # read multiple idx stmt
      {
         chop;
         $line = "$line $_";
         $_ = <SQLIN>;
      }
      $line = "$line $_";
      $line =~ s/^ //;         # substitute the leading space
      $_ = $line;         # the make the split command easier
      print (SQLINDX "$_");
      if (/\s*create unique index.*/)    #  unique index statment
      {
         $_ =~ s/unique //;
      }
      ($c,$i,$name,$rest) = split;
      print SQLDROP "drop index $name;\n";
      next;
   }
}
@EOF

chmod 755 ifx_utils/grep_indx.pl

echo x - ifx_utils/tabprof.4gl
cat >ifx_utils/tabprof.4gl <<'@EOF'
--
-- Tabprof - program to generate table usage profiles
-- Author: Sujit Pal
-- Usage:  tabprof db_name
--
DATABASE sysmaster

MAIN
   DEFINE
      cDbName CHAR(18),
      cTabName ARRAY[500] OF CHAR(18),
      nI SMALLINT, 
      nJ SMALLINT,
      nLockreqs INTEGER,
      nLockwts  INTEGER,
      nDeadlks  INTEGER,
      nIsreads INTEGER,
      nIswrites INTEGER,
      nIsrewrites INTEGER,
      nSeqscans INTEGER

   IF (NUM_ARGS() != 1) THEN
      DISPLAY "Usage: tabprof db_name"
      EXIT PROGRAM
   END IF
   LET cDbName = ARG_VAL(1)
   DATABASE cDbName
   DECLARE qTables CURSOR FOR
      SELECT tabname FROM systables
      WHERE tabtype = "T"
      AND tabid > 99
      ORDER BY tabname ASC
   LET nI = 1
   FOREACH qTables INTO cTabName[nI]
      LET nI = nI + 1
   END FOREACH
   CLOSE qTables
   DATABASE sysmaster
   DISPLAY "table                 lock_req  lock_waits   deadlocks    ",
      "ix_reads   ix_writes  ix_updates   seq_scans"
   DISPLAY " "
   FOR nJ = 1 TO nI-1
      SELECT lockreqs, lockwts, deadlks, isreads, iswrites, isrewrites, seqscans
	 INTO nLockreqs, nLockwts, nIsreads, nIswrites, nIsrewrites, nSeqscans
         FROM sysptprof
         WHERE tabname = cTabName[nJ]
         AND dbsname = cDbName
      DISPLAY cTabName[nJ], " ", nLockreqs, " ", nLockwts, " ", nDeadlks, " ",
	 nIsreads, " ", nIswrites, " ",  nIsrewrites, " ", nSeqscans
   END FOR
END MAIN
@EOF

chmod 755 ifx_utils/tabprof.4gl

echo x - ifx_utils/idxstat.4gl
cat >ifx_utils/idxstat.4gl <<'@EOF'
--
-- idxstat.4gl
-- 
-- Program to find the index characteristics (namely number of unique rows,
-- number of rows, cardinality (= number of rows/number of unique rows),
-- number of levels of the index and the number of leaves in the index.
-- Cardinality is calculated both at the index level and its subsequent
-- column level.
-- 
-- Author: Sujit Pal                  Date: 12/12/96
--
MAIN
DEFINE
   cDBName CHAR(18),
   cTabName CHAR(18),
   nI SMALLINT,
   bDescCol SMALLINT,
   nUniqRows INTEGER,
   nCardinality FLOAT,
   nLenColl INTEGER,
   cSql CHAR(512),
   cColList CHAR(500),
   cPrtList CHAR(500),
   aIndexes ARRAY[16] OF INTEGER,
   aIdxName ARRAY[16] OF CHAR(18),
   cIdxName CHAR(18),
   nTotRows INTEGER,
   rSysTables RECORD
      tabid INTEGER,
      tabname CHAR(18)
   END RECORD,
   rSysIndexes RECORD
      idxtype CHAR(1),
      levels INTEGER,
      leaves INTEGER
   END RECORD,
   rSysColumns RECORD
      colname CHAR(18)
   END RECORD

   IF (NUM_ARGS() < 1) THEN
      DISPLAY "idxstat: Usage:"
      DISPLAY "idxstat database_name [table_name]"
      EXIT PROGRAM
   END IF
   LET cDBName = ARG_VAL(1)
   IF (NUM_ARGS() > 1) THEN
      LET cTabName = ARG_VAL(2)
   ELSE 
      LET cTabName = "*"
   END IF
   DATABASE cDBName

   SET ISOLATION TO DIRTY READ

   DECLARE qTables CURSOR FOR 
      SELECT tabid, tabname FROM systables
      WHERE tabid > 99
      AND tabtype = "T"
      AND tabname MATCHES cTabName
      ORDER BY tabname
   DISPLAY "Table-Name         [U/D]Index-Name         #-rows #-uniq ",
      "cardinal levels leaves"
   DISPLAY "                   (Index columns)                       ",
      "factor"
   DISPLAY "------------------ ----------------------- ------ ------ ",
      "-------- ------ ------"
   FOREACH qTables INTO rSysTables.tabid, rSysTables.tabname
      DECLARE qIndexes CURSOR FOR
	 SELECT idxname, idxtype, part1, part2, part3, part4, part5, part6, 
	    part7, part8, part9, part10, part11, part12, part13, part14, 
	    part15, part16, levels, leaves
	 FROM sysindexes
	 WHERE tabid = rSysTables.tabid
	 ORDER BY idxtype DESC
      FOREACH qIndexes INTO cIdxName, rSysIndexes.idxtype, aIndexes[1], 
	 aIndexes[2], aIndexes[3], aIndexes[4], aIndexes[5], aIndexes[6], 
	 aIndexes[7], aIndexes[8], aIndexes[9], aIndexes[10], aIndexes[11],
	 aIndexes[12], aIndexes[13], aIndexes[14], aIndexes[15], aIndexes[16],
	 rSysIndexes.levels, rSysIndexes.leaves
	 LET cColList = NULL
	 LET cPrtList = NULL
	 FOR nI = 1 TO 16
	    LET aIdxName[nI] = NULL
	 END FOR
	 FOR nI = 1 TO 16
	    LET bDescCol = FALSE
	    IF aIndexes[nI] < 0 THEN
	       LET aIndexes[nI] = aIndexes[nI] * (-1)
	       LET bDescCol = TRUE
	    END IF
	    IF aIndexes[nI] > 0 THEN
	       SELECT colname INTO rSysColumns.colname
		  FROM syscolumns
	          WHERE tabid = rSysTables.tabid
	          AND colno = aIndexes[nI]
	       LET aIdxName[nI] = rSysColumns.colname
	       LET cColList = cColList CLIPPED, " ", 
		  rSysColumns.colname CLIPPED, ","
	       IF (bDescCol) THEN
		  LET cPrtList = cPrtList CLIPPED, " ",
		     rSysColumns.colname CLIPPED, " D,"
	       ELSE
	          LET cPrtList = cPrtList CLIPPED, " ", 
		     rSysColumns.colname CLIPPED, ","
	       END IF
	    END IF
	 END FOR
	 LET nLenColl = LENGTH(cColList CLIPPED)
	 LET cColList = cColList[1, nLenColl - 1]
	 LET nLenColl = LENGTH(cPrtList CLIPPED)
	 LET cPrtList = cPrtList[1, nLenColl - 1]
	 LET cSql = "SELECT ", cColList CLIPPED, ", COUNT(*) count FROM ", 
	    rSysTables.tabname CLIPPED, " GROUP BY ", cColList CLIPPED,
	    " ORDER BY ", cColList CLIPPED, " INTO TEMP temp_dist"
	 PREPARE pGroupBy FROM cSql
	 EXECUTE pGroupBy

	 SELECT nrows INTO nTotRows
	    FROM systables
	    WHERE tabid = rSysTables.tabid
	 SELECT COUNT(*) INTO nUniqRows 
	    FROM temp_dist
	 LET nCardinality = nTotRows / nUniqrows
	 DISPLAY rSysTables.tabname, " [", rSysIndexes.idxtype, "]", 
	    cIdxname, "  ", nTotRows USING "#####&", " ", 
	    nUniqrows USING "#####&", "  ", nCardinality USING "####&.&&", 
	    " ", rSysIndexes.levels USING "#####&", 
	    " ", rSysIndexes.leaves USING "#####&"
	 DISPLAY "                   ", "(", cPrtList CLIPPED, " )"
	 FOR nI = 1 TO 16
	    IF aIdxName[nI] IS NULL THEN
	       EXIT FOR
	    ELSE
	       LET cSql = "DROP TABLE temp_dist"
	       PREPARE pDrop1 FROM cSql
	       EXECUTE pDrop1
	       LET cColList = aIdxName[nI]
	       LET cSql = "SELECT UNIQUE ", cColList CLIPPED, " FROM ",
	          rSysTables.tabname CLIPPED, " INTO TEMP temp_dist"
	       PREPARE pUniq FROM cSql
	       EXECUTE pUniq
	    END IF
	    SELECT COUNT(*) INTO nUniqRows
	       FROM temp_dist
	    LET nCardinality = nTotRows / nUniqRows
            DISPLAY "                   ", aIdxName[nI], 
	       "            ", nUniqrows USING "#####&",
	       "  ", nCardinality USING "####&.&&"
	 END FOR
	 DISPLAY " "
	 LET cSql = "DROP TABLE temp_dist"
	 PREPARE pDrop2 FROM cSql
	 EXECUTE pDrop2

      END FOREACH
      CLOSE qIndexes
   END FOREACH
   CLOSE qTables
END MAIN
@EOF

chmod 755 ifx_utils/idxstat.4gl

chmod 755 ifx_utils

exit 0
------------------------------- Cut here -----------------------------------