This blog promotes knowledge sharing through experience and collaboration. For more product information, visit our WebSphere Commerce CSE page. For easier navigation, utilize the Categories to find posts that match your interest.
DBClean Fails On Tables With No Primary Key
Depending on which DELETE statement form DBClean uses to purge data, it would fail on tables without primary-key. Out-of-the-box, this is the case for the CACHEIVL table.
The CACHEIVL table does not have a primary key, and the DELETE statement is stored in the CLEANCONF table as
DELETE FROM cacheivl
Depending on which Fix Pack and APAR combination you're running, the default execution statement form may differ. It would be either primary-key JOIN or fullselect DELETE form. For more information refer to DBClean and Lock Escalation.
In both DELETE forms, if executed using the default sqlmode (-sqlmode 0), the statement will attempt to use the primary-key join of the table. If no primary-key is found, it will replace it by '()'.
DELETE FROM(select FROM cacheivl
DBClean will terminate with the following error.
DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098,
SQLERRMC=2;-104;42601;)|ROM CACHEIVL WHERE (|<value_expr_body>,
As a workaround, you can execute dbclean using -sqlmode 1 to execute the statement as stored in the CLEANCONF table; or you can apply APAR JR53245. This APAR will automatically detect the existence of a primary-key and use the appropriate sqlmode.