DB2 12 for z/OS SQL Enhancements: Piece-Wise DELETE

DB2 12 for z/OS, which became generally available in October of 2016, delivered a number of key enhancements pertaining to SQL statements. Over the course of a few blog entries, I'll describe some of my favorites among these enhancements. This first post in the series will cover piece-wise DELETE.

First, the backdrop: suppose you have a need to remove a large number of rows from a table, using SQL (I say, "using SQL," because a utility-based alternative, REORG with DISCARD, is not always feasible or desirable). How might you do that? Well, if the requirement is to remove all rows from table T1 that have a value greater than 100 in column C1, you could simply execute this statement:

DELETE FROM T1 WHERE C1 > 100;

Done, right? No so fast. How many rows are qualified by the C1 > 100 predicate? Suppose T1 has 500 million rows, and 50 million of those rows have a value greater than 100 in column C1? In that case, what happens when the DELETE statement shown above is executed? For one thing, the application process associated with the DELETE will potentially acquire a LOT of page or row locks (depending on whether LOCKSIZE PAGE or LOCKSIZE ROW is in effect for the table space in which T1 resides). That, in turn, could lead to the deleting program getting an error SQL code if the limit on the number of locks that can be acquired by one process (NUMLKUS in ZPARM) is reached. If that limit is not reached because lock escalation occurs (as it might, depending on the NUMLKTS value in ZPARM and the LOCKMAX setting in effect for T1's table space), other programs will not be able to access T1 until the DELETE statement completes and a COMMIT is issued by the deleting program. If the value of NUMLKUS in ZPARM is high enough so as to not be hit when the DELETE executes (or if NUMLKUS is set to 0, meaning there is no limit on the number of locks that can be held at one time by one process), and if lock escalation does not occur (because NUMLKTS is set to 0 or to a very high number, or if the table space's LOCKMAX value is 0 or a very high number), IRLM could run short on storage because of all the lock control blocks (and, in a DB2 data sharing environment, the lock list portion of the lock structure could fill up) -- not good for system stability. Oh, and one more thing: suppose that 50-million-row-impacting DELETE statement gets through 49 million rows and then encounters a problem that causes it to fail. What are you looking at then? One BIG rollback operation.

OK, so removing lots and lots of rows from a table with a single DELETE statement can be problematic. Is there a SQL alternative? Yes, but the alternative available in a pre-DB2 12 environment is not one about which an application developer would be enthused. I'm talking here about a cursor-based DELETE approach. That would involve writing a program that declares a cursor through which the to-be-deleted rows would be identified (using the preceding example, row identification would be done via a SELECT from T1 with the WHERE C1 > 100 predicate), with the DECLARE CURSOR statement including the WITH HOLD option to maintain cursor position through commits (the FOR UPDATE clause might also be required for the DECLARE CURSOR statement). The program would then OPEN the cursor, FETCH a row, and perform a DELETE WHERE CURRENT OF operation. Then FETCH another row, do another DELETE WHERE CURRENT OF, and so on, with commits performed periodically so as to release acquired locks, until all to-be-removed rows have been deleted from the target (a variant of this approach involves deleting blocks of rows using a cursor declared WITH ROWSET POSITIONING). Do-able, yes, but kind of clunky from a programming perspective. Wouldn't it be nice if there were a SQL mechanism through which large-scale DELETEs could be accomplished in a way that would be programmer-friendly while at the same time avoiding the problems associated with massive accumulation of locks (or with lock escalation)? Well, thanks to DB2 12 for z/OS (at function level V12R1M500 or above), that mechanism is here. It's called piece-wise DELETE.

Using piece-wise DELETE, how easy is it to perform a large-scale row-removal operation that does not gum up the works, concurrency-wise? This easy (and here I'll assume that we want to remove rows from the target table in chunks of 500):

1. Issue DELETE FROM T1 WHERE C1 > 100 FETCH FIRST 500 ROWS ONLY;
2. Issue a COMMIT
3. Repeat steps 1 and 2 until all to-be-removed rows have been deleted

The part of the DELETE statement highlighted in red above is syntax that's newly supported with DB2 12 (again, at function level V12R1M500 or above): FETCH FIRST n ROWS ONLY can be coded in a DELETE statement, and doing that makes the DELETE of the piece-wise variety. Every execution of the statement (as coded above) will remove 500 rows from the target table.

What if you sometimes want to use your piece-wise DELETE program to remove rows in chunks of 50, or 1000, instead of 500? No prob. You can use a variable to specify the to-be-deleted quantity in the DELETE statement's FETCH FIRST clause. And, if you're using a variable to provide the FETCH FIRST value, you can change the row-removal "chunk" size as a large-scale DELETE operation is executing.

There you have it -- an easy-to-program means of breaking up a DELETE operation into bite-sized pieces (or bite-sized units of work, in more technical parlance). In blog posts to come I'll cover others of my favorite DB2 12 SQL enhancements. I hope you'll check those out.