- Is there any frequency at which i should run REORG and RUNSTATS on my master tables?
- If i have a daily load of around 1,000,000 records into my table would it be suggestible to run a REORG and RUNSTATS everyday after the Load?
- Are there any drawbacks to REORG and RUNSTATS?
This is one of those where the correct answer is: it depends. It all depends on your environment, on your data. What you can and should do, is test how frequently you need to run them in lower environments. Play with them under load/stress. Figure out which options you may need to use.
But to give you something to start with, and not knowing the distribution of your data, I would go with
RUNSTATS daily. Maybe
REORG once a week. Now, depending on when your
LOAD occurs, I would consider running it after the
LOAD bypasses a lot of things. You want to know the number of rows and the distribution of the data in the table(s) in order to have efficiently running queries.
REORG is to clean up lost space and improve I/O. Perhaps less important after
LOAD (depending on table fragmentation).
REORG acquire locks of sorts, so again, that is where you want to test in lower environments. If you can, run them after hours for zero impact. If you need to run them during the day, play with the options to see what you can do. The systems I happened to work on allowed for full
RUNSTATS and full offline
REORGs during the day. But we didn't have the volume of transactions others have.
Hopefully I've given you some thoughts to consider and a place to start with. The (final) answer differs for everyone.