Re: script or spl for update statistics
In article <5ujprh$mcu@cssun.mathcs.emory.edu>, zeljan@laus.hr says...
> Hi,
>
> in Informix Guide To SQL Syntax there is recommendation for updating
> statistics:
> 1) run update statistics with distributions only
> 2) run update statistics high for columns with indexes
> 3) run update statistics low for multicolumn indexes
>
> I would like to do recommended update statistics regularly (via cron).
> Does anybody has SPL or script (or anything convenient) that is able to
> do such a update.
>
> +--------------------------------------------------------+
> | CC Computer Consulting d.o.o. |
> +--------------------------+-----------------------------+
> | Zeljan Silje | e-mail: zeljan@laus.hr |
> | CC Computer Consulting | Tel: +385 (20) 411-136 |
> | Janjevska 15 | +385 (1) 6552-330 |
> | 20 000 Dubrovnik | Fax: +385 (20) 411-136 |
> | Hrvatska (Croatia) | URL: http://www.laus.hr |
> +--------------------------+-----------------------------+
>
The following sql will create an sql which you can use to do update
statistics on all tables and procedures in a given database. I use this
in a script all of the time. It is a two step process, like follows
dbaccess -e glw updstats.sql then
dbaccess -e glw update_statistics.sql
the following is a copy of updstats.sql which is used to create
update_statistics.sql
Michael J. Erickson
create temp table work (
order integer,
tabname char(18),
seq integer,
update_stmnt varchar(255))
with no log in rootdbs;
create temp table tab (
tabid integer,
tabname char(18))
with no log in rootdbs;
insert into tab
select tabid,tabname
from systables
where tabid > 99
and tabtype = "T";
insert into work values (
0,
"",
0,"set isolation to dirty read");
insert into work values (
0,
"",
1,"set pdqpriority 25");
insert into work
select 99, "", 0, "update statistics for procedure " || trim(procname)
from sysprocedures;
insert into work
select 1,
tabname,
1,"update statistics medium for table " || trim(tabname) || " distributions
only"
from tab;
insert into work
select distinct
1,
tabname,
2,"update statistics high for table " || trim(tabname) ||
" (" || trim(c.colname) || ")"
from tab,
sysindexes i,
syscolumns c
where i.tabid = tab.tabid
and c.tabid = tab.tabid
and c.colno = i.part1;
insert into work
select distinct
1,
tabname,
3,"update statistics low for table " || trim(tabname) ||
" (" || trim(c.colname) || ")"
from tab,
sysindexes i,
syscolumns c
where i.tabid = tab.tabid
and c.tabid = tab.tabid
and ( c.colno = i.part2
or c.colno = i.part3
or c.colno = i.part4
or c.colno = i.part5
or c.colno = i.part6
or c.colno = i.part7
or c.colno = i.part8
or c.colno = i.part9
or c.colno = i.part10
or c.colno = i.part11
or c.colno = i.part12
or c.colno = i.part13
or c.colno = i.part14
or c.colno = i.part15
or c.colno = i.part16);
create cluster index work_ndx on work(order, tabname, seq);
--unload to "/DBTEMP/reorg/update_statistics.sql" delimiter ";"
unload to "update_statistics.sql" delimiter ";"
select update_stmnt from work;
drop table work;
drop table tab;