Document Tree
Document Properties
Kbid
2T3542
Last Modified
14-Feb-2024
Added to KB
21-Sep-2012
Public Access
Everyone
Status
Online
Doc Type
Concepts
Product
  • ICM 7.10
  • ICM 11
Concept - Mass Data Cleanup (valid to 11.10.0)

Introduction

Intershop Commerce Management 11 supports installations with large amounts of data, including products and users. Additionally, it allows for the creation and deletion of web shops at runtime. The document explains how the mass data of these web shops is removed from the database.

Asynchronous Cleanup: To avoid browser timeouts and error pages, mass data cannot be cleaned up during web request execution. Instead, jobs are used to asynchronously remove specific mass data objects. Asynchronous cleanup also allows for scheduling the cleanup job during low web traffic periods, reducing the load on the database.

Transaction Handling and Stored Procedures: Using the persistent API (ORM objects) to cleanup the data leads to a large amount of SQL statements resulting in a high load on the database server and poor runtime performance. Additionally, it is important to consider transaction handling and avoid large transactions in OLTP applications like web shops. Therefore, the cleanup jobs use PL/SQL stored procedures with accordingly optimized transaction handling.

Extensibility: The business objects are defined in one or more cartridges. Customers can also define or extend the business objects, which reference domains, products, and users in their own cartridges. Each cartridge is responsible for properly removing referencing objects.

Glossary

This glossary describes the terms in this document:

Term

Definition

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. PL/SQL's general syntax resembles that of Ada or Pascal. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

Oracle PL/SQL program unit - Stored Procedure

Procedures are similar to Functions, in that they can be executed to perform work. The primary difference is that procedures cannot be used in an SQL statement. Another difference is that it can return multiple values. Procedures are traditionally the workhorse of the coding world and functions are traditionally the smaller, more specific pieces of code. PL/SQL maintains many of the distinctions between functions and procedures found in many general-purpose programming languages, but in addition, functions can be called from SQL, while procedures cannot.

Implementation

Jobs

Each cartridge responsible for removing cross-cartridge business objects has a pipeline for triggering the removal process. The pipeline is triggered periodically by a job, also prepared by the cartridge.

Currently the following jobs for the root site are available:

  • DeleteDomainReferences

  • DeleteUserReferences

  • DeleteProductReferences

The trigger pipeline executes a deletion pipeline (definition is optional) in each started cartridge. The cartridges are processed in reverse order, following last created, first removed principle.

Mass Data Deletion Jobs

The available jobs for mass data deletion with detailed job parameters are:

Type

Cartridge

Job Name

Job Interval
in seconds

Job Enabled

Attributes

Pipeline

Description

Domain

core

DeleteDomainReferences

60

true


TriggerDeleteDomainReferences

Triggers deleted domain cleanup.

User

core

DeleteUserReferences

60

true

BlockSize = 200

TriggerDeleteUserReferences

Triggers deleted user cleanup.

Product

xcs

DeleteProductReferences

60

true

BlockSize = 200

TriggerDeleteProductReferences

Triggers deleted product cleanup. The maximum number of products which the
job can process can be set via job parameter attribute "BlockSize". Default is 200.

The System Management Console (SMC) displays the configured deletion jobs, navigate to Schedules | Scheduling within the root domain.

Cartridge Properties

Each mass data cleanup job calls all pipelines in cartridges defining corresponding property with the pipeline and start node name:

Example for bc_organization:

bc_organization.properties

intershop.cartridges.bc_organization.DomainDeletionPipeline = ORG_RemoveDomainReferences-Start

Stored Procedures

All stored procedures that remove domains, users, and products referencing objects are named

Type

ProcedureName

Domain

sp_delete*ByDomain

User

sp_delete*ForUser

Product

sp_delete*ForProduct

whereby * gives a hint about the objects, the stored procedure removes.

Replication Environments

Intershop Commerce Management 11 alters the table structure in replication environments. It means that each stored procedure must function in both replication and non-replication environments. According utility PL/SQL packages are available.

Deployment

The default location for stored procedures is <cartridge>\javasource\resources\<cartridge>\dbinit\scripts.

All stored procedures removing domains are created and replaced within the database by DBInit and DBMigrate.

The usage of preparers are:

  • Preparer (DBInit)

    • com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer resources/<cartridge>/dbinit/scripts/

      • <spSingleFile> or

      • spmainfile.ddl

  • MigrationPreparer (DBMigrate)

    • com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer resources/<cartridge>/dbinit/scripts/

      • <spSingleFile> or

      • spmainfile.ddl

Usage

All stored procedures removing domains are triggered by several executions of the pipelet ExecuteStoredProcedure.

Example

For example of sp_deleteFooBarsByDomain, see Sample Domain Deletion Stored Procedure.

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.