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, we can add rows to the CLEANCONF table to add the appropriate custom SQL statements. The SQL statements listed for the following objects are example cleanup statements for use with a DB2 database. The following files includes all of the cleanup statements that are provided with WebSphere Commerce for each database type.

• utilities_root/schema/data/wcs.staging.xml

• utilities_root/schema/dbtype/wcs.staging.xml

• utilities_root/schema/data/xml/wcs.features_bootstrap.dbtype.xml

Notes:

• The select statements are used for diagnostic output.

• Replace the ? used in the examples in the following SQL statements with values from the dbClean parameter. The possible values are -days and -name. See Database Cleanup utility command script.

• The sequence column identifies the sequence that SQL statements run to delete an object. For example, when we 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.

• The 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.

• If we are deleting a large number of user objects, such as over a million records, we can run the Database Cleanup utility offline to clean the objects from the database. By running the utility offline, we can reduce the performance impact of deleting a large number of objects that are stored across a deeply or widely nested database table hierarchy. See Database Cleanup utility.

Objects with provided cleanup SQL statements

Activity

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
activity	obsolete	1	delete from CTXMGMT where days(CURRENT TIMESTAMP) - days(LASTACCESSTIME) >= ?	no	yes

• In the production environment, this query deletes the activity tokens that are 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.

• IBM recommends to run this query frequently.

Address

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production and staging environment, this query deletes historical addresses from the ADDRESS table. The query deletes the addresses with status = 'T' that are 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.

• IBM recommends to delete historical addresses yearly.

ATP inventory

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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 we 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.

• IBM recommends 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.

Attachments

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
attachment	markedfordelete	1	select attachmenturl from attachment where cast(attachment_id as char(60)) != cast(attachmenturl as char(254)) and markfordelete =1	no	no
attachment	markedfordelete	2	delete from attachment where markfordelete =1	no	no

• These queries delete 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

Base items

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes records that are marked for delete from the BASEITEM table. When we 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.

Cache invalidations

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
CACHEIVL	obsolete	1	delete from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ?	no	yes

• In the production and staging environment, the DynaCacheInvalidation scheduler job periodically issues DynaCache invalidations 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.

• IBM recommends to run this query daily.

Calculation code

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
calculation_code	markedfordelete	3	delete from catencalcd where calcode_id in (select calcode_id from calcode where published = 2)	no	no
calculation_code	markedfordelete	4	delete from shpmodclcd where calcode_id in (select calcode_id from calcode where published = 2)	no	no

• In the production environment, these queries physically delete calculation codes that are 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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 the 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

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
calculation_rule	obsolete	1	delete from CALRULE where ENDDATE is not null AND (days(CURRENT TIMESTAMP) - days(ENDDATE) >= ?)	no	yes

• 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.

Categories

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
catalog_group	obsolete	1	delete from catgroup where markfordelete = 1	no	no

• 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.

Catalog entries

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
catentry	made_to_order	2	update catentry set state='D' where catentry_id in (select catentry_id_parent from catentrel, catentry where catentry_id_child=catentry_id and state='D') and catentry_id not in (select catentry_id_parent from catentrel, catentry where catentry_id_child=catentry_id and state!='D')	no	no
catentry	made_to_order	3	update rfqrspprod set catentry_id = null where catentry_id in (select catentry_id from catentry where state = 'D')	no	no
catentry	made_to_order	4	delete from catentry where state='D'	no	no

• In the production environment, these queries physically delete records that are marked for delete from the CATENTRY table.

• When we 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.

• IBM recommends to run these queries quarterly. Ensure that the number of mark for delete catalog entries do not exceed 5% of the total catalog entries in the CATENTRY table.

Client location

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
clientlocation	obsolete		delete from clientlocation where modificationtime <= CURRENT TIMESTAMP - ? days	no	yes

• This query deletes the client location records that have not been modified for more than the specified number of days.

Content versions

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

• IBM recommends to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

• IBM recommends to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of a sales catalog.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

• IBM recommends to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

• IBM recommends to run this query quarterly if Content Version is enabled. Do not keep more than five inactive versions of an object.

Content history

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
content_history	obsolete_for_base	10	delete from cmprmyobj where (days(current timestamp) - days(lastupdate)) >= ? and workspace = 'IBM_WC_BASE' and taskgrp='IBM_WC_BASE'	no	yes
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	yes
content_history	obsolete_taskgroups	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
content_history	obsolete_taskgroups	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

• In the staging or authoring environment, these queries delete 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 the staging or authoring environment when workspaces are enabled.

• IBM recommends to clean these records daily or weekly.

Contract

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
contract	obsolete	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
contract	obsolete	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

