Implementing deletions with DBClean using fullselects

Technote (FAQ)


Question

How do you configure DBClean to implement deletions using fullselects?

Answer

Starting with DB2 Fix Pack 8.1.4, the DELETE statement supports deletions using a fullselect. Fullselects allow you to run a DELETE directly over a deletable result set, without having to use subselect.

As an example, the following query uses the traditional subselect approach to clean the STAGLOG table:

DELETE FROM staglog
WHERE stgrfnbr
IN ( SELECT stgrfnbr
FROM staglog
WHERE stgprocessed = 1
AND stgstmp < CURRENT TIMESTAMP - ? DAYS
FETCH FIRST 1000 ROWS ONLY )

Using a fullselect, the IN clause is not longer required and leads to better running plans:


DELETE FROM (
SELECT *
FROM staglog
WHERE stgprocessed = 1
AND stgstmp < CURRENT TIMESTAMP - ? DAYS
FETCH FIRST 1000 ROWS ONLY )

To use the fullselect technique with the Dbclean utility script, you must set the sqlmode parameter to direct (1) and update the SQL text in the CLEANCONF table to include the fullselect and the FETCH FIRST <commit> ROWS ONLY clause. The FETCH FIRST <commit> ROWS ONLY clause is used by DBClean to limit the result set and implement frequent commits. DBClean continues running the delete statement until the MAX parameter is reached.


For example, when the default mode is used, the delete in the CLEANCONF table for the STAGLOG table looks similar to this one:

DELETE FROM staglog WHERE stgprocessed = 1 AND stgstmp < CURRENT TIMESTAMP - ? DAYS

Before executing, DBClean alters the SQL text to include the FETCH FIRST <commit> ROWS ONLY clause.

When the sqlmode direct is used, the query is not altered by DBClean and it is run exactly as found in the CLEANCONF table. In this case, the query should look as in the fullselect example.

DELETE FROM ( SELECT * FROM staglog WHERE stgprocessed = 1 AND stgstmp < CURRENT TIMESTAMP - ? DAYS FETCH FIRST 1000 ROWS ONLY )


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

WebSphere Commerce Professional Edition
Maintenance

Software version:

6.0, 7.0

Operating system(s):

AIX, Linux, Solaris, Windows

Software edition:

All Editions

Reference #:

1310629

Modified date:

2008-08-14

Translate my page

Machine Translation

Content navigation