Unanswered: Need help to create script for update stats
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)
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
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
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be