We recently added some SQL indexes based on the advice by the Index Adviser.  Since adding those indexes, should we reset usage statistics or is there anything we need to do for having updated information since adding those?   What are the best steps to do after adding SQL indexes to get up-to-date advice there?  Is there really anything we need to do for that?

------------------------------
Amy Vozza
------------------------------

#SQL

After having added an index based on the index advisor, I'd suggest to just delete the advise.
Birgitta

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

I agree with deleting the index advice for a table after creating those indexes.

If the newly created indexes have similar key definitions to existing indexes and you want to closely track which indexes the Db2 optimizer is using going forward, then you could reset the index usage statistics to make that comparison easier.  However - it's not required.

------------------------------
Kent Milligan
------------------------------

I recently came across the RUNSTATS command. Do you recommend calling this after an index is created as well?

------------------------------
Mike Zaringhalam
------------------------------

Original Message:
Sent: Mon April 04, 2022 11:22 AM
From: Kent Milligan
Subject: When to Reset Usage Statistics or Steps After Adding Indexes

I agree with deleting the index advice for a table after creating those indexes.

If the newly created indexes have similar key definitions to existing indexes and you want to closely track which indexes the Db2 optimizer is using going forward, then you could reset the index usage statistics to make that comparison easier.  However - it's not required.

------------------------------
Kent Milligan
------------------------------

The RUNSTATS command is for the other Db2 products.  If you have the QDBFSTCCOL system value set to *ALL, then Db2 for i is effectively automatically running it's version of that command for you.

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Wed April 20, 2022 12:28 PM
From: Mike Zaringhalam
Subject: When to Reset Usage Statistics or Steps After Adding Indexes

I recently came across the RUNSTATS command. Do you recommend calling this after an index is created as well?

------------------------------
Mike Zaringhalam
------------------------------


Original Message:
Sent: Mon April 04, 2022 11:22 AM
From: Kent Milligan
Subject: When to Reset Usage Statistics or Steps After Adding Indexes

I agree with deleting the index advice for a table after creating those indexes.

If the newly created indexes have similar key definitions to existing indexes and you want to closely track which indexes the Db2 optimizer is using going forward, then you could reset the index usage statistics to make that comparison easier.  However - it's not required.

------------------------------
Kent Milligan