Document Tree
Document Properties
Kbid
31Q250
Last Modified
20-Aug-2024
Added to KB
18-Jul-2024
Public Access
Everyone
Status
Online
Doc Type
Concepts
Product
ICM 12
Home ⋮ Knowledge Base
Concept - Product & Catalog JDBC/Batch Deletion and Unsharing
Share this document

Table of Contents


Introduction

The removal of business objects via bulk operation or via import (DELETE mode) is normally based on individual treatment of business objects. This approach is useful for a manageable quantity of objects but reaches its limits with large amounts of data.

For the removal of products, therefore, a deletion process has been implemented that works with JDBC/batch processing based on direct SQL statements. This is handled by the manager class ProductMgrJDBCBatchImpl that implements the interface ProductMgrJDBCBatch.

As the product unsharing via JDBC/batch is similar to product deletion in many ways, unsharing operations were also included in ProductMgrJDBCBatch and its implementation.

Also for the removal of a catalog and its subcategories, a deletion process has been implemented that works with JDBC/batch processing based on direct SQL statements. This is handled by the manager class CatalogMgrJDBCBatchImpl that implements the interface CatalogMgrJDBCBatch.

References

Implementation Details

Executing SQL Statements

The ProductMgrJDBCBatchImpl as well as the CatalogMgrJDBCBatchImpl use JDBC/batch processing based on direct SQL statements.

This means they execute SQL statements like delete from PRODUCT where uuid in (?) where (?) is a placeholder for a dynamically extended list of ? depending on the number of items, e.g., for 10 items to be deleted: (?,?,?,?,?,?,?,?,?,?). The table name PRODUCT is also just an example. A list of all affected tables can be found in section Affected Tables.

The SQL statements are executed as java.sql.PreparedStatement using the methods addBatch and executeBatch. The method executeBatch is only executed after a certain number of addBatch invocations.

Transaction Handling

All methods of ProductMgrJDBCBatchImpl and CatalogMgrJDBCBatchImpl mentioned in the table below will open a new transaction and commit afterwards in case there is no active transaction.
If there is already an active transaction, the removal will be executed and the transaction will be stored afterwards.

ProductMgrJDBCBatch

Class Structure

Methods

Method

Description

void removeAllProductsByDomainWithBatch(String domainID)

Removes all local products. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well.

void removeProductsByUUIDsWithBatch(List<String> productUUIDsToRemove, String domainID)

Removes all local products that match the provided UUIDs. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well.

void removeProductsBySKUsWithBatch(List<String> productSKUsToRemove, String domainID)

Removes all local products that match the provided SKUs. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well.

void unshareProductSetsFromDomainByUUIDs(List<String> productSetUUIDs, String sharingTargetDomainID, String sourceDomainID)

Unshares the given product sets from the domain. Only product sets with explicitly assigned products will be removed. Should some products be shared to the target domain through another product set, those products will remain available with all their data. In case no further product sets are assigned to the target domain after unsharing, a special empty product set will be assigned. If no such product set exists, it will be created.

void shareNothing(String sharingTargetDomainID, String sourceDomainID)

Unshares all products/product sets from the target domain that originate from the source domain. Additionally, a special empty product set will be assigned to the target domain. If no such product set exists, it will be created.

void removeSharingAssignmentsFromDomainByUUIDs(List<String> sharingTargetDomainIDs, String sourceDomainID)

Unshares all products/product sets from the target domains that originate from the source domain.

void removeProductSetsByUUIDs(List<String> productSetUUIDs, String sourceDomainID)

Unshares the given product sets from the domain and deletes the product sets afterwards. Only product sets with explicitly assigned products will be removed. In case no further product sets are assigned to the target domain after unsharing, a special empty product set will be assigned. If no such product set exists, it will be created.

void removeProductsFromProductSetByUUIDs(List<String> productUUIDsToRemove, String productSetUUID)

Unshares the products from all domains that are assigned to the product set.

Events

Product price change events only need to be created for products that have a price change. If a product gets removed, all product price change events can be deleted for that product in the domain.

A global cache clearing is executed on all affected objects. These are:

