Modern Information Technology based on DB2 is often saving the full history for any data. At some point in time most installations is faced with the challenge of deleting some of all these historic data. When this happens it is mainly to acheive better performance. Then it turns out that the deletion itself is very difficult to carry out within reasonable time.

If your tables are based upon partitioned tablespaces you can get some help using the parameter LOCKPART YES. LOCKPART YES can be set with an ALTER TABLESPACE and it demands that the tablespace is in the stopped state while the ALTER is carried out. Afterwards it is possible to use a LOCK TABLE with a PART parameter.

Let us assume that we have a tablespace with five partitions and the partitioning is not by age. Now we want to delete all rows more than three years old using SQL. Before LOCKPART YES it was only possible using a tablespace scan across all five partitions. After LOCKPART YES it is possible to let five parallel tasks do the job on each partition.

Each of the five parallel tasks must issue a LOCK TABLE using the PART parameter followed by a DELETE FROM using a WHERE clause that limits the deletion to the partition used in the PART parameter of the LOCK TABLE statement. Now you can submit these five jobs at the same time and thereby execute them in parallel. Of course all your indexes are of type 2 so you don't have to worry about type 1 indexes.