Subject: Code to Save & Recreate Indexes
From: Daniel Martin <[email-masked]>
Date: Thu, 19 Sep 1996 19:12:16 GMT
Newsgroups: comp.databases.informix

Below is a way I used to save Index info for an entire database.

It uses a combination of shell scripts and awk to create *.sql command
files which you can then edit (if needed/desired) and execute.

It all works OK on SCO Unix 3.2v4.2, Informix 4.11 and Standard
Engine 5.01.  Even so, there are NO guarantees.  There may be
several places that you will have to edit the files below to
fit your environment, pathing, etc.

Hoping all this is at least one of:
  1) Inspirational
  2) Useful
  3) Good for a Laff

READ the internal documentation of idxsave.sh and idxdesc.sql,
especially the former.  idxsave.sh writes these new files:
    idxinfo.asc    idxdrop.sql    idxcreate.sql.

The above *.sql will work with the Database and Table you specified to
idxsave.sh to expedite the condensing of the *.idx file for that Table,
especially before and after mass INSERTs and DELETEs.  Using the combo of
idxdrop.sql and idxcreate.sql is better than "bcheck -y" because in some
cases bcheck will modify the *.dat file.

The script idx displays, as two *.sql command files, the contents of
idxinfo.asc using idx*.awk.

Dan Martin   :>)

: "idx"
if [ ! -s idxinfo.asc ]
then
  echo "\nMUST FIRST RUN \"idxsave.sh\"!\n"
  exit 1
fi
awk -f idxproc1.awk idxinfo.asc
sleep 1
awk -f idxproc2.awk idxinfo.asc
# EOF idx

: "idxsave.sh by Dan Martin (DRM/CTG) Thu Jul 02 12:36:33 CDT 1992"
#
# Be SURE to mod variables DATHD and SDB below for your application
DATHD="/u/DB/DAT        # Path to the Working Data Directory
SDB="${DATHD}/your_db"  # the WORKING DATA DIRECTORY
#
#  PURPOSE: To examine and store the count, type, and construction of all
#           existing indexes for a specific Table.  The info extracted
#           will [hopefully] be sufficient to totally DROP and to later
#           accurately re-CREATE them.  This will be accomplished through
#           a multi-step strategy outlined below.  See WARNING, also below.
#
#  REASONS: Over time, the single Hard Disk file which contains info for
#           all the indexes on a given table becomes bruised and bloated
#           from repeated INSERTs and DELETEs.  See EPILOG, way below.
#
#           Batch INSERTs to a table go much faster if there are no indexes
#           at all.  Prior to running batch DELETEs, it may be useful to
#           manually create an index or two to assist WHERE clauses.  Such
#           indexes would then be manually DROPed before running the the ISQL
#           index re-creation script (idxcreate.sql).
#
# STRATEGY: 1) Use ISQL to UNLOAD a delimited text file of table-index
#              information data.
#           2) From this data file, use awk to generate an ISQL script to
#              DROP all existing indexes on the table (idxdrop.sql).
#           3) From the same data file, use awk to generate an ISQL script
#              capable of re-CREATEing them later, including the UNIQUE
#              and CLUSTER attributes (idxcreate.sql).
#
# WARNINGS: This script has NOT been tested with any Informix products other
#           than SQL and the Standard Engine ("SE").  Even though its direct
#           operation is entirely passive, the *.sql produced by it will
#           [try to] DROP all old Indexes and CREATE all new Indexes when
#           you run them.  Prudence suggests that you should check both
#           *.sql produced (idxdrop.sql and idxcreate.sql) for accuracy and
#           completeness BEFORE turning them loose.
#
#           DO NOT use either *.sql if you have ANY indexes in the Table you
#           choose which have one or more DESC attributes.  DROPing of DESC
#           indexes is NOT implemented in this version.  Any index (simple or
#           composite) with a DESC component may not be DROPed.  Re-creation
#           of DESC indexes is also NOT implemented in this version. In fact,
#           a DESC component within a composite index will cause a fatal bug
#           in the re-creation ISQL script (idxcreate.sql).
#
#    NOTES: Use file idxdesc.sql to discover any and all DESCending indexes.
#
#           You're welcome to incorporate DESC-sensing and re-creation if
#           you think it worth the effort (I didn't).  FYI, DESC indexes
#           are denoted within the sysindexes table as negative numbers
#           in the columns part[1-8].  This causes join problems with the
#           syscolumns table, which brings up another feature-wish for
#           ISQL, a new Aggregate Function: ABS(colname)   ...this would
#           return a guaranteed-positive value for a numeric column.
#
#           The DELIMITER clause in the UNLOAD statement further below will
#           cause a fatal error in SQL Releases prior to 4.0 (they don't
#           understand it).  Simply comment it out (with {} braces) if you
#           are using < 4.0.  The awk segments below expect the pipe symbol
#           ("|") to be the field-delimiter within idxinfo.asc (also produced
#           by this script).
#
#   ===>>   With Release 5.00 of several Informix products (but NOT including
#           SE [the Standard Engine]), Table sysindexes was enhanced to
#           contain Columns part9 thru part16.  As of 9/92, this script has
#           NOT been enhanced to consider the additional sysindexes.partN.
#           It may be reassuring to keep in mind that, for any Composite Index
#           consisting of N Columns, the corresponding Row in Table sysindexes
#           will contain NULL values for Columns partN+1 thru part16 anyway.
#
#           The many machinations herein are meant to be a kludgely emulation
#           of a non-existent (but badly needed) group of ISQL statements:
#
#           REMEMBER INDEXES FOR [tabname|tablist];
#           DROP     INDEXES FOR [tabname|tablist];
#           RESTORE  INDEXES FOR [tabname|tablist];
#
# I reported the need for the above to Informix Tech Support in early 1992
# and the Tech ("Ramesh") enthusiastically agreed how useful their
# inclusion would be.  So if in future the feature ever appears, y'all
# can thank moi for it.   :>)
#
USAGE1="\n Usage: $0 site table\n"
USAGE2="\t(where \"site\"  is a SITE-string such as \"bea\", \"law\", etc.)\n"
USAGE3="\t(where \"table\" is a valid SPIMS Table-name).\n"
ACTION="Action: Creates Index-DROPing and re-CREATEing scripts for \"table\"."
if [ -z "$1" ]
then
  ERR=Y