com.intershop.beehive.xcs.internal.price.ProductPriceChangeEventPO
com.intershop.component.mvc.internal.product.ProductLabelAssignmentPO
com.intershop.component.search.internal.searchaction.RedirectActionPO
com.intershop.component.mvc.internal.assignments.PageletEntryPointProductAssignmentPO
com.intershop.component.product.rating.orm.internal.ProductRatingAssignmentPO
com.intershop.component.rating.orm.internal.RatingCommentPO
com.intershop.component.rating.orm.internal.RatingCommentPOAttributeValuePO
com.intershop.component.shipping.internal.productextracharge.ProductShippingChargeOverridePO
com.intershop.component.shipping.internal.productextracharge.ProductShippingChargeOverridePOAttributeValuePO
com.intershop.component.shipping.internal.productextracharge.ProductShippingSurchargePO
com.intershop.component.shipping.internal.productextracharge.ProductShippingSurchargePOAttributeValuePO
com.intershop.component.marketing.internal.rebate.ActivePromoProductsPO
com.intershop.component.marketing.internal.rebate.RebateFilterObjectAssignmentPO
com.intershop.beehive.xcs.internal.productcompleteness.ProductCompletenessPO
com.intershop.component.product.validation.internal.result.orm.ProductValidationResultPO
com.intershop.beehive.xcs.internal.productapproval.ProductApprovalPO
com.intershop.beehive.xcs.internal.productapproval.ProductApprovalPOAttributeValuePO
com.intershop.beehive.xcs.internal.image.ImageProductAssignmentPO
com.intershop.component.image.internal.orm.ImageReferencePO
com.intershop.beehive.xcs.internal.price.DerivedProductListPricePO
com.intershop.beehive.xcs.internal.price.DerivedProductCostPricePO
com.intershop.beehive.xcs.internal.product.DerivedProductPO
com.intershop.beehive.xcs.internal.productset.ProductSetAssignmentPO
com.intershop.beehive.xcs.internal.catalog.TypedProductCategoryAssignmentPO
com.intershop.beehive.xcs.internal.product.ProductLinkPositionPO
com.intershop.beehive.xcs.internal.product.ProductLinkPO
com.intershop.beehive.xcs.internal.product.ProductLinkPOAttributeValuePO
com.intershop.beehive.xcs.internal.product.BundleAssignmentPO
com.intershop.beehive.xcs.internal.productvariation.VariableVariationAttributePO
com.intershop.beehive.xcs.internal.productvariation.VariableVariationAttributePOAttributeValuePO
com.intershop.beehive.xcs.internal.productvariation.ProductVariationPO
com.intershop.beehive.xcs.internal.productvariation.ProductVariationPOAttributeValuePO
com.intershop.beehive.xcs.internal.price.ProductCostPricePO
com.intershop.beehive.xcs.internal.price.ProductListPricePO
com.intershop.component.pricing.internal.pricelist.PriceScaleTableAssignmentPO
com.intershop.component.pricing.internal.pricelist.PriceScaleTableAssignmentPOAttributeValuePO
com.intershop.component.pricing.internal.scale.PriceScaleEntryPO
com.intershop.component.pricing.internal.scale.PriceScaleEntryPOAttributeValuePO
com.intershop.component.pricing.internal.scale.PriceScaleTablePO
com.intershop.component.pricing.internal.scale.PriceScaleTablePOAttributeValuePO
com.intershop.beehive.xcs.internal.catalog.ProductCategoryAssignmentPO
com.intershop.beehive.xcs.internal.product.ProductPORoleAssignmentPO
com.intershop.component.mvc.internal.alert.AlertPO
com.intershop.component.mvc.internal.alert.AlertPOAttributeValuePO
com.intershop.component.mvc.internal.filter.FilterProductAssignmentPO
com.intershop.beehive.xcs.internal.price.UserGroupPricePO
com.intershop.component.foundation.internal.syndication.SyndicationPO
com.intershop.component.foundation.internal.syndication.SyndicationPOAttributeValuePO
com.intershop.beehive.xcs.internal.product.ProductPO
com.intershop.beehive.xcs.internal.productset.ProductSetDomainAssignmentPO
com.intershop.beehive.xcs.internal.productset.ProductSetPO
com.intershop.beehive.xcs.internal.productset.ProductSetPOAttributeValuePO

