Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select queries directly on the backend production DB2 database?

Regards Muddu

share|improve this question
1  
What is "fine" on any particular system hugely depends on the usage of that particular system. And "long" is extremely relative. For example, with DB2 on IBM i, the system will re-evaluate access plans of a query that is running "long" (such as more than 2 seconds). And if I see a query running really long on my system or in its statement cache, then I may check whether it is written efficiently, has proper indexes, etc. –  WarrenT Jun 19 at 1:20

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.