WebSphere Commerce Database Cleanup utility

WebSphere Commerce Database Cleanup activity- Some Imp tables

Go to WCS root directory i.e
cd /opt/IBM/WebSphere/CommerceServer70/bin

1.-------------------------------cleaning records of scheduled jobs that have been completed
query for finding entries in SCHSTATUS table
db2 " select count(*) FROM SCHSTATUS WHERE (SCSSTATE = 'C' OR SCSSTATE = 'CF') AND (days(CURRENT TIMESTAMP) - days(SCSEND) >= 30)"
Cleaning SCHSTATUS table entries which are older than 30 day's
./dbclean.sh -object scheduled_job -type completed -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 30 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 10000 -max 1000000

2.--------------------------------Cleaning processed cacheivl
db2 " select count(*) from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= 30"
./dbclean.sh -object cacheivl -type obsolete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 30 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 100000 -max 100000

3.-------------------------------cleaning address which are deleted

db2 " select count(*) from address where status = 'T' and (days(CURRENT TIMESTAMP) - days(lastcreate)) >= 30 and (address_id not in (select distinct address_id from orderitems where address_id is not null)) and (address_id not in (select distinct address_id from orders where address_id is not null)) and (address_id not in (select distinct allocaddress_id from orderitems where allocaddress_id is not null))"

./dbclean.sh -object address -type obsolete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 30 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 50000

4.----------------delete guest users

db2 "select count(*) from member where member_id in (select users_id from users T1 where registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= 30 And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') and (users_id > 0))"

./dbclean.sh -object user -type guest -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 30 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 5000 -max 200000

5.----------------------stale guest orders

db2 "select count(*) from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= 60 and orders.member_id in (select distinct users_id from users where registertype = 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null)) and orders_id not in (select orders_id from orderitems where orderitems_id in (select orderitems_id from rmaitem where orderitems_id is not null))"

./dbclean.sh -object order -type stale_guest -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 60 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 50000

6.----------------------stale non-guest orders

db2 "select count(*) from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= 120 and orders.member_id in (select distinct users_id from users where registertype != 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null)) and orders_id not in (select orders_id from orderitems where orderitems_id in (select orderitems_id from rmaitem where orderitems_id is not null))"

./dbclean.sh -object order -type stale_non_guest -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 130 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 50000

7.----------------------Obselote payment rules

db2 "select count(*) from edporder where order_id not in (select orders_id from orders)"


db2 "select count(*) from ppcpayinst where (( order_id is not null and order_id not in (select orders_id from orders)) and ( rma_id is null or (rma_id is not null and rma_id not in (select rma_id from rma)))) or (order_id is null and (rma_id is not null and rma_id not in (select rma_id from rma)))"

./dbclean.sh -object PaymentRule -type obsolete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 50000

8.-------------------------Deleted promotions

db2 "select count(*) from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdpromo where px_promotion_id in (select px_promotion_id from px_promotion where status = 2 or status = 4 and px_promotion_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))) and px_cdpool_id not in (select px_cdpool_id from px_cdusage where status = 5)"

./dbclean.sh -object promotion_code -type markfordelete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 300 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 5000

./dbclean.sh -object promotion_code -type expired -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 300 -dbuser RABpinst  -dbpasswd xxxxx@123 -loglevel INFO -commit 1000 -max 5000

9.----------------------cleaning offer which are deleted

db2 " select count(*) from offer where published = 2 and not exists (select 1 from orderitems where orderitems.offer_id = offer.offer_id)"

./dbclean.sh -object offer -type markfordelete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -dbuser RABpinst  -dbpasswd xxxxxx@123 -loglevel INFO -commit 1000 -max 500000

10. ---------------------------------cleaning all processed entries in staglog(this should be run on STG environment only)

db2 "select count(*) FROM STAGLOG WHERE STGPROCESSED = 1 AND STGSTMP <= (CURRENT TIMESTAMP - (30 DAYS))"

./dbclean.sh -object staglog -type obsolete -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/demo/xml/demo.xml -db RABPDB -days 30 -dbuser RABpinst  -dbpasswd xxxxxx@123 -loglevel INFO -commit 5000 -max 1000000


Reference Links: https://www.ibm.com/support/knowledgecenter/en/SSZLC2_7.0.0/com.ibm.commerce.admin.doc/refs/rduobjects.htm