There are times when a simple UPDATE or DELETE cannot be used directly on the database due to the huge amount of data stored in the table. We already covered the topic of increasing the transaction log, see post, but sometimes this is not enough. In the cases when too much data is going to be affected by our query we have to limit the query, and run the limited query several times until the whole data set is UPDATED/DELETED. Sadly standard SQL is not always implemented or the best approach to use in DB2. In DB2 there is a specific way of doing this limiting of UPDATE/DELETE queries.
Limit UPDATE
Let us assume we have a query of the type:
UPDATE ObjectTable SET status='SENT',parent_id=null
WHERE status='READY' AND sender='BBC'
If we want to limit this query only to the first 100 rows it becomes:
UPDATE ( SELECT * FROM ObjectTable
WHERE status='READY'
AND sender='BBC'
FETCH FIRST 100 ROWS ONLY )
SET status='SENT',parent_id=null
Limit DELETE
Let us assume we have a query of the type:
DELETE FROM ObjectTable o WHERE status='READY'
If we want to limit this query only to the first 100 rows it becomes:
DELETE FROM ObjectTable
WHERE id IN (SELECT id FROM ObjectTable
WHERE status = 'READY'
order by id
fetch first 100 rows only)