fi
if [ -z "$2" ]
then
  ERR=Y
fi
if [ "$ERR" = "Y" ]
then
  echo "${USAGE1}${USAGE2}${USAGE3}${ACTION}\n"
  exit 0
else
  TABNAME=$2
fi
export DATHD
if [ ! -d ${SDB}.dbs ]
then
  echo "\nThe Directory \"${SDB}.dbs\" can not be found!\n"
  exit 1
fi
tput clear
echo "\nNow processing index-structure for table \"$TABNAME\"."
isql ${SDB} - << STOP
    SELECT tabid tabnum
      FROM systables
     WHERE tabname = "$TABNAME"
 INTO TEMP tabno;

 UNLOAD TO "./idxinfo.asc" DELIMITER "|"
    SELECT tabname, colname, systables.tabid, idxname,
           idxtype, "C" cl, part1, "A1" part, "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE sysindexes.clustered = "C"          AND
           systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part1 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           idxtype, "N", part1, "A2", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE sysindexes.clustered <> "C"         AND
           systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part1 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part2, "B2", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part2 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part3, "C3", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part3 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part4, "D4", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part4 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part5, "E5", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part5 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part6, "F6", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part6 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part7, "G7", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part7 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  UNION
    SELECT tabname, colname, systables.tabid, idxname,
           "X", "X", part8, "H8", "$SDB"
      FROM tabno, systables, sysindexes, syscolumns
     WHERE systables.tabid  = tabno.tabnum     AND
           systables.tabid  = syscolumns.tabid AND
           sysindexes.part8 = syscolumns.colno AND
           systables.tabid  = sysindexes.tabid
  ORDER BY 4, 8;

  DROP TABLE tabno;
STOP
chmod 666 ./idxinfo.asc 2>/dev/null
#
# NOTE-1: file ./idxinfo.asc file is constructed like:
#
#                                        +----------> U=Unique D=Dups  $5
#                                        | +--------> C=Clustered      $6
#                         +-- Table #    | | +------> Column number
#                         |              | | | +----> Union-source ID 
#                         |              | | | |  +-> UNIX pathname to DB
#   table  column_name    |   index_name | | | |  |
#   invadj|inv_adj_stk_no|400|v_invadj02|D|N|2|A2|databasepath|
#   invadj|inv_adj_stat|400|v_invadj02|X|X|9|B2|databasepath|
#   invadj|inv_adj_qty|400|v_invadj02|X|X|4|C3|databasepath|
#   invadj|inv_adj_tot_cost|400|v_invadj02|X|X|5|D4|databasepath|
#   1      2                3   4          5 6 7 8  9  (awk field #'s)
#
# NOTE-2: In cases where there are composite indexes (i.e., those indexes
#         composed of two or more columns), the field contents of the
#         5th and 6th fields above will always be "X".
#
sleep 3
tput clear
echo "\nData File of Index-Info for table \"$TABNAME\":"
ls -l idxinfo.asc
awk '
BEGIN {
        FS="|"
        print "{idxdrop.sql  created by idxsave.sh"
        print "   - for database in 2nd statement below:"
        print "}"
        print "CLOSE DATABASE;"
      }
              { if (NR == 1) print "DATABASE \"" $9 "\";"}
  $5 != "X"   {printf "DROP INDEX %18s; \t\t{table: %s}\n", $4, $1}