• In the production and staging environment, these queries delete 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.

Expected inventory records

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes from the RA table, the records that are marked for delete.

• When we 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

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

File uploads

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
fileupload	obsolete	1	delete from fileupload where days(current timestamp) - days(uploadtime) >=?	no	yes

• 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, we can reduce the number of records in the FILEUPLOAD table.

• IBM recommends that you run this query when the volume of records in the table is high. Run this query on the staging environment. If the site uploads directly to the production environment, run the Database Cleanup utility in the production environment.

Folder items

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
folderitem	orphaned_all	30	delete from folderitem where folderitemtype='MarketingContentType' and reference_id not in (select collateral_id from collateral)	no	no
folderitem	orphaned_all	40	delete from folderitem where folderitemtype='MarketingSpotType' and reference_id not in (select emspot_id from emspot)	no	no
folderitem	orphaned_all	50	delete from folderitem where folderitemtype='MemberGroupType' and reference_id not in (select mbrgrp_id from mbrgrp)	no	no
folderitem	orphaned_all	60	delete from folderitem where folderitemtype='ActivityType' and reference_id not in (select dmactivity_id from dmactivity)	no	no

• 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.

• IBM recommends to run this query quarterly.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
folderitem	orphaned_promotion	10	delete from folderitem where folderitemtype='PromotionType' and reference_id not in (select px_promotion_id from px_promotion)	no	no

• 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.

• IBM recommends to run this query quarterly.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
folderitem	orphaned_attribute	20	delete from folderitem where folderitemtype='AttributeDictionaryAttributeType' and reference_id not in (select attr_id from attr)	no	no

• 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.

• IBM recommends to run this query quarterly.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
folderitem	orphaned_marketing	30	delete from folderitem where folderitemtype='ActivityType' and reference_id not in (select dmactivity_id from dmactivity)	no	no
folderitem	orphaned_marketing	40	delete from folderitem where folderitemtype='MemberGroupType' and reference_id not in (select mbrgrp_id from mbrgrp)	no	no
folderitem	orphaned_marketing	50	delete from folderitem where folderitemtype='MarketingSpotType' and reference_id not in (select emspot_id from emspot)	no	no
folderitem	orphaned_marketing	60	delete from folderitem where folderitemtype='MarketingContentType' and reference_id not in (select collateral_id from collateral)	no	no

• 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.

• IBM recommends to run this query quarterly.

Folders

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
folder	orphaned	10	delete from folder where parentfolder_id is not null and parentfolder_id not in (select folder_id from folder)	no	no

• 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.

• IBM recommends to run this query quarterly.

Inventory adjustment codes

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes from the INVADJCODE table, the records that are marked for delete.

• When we 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

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
inventory_adjustments	obsolete	1	delete from invadjust where days(CURRENT TIMESTAMP) - days(adjustmentdate) >= ?	no	yes

• In the production environment, this query physically deletes from the INVADJUST table, the records that are older than a user specified time.

• When we 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.

• IBM recommends to delete inventory adjustment records monthly.

Item specification

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes from the ITEMSPC table, the records that are marked for delete.

• When we 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.

Marketing activities

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
marketing_activity	markedfordelete	3	delete from dmactattr where not exists (select 1 from dmactivity where dmactivity.dmactivity_id = dmactattr.dmactivity_id)	no	no
marketing_activity	markedfordelete	4	delete from dmemspotstats where not exists (select 1 from dmactivity where dmactivity.dmactivity_id = dmemspotstats.dmactivity_id)	no	no

• In the production and staging environment, these queries delete obsolete entries 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 associated with the deleted activities.

• IBM recommends to delete these entries weekly.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
marketing_activity	obsolete	1	delete from dmactivity where enddate is not null and (days(current timestamp) - days(enddate)) >=?	no	yes

• 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, we can delete the completed activities from the database.

• IBM recommends to delete completed marketing activities quarterly.

Marketing user behavior

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
marketing_userbehavior	obsolete	1	delete from dmuserbhvr where days(current timestamp) - days(lastupdated) >=?	no	yes

• In the production and staging environments, this query deletes obsolete records associated with recorded user behavior used for Management Center marketing activities,

• Marketing activity triggers, targets, and actions record information about events and actions 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.

  • (Professional) (Enterprise) Information for dialog activities. For example, events being waited for.

• IBM recommends to delete the obsolete records weekly.

Marketing logs

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
marketing_log	obsolete	1	delete from dmexplog where days(current timestamp) - days(created) >=?	no	yes

• In the production and staging environments, this query deletes obsolete records related to calculating Management Center experiment revenue statistics.

• To calculate the revenue 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.

