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 to add the appropriate custom SQL statements.

The SQL statements that are 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 HCL 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. For more information, see Database Cleanup utility command script.
  • 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.
  • 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 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.

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.
  • You are recommended 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.
  • You are recommended 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 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.
  • You are 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.

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.

Auctions

Object name Object type Sequence SQL Statement namearg daysarg
auction retracted 1 delete from auction where austatus = 'R' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ? no yes
auction settlement_closed 1 delete from auction where austatus = 'SC' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ? no yes
  • In the production and staging environment, the settlement_closed query deletes the auction record when the auction is finished and the record exists from before a specified day.
  • In the production and staging environment, the retracted 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 these queries frequently to reduce the number of stored records.
Object name Object type Sequence SQL Statement namearg daysarg
auctionlog obsolete 1 delete from auctionlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ? no yes
  • 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.
Object name Object type Sequence SQL Statement namearg daysarg
bidlog obsolete 1 delete from bidlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ? no yes
  • 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.

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

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 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.
  • You are recommended 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 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

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
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_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
  • In the production environment, these queries physically delete 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.
  • You are recommended 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 HCL Commerce and maintain the performance of Content Version.
  • You are recommended 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 HCL Commerce and maintain the performance of Content Version.
  • You are recommended 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 HCL Commerce and maintain the performance of Content Version.
  • You are recommended 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 HCL Commerce and maintain the performance of Content Version.
  • You are recommended 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 your staging or authoring environment when workspaces are enabled.
  • You are recommended 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.

Coupon

Object name Object type Sequence SQL Statement namearg daysarg
coupon_promotion expired 1 delete from cppmn where days(current timestamp) - days(enddate) >=? no yes
  • 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,

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

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.

Experiments

Object name Object type Sequence SQL Statement namearg daysarg
explog obsolete 1 delete from explog where (days(CURRENT TIMESTAMP) - days(created)) >= ? no yes
  • In the production and staging environment, this query deletes obsolete records that are related to calculating 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.

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

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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended to run this query quarterly.

Forum

Object name Object type Sequence SQL Statement namearg daysarg
forummsg obsolete 1 delete from forummsg where msgstatus = 'D' or (days(CURRENT TIMESTAMP) - days(posttime)) >= ? no yes
  • 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.

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

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 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.
  • You are recommended 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 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.

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 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.
  • You are recommended 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, you can delete the completed activities from the database.
  • You are recommended 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 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.
    • HCL Commerce EnterpriseInformation for dialog activities. For example, events being waited for.
  • You are recommended 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 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.
  • You are recommended to delete obsolete records that are related to calculating revenue statistics weekly.

Messages

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

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 your 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 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 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 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 your 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 your 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 your database to improve order process performance.
  • You are recommended to delete unused order records depending on your 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 12 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 your 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 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.
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 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.

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

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.
  • You are recommended 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.
  • You are recommended to run the query frequently if large number of promotion codes are being generated.

Policy

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

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

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.
  • You are recommended 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.
  • 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.
  • You are recommended 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.
  • You are recommended 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.
  • You can completely remove a store from the system to reuse the store identifier.
  • You are recommended 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 HCL Commerce Accelerator Hub store.
  • This query completely removes a store from the system to be able to reuse the store identifier.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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.
  • You are recommended 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 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.

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.
  • You are recommended 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.
  • You are recommended to run this query quarterly if workspace is enabled. Delete canceled task groups before they reach 20% of the total number of task groups.