END {
     print "{EOF idxdrop.sql}"
    }' idxinfo.asc >idxdrop.sql
chmod 666 ./idxdrop.sql 2>/dev/null
echo "\nSQL script to CHECK for DESC INDEXES in Table \"${TABNAME}\":"
echo "CLOSE DATABASE;" >./idxdesc.sql
echo "DATABASE \"${SDB}\";" >>./idxdesc.sql
cat idxdesc.sqt >>./idxdesc.sql
chmod 666 idxdesc.sql 2>/dev/null
ls -l idxdesc.sql
echo "\nSQL script to DROP ALL INDEXES for Table \"$TABNAME\":"
ls -l idxdrop.sql
# FS="|"; print "{idxcreate.sql  created by idxsave.sh}" }
awk '
BEGIN {
        FS="|"
        print "{idxcreate.sql  created by idxsave.sh"
        print "   - for database in 2nd statement below:"
        print "}"
        print "CLOSE DATABASE;"
      }
  { if (NR == 1) print "DATABASE \"" $9 "\";"}
  {if (idxname != $4 && NR != 1)
         print ");"
  }
  {if ($5 == "U")
        idxty = "UNIQUE"
   else idxty = "{DUPS}"
  }
  {if ($6 == "C")
        clust = "CLUSTER"
   else clust = "{NO CL}"
  }
  {if ($5 != "X")
        printf "CREATE %s %s INDEX %s ON %s(%s", idxty, clust, $4, $1, $2
   else printf ", %s", $2
  }
  {idxname = $4}
END {
     print ");"
     print "{EOF idxcreate.sql}"
    }' idxinfo.asc >idxcreate.sql
chmod 666 ./idxcreate.sql 2>/dev/null
echo "\nSQL script to RE-CREATE ALL INDEXES for Table \"$TABNAME\":"
ls -l idxcreate.sql
echo "\nRespective invocation of the *.sql scripts are as per below:\n
isql inpo_mt idxdesc
isql inpo_mt idxdrop
isql inpo_mt idxcreate\n"
echo "The path to the real database is set within the three *.sql above.\n"
echo "CAUTION: consult internal documentation of idxsave.sh AND idxdesc.sql."
echo "\tALWAYS run idxdesc.sql BEFORE running the other two!\n"
#
#   EPILOG: According to Informix Tech "Jeanette", an *.idx file is only
#           re-built when a CLUSTER index is created (apparently this is
#           the analogue to the ALTER TABLE statement for a *.dat file).
#
#           Jeanette says that the new ISQL features I have notated herein
#           have been given Request Numbers 2131 and 2132.
#
#           Thru testing I verified that, even after ALL indexes for a
#           table are DROPed, the *.idx file stays the same size!  In the
#           "real-life" example below, there were >10,000 rows in the table.
#
#  BEFORE (still "virgin"):
#    -rw-rw----   1 spims    other     157696 Apr  8 21:15 m_rec__135.dat
#    -rw-rw----   1 spims    other    1246208 Apr  8 21:15 m_rec__135.idx
#  AFTER DROPPING ALL INDEXES (6):
#    -rw-rw----   1 spims    other     157696 Apr  8 21:15 m_rec__135.dat
#    -rw-rw----   1 spims    other    1246208 Apr  9 11:56 m_rec__135.idx
#  AFTER RE-CREATING ALL INDEXES (6), WITH ONE AS A CLUSTER TYPE:
#    -rw-rw----   1 spims    other     157696 Apr  9 11:57 m_rec__135.dat
#    -rw-rw----   1 spims    other     784384 Apr  9 12:02 m_rec__135.idx
#
#  The deeper meaning of all this is that you may wish to edit idxcreate.sql
#  so that one (and one only) index is CREATEd with the CLUSTER attribute.
#
#  CAVEAT: Creation of a CLUSTER Index causes the data file to be completely
#          re-written which: 1) is a harsh thing to do, 2) requires enough HD
#          free space to transiently store duplicates of the *.dat and *.idx
#          files, and 3) requires one to have the time and cajones to dare
#          such rash measures on really large tables.
#
# EOF idxsave.sh

# idxproc1.awk by Dan Martin (DRM/CTG) Wed Apr  8 10:27:25 CDT 1992
# - called by shell script "idx"
#   reads from ./idxinfo.asc file which is constructed like:
#
#                                        +------: U=Unique D=Dups
#                                        | +----: C=Clustered
#                         +-- table #    | | +--: column number
#   table  column_name    |   index_name | | |
#   invadj|inv_adj_stk_no|400|v_invadj02|D|N|2|part1B|databasepath|
#   invadj|inv_adj_stat|400|v_invadj02|X|X|9|part2|databasepath|
#   invadj|inv_adj_qty|400|v_invadj02|X|X|4|part3|databasepath|
#   invadj|inv_adj_tot_cost|400|v_invadj02|X|X|5|part4|databasepath|
#   1      2                3   4          5 6 7 8     9  (awk field #'s)
#
# NOTE: In cases where there are compound indexes (i.e., those indexes
#       composed of two or more columns), the field contents of the
#       5th and 6th columns above will always be "X".