Affected Tables

The following database tables are affected (in alphabetical order):

ACTIVEPROMOPRODUCTS
ALERT
ALERT_AV
BUNDLEASSIGNMENT
DERIVEDPRODUCT
DERIVEDPRODUCTLISTPRICE
DERIVEDPRODUCTCOSTPRICE
FILTERPRODUCTASSIGNMENT
IMAGEPRODUCTASSIGNMENT
IMAGEREFERENCE
PRICESCALEENTRY
PRICESCALEENTRY_AV
PRICESCALETABLE
PRICESCALETABLE_AV
PRICESCALETABLEASSIGNMENT
PRICESCALETABLEASSIG_AV
PRODUCT
PRODUCT_PEPA
PRODUCT_VR
PRODUCT_RA
PRODUCTAPPROVAL
PRODUCTAPPROVAL_AV
PRODUCTCATEGORYASSIGNMENT
PRODUCTCOMPLETENESS
PRODUCTCOSTPRICE
PRODUCTLABELASSIGNMENT
PRODUCTLINK
PRODUCTLINK_AV
PRODUCTLINKPOSITION
PRODUCTLISTPRICE
PRODUCTPRICECHANGEEVENT
PRODUCTRATINGASSIGNMENT
PRODUCTSET
PRODUCTSET_AV
PRODUCTSETASSIGNMENT
PRODUCTSETDOMAINASSIGNMENT
PRODUCTVARIATION
PRODUCTVARIATION_AV
RATINGCOMMENT
RATINGCOMMENT_AV
REBATEFILTEROBJECTASSIGNMENT
REDIRECTACTION
SHIPPINGOVERRIDE
SHIPPINGOVERRIDE_AV
SHIPPINGSURCHARGE
SHIPPINGSURCHARGE_AV
SYNDICATION
SYNDICATION_AV
TYPEDPRODUCTCATEGORYASSM
USERGROUPPRICE
VARIABLEVARIATIONATTRIBUTE
VARIABLEVARIATIONATT_AV

Configuration

The are some configuration values which can be used to change the behavior of the product removal with JDBC/batch.

If no property is specified, the old behavior is used.

Batch Size

This value defines the number of SQL statements sent together to the database.

If no property is specified, the default value is used.

intershop.productMgrJDBCBatch.removeProducts.batchSize

Default value: 50

IN Clause Size

The queries that delete the products from the database use a SQL IN clause which allows to specify multiple values in a WHERE clause. Since the number of elements in an IN clause is limited depending on the used database system, this property can be set accordingly.

intershop.productMgrJDBCBatch.removeProducts.inClauseSize

Default value: 500

Constraints for Product Deletion

The removal of products with JDBC/batch processing only works when the recycle bin option is deactivated. Otherwise, the standard “move to recycle bin” operation will be executed. See screenshot of the Preferences page in Intershop Commerce Management.

CatalogMgrJDBCBatch

Class Structure

Methods

Method

Description

void removeCatalogForBMECatImport(Catalog catalog, Domain domain)

Removes the given catalog and its subcategories recursively, but keeps its root category. This is necessary for the BMECat import (version update).

void removeCatalog(Catalog catalog, Domain domain)

Removes the given catalog and its subcategories recursively. It also removes its root category and the catalog itself.

removeCatalogCategoriesByUUIDs(List<String> categoryUUIDsToRemove, Domain domain)

Removes the given catalog categories identified by their UUIDs and their subcategories recursively.

Clear Cache

A global cache clearing is executed on all affected objects. These are:

com.intershop.beehive.xcs.internal.catalog.ProductCategoryAssignmentPO
com.intershop.beehive.xcs.internal.catalog.TypedProductCategoryAssignmentPO
com.intershop.beehive.xcs.internal.image.ImageCategoryAssignmentPO
com.intershop.beehive.xcs.internal.image.ImageSetDefinitionCategoryAssignmentPOAttributeValuePO
com.intershop.beehive.xcs.internal.image.ImageSetDefinitionCategoryAssignmentPO
com.intershop.component.pmc.internal.pagelet.PageletEntryPointLabelAssignmtPO
com.intershop.component.pmc.internal.pagelet.PageletEntryPointPOAttributeValuePO
com.intershop.component.pmc.internal.pagelet.entrypoint.assignment.PageletPageletEntryPointAssignmentPO
com.intershop.component.pmc.validation.internal.result.pep.PageletEntryPointValidationResultPO
com.intershop.component.pmc.internal.pagelet.PageletEntryPointPO
com.intershop.component.mvc.internal.assignments.PageletEntryPointCategoryAssignmentPO
com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPORoleAssignmentPO 
com.intershop.component.mvc.internal.catalog.CatalogCategoryLabelAssignmentPO 
com.intershop.beehive.xcs.internal.catalog.CatalogCategoryLinkPOAttributeValuePO
com.intershop.beehive.xcs.internal.catalog.CatalogCategoryLinkPO
com.intershop.component.image.internal.orm.ImageReferencePO
com.intershop.beehive.xcs.internal.product.ProductPO
com.intershop.beehive.xcs.internal.product.DerivedProductPO
com.intershop.component.marketing.internal.rebate.RebateFilterObjectAssignmentPO
com.intershop.component.mvc.internal.catalog.CatalogPO 
com.intershop.component.mvc.internal.catalog.CatalogPOAttributeValuePO
com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPO 
com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPOAttributeValuePO

Affected Tables

The following database tables are affected:

CATALOG
CATALOG_AV	
CATALOGCATEGORY
CATALOGCATEGORY_AV
CATALOGCATEGORY_PEPA
CATALOGCATEGORY_RA
CATALOGCATEGORYLABELASSIGNMENT
CATALOGCATEGORYLINK_AV
CATALOGCATEGORYLINK
DERIVEDPRODUCT
IMAGECATEGORYASSIGNMENT
IMAGEREFERENCE
IMAGESETCATEGORYASSI_AV
IMAGESETCATEGORYASSIGNMENT
PAGELETENTRYPOINT
PAGELETENTRYPOINT_AV
PAGELETENTRYPOINT_PA
PAGELETENTRYPOINT_VR
PAGELETENTRYPOINTLABELASSIGNMT
PRODUCT
PRODUCTCATEGORYASSIGNMENT
REBATEFILTEROBJECTASSIGNMENT
TYPEDPRODUCTCATEGORYASSM

Configuration

The are some configuration values which can be used to change the behavior of the catalog/category removal with JDBC/batch.

If no property is specified, the old behavior is used.

IN Clause Size

The queries that delete the categories from the database use a SQL IN clause which allows to specify multiple values in a WHERE clause. Since the number of elements in an IN clause is limited depending on the used database system, this property can be set accordingly.

intershop.catalogMgrJDBCBatch.removeCatalogCategories.inClauseSize

Default value: 500

Areas of Application

Product Deletion

BMECat Import (also Subcategory Deletion)

The BMECat import does not support explicit import modes such as "INITIAL", "UPDATE" or "DELETE". There is no possibility of deletion. Instead, the import is carried out using the specified version number. It is not possible to perform two imports with the same version number. When importing data with a higher version number, all existing data including all products in the catalog from a previous import are completely removed (the catalog itself remains). As this deletion of all categories and products can take a considerable amount of time, both the previous categories and products can be removed with the approach via JDBC/batch processing instead.

Example catalog versions: (Second file with version 2.0 will automatically overwrite import with version 1.0)

<HEADER>
		<CATALOG>
			<LANGUAGE>deu</LANGUAGE>
			<CATALOG_ID>1</CATALOG_ID>
			<CATALOG_VERSION>1.0</CATALOG_VERSION>  // version 1.0
			<DATETIME type="generation_date">
				<DATE>2023-06-07</DATE>
				<TIME>11:46:14</TIME>
			</DATETIME>
			<CURRENCY>EUR</CURRENCY>
		</CATALOG>
		...
<HEADER>
		<CATALOG>
			<LANGUAGE>deu</LANGUAGE>
			<CATALOG_ID>1</CATALOG_ID>
			<CATALOG_VERSION>2.0</CATALOG_VERSION>  // version 2.0
			<DATETIME type="generation_date">
				<DATE>2023-10-12</DATE>
				<TIME>09:11:56</TIME>
			</DATETIME>
			<CURRENCY>EUR</CURRENCY>
		</CATALOG>
		...

