I know theres a ESQL and 4GL version. I have even heard that theres a PERL version, but what I want to know is if anyone has written a pure SQL version that will generate update statistics for IDS version 9.x according to IBMs performance manual rules? I am talking a complete clean sweep, not just medium and highs on indexed columns.

Am I dreaming? If such a thing exists would you share please!

There are several scripts (including sql scripts) available at http://www.iiug.org

Hi,

Check www.tek-tips.com.

Click "View All" next to "Programmers", click "View All" under "DBMS Packages",
click "IBM:Informix Dynamic Server", click "FAQ" tab and the script is under "Utilities".

I'm using AIX 5.1, IDS 9.30.UC6 and this setup works well for me.

Good luck

Try this - it's what I use:




OUTPUT TO "upd_stats.sql" WITHOUT HEADINGS
SELECT "UPDATE STATISTICS LOW DROP DISTRIBUTIONS;", "-- initialise", "--"||1
FROM systables
WHERE tabid = 1
UNION
SELECT "UPDATE STATISTICS MEDIUM;", "-- overall statistics", "--"||2
FROM systables
WHERE tabid = 1
UNION
SELECT UNIQUE "UPDATE STATISTICS LOW FOR TABLE ", TRIM(tabname) || "(" ||
TRIM(colname) || ");", "--"||3
FROM systables t, syscolumns c, sysindexes i
WHERE t.tabid = c.tabid
AND i.tabid = t.tabid
AND t.tabid > 99
AND t.tabtype = "T"
AND (i.part2 = c.colno OR i.part3 = c.colno OR i.part4 = c.colno
OR i.part5 = c.colno OR i.part6 = c.colno OR i.part7 = c.colno
OR i.part8 = c.colno OR i.part9 = c.colno OR i.part10 = c.colno
OR i.part11 = c.colno OR i.part12 = c.colno OR i.part13 = c.colno
OR i.part14 = c.colno OR i.part15 = c.colno OR i.part16 = c.colno)
UNION
SELECT UNIQUE "UPDATE STATISTICS HIGH FOR TABLE ", TRIM(tabname) || "(" ||
TRIM(colname) || ");", "--"||4
FROM systables t, syscolumns c, sysindexes i
WHERE t.tabid = c.tabid
AND i.tabid = t.tabid
AND i.part1 = c.colno
AND t.tabid > 99
AND t.tabtype = "T"
UNION
SELECT UNIQUE "UPDATE STATISTICS FOR ROUTINE ", TRIM(procname) || ";", "--"||5
FROM sysprocedures
WHERE owner = 'cshbet'
order by 3;