BEGIN {
        FS="|"
        print "{idxdrop.sql  created by idxsave.sh"
        print "   - for database in 2nd statement below:"
        print "}"
        print "CLOSE DATABASE;"
      }
          { if (NR == 1) print "DATABASE \"" $9 "\";"}
$5 != "X" {printf "DROP INDEX %18s; \t\t{table: %s}\n", $4, $1}
END {
     print "{EOF idxdrop.sql}"
    }
# EOF idxproc1.awk

# idxproc2.awk by Dan Martin (DRM/CTG) Wed Apr  8 10:27:25 CDT 1992
# - called by shell "idx"
#   reads from ./idxinfo.asc file which is constructed like:
#
#                                        +------: U=Unique D=Dups  $5
#                                        | +----: C=Clustered      $6
#                         +-- table #    | | +--: column number
#   table  column_name    |   index_name | | |
#   invadj|inv_adj_stk_no|400|v_invadj02|D|N|2|part1B|databasepath|
#   invadj|inv_adj_stat|400|v_invadj02|X|X|9|part2|databasepath|
#   invadj|inv_adj_qty|400|v_invadj02|X|X|4|part3|databasepath|
#   invadj|inv_adj_tot_cost|400|v_invadj02|X|X|5|part4|databasepath|
#   1      2                3   4          5 6 7 8     9  (awk field #'s)
#
# NOTE: In cases where there are compound indexes (i.e., those indexes
#       composed of two or more columns), the field contents of the
#       5th and 6th columns above will always be "X".

BEGIN {
        FS="|"
        print "{idxcreate.sql  created by idxsave.sh"
        print "   - for database in 2nd statement below:"
        print "}"
        print "CLOSE DATABASE;"
      }

       { if (NR == 1) print "DATABASE \"" $9 "\";"}
  {if (idxname != $4 && NR != 1)
         print ");"
  }

  {if ($5 == "U")
        idxty = "UNIQUE"
   else idxty = "{DUPS}"
  }

  {if ($6 == "C")
        clust = "CLUSTER"
   else clust = "{NOTCL}"
  }

  {if ($5 != "X")
        printf "CREATE %s %s INDEX %s ON %s(%s", idxty, clust, $4, $1, $2
   else printf ", %s", $2
  }

  {idxname = $4}

END {
     print ");"
     print "{EOF idxcreate.sql}"
    }

# EOF idxproc2.awk

    SELECT tabid table_num, tabname table_name
      FROM systables
     WHERE tabid >= 100
 INTO TEMP tabno;

    SELECT table_num, table_name, idxname idx_name, "part1" sequence,
           colname col_name, part1 col_val
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part1       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part1 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part2", colname, part2
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part2       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part2 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part3", colname, part3
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND

           sysindexes.part3       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part3 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part4", colname, part4
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part4       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part4 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part5", colname, part5
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part5       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part5 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part6", colname, part6
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part6       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part6 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part7", colname, part7
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part7       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part7 * -1) = syscolumns.colno
  UNION
    SELECT table_num, table_name, idxname, "part8", colname, part8
      FROM tabno, sysindexes, syscolumns
     WHERE tabno.table_num        = sysindexes.tabid AND
           sysindexes.part8       < 0                AND
           tabno.table_num        = syscolumns.tabid AND
          (sysindexes.part8 * -1) = syscolumns.colno
  ORDER BY 1, 3, 4;
DROP TABLE tabno;

{idxdesc.sql     (as created by idxsave.sh)

     FROM: idxdesc.sqt by Dan Martin (DRM/CTG) Mon Apr 13 16:33:34 CDT 1992

  PURPOSE: To see if there are ANY Indexes in the non-system tables that
           have been created with the DESC attribute.  FYI, DESC indexes
           are denoted within the sysindexes table as negative numbers
           in the columns part[1-8].  This would cause join problems with
           the syscolumns table, so we will multiply them by -1.

  CAUTION: Use this to determine if there ANY (at all) DESC-type indexes
           BEFORE running the script "idxsave.sh", ESPECIALLY "idxdrop.sql".

           NO ROWS will be "found" if there are NO DESC indexes.
           In this case, it is OK to run idxdrop.sql and idxcreate.sql.

           If any Rows ARE found, DO NOT run either idxdrop.sql
           or idxcreate.sql (you have been warned).

 SEE ALSO: Internal documentation within idxsave.sh, and ./README
}

{EOF idxdesc.sql}

End of E-Mail from [email-masked]