DB Re-index Best Practices

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

Does Broadcom Support provide sql statements for rebuilding database indexes (either offline or online while Automic is running)?  Or best practices?  Possible databases would be postgresql (postgres), db2, oracle, and sql server.

Environment

Release : 12.3.9

Resolution

Support is not able to provide SQL statements on re-indexing the database as this is something that should be provided by a DBA.  Many database vendors have information in their documentation about how to achieve online re-indexing which should be looked at.  If your DBA has a suggestion they can provide, we can always provide feedback if it might impact Automic.  There's also a possibility that we have other customers who run online re-indexing (rebuild or reorganize) who may be able to provide their experiences - this would be good to reach out to our community here: https://community.broadcom.com/enterprisesoftware/communities/communityhomeblogs?CommunityKey=2e1b01c9-f310-4635-829f-aead2f6587c4 

General best practices:

• Always test database-impacting events in non-production first
• Find a time to run them the first time where there is no much running or where downtime can be taken just in case performance is affected