Database Cleanup utility objects

To determine which tables and which rows to delete, the Database Cleanup utility uses the CLEANCONF table.

The following table describes the object types that have SQL statements available for the Database Cleanup utility to use. The utility runs these statements to remove objects from the database. These SQL statements are stored in the CLEANCONF database table. If you need to delete different object types or delete any listed object type under different conditions, you can add rows to the CLEANCONF table that include the appropriate custom SQL statements.

These scenarios are examples only. The wcs.staging.xml file lists all statements.

  • WC_installdir/schema/database_type/wcs.staging.xml
  • WebSphere Commerce DeveloperWCDE_installdir/schema/database_type/wcs.staging.xml
Notes:
  • Replace the ? used in the examples in the statement column with values from the dbClean parameter. The possible values are: -days and -name. For more information, see Database Cleanup utility command script.
  • Introduced in Feature Pack 2The order for cleaning up content versioning objects does not matter. However, the type inactive is the superset of types catalog, catalog_group, and catalog_entry.
  • The select statements are used for diagnostic output.
  • The sequence column identifies the sequence that SQL statements run to delete an object. For example, when you are deleting guest user objects, the sequence column identifies the order that the three SQL statements run. If multiple object types are being cleaned, the sequence for one object type, such as user objects, does not affect the sequence for another object type, such as order objects.
Sample database cleanup SQL statements for objects

Sample database cleanup SQL statements for objects.
Object name Object type Sequence SQL Statement namearg daysarg Link
tickler obselete 1 delete from tickler where (days(current timestamp) - days(lastupdate)) >= ? and status = 1 no yes

tickler_obsolete
catentry made_to_order 4 delete from catentry where state='D' no no catentry_made_to _order
cacheivl obsolete 1 delete from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ? no yes cacheivl_obsolete
fileupload obsolete 1 delete from fileupload where days(current timestamp) - days(uploadtime) >=? no yes fileupload_obsolete
store markedfordelete 3 delete from storeent where markfordelete=1 no no store_markedfordelete
user registered 2 delete from member where member_id in (select users_id from users where registertype= 'R' and (days(CURRENT TIMESTAMP) - days(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')))) no yes user_registered
3 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) no no
4 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) no no
organization specified 2 delete from member where member_id in (select orgentity_id from orgentity where orgentity_id = ?) yes no organization_specified
user guest 2 delete from member where member_id in (select users_id from users T1 where registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ? And not Exists (select 1 from orders where orders.member_id=T1.users_id and status != 'Q') and (users_id > 0)) no yes user_guest
3 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) no no
4 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) no no
calculation_code markedfordelete 1 delete from calcodedsc where calcode_id in (select calcode_id from calcode where published = 2) no no calculation_code_markedfordelete
2 delete from calcodemgp where calcode_id in (select calcode_id from calcode where published = 2) no no
3 delete from catencalcd where calcode_id in (select calcode_id from calcode where published = 2) no no
4 delete from shpmodclcd where calcode_id in (select calcode_id from calcode where published = 2) no no
5 delete from rldiscount where calcode_id in (select calcode_id from calcode where published = 2) no no
address obsolete 1 delete from address where status = 'T' and (days(CURRENT TIMESTAMP) - days(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)) no yes address_obsolete
order completed 1 delete from orders where status = 'C' and (days(CURRENT TIMESTAMP) - days(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') no yes order_completed
3 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) no no
4 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) no no
order canceled 1 delete from orders where status ='X' and (days(CURRENT TIMESTAMP) - days(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)) no yes order_canceled
3 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) no no
4 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) no no
order shipped 1 delete from orders where status ='S' and (days(CURRENT TIMESTAMP) - days(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)) no yes order_shipped
3 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) no no
4 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) no no
order deposited 1 delete from orders where status ='D' and (days(CURRENT TIMESTAMP) - days(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)) no yes order_deposited
2 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) no no
3 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) no no
order stale_guest 1 delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(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)) no yes order_stale_guest
3 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) no no
4 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) no no
order stale_non_guest 1 delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(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)) no yes order_stale_non_guest
3 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) no no
4 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) no no
order quotation 1 delete from orders where orders_id in (select child_id from ordquotrel where reltype='markedForDelete') no no order_quotation
2 delete from ordquotrel where child_id is null and reltype='markedForDelete' no no
order markfordelete 1 delete from orderitems where orders_id in (select orders_id from orders where type = 'BIN' and status = 'J') no no order_markfordelete
2 delete from orders where type = 'TRH' and status = 'X' no no
catentry without_orderitems 1 delete from catentry where markfordelete = 1 and ((days(CURRENT TIMESTAMP) - days(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 ) ) no yes catentry_without_orderitems
catentry without_orderitems_iitems 1 delete from catentry where markfordelete = 1 and ((days(CURRENT TIMESTAMP) - days(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)) no yes catentry_without_orderitems_iitems
calculation_code obsolete 1 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) no no calculation_code_obsolete
calculation_rule obsolete 1 delete from CALRULE where ENDDATE is not null AND (days(CURRENT TIMESTAMP) - days(ENDDATE) >= ?) no yes calculation_rule_obsolete
fulfillment_center obsolete 1 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 ffmcenter_id is not null) no no fulfillment_center_obsolete
store specified 1 delete from storeent where storeent_id = ? and type='S' yes no store_specified
itemspecification obsolete 1 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) no no itemspecification_obsolete
baseitem obsolete 1 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)) no no baseitem_obsolete
account obsolete 1 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) no no account_obsolete
contract obsolete 1 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) no no contract_obsolete
2 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) )) no no
3 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)) no no
4 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) no no
5 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) no no
offer markfordelete 1 delete from offer where published = 2 and not exists (select 1 from orderitems where orderitems.offer_id = offer.offer_id) no no offer_markfordelete
policy obsolete 1 delete from policy where days(current timestamp) - days(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) no yes policy_obsolete
rfq obsolete 1 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) no no rfq_obsolete
2 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) no no
3 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) no no
atp_inventory obsolete 1 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) no no atp_inventory_obsolete
inventory_adjustment_codes obsolete 1 delete from invadjcode where markfordelete = 1 and invadjcode_id not in (select distinct invadjcode_id from invadjust where invadjcode_id is not null) no no inventory_adjustment_codes_obsolete
inventory_adjustments obsolete 1 delete from invadjust where days(CURRENT TIMESTAMP) - days(adjustmentdate) >= ? no yes inventory_adjustments_obsolete
rtnreasons obsolete 1 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) no no rtnreasons_obsolete
rma abandoned 1 delete from rma where status in ('PRC', 'EDT') and (days(CURRENT TIMESTAMP) - days(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) no yes rma_abandoned
rma canceled 1 delete from rma where status = 'CAN' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? no yes rma_canceled
rma not_approved 1 delete from rma where status = 'PND' and (days(CURRENT TIMESTAMP) - days(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) no yes rma_not_approved
rma approved_or_partly_approved 1 delete from rma where status = 'PND' and (days(CURRENT TIMESTAMP) - days(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) no yes rma_approved_or_partly_approved
rma completed 1 delete from rma where status = 'CLO' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and rma_id not in (select rma_id from rtnreceipt) no yes rma_completed
auction settlement_closed 1 delete from auction where austatus = 'SC' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ? no yes auction_settlement_closed
auction retracted 1 delete from auction where austatus = 'R' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ? no yes

auction_retracted
staglog obsolete 1 delete from staglog where stgprocessed = 1 and stgstmp <= (current timestamp - (? days)) no yes staglog_obsolete
msgmemrel obsolete 1 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 (days(CURRENT TIMESTAMP) - days(posttime)) >= ?))) no yes msgmemrel_obsolete
message obsolete 1 delete from message where message_id not in (select message_id from msgmemrel) or (days(CURRENT TIMESTAMP) - days(posttime)) >= ? no yes message_obsolete
usrtraffic obsolete 1 delete from cpgnlog where usrtraffic_id in (select usrtraffic_id from usrtraffic where (days(CURRENT TIMESTAMP) - days(stmp)) >= ?) no yes usrtraffic_obsolete
2 delete from usrtraffic where (days(CURRENT TIMESTAMP) - days(stmp)) >= ? no yes
cpgnlog all 1 delete from cpgnlog no no cpgnlog_all
cpgnstats all 1 delete from cpgnstats no no cpgnstats_all
auctionlog obsolete 1 delete from auctionlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ? no yes auctionlog_obsolete
bidlog obsolete 1 delete from bidlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ? no yes bidlog_obsolete
autobidlog obsolete 1 delete from autobidlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ? no yes autobidlog_obsolete
forummsg obsolete 1 delete from forummsg where msgstatus = 'D' or (days(CURRENT TIMESTAMP) - days(posttime)) >= ? no yes

forummsg_obsolete
expected_inventory_records obsolete 1 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) no no expected_inventory_records_obsolete
expected_inventory_record_details obsolete 1 delete from radetail where markfordelete = 1 and radetail_id not in (select distinct radetail_id from receipt where radetail_id is not null) no no expected_inventory_record_details_obsolete
vendor obsolete 1 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) no no vendor_obsolete
attachment obsolete 1 delete from attachment where days(current timestamp) - days(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) no yes

attachment_obsolete
attachment markedfordelete 2 delete from attachment where markfordelete =1 no no

attachment_markedfordelete
coupon_promotion expired 1 delete from cppmn where days(current timestamp) - days(enddate) >=? no yes coupon_promotion_expired
catalog_group obsolete 1 delete from catgroup where markfordelete = 1 no no catalog_group_obsolete
productset obsolete 1 delete from productset where markfordelete = 1 and productset_id not in (select productset_id from tradeposcn where productset_id is not null) no no productset_obsolete
productset obsolete_tradposcn 1 delete from productset where productset_id in (select productset_id from tradeposcn where productset_id is not NULL and markfordelete = 1 and type = 'C') no no productset_obsolete_tradposcn
tradeposcn obsolete 1 delete from tradeposcn where markfordelete = 1 and type = 'S' no no tradeposcn_obsolete
experiment obsolete 1 delete from expresult where experiment_id in (select experiment_id from experiment where status = 'D') no no experiment_obsolete
2 delete from expstats where experiment_id in (select experiment_id from experiment where status = 'D') no no
3 delete from experiment where status = 'D' no no
explog obsolete 1 delete from explog where (days(CURRENT TIMESTAMP) - days(created)) >= ? no yes explog_obsolete
PaymentRule obsolete 1 delete from edporder where order_id not in (select orders_id from orders) no no PaymentRule_obsolete
2 delete from edprma where rma_id not in (select rma_id from rma) no no
3 delete 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))) no no
PaymentRule sensitive_delete 4 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 (days(CURRENT TIMESTAMP) - days(lastupdate))>= ?)) no yes PaymentRule_sensitive_delete
5 delete from ppcextdata where ppcpayinst_id in (select ppcpayinst_id from ppcpayinst where rma_id in (select rma_id from rma where status='CLO' and (days(CURRENT TIMESTAMP) - days(lastupdate))>= ?)) no yes
activity obsolete 1 delete from CTXMGMT where days(CURRENT TIMESTAMP) - days(LASTACCESSTIME) >= ? no yes activity_obsolete
marketing_activity markedfordelete 1 delete from dmelestats where not exists (select 1 from dmelement where dmelement.dmelement_id = dmelestats.dmelement_id) no no marketing_activity_markedfordelete
2 delete from dmexpstats where not exists (select 1 from dmelement where dmelement.dmelement_id = dmexpstats.testelement_id) no no
3 delete from dmactattr where not exists (select 1 from dmactivity where dmactivity.dmactivity_id = dmactattr.dmactivity_id) no no
4 delete from dmemspotstats where not exists (select 1 from dmactivity where dmactivity.dmactivity_id = dmemspotstats.dmactivity_id) no no
marketing_activity obsolete 1 delete from dmactivity where enddate is not null and (days(current timestamp) - days(enddate)) >=? no yes marketing_activity_obsolete
marketing_userbehavior obsolete 1 delete from dmuserbhvr where days(current timestamp) - days(lastupdated) >=? no yes marketing_userbehavior_obsolete
marketing_log obsolete 1 delete from dmexplog where days(current timestamp) - days(created) >=? no yes marketing_log_obsolete
notify obsolete 1 delete from notify where not exists ( select notificationid from orders where notify.notificationid = orders.notificationid) no no notify_obsolete
promotion_code expired 1

Feature Pack 4Feature Pack 2Feature Pack 5Feature Pack 3Feature Pack 1delete 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 (days(current timestamp) - days(enddate)) >= ? 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)

Feature Pack 6 or later*delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdpool where reference_id in (select px_promotion_id from px_promotion where (days(current timestamp) - days(enddate)) >= ? 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

no yes promotion_code_expired
promotion_code markfordelete 1

Feature Pack 4Feature Pack 2Feature Pack 5Feature Pack 3Feature Pack 1delete 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)

Feature Pack 6 or later*delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdpool where reference_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)

 no no promotion_code_markfordelete
order completed 10

