Db2 – Can indexes be deactivated for particular queries in DB2

There are two major concerns here. First you are using significant server resource (memory, CPU, disk) on a produciton box which, presumably, is busy. This will be delaying other work. In the extreme, your huge query may fully consume some shared resource and cause other work to fail. Second, your query may be locking data others are trying to use. There are ways around this which either compromise the integrity of your results or use further server resources.

Mitigations include

• cut the job into smaller sub-jobs. If you're processing data for a year, for example, submit 12 separate monthly jobs sequentially and combine these results afterwards. Your elapsed time will be longer but your impact will be less.
• run during slack times, after-hours or weekends.
• copy the data to another server and run the job there.
• ensure the SQL is efficient and the DB adequately indexed for this task.

If this work needs to be done, and it needs to be done on this box, then you don't have much choice. Inform your management of the risks and good luck.

Indexes essentially duplicate data on disk. The idea though is the tradeoff in performance. Extra disk used, but faster response looking up data.

But since indexes do cost extra disk, one should weigh carefully whether or not the index is actually needed. If not needed, it will cost that extra disk and may even cost you in terms of performance (especially on inserts, updates, and deletes).