The all-important (to ProTop) database analysis

Having a recent database analysis stored in your PROTOPDIR/dbanalys directory is central to informing ProTop about the state of your database. Got one?

Regularly generating a database analysis and storing a copy in your ProTop installation informs the following ProTop features:

  • (^r) dbanalys review & reports
  • (t) Table Activity
  • (i) Index Activity
  • (l) LOB Activity
  • (H) Health Check
  • (^d) dump & load scripts
  • (^u) SQL update stats script

You can generate a database analysis for ProTop in a number of ways:

  1. Start ProTop RT and use the "!" command key to generate the report in the proper location.  This option will warn you that it can be a very intensive process and is best run during the off hours.
  2. From the operating system, you can simply cd to [PROTOPDIR]/bin and run dbanalys.sh [friendlyName].  If this is a large database this shell will remain in use until the report ends.  Or ...
  3. To automate it using your preferred scheduler, add
    [PROTOPDIR]/bin/dbanalys.sh [friendlyName] 
    to your scheduler.
  4. To use ProTop's scheduler, you will find an example command line in etc/schedule.cfg.

    NOTE: If you do not already have a localized version, copy etc/schedule.cfg to etc/schedule.[*].cfg. e.g. etc/schedule.yourCustId.cfg or etc/schedule.yourServerName.cfg. Edit your localized file and not the original etc/schedule.cfg file, which is overwritten at each update of ProTop.

    Edit your localized schedule.cfg and find that line that contains dbanalys.sh. In a recent version of ProTop, it will look like this:
    #+++# 0 1 * * 0 foreachdb.sh dbanalys.sh > ${PTTMP}/foreachdb_dbanal.err 2>&1
    If you have one or more databases to generate the report for, and running them at 1 AM on Sunday works for you, simply remove the comment from the beginning of that line. Check back after Sunday has passed and you should see files in your dbanalys directory that end with ".dba".