Update Statistics script generator

The createstats script should be executed by user "informix" or the owner of the DB. Change all occurences of "sysadm" in the SQL script to this value, as well.

The script will generate a SQL script called DBname_stats.sql only for tables that are populated
(nrows <> 0), just so you're aware.

I'm using IDS 930.UC6 on an IBM 630 with AIX 5.1 so your mileage may vary.

I use the time command to provide timing on how long these steps take.

My procedure is this:

time dbaccess <DBname> << EOF
update statistics low drop distributions;
EOF <cr>


time dbaccess <DBname> << EOF
update statistics medium distributions only;
EOF <cr>

time createstats <DBname> <cr>

then run the <DBname>_stats.sql script to generate a SQL script for <DBname> database

time dbaccess <DBname> <DBname>_stats.sql <cr>

I received this script from a PeopleSoft Informix DBA and it's worked well for me.

Good luck,

Mike



#!/bin/ksh

usage() {
echo "usage: $1 database" ; exit 1
}
if (($# != 1)) && (($# != 2))
then
usage $0
fi

{
dbaccess $1 - <<EOT!
select c.tabname, colname, 1 hi, 0 lo --hi is 1 if column heads index, 0 if not
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b) --gets tables that don't have indexes
where tabtype = "T" and c.owner = 'sysadm' --tables only
and c.nrows <> 0
and part1 <> 0 --if not zero column is in index
and a.tabid = c.tabid
and a.tabid = b.tabid
and (colno = part1 or colno = part1 * -1) --if column is decending "part" columns in informix.sysindexes
union --will be negative
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part2 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part2 or colno = part2 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part3 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part3 or colno = part3 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part4 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part4 or colno = part4 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part5 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part5 or colno = part5 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part6 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part6 or colno = part6 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part7 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part7 or colno = part7 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part8 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part8 or colno = part8 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part9 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part9 or colno = part9 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part10 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part10 or colno = part10 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part11 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part11 or colno = part11 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part12 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part12 or colno = part12 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part13 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part13 or colno = part13 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part14 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part14 or colno = part14 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part15 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part15 or colno = part15 * -1)
union
select c.tabname, colname, 0 hi, 1 lo
from informix.systables c, outer(informix.sysindexes a, informix.syscolumns b)
where tabtype = "T" and c.owner = 'sysadm'
and c.nrows <> 0
and part16 <> 0
and a.tabid = b.tabid
and a.tabid = c.tabid
and (colno = part16 or colno = part16 * -1)
into temp stattab with no log;

-- # selects all tables from temp table

output to $1_stats.sql without headings
select distinct "update statistics medium for table ", trim(tabname)|| " distributions only;"
from stattab;

-- # group by tabname and colname, if hi > 0 column heads 1 or more indexes else if hi = 0 column is
-- # in 1 or more indexes but doesn't head an index. The group by insures that stats are only run once
-- # on a column no matter how many indexes it is in

output to pipe "pr -t" without headings
select "update statistics high for table ", trim(tabname)|| "(" || trim(colname) || ") ;" from stattab
where tabname <> " "
and colname <> " "
group by tabname, colname
having sum(hi) > 0
union
select "update statistics low for table ", trim(tabname)|| "(" || trim(colname) || ") ;" from stattab
where tabname <> " "
and colname <> " "
group by tabname, colname
having sum(hi) = 0
EOT!
} | cat >> $1_stats.sql