• IBM recommends to delete obsolete records related to calculating revenue statistics weekly.

Notifications

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
notify	obsolete	1	delete from notify where not exists ( select notificationid from orders where notify.notificationid = orders.notificationid)	no	no

• 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.

Offer

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

Orders

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
order	canceled	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

• In the production environment, these queries delete the canceled order records that were not returned and not updated for a user specified time.

• Delete old cancel order records from the database to improve order process performance.

• You do not need to run the queries frequently. Run the queries when your volume of records is large.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
order	completed	15	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	20	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	25	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
order	completed	30	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

• In the production environment, these queries delete 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.

• When you run these queries, the query with sequence 20 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.

• Delete completed order records from the database to improve order capture process performance.

• You do not need to run the queries frequently. Run the queries when your volume of records is large or if large numbers of promotion codes are generated.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
order	deposited	15	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	20	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	20	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
order	deposited	25	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

• In the production environment, these queries delete the order records that were deposited but not returned, and not updated for a user specified time.

• When you run these queries, the query with sequence 20 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.

• Delete out of date and deposited order records from the database to improve order process performance.

• You do not need to run the queries frequently. Run the queries when your volume of records is large or if large numbers of promotion codes are being generated.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, these queries 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 the database to improve order process performance.

• IBM recommends to delete unused order records depending on the store requirements.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, these queries 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 queries frequently. Run the queries when your volume of records is large.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
order	shipped	15	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	20	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	25	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
order	shipped	30	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

• In the production environment, these queries delete the order records that were shipped, not returned, and not updated for a user specified time.

• When you run these queries, the query with sequence 20 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.

• Delete old shipped order records from the database to improve order process performance.

• You do not need to run the queries frequently. Run the queries when your volume of records is large or if large number of promotion codes are generated.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
order	stale_guest	1	delete from orders where orders_id in (select o from users u, (select o.orders_id o, o.member_id m, o.status s, o.lastupdate l, i.orderitems_id i from orders o, orderitems i where o.orders_id = i.orders_id and i.inventorystatus = 'NALC' union all select o.orders_id o, o.member_id m, o.status s, o.lastupdate l, i.orderitems_id i from orders o, store s, orderitems i where o.orders_id = i.orders_id and o.storeent_id = s.store_id and inventorysystem = -2) q left join rmaitem on i = orderitems_id where rmaitem_id is null and registertype = 'G' and m = users_id and s in ('P','I','W','N') and l <= CURRENT TIMESTAMP - ? DAYS)	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
order	stale_guest	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

• 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 the 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
order	stale_non_guest	1	delete from orders where orders_id in (select o from users u, (select o.orders_id o, o.member_id m, o.status s, o.lastupdate l, i.orderitems_id i from orders o, orderitems i where o.orders_id = i.orders_id and i.inventorystatus = 'NALC' union all select o.orders_id o, o.member_id m, o.status s, o.lastupdate l, i.orderitems_id i from orders o, store s, orderitems i where o.orders_id = i.orders_id and o.storeent_id = s.store_id and inventorysystem = -2) q left join rmaitem on i = orderitems_id where rmaitem_id is null and registertype <> 'G' and m = users_id and s in ('P','I','W','N') and l <= CURRENT TIMESTAMP - ? DAYS)	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
order	stale_non_guest	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

• 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 the 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.

Organization

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
organization	specified	1	select attachmenturl from attachment where cast(attachment_id as char(60)) != cast(attachmenturl as char(254)) and member_id in (select orgentity_id from orgentity where orgentity_id = ?)	yes	no
organization	specified	2	delete from member where member_id in (select orgentity_id from orgentity where orgentity_id = ?)	yes	no

• In the production and staging environment, these queries delete organizations that are no longer required in the 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 the site.

Payment rule

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
PaymentRule	obsolete	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

• In the production environment, these queries 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 these queries frequently. Run the queries when your volume of records is large.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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 lastupdate <= (current timestamp - ? days)))	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

• In the production environment, these queries delete 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 these queries frequently. Run the queries when your volume of records is large.

Preview token

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
previewtoken	obsolete	1	delete from previewtoken where (days(CURRENT TIMESTAMP) - days(enddate)) >= ? or status = 'R'	no	yes

Promotion codes

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
promotion_code	expired	10	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

• 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.

• IBM recommends to run the query frequently if large number of promotion codes are being generated.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
promotion_code	markfordelete	10	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

• In the production environment, the 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.

• IBM recommends to run the query frequently if large number of promotion codes are being generated.

Return reasons

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes from the RTNREASON table, the records that are marked for delete.

• When we 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.

Requests for quotes

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
rfq	obsolete	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

