DbAnalyzer - index analysis

Mimer dbanalyzer is a tool to provide optimization recommendations for a database schema, based on the analysis of primary keys, unique constraints and indexes. The current version provides the following optimization recommendations:

Remove unnecessary unique indexes that are duplicate with other unique indexes or primary keys.

Remove unnecessary unique constraints that are duplicate with any unique indexes, other unique constraints or primary keys.

Remove unnecessary indexes that are duplicate with other explicitly defined indexes and unique indexes, as well as indexes implicitly defined by unique constraints and foreign keys.

Suggest primary keys for tables without explicitly defined primary keys, based on the defined unique constraints and unique indexes.

The tool is also used to report the usage statistics of indexes and tables. For each index and table, the tool summarizes how many SQL and stored procedure statements have used the index and accessed the table, since the latest start of the server.

Command syntax

dbanalyzer is controlled by the following command-line parameters:

dbanalyzer [-m|-s] [-u user] [-p password] [-e program] [-i programpass]

             [-c schema] [-t table] [-a] [-g] [database]


dbanalyzer [-v version]

Command-line Arguments (Unix):



-u username


Ident name to be used when connecting to database server. If the switch is not given the user is prompted for a username.

To connect using OS_USER, give -u "", --username="", or leave the username empty when prompted.


Run dbanalyzer in multi user mode.


Run dbanalyzer in single user mode.

-p password


Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified as described above.

-e program


Name of a program to enter.

-i programpass


Password of program to enter.

-c schema


Name of the schema to be analyzed. If omitted, the user name is used as default schema name.

-t table


Name of the table to be analyzed. If omitted, all tables of the analyzed schema will be analyzed.



Report the usage statistics of tables and indexes.



Report the schema analysis of tables.

If neither -a nor -g is specified, schema analysis is performed by default.



Print version information.


Specifies the name of the database. If a database is not specified, the default database will be monitored.

The default database is determined by the setting of the MIMER_DATABASE environment variable.

Command examples

The following command analyzes all tables and their indexes in database DBNAME that user USRNAME with a password USRPASS has access to:

dbanalyzer --username=USRNAME --password=USRPASS DBNAME


The following command reports usage statistics of all tables and their indexes in database DBNAME that user USRNAME with a password USRPASS has access to:

dbanalyzer --username=USRNAME --password=USRPASS --statistics DBNAME


To include both schema analysis and usage statistics of the above tables and indexes:

dbanalyzer -uUSRNAME -pUSRPASS --analysis --statistics DBNAME


For all tables in a particular schema SCHNAME in the previous example:



For a particular table TBLNAME in schema SCHNAME, in database DBNAME, that user USRNAME has access to:



One may also analyze the tables and indexes that a program ident PROGNAME with password PROGPASS has access to:



VMS:On VMS, arguments are converted to lower case by default. To retain upper case characters, enclose the argument in double quotes, e.g. -p"SecretPwd".

An example with output

Consider the following table created by user SYSADM in database DB:

create table TT (c1 int not null, c2 int not null, c3 int,

    constraint TT_U unique (c1, c2));

create index TT_IDX1 on TT (c1, c2);

create index TT_IDX2 on TT (c3);


Note that table TT doesn't have a primary key, and TT_IDX1 is redundant because the unique constraint TT_U defines the same index implicitly.

Assume that 11 statements have accessed table TT, 2 of them have used index TT_IDX2, and 4 have used the implicit index created by TT_U.

Each statement may have been executed many times since the server started.

The following command is executed to analyze the table TT and report the usage of the table and its indexes:

dbanalyzer DB -u SYSADM -p SYSADM -s SYSADM --table=TT --analysis


The output of the example command looks as follows:

****Index and unique constraints optimization suggestions*****

Start analyzing table SYSADM.TT

 Used by 11 statements since server started

 Unique constraints:

   Unique constraint TT_U(c1, c2)

       Implicit index used by 4 statements since server started

 No redundant unique constraints are found

 No foreign keys are found


   Index TT_IDX2(c3)

       Index used by 2 statements since server started

   Redundant index TT_IDX1(c1, c2)

       Index used by 0 statements since server started

       Duplicate with TT_U. To remove it, use the following SQL:


  Primary key is not defined. You may change an existing unique constraint or index to primary key with the following SQL:





Usage statistics report created at 2022-05-12 15:44:01

Indexes are printed following their respective tables


Schema Name             Object Name             Type        Use Count


SYSADM                  TT                      Table       11

SYSADM                  TT_U                    Index       4

SYSADM                  TT_IDX1                 Index       0

SYSADM                  TT_IDX2                 Index       2



In this example, dbanalyzer lists the implicit and explicit indexes for the table SYSADM.TT, and checks if any of them are redundant. It identifies TT_IDX1 as a duplicate index of the unique constraint TT_U, and provides SQL statements to remove this index.

dbanalyzer identifies that no primary key is defined for the table. It suggests to use the columns of an existing unique constraints as the primary key.

The tool reports how many statements have used each table and index. These numbers are also summarized in a table in the end of the report.


A user or program ident can only see analysis of tables and indexes which it has SELECT privilege to. However, the summarized usage statistics of each table/index contain the statements used by all users and programs.

Only statistics for statements retained by the server is kept.