Document Properties
Kbid2P8622
Last Modified04-Feb-2020
Added to KB05-Jun-2018
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
Product
  • ICM 7.6
  • ICM 7.7
  • ICM 7.8
  • ICM 7.9

Cookbook - DBInit (valid to 7.9)

1 Introduction

This cookbook answers typical questions arising in the context of the DBInit tool.

2 Recipe: Clean Database Account and DBInit

2.1 Problem

How to start DBinit to initialize a fresh database schema without any old database objects?

2.2 Solution

Starting DBinit with the default option -r=true or --recreate-tables=true will only drop the tables existing in the current deployed or development-configured version of all *.orm files.
Older tables or other database objects are not removed by default. To avoid conflicts regarding still existing old schema objects during DBInit call the ant task dbschema-clean beforehand.

cd IS_HOME/tool/misc
ant dbschema-clean

2.3 Discussion

ES1|...eserver/tools/misc> ant dbschema-clean
Buildfile: build.xml
build.environment:
dbschema-clean:
environment:
dump.clean:
     [echo] Clearing Database
     [echo]    dbtool.bat -p intershop.jdbc.password -is.share .../share .../bin/sqlplus -L _user_@ISSERVER.world @...eserver/tools/misc/droptables.sql
     [exec] SQL*Plus: Release ...
     [exec] Enter password:
     [exec] Connected to:
     [exec] Oracle Database ... Production ...
     [exec]
     [exec] [2011-11-07 15:34:09.753 +00:00] drop all objects of the db user.
     [exec] [2011-11-07 15:34:09.753 +00:00] user: _user_@jdbc:oracle:thin:@_host_:_port_:_sid_
     [exec] [2011-11-07 15:34:09.753 +00:00] please wait...
     [exec]
     [exec] [2011-11-07 15:34:39.883 +00:00] all objects of the db user dropped.
     [exec]
     [exec] Disconnected from Oracle Database ...
     [exec] ...
BUILD SUCCESSFUL
Total time: 31 seconds

3 Recipe: Java Debugging With DBInit/DBMigrate

3.1 Problem

How to debug the DBinit/ DBMigrate preparer within Intershop Studio?

3.2 Solution

Starting DBinit/ DBMigrate with the option -debug=debug_port or edit dbinit.bat|sh enables the Java debugging and Intershop Studio can attach to debug the DBinit/ DBMigrate process.

version

debug config

< 7.2.0

dbinit|dbmigrate -debug=debug_port

> 7.2.0

edit eserver/bin/dbinit.bat|sh

SET DEBUG=-Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,address=6666,suspend=n

Note:

Pipeline debugging is not available within DBinit/ DBMigrate.

3.3 Discussion

Example of debugging the DatabaseTablesPreparer(core) on port 6666 with Intershop version > 7.2.

ES1|.../eserver/bin> dbinit
Listening for transport dt_socket at address: 6666

Intershop Studio Configuration:

Intershop Studio Debug:

4 Recipe: DBInit/DBMigrate and New Database Tables, Indexes, Constraints and PL/SQL Code

Note

This recipe applies to DBInit and DBMigrate because there is a strong relationship between both of them when it comes to new tables, indexes and constraints as well as PL/SQL code. As a rule of thumb, indexes, constraints and named PL/SQL code should be recreated every time a DBMigrate is executed.

4.1 Problem

How to install new database tables, indexes, constraints, named PL/SQL code with DBInit and DBMigrate?
How to execute PL/SQL scripts with DBInit and DBMigrate?

4.2 Solution

4.2.1 Tables, Constraints and Indexes

New cartridge-specific EDL files define tables, constraints and index structures in the database.

These structures are generated with the Intershop Studio into the *.orm, dbindex.ddl and dbconstraint.ddl files.

  1. Start Intershop Studio.
  2. Open the Package Explorer.
  3. Expand the cartridge item.
  4. Right-click the EDL folder.
  5. From the context menu select Code Generator for Intershop.
  • Generate Code for *.orm files ( DatabaseTablesPreparer)
  • Generate Database Indexes for dbindex.ddl files ( DatabaseIndexesPreparer)
  • Generate Database Constraints for dbconstraints.ddl files ( DatabaseConstraintsPreparer)

You should also create the enfinitytable.sql file which is used to describe the classification of a table.
The enfinitytable.sql, dbindex.ddl and dbconstraints.ddl file should be placed at the location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts.
After that, the enfinitytable.sql, dbindex.ddl and dbconstraints.ddl files have to be used with the appropriate preparers in the dbinit.properties file, see below for an example.

Note

A similar version independent migration.properties file should be created to recreate the table descriptions, indexes and constraints upon every DBMigrate.

4.2.2 Named PL/SQL Code

The named PL/SQL code is stored within individual *.sql files (or *.pks and *.pkb files for PL/SQL package definitions and bodies) and should be included into spmainfile.ddl. The *.sql files and the file spmainfile.ddl should be placed at location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts. After that the spmainfile.ddl has to be used with the appropriate preparer in the dbinit.properties file, see below for an example.

Note

A similar version-independent migration.properties file should be created to recreate the PL/SQL code upon every DBMigrate.

4.2.3 PL/SQL Scripts

Single PL/SQL scripts should be placed at location <cartridge>/javasource/resources/<cartridge>/dbinit/scripts. After that, the PL/SQL script should be used with the appropiate preparer in the dbinit.properties file, see below for an example. For DBMigrate there are two scenarios:

1) If the script must be executed every time a DBMigrate is executed, one should call the script with the appropriate preparer in a version independent migration.properties file.

2) If the script must be executed only for a specific version, you should call the script with the appropiate preparer in a version dependent migration-to-<version>.properties file.

4.2.4 Examples

IS_SOURCE/<cartridge>/staticfiles/cartridge/dbinit.properties
#
# Database Tables Preparer, implicit added by DBInit as pre.Class1
# pre.Class1 = com.intershop.beehive.core.dbinit.preparer.database.DatabaseTablesPreparer

#
# Indexes
Class1  = com.intershop.beehive.core.dbinit.preparer.database.DatabaseIndexesPreparer \
          resources/<cartridge>/dbinit/scripts/dbindex.ddl

#
# Constraints
Class2  = com.intershop.beehive.core.dbinit.preparer.database.DatabaseConstraintsPreparer \
          resources/<cartridge>/dbinit/scripts/dbconstraints.ddl

#
# Stored Prodecure/Function main file
Class3  = com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/spmainfile.ddl

#
# Single PL/SQL script
Class4  = com.intershop.beehive.core.dbinit.preparer.database.SQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/<sql_file>.sql
resources/<cartridge>/dbinit/scripts/spmainfile.ddl
spool spmainfile.log

@sp_delete_X_ByDomain.sql
@sp_delete_Y_ByDomain.sql
...
@sf_foo_bar.sql

spool off
exit
IS_SOURCE/<cartridge>/staticfiles/cartridge/migration.properties
#
# Database Tables Preparer
Class1  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseTablesPreparer

#
# Indexes
Class2  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseIndexesPreparer \
          resources/<cartridge>/dbinit/scripts/dbindex.ddl

#
# Constraints
Class3  = com.intershop.beehive.core.dbmigrate.preparer.database.DatabaseConstraintsPreparer \
          resources/<cartridge>/dbinit/scripts/dbconstraints.ddl

#
# Stored Prodecure/Function main file
Class4  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/spmainfile.ddl

#
# Single PL/SQL script
Class5  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbinit/scripts/<sql_file>.sql
IS_SOURCE/<cartridge>/staticfiles/cartridge/migration-to-<version>.properties
#
# Single PL/SQL script
Class1  = com.intershop.beehive.core.dbmigrate.preparer.database.ExecuteSQLScriptPreparer \
          resources/<cartridge>/dbmigrate/scripts/<version>/<sql_file>.sql

5 Recipe: Execute a Single Preparer

5.1 Problem

The developer creates new DBinit preparer or changes the data. Sometimes the first execution fails. The old UI-driven dbinit tools supported the execution of selected preparers. How can the developer do the same thing in the dbinit command line tool?

5.2 Solution

The dbinit command line tool provides two options to select preparers.

  • A single preparer:

    dbinit.bat --exec-id=<cartridge>:<preparer>
    REM example
    dbinit.bat --exec-id=data_fs2:Class2
    REM Attention: You need to provide the name of the entry from the dbinit.properties (Class2) not the class name of the preparer (DatabaseConstraintsPreparer).
  • All preparers of the cartridge can be executed:

    dbinit.bat -t=<cartridge>
    REM example
    dbinit.bat -t=data_fs2

Note

The batch or shell script contains the debug port, so it is easy to debug your failed implementation.

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