Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    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)

    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.

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    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-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts