Administer > Database Cleanup utility
Database Cleanup utility objects
The Database Cleanup utility refers to the CLEANCONF table to determine which tables and which rows to delete when a particular object and object type are specified. The following table describes preconfigured deletion scenarios from the CLEANCONF table. You can configure the own deletion objects by adding similar rows to the CLEANCONF table.These scenarios are examples only.
To get an accurate listing of all statements, see the cleanconf rows in the wcs.staging.xml file:
WC_INSTALL/schema/database_type/wcs.staging.xml
Preconfigured deletion scenarios from the CLEANCONF table...
OBJECTNAME TYPE STATEMENT account obsolete delete from trading where markfordelete = 1 and trdtype_id = 0 and trading_id not in (select account_id from trading where account_id is not null) and trading_id not in (select distinct account_id from ordpaymthd where account_id is not null) address obsolete delete from address where status = 'T' and (sysdate - ?) >= lastcreate 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)) atp_inventory obsolete delete from receipt where qtyonhand = 0 and qtyinkits = 0 and receipt_id not in (select distinct receipt_id from ordpickhst where receipt_id is not null) and receipt_id not in (select distinct receipt_id from ordshiphst where receipt_id is not null) attachment markedfordelete delete from attachment where markfordelete =1 attachment obsolete delete from attachment where (sysdate - ?) >= timeupdated and (attachusg_id= 'Contract' or attachusg_id= 'RFQ' or attachusg_id= 'PattributeAttachment') and attachment_id not in (select distinct attachment_id from trdattach) and attachment_id not in (select distinct attachment_id from pattrvalue where attachment_id is not null) auction retracted delete from auction where austatus = 'R' and (sysdate - ?) >= updatetime auction settlement_closed delete from auction where austatus = 'SC' and (sysdate - ?) >= updatetime auctionlog obsolete delete from auctionlog where (sysdate - ?) >= actiontime autobidlog obsolete delete from autobidlog where (sysdate - ?) >= actiontime baseitem obsolete delete from baseitem where markfordelete = 1 and baseitem_id not in (select baseitem_id from catentry where baseitem_id is not null) and baseitem_id not in (select distinct baseitem_id from itemspc where markfordelete = 0 and itemspc_id in (select distinct itemspc_id from orderitems where itemspc_id is not null) or itemspc_id in (select distinct itemspc_id from oicomplist where itemspc_id is not null) or itemspc_id in (select distinct itemspc_id from versionspc where versionspc_id in (select distinct versionspc_id from receipt)) or itemspc_id in (select distinct itemspc_id from radetail) or itemspc_id in (select distinct itemspc_id from bkordalloc) or itemspc_id in (select distinct itemspc_id from invreserve where itemspc_id is not null) or itemspc_id in (select distinct itemspc_id from rmaitem) or itemspc_id in (select distinct itemspc_id from rmaitemcmp) or itemspc_id in (select distinct itemspc_id from catentry where itemspc_id is not null)) bidlog obsolete delete from bidlog where (sysdate - ?) >= actiontime cacheivl obsolete delete from cacheivl where (sysdate - ?) >= inserttime calculation_code markedfordelete delete from calcodedsc where calcode_id in (select calcode_id from calcode where published = 2) calculation_code markedfordelete delete from calcodemgp where calcode_id in (select calcode_id from calcode where published = 2) calculation_code markedfordelete delete from catencalcd where calcode_id in (select calcode_id from calcode where published = 2) calculation_code markedfordelete delete from shpmodclcd where calcode_id in (select calcode_id from calcode where published = 2) calculation_code markedfordelete delete from rldiscount where calcode_id in (select calcode_id from calcode where published = 2) calculation_code obsolete delete from calcode where published = 2 and calcode_id not in (select distinct calcode_id from ordadjust where calcode_id is not null) and calcode_id not in (select distinct calcode_id from stencalusg where calcode_id is not null) and calcode_id not in (select distinct calcode_id from ordcalcd where calcode_id is not null) and calcode_id not in (select distinct calcode_id from ordicalcd where calcode_id is not null) catalog_group obsolete delete from catgroup where markfordelete = 1 catentry made_to_order delete from catentry where state='D' catentry without_orderitems delete from catentry where markfordelete = 1 and ((sysdate - ?) >= lastupdate or lastupdate is null) and catentry_id not in (select distinct catentry_id from auction) and catentry_id not in (select distinct catentry_id from orderitems where catentry_id is not null) and catentry_id not in (select distinct catentry_id from oicomplist where catentry_id is not null) and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null) and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems where offer_id is not null) ) catentry without_orderitems_iitems delete from catentry where markfordelete = 1 and ((sysdate - ?) >= lastupdate or lastupdate is null) and catentry_id not in (select distinct catentry_id from auction) and catentry_id not in (select distinct catentry_id from orderitems where catentry_id is not null) and catentry_id not in (select distinct catentry_id from oicomplist where catentry_id is not null)and catentry_id not in (select distinct catentry_id from iitem) and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null) and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems where offer_id is not null)) contract obsolete delete from trading where markfordelete = 1 and trdtype_id = 1 and trading_id not in (select distinct trading_id from orderitems where trading_id is not null) and trading_id not in (select distinct trading_id from rma where trading_id is not null) and trading_id not in (select distinct trading_id from ordpaymthd where trading_id is not null) and trading_id not in (select distinct account_id from ordpaymthd where account_id is not null) contract obsolete delete from productset where markfordelete = 1 and productset_id not in (select distinct productset_id from tradeposcn where productset_id is not null and tradeposcn_id in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems where offer_id is not null ) )) contract obsolete delete from tradeposcn where markfordelete = 1 and tradeposcn_id not in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems where offer_id is not null)) contract obsolete delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'ContractSubmit') and entity_id not in (select trading_id from trading) contract obsolete delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'ContractSubmit') and entity_id not in (select trading_id from trading) coupon_promotion expired delete from cppmn where (sysdate - ?) >= enddate cpgnlog all delete from cpgnlog cpgnstats all delete from cpgnstats expected_inventory_record_details obsolete delete from radetail where markfordelete = 1 and radetail_id not in (select distinct radetail_id from receipt where radetail_id is not null) expected_inventory_records obsolete delete from ra where markfordelete = 1 and ra_id not in (select distinct ra_id from receipt, radetail where receipt.radetail_id = radetail.radetail_id) experiment obsolete delete from expresult where experiment_id in (select experiment_id from experiment where status = 'D') experiment obsolete delete from expstats where experiment_id in (select experiment_id from experiment where status = 'D') experiment obsolete delete from experiment where status = 'D' explog obsolete delete from explog where (sysdate - ?) >= created fileupload obsolete delete from fileupload where (sysdate - ?) >= uploadtime forummsg obsolete delete from forummsg where msgstatus = 'D' or (sysdate - ?) >= posttime fulfillment_center obsolete delete from ffmcenter where markfordelete = 1 and ffmcenter_id not in (select distinct ffmcenter_id from radetail where ffmcenter_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from inventory where ffmcenter_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from rma where ffmcenter_id is not null ) and ffmcenter_id not in (select distinct ffmcenter_id from orderitems where ffmcenter_id is not null) and ffmcenter_id not in (select distinct allocffmc_id from orderitems where allocffmc_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from store where ffmcenter_id is not null) and ffmcenter_id not in (select distinct rtnffmctr_id from store where rtnffmctr_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from receipt where ffmcenter_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from auction where ffmcenter_id is not null) and ffmcenter_id not in (select distinct ffmcenter_id from auctionlog where ffmcente inventory_adjustment_codes obsolete delete from invadjcode where markfordelete = 1 and invadjcode_id not in (select distinct invadjcode_id from invadjust where invadjcode_id is not null) inventory_adjustments obsolete delete from invadjust where (sysdate - ?) >= adjustmentdate itemspecification obsolete delete from itemspc where markfordelete = 1 and itemspc_id not in (select distinct itemspc_id from orderitems where itemspc_id is not null) and itemspc_id not in (select distinct itemspc_id from oicomplist where itemspc_id is not null) and itemspc_id not in (select distinct itemspc_id from versionspc where versionspc_id in (select distinct versionspc_id from receipt)) and itemspc_id not in (select distinct itemspc_id from radetail) and itemspc_id not in (select distinct itemspc_id from bkordalloc) and itemspc_id not in (select distinct itemspc_id from invreserve where itemspc_id is not null) and itemspc_id not in (select distinct itemspc_id from rmaitem ) and itemspc_id not in (select distinct itemspc_id from rmaitemcmp) and itemspc_id not in (select distinct itemspc_id from catentry where itemspc_id is not null) message obsolete delete from message where message_id not in (select message_id from msgmemrel) or (sysdate - ?) >= posttime msgmemrel obsolete delete from msgmemrel where message_id in (select m.message_id from message ms, msgmemrel m where ms.message_id = m.message_id and (status = 'D' or ((status = 'O' or sendstat = 'S') and (sysdate - ?) >= posttime))) order canceled delete from orders where status ='X' and (sysdate - ?) >= lastupdate 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)) order canceled delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order canceled delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order completed delete from orders where status = 'C' and (sysdate - ?) >= lastupdate 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)) and orders_id not in (select orders_id from orderitems where orderitems.status != 'C') order completed delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order completed delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order deposited delete from orders where status ='D' and (sysdate - ?) >= lastupdate 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)) order deposited delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order deposited delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order markfordelete delete from orderitems where orders_id in (select orders_id from orders where type = 'BIN' and status = 'J') order markfordelete delete from orders where type = 'TRH' and status = 'X' order quotation delete from orders where orders_id in (select child_id from ordquotrel where reltype='markedForDelete') order quotation delete from ordquotrel where child_id is null and reltype='markedForDelete' order shipped delete from orders where status ='S' and (sysdate - ?) >= lastupdate 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)) order shipped delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order shipped delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order stale_guest delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (sysdate - ?) >= lastupdate 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)) order stale_guest delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order stale_guest delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order stale_non_guest delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (sysdate - ?) >= lastupdate 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)) order stale_non_guest delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) order stale_non_guest delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'OrderProcess') and entity_id not in (select orders_id from orders) organization specified delete from member where member_id in (select orgentity_id from orgentity where orgentity_id = ?) pastats all delete from pastats PaymentRule obsolete delete from edporder where order_id not in (select orders_id from orders) PaymentRule obsolete delete from edprma where rma_id not in (select rma_id from rma) PaymentRule obsolete delete from ppcpayinst where order_id not in (select orders_id from orders) or rma_id not in (select rma_id from rma) PaymentRule sensitivedelete delete from ppcextdata where ppcpayinst_id in (select ppcpayinst_id from ppcpayinst where order_id in (select orders_id from orders where status='D' and (sysdate - ?) >= lastupdate ) or rma_id in (select rma_id from rma where status='CLO' and (sysdate - ?) >= lastupdate)) pcstats all delete from pcstats pestats all delete from pestats policy obsolete delete from policy where (sysdate - ?) > endtime and policy_id not in (select distinct policy_id from ordpaymthd where policy_id is not null) and policy_id not in (select distinct policy_id from rma where policy_id is not null) productset obsolete delete from productset where markfordelete = 1 and productset_id not in (select productset_id from tradeposcn where productset_id is not null) productset obsolete_tradposcn delete from productset where productset_id in (select productset_id from tradeposcn where productset_id is not NULL and markfordelete = 1 and type = 'C') rfq obsolete delete from trading where markfordelete = 1 and trdtype_id in (2, 3, 4) and trading_id not in (select distinct trading_id from orderitems where trading_id is not null) and trading_id not in (select distinct trading_id from rma where trading_id is not null) and trading_id not in (select distinct trading_id from ordpaymthd where trading_id is not null) and trading_id not in (select distinct account_id from ordpaymthd where account_id is not null) rfq obsolete delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'RFQResponse') and entity_id not in (select trading_id from trading) rfq obsolete delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'RFQResponse') and entity_id not in (select trading_id from trading) rma abandoned delete from rma where status in ('PRC', 'EDT') and (sysdate - ?) >= lastupdate and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN')) and rma_id not in (select rma_id from rtnreceipt) rma approved_or_partly_approved delete from rma where status = 'PND' and (sysdate - ?) >= lastupdate and rma_id in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') ) and rma_id not in (select rma_id from rtnreceipt) rma canceled delete from rma where status = 'CAN' and (sysdate - ?) >= lastupdate rma completed delete from rma where status = 'CLO' and (sysdate - ?) >= lastupdate and rma_id not in (select rma_id from rtnreceipt) rma not_approved delete from rma where status = 'PND' and (sysdate - ?) >= lastupdate and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') ) and rma_id not in (select rma_id from rtnreceipt) rtnreasons obsolete delete from rtnreason where markfordelete = 1 and rtnreason_id not in (select distinct rtnreason_id from rtnrcptdsp where rtnreason_id is not null) and rtnreason_id not in (select distinct rtnreason_id from rmaitem where rtnreason_id is not null) sastats all delete from sastats staglog obsolete delete from staglog where stgprocessed = 1 and (sysdate - ?) >= stgstmp store markedfordelete delete from storeent where markfordelete=1 store specified delete from storeent where storeent_id = ? and type='S' tickler obselete delete from tickler where (sysdate - ?) >= lastupdate and status = 1 tradeposcn obsolete delete from tradeposcn where markfordelete = 1 and type = 'S' user guest delete from member where member_id in (select users_id from users T1 where registertype='G' and (sysdate - ?) >= prevlastsession And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') and (users_id > 0)) user guest delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'UserRegistrationAdd') and entity_id not in (select member_id from member) user guest delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'UserRegistrationAdd') and entity_id not in (select member_id from member) user registered delete from member where member_id in (select users_id from users where registertype= 'R' and (sysdate - ?) >= lastsession and (users_id not in (select member_id from orders where status != 'Q')) and (users_id > 0) and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null and status != 'Q') or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null and status != 'Q') or address_id in (select address_id from orders where address_id is not null and status !='Q')))) user registered delete from aprvstatus where flowtype_id in (select flowtype_id from flowtype where identifier = 'UserRegistrationAdd') and entity_id not in (select member_id from member) user registered delete from flinstance where flowtype_id in (select flowtype_id from flowtype where identifier = 'UserRegistrationAdd') and entity_id not in (select member_id from member) usrtraffic obsolete delete from cpgnlog where usrtraffic_id in (select usrtraffic_id from usrtraffic where (sysdate - ?) >= stmp) usrtraffic obsolete delete from usrtraffic where (sysdate - ?) >= stmp vendor obsolete delete from vendor where markfordelete = 1 and vendor_id not in (select distinct vendor_id from ra) and vendor_id not in (select distinct vendor_id from receipt where vendor_id is not null)
Related concepts
Database Cleanup utility
Configure databases for use with the staging utilities
Set the PATH environment variables for WebSphere Commerce utilities
Clean the database
Add a configuration to the Database Cleanup utility
Delete Sales Assistant statistic objects
Delete staged objects
Delete store objects
Delete user objects
Delete user traffic log objects
Delete vendor objects
Delete address objects
Delete available to promise inventory objects
Delete attachment objects
Delete auction objects
Delete auction log objects
Delete autobid log objects
Delete base item objects
Delete bid log objects
Delete cacheivl objects
Delete calculation code objects
Delete campaign objects
Delete campaign statistic objects
Delete catalog entry objects
Delete catalog group objects
Delete coupon objects
Delete expected inventory record objects
Delete details about expected inventory record objects
Delete forum message objects
Delete fulfillment center objects
Delete inventory code adjustment objects
Delete inventory adjustment objects
Delete specified item information objects
Delete member message relationship objects
Delete message objects
Delete order objects
Delete organization objects
Delete Product Advisor statistic objects
Delete Product Comparison statistic objects
Delete Product Explorer statistic objects
Delete policy objects
Delete product set objects
Delete returned item objects
Delete return reason objects
Delete account objects
Delete calculation rule objects
Delete distributor objects
Delete file upload objects
Dbclean utility script
Examples: Deleting objects