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:
- 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.
- From the operating system, you can simply cd to [PROTOPDIR]/bin and run:
bin/dbanalys.sh <dbpath/dbname> <friendlyName>
This shell will remain in use until the report ends. Or ... - To automate it using your preferred scheduler, add
[PROTOPDIR]/bin/dbanalys.sh <dbpath/dbname> <friendlyName>
to your scheduler. - 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, remove the comment from the beginning of that line. Check back after Sunday; you should see files in your dbanalys directory that end with ".dba".
WARNING: The ProTop scheduler is single-threaded. Be sure to put any run of the dbanalys into the background by adding & to the end of the scheduler line.
Does your database analysis take too long?
If your database analysis takes too long due to the size of your database and or the size of the time window in which you have to complete the analysis, consider multi-threading it.
See $PROTOP/util/gentab.p. It generates a sample script to run tabanalys on each storage area in parallel.