dbnanalys review & reports (^r)
If a DB Analysis is available to ProTop (see Generate DB Analysis), a list of reports will be displayed in a file selection box. Choose any file to open it in the ProTop Report Viewer.
These are the reports created in [PROTOPDIR]/rpt:
| Report Name | Description |
| friendlyName.metadict.rpt | Table, Index and Field definitions for the "_" (meta data) tables |
| friendlyName.dict.rpt | Table, Index and field definitions for your application tables |
| friendlyName.tbl.rpt | Application table statistics and suggested RPB values |
| friendlyName.redundant.rpt | A list of redundant indexes, if any |
| friendlyName.idxoverlap.rpt | Shows indexes that have common components in the same order (potential condidates for removal; see idxoverlap below) |
| friendlyName.rmchain.rpt | Shows RM chain summary statistics for your application tables |
| friendlyName.idxcompact.rpt | A list of indexes that should be compacted, if any (see below) |
| friendlyName.area.rpt | Existing storage area summary |
idxcompact.friendlyName.sh[bat]
Ctrl-r also creates [PROTOPDIR]/bin/idxcompact.friendlyName.sh[bat]. It contains a command line to compact each index that needs to be compacted. This script can be run manually or tweaked as you see fit and run from your scheduler. The script can be run at any time you have resource bandwidth as idxcompact only locks 1-3 index blocks at a time allowing for other simultaneous read/write activity on the index.
See the ProTop File Viewer page for instructions on how to navigate in the file viewer or hit '?' to bring up a help panel. 'x' or 'q' will exit the report display and return you to the previous screen.
*.idxoverlap.rpt con't
The idea is to show indexes that have common components in the same order. In particular if many indexes all have the same leading components and just add an additional component you might be wasting a lot of storage and suffering a performance penalty updating indexes that you do not need and quite likely do not even use.
Consider a table with 3 indexes:
Index 1:
custNum
Index 2:
custNum orderNum
Index 3:
custNum orderNum lineNum
Index 1 overlaps index 2 and 3, and 2 overlaps with index 3.
They are not, however, necessarily redundant since (in this case) it is quite likely that we want to enforce a uniqueness constraint on the fields shown. It is, none the less, useful to know about and if you are not enforcing uniqueness constraints with such indexes then you may very well be able to eliminate some of them.
In the case above you might decide to argue that you should make orderNum a single component unique index as index 2. Order numbers would then be global to the application rather than tied to the customer (which is likely to be true).
You probably wouldn’t do that with line numbers but you could also sensibly remove custNum from index 3.
This thinking works well with “queries” (FOR EACH and SQL statements) but potentially runs into problems with FIND statements because FIND only ever uses a single index and thus you need to pay attention to using just the relevant fields in a WHERE clause. And, as we all know, expecting programmers to pay attention is asking for trouble. (This particular example might be programmer resistant but more complex indexes with more complex WHERE clauses could get a lot more interesting.)