In order to activate the removal of products and/or catalog (and subcategories) via JDBC/batch processing, the following properties/preferences can be set:

intershop.bmeCatImport.removeProductsWithJDBCBatch=true
intershop.bmeCatImport.removeCatalogWithJDBCBatch=true

Product Bulk Deletion

Bulk deletion can be used to delete all products in a channel or master repository or to delete products based on search results or manual selection. Here it is suitable to use the approach via JDBC/batch processing.

The bulk deletion option is available at the master product and channel product level (also in partner channels) with the "Edit Selected" or "Edit All" buttons only. JDBC/batch processing is not applied if the standard "Delete" button is used, as no bulk deletion is performed.

The selected product deletion preference (for using the recycle bin option or not) will be applied to this product bulk deletion operation. See Constraints for Product Deletion.

In order to activate the bulk removal of products via JDBC/batch processing, the following property can be set:

intershop.productBulkOperation.removeProductsWithJDBCBatch=true

Standard Product Import (XML) in DELETE Mode

There is also an effective way to remove a mass of products from the system by using the standard XML product import in DELETE mode. The product import is available at organization level for the master repository as well as on channel level for the channel repository.

Further performance improvements could be achieved by using the deletion approach via JDBC/batch processing for the DELETE mode. The other product import modes like “INITIAL” or “UPDATE” have not changed.

The selected product deletion preference (for using the recycle bin option or not) will also be applied to this standard product import (XML) in DELETE mode. See Constraints for Product Deletion.

It is possible to configure the number of products that are processed in a chunk. The products to be deleted are collected in the bulk method until the required number that has been configured is reached. Then, the accumulated products are deleted all at once. At the end of the process, the remaining products are also deleted in the finish method. The default chunk size is 5000.

To change the default chunk size, the following property can be set (e.g., 1000):

intershop.productImport.deleteMode.chunkSize.removeProductsWithJDBCBatch=1000

In order to activate the removal of products in product import with DELETE mode via JDBC/batch processing, the following property can be set:

intershop.productImport.deleteMode.removeProductsWithJDBCBatch=true

Product Unsharing

The unsharing of products via JDBC/batch processing is available on organization and partner channel level. To activate this feature, the following property can be set:

intershop.productSharing.unshareProductsWithJDBCBatch=true

All of the following actions can use JDBC/batch processing:

Delete Channel Sharing Assignments

Unbenannt-20240327-145010.png

Delete Sharing Groups

Unbenannt-20240327-145145.png

Remove Products from a Sharing Group

Unbenannt-1-20240327-145233.png

Delete Channel Assignments from a Sharing Group

Unbenannt-20240327-145301.png

Delete Sharing Group Assignments from a Channel

Unbenannt-1-20240327-145328.png

Change Sharing Type of a Channel to “Share Nothing”

Unbenannt-20240327-145350.png

Catalog/Category Deletion

In Intershop Commerce Management it is also possible to delete a catalog with all subcategories. The catalog assignment of the products is also deleted but the products themselves are retained.

As this deletion of a large category tree can take quite some time, the catalog/categories can be removed via JDBC/batch processing.

image-20240304-135515.pngimage-20240304-135610.png

In order to activate the removal of catalogs and categories via JDBC/batch processing, the following property can be set:

intershop.catalogAction.removeCatalogWithJDBCBatch=true

Catalog/Category Unsharing

In Intershop Commerce Management it is possible to unshare a master catalog shared with the channel. The catalog’s product assignments, product and category links, label assignments, promotion assignments, dynamic product assignment filters, image assignments, and content configurations created in the channel are deleted. The channel products and/or the products sharing from master are retained.

As this deletion of a large number of relations of the channel products and channel level changes of catalog content can take quite some time, the catalog can be removed via JDBC/batch processing.

In order to activate the removal of catalogs and categories via JDBC/batch processing, the following property can be set:

intershop.mastercatalogAction.unshareCatalogWithJDBCBatch

image-20240621-083258.png

Disclaimer
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.