Toad World® Forums

Using automation to prevent locking the DB while updating a record

Hi,
This is a trick I learned recently and would like to share with others.
Our database serves as part of a BPM and stores many aspects of the business process. As this is an evovlving system, I sometines have to update a customer record to enable the business process to proceed.
When I have to update a record in a Production environment, I use the Query Builder to see the whole picture, save the existing state using a TEF ( select to file automation file) and then execute the update.
The problem I encountered was that as I use the same statement for the update ( after commenting out all the results besides the specific ID that I need) as the one I used in the select to file, DB2 sometimes locks many tables while doing a simple update. This is unacceptable in Production.
Using the loop data set (even for one record) or setting a variable based on a SQL and then using the variable in the update, solves this problem. The update is based on the single ID that is the result of the join, but does not query all the tables again.
Sincerely,
Shimon