DB2ADVIS for db2 queries tuning

Unlike other databases , DB2 has a unique feature that suggests you what indexes you need to create to tune a particular query. It also tells you if there are any unused indexes so that they can be deleted to save the memory. DB2ADVIS is the command that does this.

Inorder a DB2ADVIS command to be run , the db2profile must need to be run along with EXPLAIN.DDL

Go to the server where the db2 is installed, navigate to the 'sqllib' path and enter db2profile command, as below,

abcd\> ~servername/sqllib/db2profile

• To run the EXPLAIN.DDL, first connect to the database as below,
• db2 connect to <database>
• Change the directory to where the EXPLAIN.DDL is available. EXPLAIN.DDL file is available at the sqllib/misc path..
• cd /db2/<servername>/sqllib/misc/
• Now run the EXPLAIN.DDL as below,
• db2 -tvf  EXPLAIN.DDL
• Now you are set to run the DB2ADVIS.
• For this make sure you have the query in a text file (ex: query.in), the query.in file shouldn't have any extra spaces, otherwise the DB2ADVIS will fail.
• Run the DB2ADVIS as below,
• db2advis -d <database_name> -i query.in -o queryadvis.advis
• Here, queryadvis.advis is the output file where you will have the indexes that needs be created...
• Surprisingly these suggestions be available along with the create index queries.

<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-4290849981025242"

     crossorigin="anonymous"></script>