Here are the DB2 concepts to improve performance and avoid locking. SQL failures degrade performance. Below is the list of Technics avoid locking. You may also like DB2 Complex Interview Questions.
These Four Technics Avoid Locking in DB2
You aware that locking mechanism maintains data concurrency . However, unnecessary locking causes other applications to wait. Here are technics to avoid extra locking and to improve DB2-performance.
Issue COMMIT statements, even for READ, UPDATE, DELETE and INSERT operations of SQL. If you issue the COMMIT, it releases exclusive locks.
2. FOR READ ONLY or FOR FETCH ONLY
Use these phrases even in the SELECT statement. So that it avoids exclusive locking. This improves concurrency.
3. 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.
4. 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.
Get new content delivered directly to your inbox.