Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: update stats generator

    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!

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    There are several scripts (including sql scripts) available at http://www.iiug.org
    rws

  3. #3
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    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

  4. #4
    Join Date
    Aug 2003
    Posts
    68
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •