Document Properties
Kbid2T3542
Last Modified04-Feb-2020
Added to KB21-Sep-2012
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
Product
  • ICM 7.6
  • ICM 7.7
  • ICM 7.8
  • ICM 7.9
  • ICM 7.10

Concept - Mass Data Cleanup

Product Version

7.0

Product To Version

 

Status

final

1 Introduction

Intershop 7 supports installations with huge amount of data like products and users. Further, it supports the creation and deletion of web shops at runtime. The document describes how the mass data of these web shops is removed from the database.

Asynchronous Cleanup: The cleanup of mass data can not happen during execution of a web request, because the browser would run into a timeout resulting in an error page. That's why, mass data is asynchronously removed using the jobs for particular mass data objects. Another advantage of asynchronous cleanup is, that the cleanup job can be scheduled to run in the night to reduce the load on database during high web traffic periods.

Transaction Handling and Stored Procedures: Using the persistent API (ORM objects) to cleanup the data would lead to a huge amount of SQL statements resulting in a high load at database server as well as poor runtime performance. Furthermore, the transaction handling needs to be considered. Huge transactions should be avoided in OLTP applications like web shops. That's why, the cleanup jobs use PL/SQL stored procedures with accordingly optimized transaction handling.

Extensibility: The business objects are defined in one or more cartridges, additionally the customers can define or extend the business objects (which references domains, products and users) in their own cartridges, a cartridge must take care itself of properly removing referencing objects.

2 Implementation

2.1 Jobs

Each cartridge which has to take care of removing cross-cartridge business objects provides a pipeline to trigger its removal process. This pipeline is usually triggered periodically by a job (also prepared by the cartridge).

Currently the following jobs for the root site are prepared:

  • DeleteDomainReferences
  • DeleteUserReferences
  • DeleteProductReferences

Such a trigger pipeline tries to executes a deletion pipeline (definition is optional) in each started cartridge (processing the list of started cartridges in reverse order -> last created, first removed principle).

2.1.1 Mass Data Deletion Jobs

The known 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) can display the configured deletion jobs, see "Schedules > Scheduling" within the root domain.

2.1.2 Cartridge Properties

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

Example for bc_organization:

bc_organization.properties
intershop.cartridges.bc_organization.DomainDeletionPipeline = ORG_RemoveDomainReferences-Start

2.2 Stored Procedures

All stored procedures removing 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 7 changes the table structure in replication environments. Means, each stored procedure need to work within replication and non-replication environments. According utility PL/SQL packages are available.

2.2.1 Deployment

The default location for stored procedures are <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 prepares 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

2.2.2 Usage

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

2.2.3 Example

For example of sp_deleteFooBarsByDomain, see Sample Domain Deletion Stored Procedure

3 Glossary

This glossary describes the terms in this document:

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

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.

Customer Support
Knowledge Base
Product Resources
Support Tickets