Discussion:
update stats / dostats
(too old to reply)
t***@gmail.com
2015-04-08 18:11:12 UTC
Permalink
hello

i am trying to track somewhat of a mystery.
to see when stats were last run, i have been using this sql from this site :

http://www-01.ibm.com/support/docview.wss?uid=swg21154913

database wlms;
SELECT UNIQUE tabname,colname,constructed,mode,
resolution,confidence
FROM systables t, syscolumns c, sysdistrib d
WHERE d.tabid = c.tabid AND d.colno = c.colno AND
d.tabid = t.tabid AND
t.tabname= '<table name>'
ORDER BY 1,2;

I use dostats and then re-run the sql and it does not show that the stats were just constructed.
When i manually run update stats medium/high for <table>, it will then show that stats were run.....but not every time.

Is the sql provided by IBM no longer valid for checking when stats were last run?

thanks
Mike Walker
2015-04-08 18:52:21 UTC
Permalink
This could be because the stats were not actually updated.

With dostats, you may want to look at the -b/-B and -a/-A options which are
used to filter the tables that have changed by a certain amount since the
last time stats were run, or have not had stats updated for a specified
time. These options are intended to speed up the update statistics by
limiting it to those tables that need it the most.

When running manually, you may be running into a similar thing, which is
determined by the AUTO_STAT_MODE and STATCHANGE configuration parameters.
Again these are used to limit the update stats to those tables with stale
distributions. You can override this by adding the FORCE keyword to the
update statistics to force the statistics to be rebuilt regardless.

Mike

-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of ***@gmail.com
Sent: Wednesday, April 08, 2015 12:11 PM
To: informix-***@iiug.org
Subject: update stats / dostats

hello

i am trying to track somewhat of a mystery.
to see when stats were last run, i have been using this sql from this site :

http://www-01.ibm.com/support/docview.wss?uid=swg21154913

database wlms;
SELECT UNIQUE tabname,colname,constructed,mode,
resolution,confidence
FROM systables t, syscolumns c, sysdistrib d
WHERE d.tabid = c.tabid AND d.colno = c.colno AND
d.tabid = t.tabid AND
t.tabname= '<table name>'
ORDER BY 1,2;

I use dostats and then re-run the sql and it does not show that the stats
were just constructed.
When i manually run update stats medium/high for <table>, it will then show
that stats were run.....but not every time.

Is the sql provided by IBM no longer valid for checking when stats were last
run?

thanks
Mark Scranton
2015-04-09 13:17:50 UTC
Permalink
Post by Mike Walker
This could be because the stats were not actually updated.
With dostats, you may want to look at the -b/-B and -a/-A options which are
used to filter the tables that have changed by a certain amount since the
last time stats were run, or have not had stats updated for a specified
time. These options are intended to speed up the update statistics by
limiting it to those tables that need it the most.
When running manually, you may be running into a similar thing, which is
determined by the AUTO_STAT_MODE and STATCHANGE configuration parameters.
Again these are used to limit the update stats to those tables with stale
distributions. You can override this by adding the FORCE keyword to the
update statistics to force the statistics to be rebuilt regardless.
Mike
-----Original Message-----
Sent: Wednesday, April 08, 2015 12:11 PM
Subject: update stats / dostats
hello
i am trying to track somewhat of a mystery.
http://www-01.ibm.com/support/docview.wss?uid=swg21154913
database wlms;
SELECT UNIQUE tabname,colname,constructed,mode,
resolution,confidence
FROM systables t, syscolumns c, sysdistrib d
WHERE d.tabid = c.tabid AND d.colno = c.colno AND
d.tabid = t.tabid AND
t.tabname= '<table name>'
ORDER BY 1,2;
I use dostats and then re-run the sql and it does not show that the stats
were just constructed.
When i manually run update stats medium/high for <table>, it will then show
that stats were run.....but not every time.
Is the sql provided by IBM no longer valid for checking when stats were last run?
thanks
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
One note - I believe STAT_CHANGE and AUTO_STAT_MODE is only available with IDS 11.7 and beyond. Can anyone confirm?

Thanks -
Mark Scranton
The Mark Scranton Group
www.markscranton.com

Loading...