Code to Save & Recreate Indexes

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 "./idxinf