dbanalys Review & Reports (^r)

If ProTop has access to a database analysis, reports will be generated, and a list will be displayed.


These are the reports created in [PROTOPDIR]/rpt. Click the link to see an example:

Report Name Description
 friendlyName.area.rpt  Existing storage area summary
 friendlyName.dbparam.rpt  Database description, features, and startup parameters
 friendlyName.dict.rpt Table, index, and field definitions for your application   tables
 friendlyName.idxcompact.rpt  A list of indexes that should be compacted, if any (see   idxcompact below for more information)
 friendlyName.idxoverlap.rpt  Shows indexes that have common components in the   same order with potential candidates for removal (see   idxoverlap below)
 friendlyName.metadict.rpt  Table, index, and field definitions for the "_" (metadata)   tables
 friendlyName.redundant.rpt  A list of redundant indexes, if any
 friendlyName.rmchain.rpt  Shows RM chain summary statistics for your application   tables 
 friendlyName.servers.rpt  Database parameters common to all client/server   connections
 friendlyName.tbl.rpt  Application table statistics and suggested RPB values

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 anytime 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.

NOTE: See the ProTop File Viewer (v) page for instructions on navigating 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.

friendlyName.idxoverlap.rpt continued

The idxoverlap report aims to identify indexes with the same components in the same order. If several indexes have the same leading components and you add a new index by copying an existing definition and adding a new component, you might be wasting a lot of storage and suffering a performance penalty by updating indexes you do not need and likely do not even use.

Consider a table with three indexes:

Index 1:

custNum 

Index 2:

custNum orderNum 

Index 3:

custNum orderNum lineNum 

Index 1 overlaps with Index 2 and 3, and 2 overlaps with Index 3.

However, they are not necessarily redundant since (in this case) we likely want to enforce a uniqueness constraint on the fields shown. Knowing about them is helpful, and if you are not enforcing uniqueness constraints with such indexes, you may be able to eliminate some of them.

In the case above, you might argue that you should make orderNum in index 2 a single-component unique index. Order numbers would then be global to the application rather than tied to the customer (which is likely accurate).

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 uses a single index. 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 much more interesting.)


See also:

File Upload

View Reports (v)