Feature Pack 4Feature Pack 2Feature Pack 5Feature Pack 3Feature Pack 1delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status = 'C' and (days(CURRENT TIMESTAMP) - days(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'))) and px_cdpool_id in (select px_cdpool_id from px_cdpromo where px_promotion_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

Feature Pack 6 or later*delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status = 'C' and (days(CURRENT TIMESTAMP) - days(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'))) and px_cdpool_id in (select px_cdpool_id from px_cdpool where reference_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

 no yes order_completed
order shipped 10

Feature Pack 4Feature Pack 2Feature Pack 5Feature Pack 3Feature Pack 1delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status ='S' and (days(CURRENT TIMESTAMP) - days(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 px_cdpool_id in (select px_cdpool_id from px_cdpromo where px_promotion_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

Feature Pack 6 or later*delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status ='S' and (days(CURRENT TIMESTAMP) - days(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 px_cdpool_id in (select px_cdpool_id from px_cdpool where reference_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

 no yes order_shipped
order deposited 10

Feature Pack 4Feature Pack 2Feature Pack 5Feature Pack 3Feature Pack 1delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status ='D' and (days(CURRENT TIMESTAMP) - days(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 px_cdpool_id in (select px_cdpool_id from px_cdpromo where px_promotion_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

Feature Pack 6 or later*delete from px_cdpool where px_cdpool_id in (select px_cdpool_id from px_cdusage where status = 5 and orders_id in (select orders_id from orders where status ='D' and (days(CURRENT TIMESTAMP) - days(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 px_cdpool_id in (select px_cdpool_id from px_cdpool where reference_id in (select px_promotion_id from px_cdspec where codetype = 1 or codetype = 2))

 no yes order_deposited
content_version inactive 10 delete from cmversninfo where cmversninfo_id in (select cmversninfo_id from cmversninfo where ((days(current timestamp) - days(createtime)) >= ?) and cmversninfo_id not in (select cmversninfo_id from cmactversn)) no yes content_version_inactive
content_version catalog 10 delete from cmversninfo where ui_object_name in ( 'SalesCatalog') and ((days(current timestamp) - days(createtime)) >= ?) and cmversninfo_id not in (select cmversninfo_id from cmactversn) no yes content_version_catalog
content_version catalog_group 10 delete from cmversninfo where ui_object_name in ( 'CatalogGroup', 'SalesCatalogGroup') and ((days(current timestamp) - days(createtime)) >= ?) and cmversninfo_id not in (select cmversninfo_id from cmactversn) no yes content_version_catalog_group
content_version catalog_entry 10 delete from cmversninfo where ui_object_name in ( 'Product', 'ProductSKU', 'CatalogGroupSKU', 'Bundle', 'Kit') and ((days(current timestamp) - days(createtime)) >= ?) and cmversninfo_id not in (select cmversninfo_id from cmactversn) no yes content_version_catalog_entry
subscription expired_timebased_subscr 1 delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='TIME-BASED' and status =2 no yes subscription_expired_timebased_subscr
subscription canceled_timebased_subscr 1 delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='TIME-BASED' and status=3 no yes subscription_canceled_timebased_subscr
subscription expired_recOrder 1 delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='NONE' and status =2 no yes subscription_expired_recOrder
subscription canceled_recOrder 1 delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='NONE' and status =3 no yes subscription_canceled_recOrder
STAStatistics obsolete 10 delete from srchstat where (days(CURRENT TIMESTAMP) - days(logdate)) >= ? no yes STAStatistics_obsolete
content_history obsolete_taskgroups 10 delete from cmftaskgrp where status in (5,6,12,13) and commitdate < (current timestamp - ? days) no yes content_history_obsolete_taskgroups
20 delete from cmfwkspc o where o.status in (2,3) and not exists (select 1 from cmfwstgrel i where o.cmfwkspc_id = i.cmfwkspc_id) no no
30 delete from cmftask o where o.status = 2 and (o.completedate < (current timestamp - ? days) or not exists (select 1 from cmftgtskrel i where o.cmftask_id = i.cmftask_id)) no yes
40 delete from cmprmyobj o where o.lastupdate < (current timestamp - ? days) or (o.workspace != 'IBM_WC_BASE' and not exists (select 1 from cmftaskgrp i where o.taskgrp = i.identifier)) no yes
scheduled_job completed 10 DELETE FROM SCHSTATUS WHERE (SCSSTATE = 'C' OR SCSSTATE = 'CF') AND (days(CURRENT TIMESTAMP) - days(SCSEND) >= ?) no yes scheduled_job_completed
20 DELETE FROM SCHCONFIG WHERE SCCAPPTYPE = 'broadcast' AND NOT EXISTS (SELECT 1 FROM SCHACTIVE A WHERE SCHCONFIG.SCCJOBREFNUM = A.SCSJOBNBR) AND NOT EXISTS (SELECT 1 FROM SCHBRDCST B WHERE SCHCONFIG.SCCJOBREFNUM = B.JOBREFNUM AND B.JOBSTATE = 'R') AND (days(CURRENT TIMESTAMP) - days(SCCSTART) >= ?) no yes
30 DELETE FROM SCHCONFIG WHERE (SCCAPPTYPE != 'broadcast' OR SCCAPPTYPE IS NULL) AND NOT EXISTS (SELECT 1 FROM SCHACTIVE A WHERE SCHCONFIG.SCCJOBREFNUM = A.SCSJOBNBR) AND (days(CURRENT TIMESTAMP) - days(SCCSTART) >= ?) no yes
workspace obsolete 10 delete from cmftaskgrp where status in (5, 13) and templatetype = 0 and (promotiondate is null and (days(CURRENT TIMESTAMP) - days(approvedate)) >=  ?) no yes workspace_obsolete
20 delete from cmftaskgrp where status in (5, 13) and templatetype = 0 and (promotiondate is not null and (days(CURRENT TIMESTAMP) - GREATEST(days(approvedate), days(promotiondate))) >=  ?) no yes
30 delete from cmftask T1 where not exists (select 1 from cmftgtskrel T2 where T1.cmftask_id=T2.cmftask_id ) no no
40 delete from cmfwkspc T1 where status in (2, 3) and not exists (select 1 from cmfwstgrel T2 where T1.cmfwkspc_id = T2.cmfwkspc_id) no no
workspace canceled 10 delete from cmftaskgrp where status = 6 and templatetype = 0 no no workspace_canceled
20 delete from cmftask T1 where not exists (select 1 from cmftgtskrel T2 where T1.cmftask_id=T2.cmftask_id ) no no
30 delete from cmfwkspc T1 where status in (2, 3) and not exists (select 1 from cmfwstgrel T2 where T1.cmfwkspc_id = T2.cmfwkspc_id) no no
folder orphaned 10 delete from folder where parentfolder_id is not null and parentfolder_id not in (select folder_id from folder) no no folder_orphaned
folderitem orphaned_all 10 delete from folderitem where folderitemtype='PromotionType' and reference_id not in (select px_promotion_id from px_promotion) no no folderitem_orphaned_all
20 delete from folderitem where folderitemtype='AttributeDictionaryAttributeType' and reference_id not in (select attr_id from attr) no no
30 delete from folderitem where folderitemtype='MarketingContentType' and reference_id not in (select collateral_id from collateral) no no
40 delete from folderitem where folderitemtype='MarketingSpotType' and reference_id not in (select emspot_id from emspot) no no
50 delete from folderitem where folderitemtype='MemberGroupType' and reference_id not in (select mbrgrp_id from mbrgrp) no no
60 delete from folderitem where folderitemtype='ActivityType' and reference_id not in (select dmactivity_id from dmactivity) no no
folderitem orphaned_promotion 10 delete from folderitem where folderitemtype='PromotionType' and reference_id not in (select px_promotion_id from px_promotion) no no folderitem_orphaned_promotion
folderitem orphaned_attribute 10 delete from folderitem where folderitemtype='AttributeDictionaryAttributeType' and reference_id not in (select attr_id from attr) no no folderitem_orphaned_attribute
folderitem orphaned_marketing 10 delete from folderitem where folderitemtype='ActivityType' and reference_id not in (select dmactivity_id from dmactivity) no no folderitem_orphaned_marketing
20 delete from folderitem where folderitemtype='MemberGroupType' and reference_id not in (select mbrgrp_id from mbrgrp) no no
30 delete from folderitem where folderitemtype='MarketingSpotType' and reference_id not in (select emspot_id from emspot) no no
40 delete from folderitem where folderitemtype='MarketingContentType' and reference_id not in (select collateral_id from collateral) no no

tickler_obsolete

  • This query physically deletes all to-do items that are closed and have not been updated within a specified number of days. These records are deleted from the TICKLER database table.
  • Run this query when you need to reduce the volume of stored to-do items.

catentry_made_to_order

  • In the production environment, this query physically deletes records that are marked for delete from the CATENTRY table.
  • When you use the business tooling or data load to remove a catalog entry, the catalog entry is only marked for delete in the database. It is not physically deleted. The CATENTRY table can grow over time and contain several mark for delete catalog entries. When the records that are marked are no longer referenced by any other business object, for example order, delete the records from the table to improve database performance.
  • It is recommended to run this query quarterly. Ensure that the number of mark for delete catalog entries do not exceed 5% of the total catalog entries in the CATENTRY table.

cacheivl_obsolete

  • In the production and staging environment, the DynaCacheInvalidation scheduler job periodically issues DynaCache invalidations that are specified by rows in the CACHEIVL table. The scheduler uses the INSERTTIME column to determine which rows to process. To improve performance, the job does not delete the rows as they are processed. The Database Cleanup utility is used to delete CACHEIVL rows that are older than a specified amount of time.
  • It is important to prevent the size of the CACHEIVL table from growing indefinitely.
  • It is recommended to run this query daily.

fileupload_obsolete

  • This query deletes all records of uploaded files that are obsolete. Records older than the specified upload time are deleted from the iFILEUPLOAD table.
  • By running this query, you can reduce the number of records in the FILEUPLOAD table.
  • It is recommended that you run this query when the volume of records in the table is high. Run this query on the staging environment. If your site uploads directly to the production environment, run the Database Cleanup utility in the production environment.

store_markedfordelete

  • In the development or staging environment, this query deletes all stores that are marked for deletion. Extended-sites stores are the only stores that can be marked for deletion from the WebSphere Commerce Accelerator Hub store.
  • This query completely removes a store from the system to be able to reuse the store identifier.
  • It is recommended to delete a store only during initial environment setup.

user_registered

  • In the production environment, this query deletes registered user accounts that are inactive for a specified amount of time.
  • The member and users tables can grow large over time. Delete inactive user accounts to improve database performance.
  • It is recommended to delete inactive user accounts yearly.
  • WebSphere Commerce Version 7.0.0.9 If you are deleting a large number of user objects, such as over a million records, you can run the Database Cleanup utility offline to clean the objects from the database. By running the utility offline, you can reduce the performance impact of deleting a large number of objects that are stored across a deeply or widely nested database table hierarchy. For more information, see Database Cleanup utility.

organization_specified

  • In the production and staging environment, this query deletes organizations that are no longer required in your site. Deleting an organization cascade deletes everything that the organization owns, for example, a store or a contract.
  • Deleting an organization prevents obsolete organizations from appearing in the Organization Administration Console.
  • Delete an organization only if it is not relevant in your site.

user_guest

  • In the production environment, this query deletes guest users that have not visited the site for a specified amount of time.
  • The member and users tables can grow large over time. Delete inactive guest users to improve database performance.
  • It is recommended to delete inactive guest user accounts quarterly.
  • WebSphere Commerce Version 7.0.0.9 If you are deleting a large number of user objects, such as over a million records, you can run the Database Cleanup utility offline to clean the objects from the database. By running the utility offline, you can reduce the performance impact of deleting a large number of objects that are stored across a deeply or widely nested database table hierarchy. For more information, see Database Cleanup utility.

calculation_code_markedfordelete

  • In the production environment, this query physically deletes calculation code that is marked for delete.
  • Deleting a calculation code also cleans all related information, for example, the related CATENTRY, and shipping method.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

address_obsolete

  • In the production and staging environment, this query deletes historical addresses from the ADDRESS table with status = 'T' that were created before a specified time and that are not referenced by any orders or order items.
  • The ADDRESS table can grow large over time. Delete historical addresses to improve database performance.
  • It is recommended to delete historical addresses yearly.

order_completed

  • In the production environment, this query deletes order records that satisfy the following conditions:
    1. The orders were submitted.
    2. Payments were approved: status = 'C'.
    3. No return orders and orders were not updated for a user specified time.
  • Delete completed order records from your database to improve order capture process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_cancelled

  • In the production environment, this query deletes the canceled order records that were not returned and not updated for a user specified time.
  • Delete old cancel order records from your database to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_shipped

  • In the production environment, this query deletes the order records that were shipped, not returned, and not updated for a user specified time.
  • Delete old shipped order records from your database to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_deposited

  • In the production environment, this query deletes the order records that were deposited but not returned, and not updated for a user specified time.
  • Delete out of date and deposited order records from your database to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_stale_guest

  • In the production environment, this query deletes guest user order records that were not updated for a user specified time and satisfy one of the following order status conditions:
    • Pending.
    • Submitted.
    • Approval denied.
    • Pending approval.
    • No inventory is allocated.
    • No back-order.
  • Delete stale guest user orders from your database to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_stale_non_guest

  • In the production environment, this query deletes non-guest user order records that were not updated for a user specified time and satisfy one of the following order status conditions:
    • Pending.
    • Submitted.
    • Approval denied.
    • Pending approval.
    • No inventory is allocated.
    • No back-order.
  • Delete stale non-guest user orders from your database to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_quotation

  • In the production environment, this query deletes the child orders that have no quotation relationship with parent orders reltype='markedForDelete' from ORDERS and ORDQUOTREL tables.
  • Delete child order records to improve order process performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

order_markfordelete

  • In the production environment, this query physically deletes from the ORDERS and ORDERITEMS table, the cancel, and junk orders.
  • When customers delete or cancel order or order items in the store, for database performance reasons, it is marked as junk/cancel order. Delete the unused order records from your database to improve order process performance.
  • It is recommended to delete unused order records depending on your store requirements.

catentry_without_orderitems

  • In the production environment, this query physically deletes from the CATENTRY table, the catalog entries that are marked for delete and are not referenced by any order or order items.
  • When you use Management Center to remove a catalog entry, the catalog entry is only marked for delete in the database. The catalog entry is not physically deleted. The CATENTRY table can grow over time and contain several mark for delete catalog entries. When the entries are no longer referenced by any other business object, the entries need to be removed from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

catentry_without_orderitems_iitems

  • In the production environment, this query physically deletes from the CATENTRY table, the catalog entries that are marked for delete and are not referenced by any order or order items.
  • When you use Management Center to remove a catalog entry, the catalog entry is only marked for delete in the database. The catalog entry is not physically deleted. The CATENTRY table can grow over time and contain several mark for delete catalog entries. When the entries are no longer referenced by any other business object, the entries need to be removed from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

calculation_code_obsolete

  • In the production environment, this query physically deletes from the CALCODE table, the mark for delete and never used calculation code.
  • If there are many calculation codes in your database, order calculation performance can be impacted. Deleting unused and mark for delete calculation codes can improve order calculation performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

calculation_rule_obsolete

  • In the production environment, this query deletes the calculation rules that are expired for the specified time in the CALRULE database table.
  • Deleting out-of-date calculation rules can improve order calculation performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

fulfillment_center_obsolete

  • In the production environment, this query physically deletes from FFMCENTER table, the fulfillment centers that are marked for delete and were never used.
  • Clean the never used and marked for delete fulfillment centers to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

store_specified

  • In the development or staging environment, this query deletes the specified store from the system.
  • You can completely remove a store from the system to reuse the store identifier.
  • It is recommended to delete a store only during initial environment setup.

itemspecification_obsolete

  • In the production environment, this query physically deletes from the ITEMSPC table, the records that are marked for delete.
  • When you use the business tooling or data load to remove a catalog entry specification information, the record is only marked for delete in the database. The record is not physically deleted. The ITEMSPC table can grow over time and contain several mark for delete records. When the records are no longer referenced by any other business object, they must be removed from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

baseitem_obsolete

  • In the production environment, this query physically deletes from the BASEITEM table, the records that are marked for delete.
  • When you use the business tooling or data load to remove a catalog entry base item information, the record is only marked for delete in the database. The information is not physically deleted. The BASEITEM table can grow over time and contain several mark for delete record. When the records are no longer referenced by any other business object, they must be removed from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

account_obsolete

  • In the production and staging environment, this query deletes the accounts that are marked for delete.
  • You do not need to run this query frequently. Run this query when you have many accounts records that are marked for delete.

contract_obsolete

  • In the production and staging environment, this query deletes the contracts and related data that is marked for delete.
  • You do not need to run this query frequently. Run this query when you have many contracts that are marked for delete.

offer_markfordelete

  • In the production environment, this query physically deletes from the OFFER table, offers that were never used and are marked for delete.
  • Clean unused offer from database to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

policy_obsolete

  • In the production and staging environment, this query deletes the policy record when the record is not in use and exists from before a specified day.
  • You do not need to run this query frequently. Run this query only when you have many unused policy records that are marked for delete.

rfq_obsolete

  • In the production and staging environment, this query deletes the RFQ records that are marked for delete.
  • You do not need to run this query frequently. Run this query only when you have many RFQ records that are marked for delete.

atp_inventory_obsolete

  • In the production environment, this query deletes ATP inventory records from the RECEIPT table that have zero quantity remaining.
  • When a customer places an order, an ATP inventory record is assigned for item allocation. When you use the business tooling to release items to fulfillment for an order, the quantity of the assigned ATP inventory record is decreased accordingly. After the quantity of an ATP inventory record decreases to zero, this record cannot be used any more. The RECEIPT table can grow over time and contain many obsolete ATP inventory records. When the records are no longer referenced by any other business object, they must be removed from the table to improve database performance.
  • It is recommended to delete obsolete ATP inventory records quarterly. Delete the records more frequently if the number of obsolete records exceed 5% of the total ATP inventory entries in the RECEIPT table.

inventory_adjustment_codes_obsolete

  • In the production environment, this query physically deletes from the INVADJCODE table, the records that are marked for delete.
  • When you use the business tooling to delete an inventory adjustment code, the inventory adjustment code record is only marked for delete. The inventory adjustment code is not physically deleted. When a record is no longer referenced by any inventory adjustment record, it must be removed from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

inventory_adjustments_obsolete

  • In the production environment, this query physically deletes from the INVADJUST table, the records that are older than a user specified time.
  • When you use the business tooling to adjust the quantity of an inventory record, an inventory adjustment record is created to track adjustment history. Delete inventory adjustment records if there is no error in quantity of the relevant items and the items that are subtracted from the inventory have no business value. Delete inventory adjustment records to improve database performance.
  • It is recommended to delete inventory adjustment records monthly.

rtnreasons_obsolete

  • In the production environment, this query physically deletes from the RTNREASON table, the records that are marked for delete.
  • When you use the business tooling to delete a return reason, the return reason record is only marked for delete instead of physically being deleted. Delete the return reason record that is marked for delete if it is not referenced by any other business objects, for example disposition.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

rma_abandonded

  • In the production environment, this query physically deletes from the RMA table, the records that were abandoned for a user specified time.
  • When you or a customer initiates a return request, a return merchandising authorization (RMA) record is created in the RMA table. The RMA record is marked as being edited before the request is submitted. A return request that is not submitted can be edited later. If an unsubmitted return request is referenced by an approved return item record, or by a return item that was received, it must be processed. Otherwise, a return request will not be edited again if it was abandoned for a long time and can be removed from the database. Delete the abandoned RMA record to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

rma_canceled

  • In the production environment, this query physically deletes from the RMA table, the records that were canceled for a set number of days.
  • When a customer or business user explicitly cancels a return request, the RMA record is marked as canceled instead of physically being deleted. Canceled RMA records that are older than a certain age can be removed from the table. Deleting canceled RMA records can improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

rma_not_approved

  • In the production environment, this query deletes the return merchandising authorization (RMA) records if the following conditions apply:
    • The RMA records are not approved for a user specified amount of time.
    • The related return items are not approved.
    • The RMA records are not referenced by any other business objects, for example, received return items.
  • RMA and RMA items may not be automatically approved because the items are not returnable or refundable. Delete the RMA records to improve database performance

rma_approved_or_partly_approved

  • In the production environment, this query deletes the return merchandising authorization (RMA) records if the following conditions apply:
    • The RMA records are not approved for a set number of days.
    • Some of the related return items are already approved.
    • The RMA records are not referenced by any other business objects, for example, received return items.
  • An RMA record may not be approved even if its related return items are approved. Delete the RMA records to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

rma_completed

  • In the production environment, this query deletes the return merchandising authorization (RMA) records that were closed for a set number of days.
  • When a return process is completed, the RMA record is marked as closed. Closed RMA records that are not referenced by any return item record can be removed from the table. Delete the RMA records to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

auction_settlement_closed

  • In the production and staging environment, this query deletes the auction record when the auction is finished and the record exists from before a specified day.
  • If your site generates many auction records, run this query frequently to reduce the number of stored records.

auction_retracted

  • In the production and staging environment, this query deletes the auction record when the auction status is retracted 'R", which indicates a canceled auction. Only the auction records that are marked as retracted for longer than the specified number of days are deleted.
  • If your site generates many auction records, run this query frequently to reduce the number of stored records.

staglog_obsolete

  • In the staging environment, this query cleans up the staging logs after all the records in the staged tables are successfully propagated and reach a certain age.
  • StagLog is the fastest growing table in the staging environment. Perform regular cleanup of the staging log table to maintain the performance of stagingprop, and improve performance of Quick Publish within workspaces.
  • It is recommended to clean the staging logs after every successful stagingprop execution.

msgmemrel_obsolete

  • In the production and staging environment, this query deletes the relationship data between the message and a member when the data is finished and exists from before a specified day.
  • You do not need to run this query frequently. Run this query when your number of message and member relationship records is large.

message_obsolete

  • In the production and staging environment, this query deletes the message that exists from before a specified day.
  • You do not need to run this query frequently. Run this query when you need to reduce the number of message records.

usrtraffic_obsolete

  • In the production and staging environment, this query deletes user traffic logging records that occur for a specified number of days.
  • You do not need to run this query frequently. Run the query when your volume of records is large to reduce any performance impact from having a large volume of records.

cpgnlog_all

  • In the production and staging environment, this query deletes all the campaign logging events.
  • When using the WebSphere Commerce Accelerator Marketing tool, the Campaign Logging Event Listener can be enabled to capture information about recommendations that are displayed in e-Marketing Spots. If this information is not required, for example, it was copied to a data warehouse database for analysis, the existing records can be deleted.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

cpgnstats_all

  • In the production and staging environment, this query deletes all the campaign statistics.
  • When using Accelerator Marketing, the Campaign Logging Event Listener can be enabled to capture the number of impressions and clickthroughs for recommendations that are displayed in e-Marketing spots. If this information is no longer required, for example, it was been copied to a data warehouse database for analysis, the existing records can be deleted.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

auctionlog_obsolete

  • In the production and staging environment, this query deletes the auction log that exists from before a specified date.
  • You do not need to run this query frequently. Run this query when you need to reduce the size of the auction log record.

bidlog_obsolete

  • In the production and staging environment, this query deletes the bid log that exists from before a specified day.
  • You do not need to run this query frequently. Run this query when you need to reduce the size of the bid log record.

autobidlog_obsolete

  • In the production and staging environment, this query deletes the auto bid log that exists from before a specified day.
  • You do not need to run this query frequently. Run this query when you need to reduce the size of the auto bid log record.

forummsg_obsolete

  • In the production and staging environment, this query deletes the forum messages that are marked as deleted for the specified number of days.
  • Run this query when you need to reduce the volume of deleted messages.

expected_inventory_records_obsolete

  • In the production environment, this query physically deletes from the RA table, the records that are marked for delete.
  • When you use the business tooling to delete an expected inventory item, the expected inventory record is only marked for delete instead of being physically deleted. These records can be removed from the table if they are no longer used by any inventory receipt records.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

expected_inventory_record_details_obsolete

  • In the production environment, this query physically deletes from the RADETAIL table, the records that are marked for delete.
  • Expected inventory records that are marked for delete and not referenced by any inventory receipt records can be removed from the table.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

vendor_obsolete

  • In the production environment, this query physically deletes from the VENDOR table, the records that are marked for delete.
  • When you use the business tooling to delete a vendor, the vendor record is only marked for delete instead of being physically deleted. These records can be removed from the table if they are no longer referenced by any inventory or expected inventory records.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

attachment_obsolete

  • This query deletes attachments that are not updated for a specified number of days. Only attachments with a status of Contract, RFQ, or PattributeAttachment are deleted. These records are deleted only when the attachment is not included in a trading agreement or as part of a personalized attribute.
  • You do not need to run this query frequently. Run the query when you need to reduce the size of the ATTACHMENT database table.

attachment_markedfordelete

  • This query deletes attachments that are marked for delete within the ATTACHMENT database table.
  • You do not need to run this query frequently. Run the query when your volume of records is large.
Feature Pack 5 or later

coupon_promotion_expired

  • In the production environment, this query deletes entries that are past their end date by a specific number of days from the CPPMN database table.
  • It is recommended that you run this query regularly according to your business requirements to remove the expired entries from the CPPMN table,

catalog_group_obsolete

  • In the production environment, this query physically deletes from the CATGROUP table, the catalog groups, which are marked for delete.
  • The CATGROUP table can grow over time and contain many mark for delete catalog groups. Delete catalog group records from the table to improve the database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

productset_obsolete

  • In the production environment, this query physically deletes the product sets, which are mark for delete and are not referenced by any trading position container.
  • The PRODUCTSET table can grow over time and contain many mark for delete product sets. When the records are not referenced by other business objects, for example: trading position container, delete the records from the table to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

productset_obsolete_tradposcn

  • In the production environment, this query physically deletes the product sets, which are mark for delete and are not referenced by any trading position container that is used for custom price list.
  • The PRODUCTSET table can grow over time and contain many mark for delete product sets. When the records are no longer referenced by other business objects, for example trading position container, delete the records from the table to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

tradeposcn_obsolete

  • In the production environment, this query physically deletes from the TRADEPOSCN table, the trading position records that are marked for delete.
  • When you use the business tooling to delete a trading position, the trading position record is only marked for delete instead of being physically deleted. These records can be removed from the table if the price list is not created by a customer or external.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

experiment_obsolete

  • In the staging environment, this query physically deletes records that are marked for delete in the EXPERIMENT table.
  • When business users use WebSphere Commerce Accelerator to delete an experiment, the experiment is only marked for delete in the database instead of being physically deleted. The EXPERIMENT table can grow over time and contain many mark for delete experiments. These records are not required. Delete the records from the table to improve database performance.
  • It is recommended that you run this query quarterly. Ensure that the number of mark for delete records for experiments do not exceed 5% of the total experiments in the EXPERIMENT table.

explog_obsolete

  • In the production and staging environment, this query deletes obsolete records that are related to calculating WebSphere Commerce Accelerator experiment revenue statistics.
  • To calculate the revenue that is associated with recommendations made under an Accelerator experiment, the Experiment Logging Event Listener must be enabled. When this listener is enabled, entries are created in the EXPLOG database table that contains the recommendations that are displayed in an e-Marketing Spot as a result of an experiment. When an order is placed, the recommendations that are recorded in the EXPLOG table for the current session are compared to the contents of the order. The comparison checks if the recommendations influenced the order. Older entries can be deleted because the older entries are not considered as part of the current session when evaluating an order.
  • It is recommended to delete these records weekly.

PaymentRule_obsolete

  • In the production environment, this query deletes from EDPORDER, EDPRMA and PPCPAYINST tables, credit and payment instruction records that are not referenced by orders or returns.
  • When an order request is submitted, an order credit record and one or more payment instruction records are created. When a return request is submitted, a refund credit record and one or more payment instruction records are created. These credit and payment instruction records can be removed from the tables when they are no longer referenced by any order or return records.
  • You do not need to run this query frequently. Run the query when your volume of records is large.

PaymentRule_sensitive_delete

  • In the production environment, this query deletes from the PPCEXTDATA table, the extra financial transaction data records not used by orders or returns and not updated for a set number of days.
  • When an order or return request is submitted, several extra financial transaction data records are created together with and referenced by a payment instruction record. Then, the payment instruction record is referenced by an order record or both an order record and a return record. Delete the extra financial transaction data record if it is older than a certain age, and the referencing order and return are all closed,
  • You do not need to run this query frequently. Run the query when your volume of records is large.

activity_obsolete

  • In the production, this query deletes the old activity token if the token is not used for a specified time period.
  • The activity token increases when a user is created or when an existing user logs in. There is no business scenario for deleting the activity token, however since this table can be large, deleting obsolete entries can improve your overall database performance.
  • It is recommended that you run this query frequently.

marketing_activity_markedfordelete

  • In the production and staging environment, this query deletes obsolete entries that are associated with deleted Management Center marketing activities.
  • When running Management Center marketing activities, statistics are collected in several database tables. There is no foreign key from the statistics tables to the DMACTIVITY database table. If an activity is deleted, obsolete records remain in the marketing statistics tables. These queries can be run to delete the obsolete statistics entries that are associated with the deleted activities.
  • It is recommended to delete the obsolete entries weekly.

marketing_activity_obsolete

  • In the staging environment, this query deletes Management Center marketing activities that are completed.
  • A Management Center marketing activity is completed when the current date is after the activity end date. If you do not need to view the completed activities, for example: view the statistics of old activities, you can delete the completed activities from the database.
  • It is recommended to delete completed marketing activities quarterly.

marketing_userbehavior_obsolete

  • In the production and staging environments, this query deletes obsolete records that are associated with recorded user behavior used for Management Center marketing activities,
  • Marketing activity triggers, targets, and actions record information about events and actions that are associated with a user. Information that is recorded includes:
    • Information for targets and triggers. For example, browsed product and categories.
    • Information for experiments. For example, assigned test element or session time.
    • Information for actions. For example, recently viewed lists.
    • Information for activities. For example, when Repeatable is false.
    • WebSphere Commerce EnterpriseWebSphere Commerce ProfessionalInformation for dialog activities. For example, events being waited for.

      Depending on the definition of the marketing activities and business requirements, you can delete records that were not updated for a specified time.

  • It is recommended to delete the obsolete records weekly.

marketing_log_obsolete

  • In the production and staging environments, this query deletes obsolete records that are related to calculating Management Center experiment revenue statistics.
  • To calculate the revenue that is associated with recommendations made under a Management Center experiment, entries are created in the DMEXPLOG database table. The entries contain the recommendations that are displayed in an e-Marketing Spot as a result of an experiment. When an order is placed, the recommendations in the DMEXPLOG table for the current session are compared to the contents of the order to see whether the recommendations influenced the order. Older entries can be deleted because the entries are not considered as part of the current session when evaluating an order.
  • It is recommended to delete obsolete records that are related to calculating revenue statistics weekly.
WebSphere Commerce Version 7.0.0.0

notify_obsolete

  • In the production environment, this query physically deletes obsolete order notification records from the NOTIFY database table.
  • When order records are deleted from the ORDERS table, you do not require notifications of that order record in the NOTIFY table. Delete those order notification records from the NOTIFY table to improve database performance.
  • You do not need to run this query frequently. Run the query when your volume of records is large.
Introduced in Feature Pack 1

promotion_code_expired

  • In the production environment, this query cleans up the promotion codes in the PX_CDPOOL table by removing all entries for a promotion whose end date has passed by a specified number of days. It also checks whether the codes are of type basic generated code or advanced generated code and that they are not currently redeemed in an order.
  • The PX_CDPOOL table can grow large depending upon how many codes are being used as per business requirements.
  • It is recommended to run this query frequently if large number of promotion codes are being generated.
Introduced in Feature Pack 1

promotion_code_markfordelete

  • In the production environment, this query cleans up the promotion codes in the PX_CDPOOL table by removing any entries for a promotion that is marked for delete or obsolete. It also checks whether the codes are of type basic generated code or advanced generated code and that they are not currently redeemed in an order.
  • The PX_CDPOOL table can grow large depending upon how many codes are being used as per business requirements.
  • It is recommended to run this query frequently if large number of promotion codes are being generated.
Introduced in Feature Pack 1

order_completed

  • In the production environment, this query cleans up the promotion codes in the PX_CDPOOL table by removing any entries that are redeemed in an order. It also checks whether the codes are of type basic generated code or advanced generated code. The order must be completed and not updated for a specified number of days. In addition, all the order items must be in the complete state and cannot be marked to be returned.
  • The PX_CDPOOL table can grow large depending upon how many codes are being used as per business requirements.
  • It is recommended to run this query frequently if large number of codes are being generated.
Introduced in Feature Pack 1

order_shipped

  • In the production environment, this query cleans up the promotion codes in the PX_CDPOOL table by removing any entries that are redeemed in an order. It also checks whether the codes are of type basic generated code or advanced generated code. The order must be shipped and not updated for a specified number of days. In addition, all the order items must be in the complete state and cannot be marked to be returned.
  • The PX_CDPOOL table can grow large depending upon how many codes are being used as per business requirements.
  • It is recommended to run this query frequently if large number of codes are being generated.
Introduced in Feature Pack 1

order_deposited

  • In the production environment, this query cleans up the promotion codes in the PX_CDPOOL table by removing any entries that are redeemed in an order. It also checks whether the codes are of type basic generated code or advanced generated code. This order must be deposited and not updated for a specified number of days. In addition, all its order items must be in the complete state and cannot be marked to be returned.
  • The PX_CDPOOL table can grow large depending upon how many codes are being used as per business requirements.
  • It is recommended to run this query frequently if large number of codes are being generated.
Introduced in Feature Pack 2

content_version_inactive

  • In the staging environment, this query cleans up inactive versions that are after a certain age.
  • Perform regular cleanup of content versions to minimize the number of inactive versions that are kept within WebSphere Commerce and maintain the performance of Content Version.
  • It is recommended to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.
Introduced in Feature Pack 2

content_version_catalog

  • In the staging environment, this query cleans up inactive versions of sales catalog that are after a certain age and is not the current version.
  • Perform regular cleanup of content versions to minimize the number of inactive versions that are kept within WebSphere Commerce and maintain the performance of Content Version.
  • It is recommended to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of a sales catalog.
Introduced in Feature Pack 2

content_version_catalog_group

  • In the staging environment, the query cleans up inactive versions of catalog groups, which also include sales catalog groups that are after a certain age and is not the current version.
  • Perform regular cleanup of content versions to minimize the number of inactive versions that are kept within WebSphere Commerce and maintain the performance of Content Version.
  • It is recommended to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.
Introduced in Feature Pack 2

content_version_catalog_entry

  • In the staging environment, this query cleans up inactive versions of catalog entries after a certain age and is not the current version. These catalog entries include products, product SKUs, catalog group SKUs, bundles, and kits.
  • Perform regular cleanup of content versions to minimize the number of inactive versions kept within WebSphere Commerce and maintain the performance of Content Version.
  • It is recommended to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.
Introduced in Feature Pack 2

subscription_expired_timebased_subscr

  • In the production environment, this query deletes time-based subscriptions that are expired. It expects DAYS as the mandatory input field that specifies the difference between the LASTUPDATE field and the current time.
  • If expired subscriptions are not deleted, there is no impact on performance. Delete the expired records to maintain a lower number of records in your system.
  • It is recommended to run this query if there is a high number of expired subscription orders over a set time.
WebSphere Commerce Version 7.0.0.0Introduced in Feature Pack 2

subscription_canceled_timebased_subscr

  • In the production environment, this query deletes time-based subscriptions that are canceled. It expects DAYS as the mandatory input field that specifies the difference between the LASTUPDATE field and the current time.
  • If canceled subscription records are not deleted, there is no impact on performance. Delete the canceled records to maintain a lower number of records in your system.
  • It is recommended to run this query if there are a high number of time-based subscription orders that were canceled over a set time.
WebSphere Commerce Version 7.0.0.0Introduced in Feature Pack 2

subscription_expired_recOrder

  • In the production environment, this query deletes recurring orders that are expired. It expects DAYS as the mandatory input field that specifies the difference between the LASTUPDATE field and the current time.
  • If expired recurring orders are not deleted, there is no impact on performance. Delete the expired records to maintain a lower number of records in your system.
  • It is recommended to run this query if there is a high number of expired recurring orders over a set time.
WebSphere Commerce Version 7.0.0.0Introduced in Feature Pack 2

subscription_canceled_recOrder

  • In the production environment, this query deletes recurring orders that were canceled. It expects DAYS as the mandatory input field that specifies the difference between the LASTUPDATE field and the current time.
  • If canceled recurring orders are not deleted, there is no impact on performance. Delete the canceled records to maintain a lower number of records in your system.
  • It is recommended to run this query if there is a high number of canceled recurring orders over a set time.
Introduced in Feature Pack 3

STAStatistics_obsolete

  • In the production and staging environment, this query deletes searches statistics.
  • You can use the query to capture all keyword search events from the storefront. If the DownloadStatistics scheduler job is set up in the staging environment, the statistics data is pulled from production into the staging database. Therefore, only staging must be cleaned.
  • It is recommended to clean or archive data monthly.
Feature Pack 8

content_history_obsolete_taskgroups

  • In the staging or authoring environment, this query deletes change history records for approved or canceled task groups from the CMFTASKGRP, CMFWKSPC, and CMPRMYOBJ database tables. These tables include a log of the content objects that are changed within a task group. The Database Cleanup utility can delete the obsolete records from these tables when the records no longer need to be kept.
  • Run the Database Cleanup utility to clean these records in your staging or authoring environment when workspaces are enabled.
  • It is recommended to clean these records daily or weekly.

scheduled_job_completed

  • In the production environment, this query deletes scheduler configure records from the SCHCONFIG database table. The deleted records are for scheduler jobs that are not scheduled to run and has a start time that is earlier that a specific date. This query can also be used to delete the scheduler status records in the SCHSTATUS database table. The status records are deleted when the status is C or CF and the job was ran before a specific date.
  • Reduce the number of records in these tables to avoid a performance reduction from the scheduler framework. Ensure that the records of the scheduler tables are less that 100,000.
  • It is recommended that this query runs based on how many scheduler jobs are configured and the frequency that these jobs run.
Feature Pack 5 or later

workspace_obsolete

  • In the staging environment, this query cleans up workspace metadata that are obsolete and deletes:
    • All orphan tasks.
    • InstanceTask groups, committed or scheduled commit, of a certain age that are completed or failed to publish.
    • Workspaces without task groups that are completed or workspaces that are canceled.
  • Perform regular cleanup of the workspace metadata to maintain the performance of workspace, particular in the approval process.
  • It is recommended to run this query quarterly if workspace is enabled. Delete completed taskgroups before they reach 120 days old.
Feature Pack 5 or later

workspace_canceled

  • In the staging environment, this query cleans up workspace metadata that are canceled and deletes:
    • Any orphan tasks.
    • InstanceTask groups that are canceled.
    • Workspaces without taskgroups that are completed or workspaces that are canceled.
  • Perform regular cleanup of the workspace metadata to maintain the performance of workspace, particular in the approval process.
  • It is recommended to run this query quarterly if workspace is enabled. Delete canceled taskgroups before they reach 20% of the total number of taskgroups.
Feature Pack 6 or later

folder_orphaned

  • In the staging environment, this query cleans up folders that are orphaned. Orphaned folders are folders with an invalid parent folder.
  • Performing regular cleanup of folders ensures the performance of folder retrieval in the Management Center.
  • It is recommended to run this query quarterly.
Feature Pack 6 or later

folderitem_orphaned_all

  • In the staging environment, this query cleans up orphaned folder items under the promotion folder, attribute folder, and marketing folder, which can contain content, e-Marketing Spot, customer segment, and activities. Orphaned folder items are items that their corresponding real business objects no longer exist.
  • Performing regular cleanup of folder items ensures the performance of item retrieval in the Management Center.
  • It is recommended to run this query quarterly.
Feature Pack 6 or later

folderitem_orphaned_promotion

  • In the staging environment, this query cleans up orphaned folder items under the Promotion folder only. Orphaned folder items are items that their corresponding real business objects no longer exist.
  • Performing regular cleanup of folder items ensures the performance of item retrieval in the Management Center.
  • It is recommended to run this query quarterly.
Feature Pack 6 or later

folderitem_orphaned_attribute

  • In the staging environment, this query cleans up orphaned folder items under the Attribute folder only. Orphaned folder items are items that their corresponding real business objects no longer exist.
  • Performing regular cleanup of folder items ensures the performance of item retrieval in the Management Center.
  • It is recommended to run this query quarterly.
Feature Pack 6 or later

folderitem_orphaned_marketing

  • In the staging environment, this query cleans up orphaned folder items under the Marketing folder only, which contains content, e-Marketing Spots, customer segments, and activities. Orphaned folder items are items that their corresponding real business objects no longer exist.
  • Performing regular cleanup of folder items ensures the performance of item retrieval in the Management Center.
  • It is recommended to run this query quarterly.
* Feature Pack 4Feature Pack 2Feature Pack 5WebSphere Commerce Version 7.0.0.0Feature Pack 3Feature Pack 1You must apply the interim fixes for APAR #JR48653 and JR45153 to add support for this query.