In DB2, you need logic to handle SQL failures. Else, it keeps locking the objects. Due to this, other resources have to wait. And this makes your applications slower. Below is the list of Technics you can use to avoid unnecessary locking. Here is DB2 Complex Interview Questions.
Please donate. We commit to provide valuable content for you.
Top Technics To Avoid Locking
The locking mechanism avoids concurrency issues. At the same time, unnecessary locking causes other applications to wait. Try using the below technics to avoid extra locking and to improve performance.
Issue COMMIT statements, even for READ, UPDATE, DELETE and INSERT operations of SQL. If you issue the COMMIT, it releases exclusive locks.
FOR READ ONLY or FOR FETCH ONLY
Use these phrases even in the SELECT statement. So that it avoids exclusive locking. This improves concurrency.
INSERT, DELETE, and UPDATE
Use these operations at the end of the particular work. So that it releases exclusive locks. Because these operations need exclusive lock. Here you can read UOW.
FOR READ ONLY or FOR UPDATE
Declare these clauses in the CURSORS. These will avoid unnecessary exclusive locks. In the FOR UPDATE, you need to give the columns, you are going to update. Else, the exclusive lock applies to all the columns.