Hi,

I need to create one script for update stats.

Keeping the below points in consideration.

Could any one help please.

Identify tables/indexes/packages etc that should have stats and don’t
1. List tables/indexes/package without stats
a. That aren’t volatile
b. That aren’t temporary
2. List tables/indexes that automatic stats is disabled on…

Identify tables/indexes that have stale stats
1. List tables/indexes with stale stats
a. Age of stats – probably each table / index should have updated statistics say every 6 weeks?
b. Where the IUD counter indicates they should be scheduled
2. Where the frequency of IUD activity is significant prior to the schedule statistical run
3. Where bulk loads etc are performed
4. Packages that haven’t had stats run on them when their tables have been.

Identify tables / indexes with inappropriate statistical profiles
1. List stats that use a non-automatic stats profile
2. Packages that use static sql verses dynamic sql (ie statistics bound during compilation)

Operational performance
1. Automatic statistics enabled
2. Minutes to run statistics per object
3. check that there hasn’t been an operational issue during run stats eg for DB2
a. SQL2314W Some statistics are in an inconsistent state
b. STAT_HEAP_SZ is too small

It will be grateful. Thanks a lot in advance.

a lot of questions
stats_time is available in catalog to check the last runstats
loads are not registered in db - only entry in diag.log
you could create a table (as we do) with table-schema-interval-last stats... to control the fix interval between stats.. pctfree can be changed if interval is too small
other cols in catalog could be monitored - as overflow....
some doc in ic
Using RUNSTATS - IBM DB2 9.7 for Linux, UNIX, and Windows