• In the production and staging environment, these queries delete 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.

Return merchandise authorizations (RMA)

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
rma	canceled	1	delete from rma where status = 'CAN' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?	no	yes

• 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
rma	completed	1	delete from rma where status = 'CLO' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?	no	yes

• 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.

Scheduled jobs

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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.SCCJOBREFUM = 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
scheduled_job	completed	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

• In the production environment, these queries 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. These queries 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.

• IBM recommends to run these queries based on how many scheduler jobs are configured and the frequency that these jobs run.

Search statistics

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
STAStatistics	obsolete	10	delete from srchstat where (days(CURRENT TIMESTAMP) - days(logdate)) >= ?	no	yes

• In the production and staging environment, this query deletes searches statistics.

• We 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.

• IBM recommends to clean or archive data monthly.

STAGLOG records

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
STAGLOG	obsolete	1	DELETE FROM STAGLOG WHERE STGPROCESSED = 1 AND STGSTMP <= (CURRENT TIMESTAMP - (? DAYS))	no	yes

• 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.

• IBM recommends to clean the staging logs after every successful stagingprop execution.

Store

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
store	specified	1	delete from storeent where storeent_id = ? and type='S'	yes	no

• In the development or staging environment, this query deletes the specified store from the system.

• We can completely remove a store from the system to reuse the store identifier.

• IBM recommends to delete a store only during initial environment setup.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
store	markedfordelete	1	select directory from store where store_id in (select storeent_id from storeent where markfordelete=1)	no	no
store	markedfordelete	2	select substr(filepath,1,length(rtrim(filepath))-length(rtrim(filename))-1) from fileupload where store_id in (select storeent_id from storeent where markfordelete=1)	no	no
store	markedfordelete	3	delete from storeent where markfordelete=1	no	no

• In the development or staging environment, these queries delete 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.

• IBM recommends to delete a store only during initial environment setup.

Subscriptions

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
subscription	expired_timebased_subscr	1	delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='TIME-BASED' and status =2	no	yes

• 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.

• IBM recommends to run this query if there is a high number of expired subscription orders over a set time.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
subscription	canceled_timebased_subscr	1	delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='TIME-BASED' and status=3	no	yes

• 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.

• IBM recommends to run this query if there are a high number of time-based subscription orders that were canceled over a set time.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
subscription	expired_recOrder	1	delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='NONE' and status =2	no	yes

• 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.

• IBM recommends to run this query if there is a high number of expired recurring orders over a set time.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
subscription	canceled_recOrder	1	delete from subscription where (days(current timestamp) - days(lastupdate)) >= ? and subscptype_id='NONE' and status =3	no	yes

• 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.

• IBM recommends to run this query if there is a high number of canceled recurring orders over a set time.

Tickler

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
tickler	obsolete	1	delete from tickler where (days(current timestamp) - days(lastupdate)) >= ? and status = 1	no	yes

• 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.

Users

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• 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.

• IBM recommends to delete inactive guest user accounts quarterly.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
user	registered	1	select attachmenturl from attachment where cast(attachment_id as char(60)) != cast(attachmenturl as char(254)) and 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	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
user	registered	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

• In the production environment, these queries 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.

• IBM recommends to delete inactive user accounts yearly.

User traffic

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
usrtraffic	obsolete	2	delete from usrtraffic where (days(CURRENT TIMESTAMP) - days(stmp)) >= ?	no	yes

• 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.

Vendors

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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

• In the production environment, this query physically deletes from the VENDOR table, the records that are marked for delete. When we 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.

Workspace

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
workspace	obsolete	30	delete from cmftask T1 where not exists (select 1 from cmftgtskrel T2 where T1.cmftask_id=T2.cmftask_id )	no	no
workspace	obsolete	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

• In the staging environment this query cleans up workspace metadata that are obsolete and deletes the following objects:

  • 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.

• Run this query to perform regular cleanup of the workspace metadata to maintain the performance of workspace, particular in the approval process.

• IBM recommends to run this query quarterly if workspace is enabled. Delete completed task groups before they reach 120 days old.

Object name	Object type	Sequence	SQL Statement	namearg	daysarg
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
workspace	canceled	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

• In the staging environment, this query cleans up workspace metadata that are canceled and deletes the following objects:

  • Any orphan tasks.

  • InstanceTask groups that are canceled.

  • Workspaces without task groups that are completed or workspaces that are canceled.

• Run this query to perform regular cleanup of the workspace metadata to maintain the performance of workspace, particular in the approval process.

• IBM recommends to run this query quarterly if workspace is enabled. Delete canceled task groups before they reach 20% of the total number of task groups.

Related concepts
Database Cleanup utility