: "@(#)shar.sh 1.8" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Tuesday April 4 13:53:39 BST 2000 by informix # # Files archived in this archive: # idsupdstats.sql # readme.ids # readme.se # seupdstats.sql # #-------------------- if [ -f idsupdstats.sql -a "$1" != "-c" ] then echo shar: idsupdstats.sql already exists else echo 'x - idsupdstats.sql (1357 characters)' sed -e 's/^X//' >idsupdstats.sql <<'SHAR-EOF' XOUTPUT TO "obnoxio.sql" WITHOUT HEADINGS XSELECT "UPDATE STATISTICS LOW DROP DISTRIBUTIONS;", "-- initialise", "--"||1 XFROM systables XWHERE tabid = 1 XUNION XSELECT "UPDATE STATISTICS MEDIUM;", "-- overall statistics", "--"||2 XFROM systables XWHERE tabid = 1 XUNION XSELECT UNIQUE "UPDATE STATISTICS LOW FOR TABLE ", TRIM(tabname) || "(" || X TRIM(colname) || ");", "--"||3 XFROM systables t, syscolumns c, sysindexes i XWHERE t.tabid = c.tabid XAND i.tabid = t.tabid XAND t.tabid > 99 XAND t.tabtype = "T" XAND (i.part2 = c.colno OR i.part3 = c.colno OR i.part4 = c.colno XOR i.part5 = c.colno OR i.part6 = c.colno OR i.part7 = c.colno XOR i.part8 = c.colno OR i.part9 = c.colno OR i.part10 = c.colno XOR i.part11 = c.colno OR i.part12 = c.colno OR i.part13 = c.colno XOR i.part14 = c.colno OR i.part15 = c.colno OR i.part16 = c.colno) XUNION XSELECT UNIQUE "UPDATE STATISTICS HIGH FOR TABLE ", TRIM(tabname) || "(" || X TRIM(colname) || ");", "--"||4 XFROM systables t, syscolumns c, sysindexes i XWHERE t.tabid = c.tabid XAND i.tabid = t.tabid XAND i.part1 = c.colno XAND t.tabid > 99 XAND t.tabtype = "T" XUNION XSELECT UNIQUE "UPDATE STATISTICS LOW FOR TABLE ", TRIM(tabname) || "(" || X TRIM(colname) || ");", "--"||4 XFROM systables t, syscolumns c, sysindexes i XWHERE t.tabid = c.tabid XAND i.tabid = t.tabid XAND i.part1 = c.colno XAND t.tabid > 99 XAND t.tabtype = "T" Xorder by 3; SHAR-EOF if [ `wc -c readme.ids <<'SHAR-EOF' Xidsupdstats.sql X--------------- X XThis is an SQL script to generate UPDATE STATISTICS commands according to the Xrecommendations for Informix Dynamic Server 7.31, which is the only platform XI've tested it on at all apart from IDS.2000 on NT. It is totally vanilla, Xmakes no provision for any special cases and does not handle locking or any Xother issues gracefully, or at all. YMMV, all disclaimers apply, and it's not Xmy fault if it doesn't work. X XThe nice thing is, it doesn't require you to mess around with ESQL/C, 4GL or Xanything else. All you do is execute X X$ dbaccess your_db_name idsupdstats X Xfrom the command line, which generates a file called obnoxio.sql (vanity, thy Xname is Clown!) X XYou then issue the command X X$ dbaccess your_db_name obnoxio X Xfrom the command line, and Bob's your aunty's live-in lover. X XIf you're wondering why there's an UPDATE STATISTICS LOW on the same columns Xthat have just had UPDATE STATISTICS HIGH, there seems to be some sort of Xbug with UPDATE STATISTICS HIGH not handling individual columns correctly and Xnot doing an implicit UPDATE STATISTICS LOW. X XComments, questions and large cheques made out to "Cash" should be sent to Xobnoxio@hotmail.com, where I will largely ignore them or not, depending on how XI feel. SHAR-EOF if [ `wc -c readme.se <<'SHAR-EOF' Xseupdstats.sql X-------------- X XThis is an SQL script to generate UPDATE STATISTICS commands according to the Xrecommendations for Informix Dynamic Server 7.31, I've tested it on SE 7.24. XIt is totally vanilla, makes no provision for any special cases and does not Xhandle locking or any other issues gracefully, or at all. YMMV, all Xdisclaimers apply, and it's not my fault if it doesn't work. X XThe nice thing is, it doesn't require you to mess around with ESQL/C, 4GL or Xanything else. All you do is execute X X$ dbaccess your_db_name seupdstats X Xfrom the command line, which generates a file called obnoxio.sql (vanity, thy Xname is Clown!) X XYou then issue the command X X$ dbaccess your_db_name obnoxio X Xfrom the command line, and Bob's your aunty's live-in lover. X XComments, questions and large cheques made out to "Cash" should be sent to Xobnoxio@hotmail.com, where I will largely ignore them or not, depending on how XI feel. SHAR-EOF if [ `wc -c seupdstats.sql <<'SHAR-EOF' XOUTPUT TO "obnoxio.sql" WITHOUT HEADINGS XSELECT "UPDATE STATISTICS LOW DROP DISTRIBUTIONS;", "--"||1 XFROM systables XWHERE tabid = 1 XUNION XSELECT "UPDATE STATISTICS MEDIUM;", "--"||2 XFROM systables XWHERE tabid = 1 XUNION XSELECT UNIQUE "UPDATE STATISTICS LOW FOR TABLE " || TRIM(tabname) || "(" || X TRIM(colname) || ");", "--"||3 XFROM systables t, syscolumns c, sysindexes i XWHERE t.tabid = c.tabid XAND i.tabid = t.tabid XAND t.tabid > 99 XAND t.tabtype = "T" XAND (i.part2 = c.colno OR i.part3 = c.colno OR i.part4 = c.colno XOR i.part5 = c.colno OR i.part6 = c.colno OR i.part7 = c.colno XOR i.part8 = c.colno) XUNION XSELECT UNIQUE "UPDATE STATISTICS HIGH FOR TABLE " || TRIM(tabname) || "(" || X TRIM(colname) || ");", "--"||4 XFROM systables t, syscolumns c, sysindexes i XWHERE t.tabid = c.tabid XAND i.tabid = t.tabid XAND i.part1 = c.colno XAND t.tabid > 99 XAND t.tabtype = "T" Xorder by 2; SHAR-EOF if [ `wc -c