From: Daniel Martin Subject: Code to Save & Recreate Indexes Newsgroups: comp.databases.informix Date: Thu, 19 Sep 1996 19:12:16 GMT 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 drmartin@tyrell.net