OWB to ODI migration

Oracle has finaliy presented an oficial OWB to ODI migration utility
and article describing typical migration path!

Alternative tools and ways for OWB to ODI migration:

  1. OWB2ODI Converter
  2. Export Metadata from OWB by OMBScript and generation of ODI XML files.
Veröffentlicht unter DWH, Oracle

ETL tool comparison

Loading Type

Informatica: ETL, ELT with “Pushdown Optimization Option”
Pentaho: ETL
DataStage: ETL
Microsoft SSIS: ELT

System Plfatform

OWB: Windows, Unix
ODI: Windows, Unix
Informatica: Windows, Unix
Pentaho: Windows, Unix, Other Platforms (JDK)
DataStage: Windows, Unix, Other Platforms
Microsoft SSIS: Windows


OWB: Parallel steps in OWB flows (Mapping or subflows). Parallel SQL.
ODI: Parallel processes in scenarios.
Informatica: Partition Option (parallel threads). Asynchronous task steps in workflows.
Pentaho: Parallel job execution (asynchronous). Parallel mapping execution (e.g. parallel DML).
DataStage: Parallel jobs and parallel stages.
Microsoft SSIS: asynchronous data flows (tasks) in a package.


ODI: Load balancing for distributed agents executing etl scenarios.
Informatica: Informatica cluster. Public cloud version.
Pentaho: Cluster (carte server).
DataStage: MPP, cluster. AWS cloud.
Microsoft SSIS: SQL Server Cluster. Azure cloud.

ETL Proccess

OWB: jobs with processes(PL/SQL) and mappings (generated PL/SQL)
ODI: Scenarios containing processes (knowledge modules)
Pentaho: jobs with transformations
Informatica: sessions with workflows/worklets and tasks/tasklets
DataStage: Server job, DataStage job, Sequence Job (workflow)
Microsoft SSIS: package

Deployment facility

OWB: Yes (Configurations, Export/Import between repositories)
ODI: Yes
Informatica: Sessions with environment specific parameter files.
Pentaho: Export/Import XML files between environments. Environment properties (as file or db content).
DataStage: Information Server Manager – build, deploy. Transfer binaries (same platform). ODF and XML.
Microsoft SSIS: Yes

Engine based / code generator

OWB: PL/SQL packages executed by OWB repository.
ODI: Agents executing generated scenarios (Interpreter).
Informatica: Engine.
Pentaho: Engine interpreting XML jobs and transformations.
DataStage: Binaries (linked libraries).
Microsoft SSIS: SSIS engine executing packages.

Environment configuration

OWB: In repository of target environment.
ODI: ?
Informatica: Environment specific parameter files on session level. Multiple datasources/targets.
Pentaho: Environment properties (as file or db content). Any kind of JDBC connection.
DataStage: Configuration files (APT_CONFIG_FILE)
Microsoft SSIS: “Configurations” in packages.

Numbers of available transformation functions

OWB: about 30
Informatica: 58
Pentaho: 60
DataStage: 70
Microsoft SSIS: 30

Slowly Changing Dimension

OWB: SCD Mappings (Enterprise ETL Option)
ODI: Yes
Informatica: Supports Full history, recent values, Current & Prev values.
Pentaho: Yes
DataStage: SCD “stage” for Type 1 and 2
Microsoft SSIS: Yes, with a Wizard but difficult to modify

Rejected Records

OWB: Error tables
ODI: Some (oracle) “integration knowledge modules” has rejection logic. “check knowledge modules” for business check rejection suggested.
Informatica:. Reject (bad) files. Rejected records in logs.
Pentaho: Error handling data connections and validation nodes
DataStage: Yes
Microsoft SSIS: Can use conditional split transformation and error redirection to either CSV files or tables.

Debugging Facility

OWB: basic debugging, breakpoints
ODI: simulation mode
Informatica: session debugger, breakpoints, data movement, data analysis
Pentaho: mapping debugger, breakpoints, data movement, data analysis
DataStage: Supports basic debugging facilities for testing.
Microsoft SSIS: breakpoints, Data Viewer and error handling

Languages (extensibility)

ODI: Java, Jython, Java Bean Shell
Informatica: Java transformations.
Pentaho: Java, JavaScript
DataStage: Script, C++
Microsoft SSIS: C#, VB.NET (2005)

Application Integration Functionality

OWB: Difficult
ODI: JAX-WS, JMS connectors
Informatica: WebServices (XML, JSON), JMS, IBM MQ, Tibco Rendevous
Pentaho: JMS node, possible (e.g. java transformation nodes)
DataStage: WISD ingegration (web services), InfoSphere Streams, IBM MQ stages, Java/C++ API.
Microsoft SSIS: WebService Task, MSMQ integration. Any .NET functionality possible.

CDC and real time ETL

OWB: Not available
ODI: Oracle Golden Gate knowledge modules
Informatica: PowerExchange CDC
Pentaho: possible (e.g. java transformation nodes)
DataStage: IBM InfoSphere CDC. Always-on stages (e.g. ISD, MQClient or custom Java).
Microsoft SSIS: StreamInsight

Metadata: Ability to view & navigate metadata on the web

OWB: OWB Repository Browser
ODI: ODI Metadata Navigator (weblogic)
Informatica: Job sessions can be monitored using Informatica Classes
Pentaho: Pentaho Repository
DataStage: InfoSphere Metadata workbench. DataStage Operations Console for operational view (monitoring, statistics, performance)
Microsoft SSIS: Does not support

Ability to Customize views of metadata for different users

ODI: User roles (repository)
Informatica: Supports.
Pentaho: User rights (read,write,access) on jobs and transformations
DataStage: User roles
Microsoft SSIS: Does not support

Metadata repository can be stored in RDBMS

OWB: Yes (only)
ODI: Yes (only)
Informatica: Yes
Pentaho: Yes (XML, RDBMS, Enterprise Repository)
DataStage: No. But the proprietary meta data can be moved to a RDBMS using the DOC Tool
Microsoft SSIS: Does not support

Metadata API

OWB: OBMScript. Oracle repository tables.
ODI: Java API. DB repository tables.
Pentaho: XML files. DB repository tables (DB repository). JSR-283 (Enterprise repository).
Microsoft SSIS:

Command line operation

ODI: Yes (start, stop, sleep and many others)
Informatica: Yes (Pmcmd – start, stop, abort or schedule workflow )
Pentaho: Start/Stop of job and transformations (pan and kitchen)
DataStage: Start/Stop, compile, export/import metadata (many different commands)
Microsoft SSIS: Yes (Dtexec.exe)

Ability to maintain versions of mappings

OWB: Very Difficult (through export/import with OMBScript on object level possible).
ODI: Yes. Internal version management on object level.
Informatica: Yes (Additional licence needed). SVN throught XML export/import possible.
Pentaho: SVN (for XML repositories) or in basic locking/versioning on object level in “Enterprise repository”
DataStage: CSV and ClearCase support.
Microsoft SSIS: Difficult (binary dtsx files).

Job Controlling & Scheduling

OWB: No (external scheduler or Oracle scheduler needed)
ODI: Yes
Informatica: Very good
Pentaho: only for “Enterprise repository”
DataStage: Does not support directly (no option). But possible to call custom programs after the job get executed.
Microsoft SSIS: Job Agent, Email Notification


OWB: Average. Slow learning curve. Bugs (out of support). Slow UI.
ODI: Average. Slow learing curve. Own ETL tooling vision.
Pentaho: Very good. Steep learning curve. Less mature than competitors (e.g. weak logging and monitoring). Good UI. No installation needed.
Informatica: Very good. Steep learning curve. Clean and intuitive UI.
Microsoft SSIS: Good. Steap learning curve. VisualStudio as UI. C# knowledges needed.


OWB: Included in Oracle DB. Additional options could be necessary (Partitioning, Enterprise ETL, etc.). About 15.000 core/year.
ODI: Per Core or Named User. About 30.000 per core/year
Pentaho: Free. Support costs about 30.000 per CPU/year.
Informatica: Per Server. Additional options could be necessary (e.g. “Pushdown Optimization”). Expensive and intransparent.
DataStage: Per Processor (Processor Value Unit)
Microsoft SSIS: Part of SQL Server license (CAL or Core).

Veröffentlicht unter DWH

IBM Message Broker logging overview

—Provided from IBM Message Broker platform

  • Rudimentary Activity-Log (Start/End Broker, deployments etc. Operational perspective)
  • Standart error logging (flow or transaction fails, queue full etc.)
  • Logging in Flow (LOG Command in ESQL, Logging from Java Compute Node)
  • Trace Node
  • Monitoring events


  • Log4j
  • File Output Node
  • Logging into Database
  • Logging into MQ Queue
Logging type Target Control Content
Activity Log Syslog No Operational info
Standart error log Syslog No Operational info (unhandled exceptions)
Logging in flow Syslog Output from ESQL (LOG statement) and Java ComputeNode (e.g. MbService.logInformation) Any. single line of text.
Trace node One of: local error log (syslog), user trace (binary), file. Enable/Disable trace. Message from message flow and additional predefined message from message catalog.
Monitoring events MQ Topic Enable/Disable events. Only method for logging BEFORE flow begins (transaction start). Only method for log transactions. Anything in $Root, $Enviroment, $LocalEnviroment, $Properties, $DestinationList.
Log4j Any log4j destination Only from Java ComputeNode Any
FileOutputNode Any file path Per dynamic routing possible. Anything in $Root, $Enviroment, $LocalEnviroment, $Properties, $DestinationList.
Database logging Database From ESQL or Java Compute Node Any
Logging into Queue Queue MQ Output node in flow needed. Online Enable/Disable per dynamic routing possible. Message (But any message can be constructed by preceding node).

Local Enviroment (Dynamic Routing)
Message Broker Logs
Message Broker Trace
Message Flow Monitoring

Veröffentlicht unter IBM Message Broker

IBM Message Broker automatic deployment (windows)

IBM Message Broker folgt der Unix Ideologie und hat eine Reihe von Konfiguration/Administration CommandLine Tools.
Im Unix Style, werden diese Tools durch Shell Scripte erweitert und automatisiert.
Im Windows kann das aber zum Problem werden.
Zum Beispiel ist die Einbidung von IBM Message Broker ins CI Enviroment (Jenkins, Hudson, CrusieControl etc.) ein solches Problem.

Ein BAR File (ein Message Broker Deployment Archive) wird auf Windows mittels “mqsideploybar.exe” Commando deployed.
Das Programm geht von einem Enviroment aus, das durch Asuführung von “mqsiprofile.cmd” vorbereitet wird.
Die Ausführung von CMD Dateiein im Windows öffnet ein neues Shell und terminiert nicht.
Dadurch könnnen zwei oben nacheinander follgende Aufrufe im ANT nicht sequentiell ausgeführt werden (ANT ist immer noch ein sehr verbreiteter Deployment Instrument für komplexe Enviroments).

Um das Problem unzugehen, braucht man eine kleine BAT Datei, die den “mqsiprofile.cmd” mit “call” Aufruf “sourced”.

rem %1 - broker name
rem %2 - execution group name
rem %3 - bar file

call mqsiprofile.cmd
mqsideploy.exe %1 -e %2 -a %3 -m

Der ANT Aufruf sieht dann follgendermasen aus:

<exec executable="deploy.bat" spawn="false" resolveexecutable="true" vmlauncher="false">
  <arg value="${broker}" />
  <arg value="${executiongroup}" />
  <arg value="${barfile}" />


Veröffentlicht unter IBM Message Broker

Oracle ODI Interface-Sprache

Oracle ODI erkennt (wie jede andere gute Java Anwendung) die Sprache von der Betriebsystem-Umgebung.

Aus unterschiedlichen Gründen (multinationales Entwickler-Team, schlecte Übersetzung, englische Dokumentation oder Tutorials) möchte man jedoch eine andere IDE Sprache festlegen. Um die Sprache in der aktuellen ODI Version zu ändern (, muss man entsprechende Language Parameter an die Java-Virtual Machine übergeben.

Das ODI Client liest beim Start follgende Konfigurations-Datei [ODI_HOME]\oracledi\client\ide\bin\ide.config
Man muss also nur zwei zusätzliche Zeilen am Ende dieser Datei hinzufügen, um z.B. den US Locale zu setzen:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=us

Oracle Data Integrator

Veröffentlicht unter DWH, Oracle

Java Hibernate + Oracle. Performance optimization.

Man kann grundsätzlich auf 3 verschiedenen Ebenen das Programm optimieren:

  1. Datenmodell Ebene
  2. Oracle Tuning (Indexes, Hints, Partitioning, Hardware und Betriebssystem Optimierung)
  3. Hibernate Tuning (fetch mode, caching, programm logic)

Das Tuning auf dem Datenmodell Ebene ist eher unwahrscheinlich, wird meistens nur bei besonders hartnäckigen Problemen gemacht, in meisten Fällen wird dabei eine Denormalisierung von Daten durchgeführt. Optimierungen auf Datenmodell Ebene werden oft noch bei der Architektur-Phase gemacht.
Das Tuning auf Oracle Seite ist sehr nützlich und muss auf jeden Fall bei Performance Problemen gemacht werden.
Das Hibernate Tuning ist meist flexibles aber gleichzeitig meist komplexes Prozess.

Um eine effektive Optimierung machen zu können, muss zuerst das Performance-Problem lokalisiert werden. Das erreicht man durch Generierung von realitätsnahen Workloads auf der Applikation, mit gleichzeitigem Logging von allen Hibernate+Datenbank relevanten Events (Caching, SQL’s, timings).


Hibernate hat einen eingebauten Mechanismus zum loggen von SQL Statements auf Java Konsole, das mit einem Konfigurations-Parameter eingeschaltet werden kann (Spring Beispiel)

<bean id="exampleHibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
 <property name="properties">
   <prop key="hibernate.show_sql">true</prop>  <-- show_sql statements on console -->
   <prop key="hibernate.use_sql_comments">true</prop>  <-- generate additional comments for every statement -->
   <prop key="hibernate.format_sql">true</prop>  <-- format_sql_statements -->
   <prop key="hibernate.generate_statistics">true</prop>  <-- generate statistics for sql statements -->

Alternativer Weg ist das Logging von SQL Statements mit log4j framework.

# Log all SQL Statements
<logger name="org.hibernate.SQL">
    <level value="DEBUG"/>
# Logs SQL statements for id generation
<logger name="org.hibernate.id">
    <level value="INFO"/>
# Logs the JDBC-Parameter which are passed to a query (very verboose)
<logger name="org.hibernate.type">
    <level value="DEBUG"/>
# Logs cache related activities
<logger name="org.hibernate.cache">
    <level value="DEBUG"/>

Nächste Alternative wäre die Verwendung von speziellen JDBC logging frameworks wie log4jdbc oder P6Spy, die wesentlich detailliertere Informationen über JDBC Aktivitäten liefern können, wie z.B. “timings”, “result sets”, connection statistics etc. Solche frameworks werden als Proxy für DataSources oder Connection eingeschaltet, was sich leicht im Java Code, oder deklarative (Spring) implementieren lässt.

Noch ein Weg, um Übersicht über Hibernate Aktivitäten zu bekommen, ist die Verwendung von Hibernate Statistiken.
Die Sammlung von Statistiken wird mit “generate_statistics” Flag eingeschaltet, danach kann man (in regelmäßigen Abständen) die Metriken auf Konsole oder in JMX publizieren. Hier ein Beispiel der JMX Publikation mit Hilfe von Spring:

<bean id="mbeanServer" class="org.springframework.jmx.support.MBeanServerFactoryBean">
	<property name="locateExistingServerIfPossible" value="true"/>
<bean id="jmxExporter" class="org.springframework.jmx.export.MBeanExporter">
	<property name="server" ref="mbeanServer"/>
	<property name="beans">
			<entry key="Hibernate:application=Statistics" value-ref="jmxStatisticsBean"/>
<bean id="jmxStatisticsBean" class="org.hibernate.jmx.StatisticsService">
	<property name="statisticsEnabled" value="true"/>
	<property name="sessionFactory" ref="ganesha.sessionFactory"/>

Es gibt mehrere Tools für Visualisierung von Hibernate JMX Statistiken, ich kann z.B. Hibernate-JConsole Tool empfehlen, das eine sehr übersichtliche Darstellung von Metriken liefert. Hibernate-jconsole ist ein JConsole/VisualVM plugin, das beim Ausführen aber unbedingt das Hibernate JAR braucht.
Beispiel der Verwendung: java -jar hibernate-jconsole-1.0.4.jar -J-Dhibernate.classpath=/home/user/hibernate-core-3.6.2.Final.jar

Hibernate Statistiken haben in der jetzigen Version zwei kleine Macken. Erstens – werden die SQL Abfragen die mit Criteria API generiert wurden nicht berücksichtigt (es gibt dazu ein Bug/Workarround. Zweitens – werden keine impliziten Queries in die Statistiken eingenommen (z.B. Lazzy-Load Queries oder Queries die durch FetchMode.SELECT generiert wurden). Um auch die implizite Queries analysieren zu können (und überhaupt sehen zu können), werden SQL logging von Hibernate oder spezielle JDBC logging frameworks benötigt.
Ein weiteres Problem von Hibernate-Statistiken, ist die Detaillierung von Cache Information. Da Hibernate sich von Caching-Technologie abstrahiert, kann es nur rudimentäre Informationen darüber liefern wie z.B. (Hits, Misses, Puts). Um mehr Daten über die Caches zu sehen, werden spezifische Tools verwendet abhängig von eingesetztem Cache-Provider. Zum Beispiel, für EHCache gibt es auch ein JMX Monitoring Mechanismus (kann parallel zum Hibernate-Statistik verwendet werden), das zusätzliche Informationen liefert wie: Anzahl Objekte im Speicher und auf der Festplatte, Speicherverbrauch, Evicts.

Ein komplett anderer Logging-Ansatz wäre die Nutzung von Oracle Datenbank-Tools (trace, “Top Statements” in Enterprise Manger, AWR). Solche Instrumenten erfordern leider oft Administrations-Rechte, was ein üblicher Java-Developer selten bekommt.

Oracle Tuning

Auf der Oracle Seite sollte man sich als Erstes den Execution Plan von SQL Statements anschauen. Das häufigste und einfachste Performance-Problem, sind die fehlenden Indexe. Oracle hat allerdings viele weitere Optimierungs-Möglichkeiten, die aber nur durch einen erfahrenen Oracle Spezialist geplant und implementiert werden sollen:

  • Caching
  • Partitionierung
  • SQL Performance Analyzer Tool
  • SQL Profiles (static execution plans)
  • Optimierungen auf Hardware und Betriebsystem-Ebene

Oracle hat interne Optimierungs-Mechanismen (Statistiken, Optimizer, Caches), die dynamisch versuchen den Durchsatz von der Datenbank zu verbessern. Aus diesem Grund, beim Performance-Tests, müssen folgende Bedingungen maximal erfühlt werden:

  • Hardware und Software vom Oracle Test-Server soll identisch zum Produktiv-Server sein.
  • Der Datenbank-Inhalt soll identisch zum Produktive-Server sein.
  • Auf dem Test-Server während des Tests müssen gleiche Prozesse laufen, wie auf dem Produktive Server.

Idealerweise sollte man direkt auf Produktive-Server testen, was leider nur sehr selten möglich ist.
Alternative erzeugt man eine vollständige Kopie von Produktive-System (z.B. mit FileSystem snapshots, oder RMAN). Mit Hilfe von realitätsnahen Test-Workloads, oder speziellen Mechanismen zum Workload-Simulation, wie Oracle Database Replay, wird auf dem Test-System das Produktive-Workload simuliert. Erst dann kann man mit den Tests und Optimierungen beginnen, um die beste Ergebnisse zu bekommen (bzw. überhaupt das Performance-Problem zu lokalisieren).

Hibernate Tuning

Im Hibernate gibt es grundsätzlich zwei Optimierungs-Möglichkeiten:

  1. Optimierung von Assoziationen (Eager vs Lazy loading, FetchMode, BatchSize etc.)
  2. Caching (Query und Second-Level caching)

Mit Caching, versucht man die Datenbank Abfragen vollständig zu vermeiden.
Hibernate hat zwei Ebenen von Entity Caches – First Level Cache (gehört zur Hibernate Session) und ein Second-Level-Cache (wird von mehreren Hibernate Sessions zusammenverwendet). Mit dem Second-Level-Cache (und zusätzlich einem Query-Cache) erreicht man den maximalem Performance-Schub. In dem First und Second-Level-Caches befinden sich die Entities. Das Query-Cache enthält die Ergebnisse von Query Abfragen, und zwar nur die ID’s von den Entities, die von der Abfrage gelesen wurden, die Entities selbst werden in dem Second-Level-Cache gehalten. Dadurch ist ein Query-Cache ohne aktiviertem Second-Level-Cache sinnlos!
Beim Konfigurieren von Caches muss man 3 Sachen beachten:

  1. Cache Provider und seine Features wie Speicher-Typ (Memory, Festplatte, Cluster), Unterstützung für Concurency-Strategien und Query-Cache
  2. Concurency-Strategy für jeden einzelnen Entity-Typ (read-only, nonstrict-read-write, read-write, transactional)
  3. Gute Cache-Kandidaten – nicht alle Entities eignen sich für das Caching

Für unveränderliche Objekte kann Caching nahezu sorglos definiert werden, beim veränderlichen Objekten muss man Eigenschaften betrachten wie: Entity-Speicherverbrauch, Verhältnis zwischen Lese/Schreib-Operationen, Concurency und Invalidierungs-Strategie.
Objekte in First und Second-Level Caches werden automatisch invalidiert, sobald ein Entity geändert wird. Für verteilte Anwendungen, mit Caching-Provider ohne Cluster-Unterstützung, muss man sich um die Invalidierung von Objekten selbst kümmern, dafür gibt es im Hibernate spezielle “evict” Funktionen am SessionFactory für einzelne Entities und ganze Cache-Regionen invalidieren.
Beim Aktivieren von Query-Cache, wird gleichzeitig noch ein zusätzlicher UpdateTimestampsRegion erstellt, in dem, die Zeiten der letzten Änderung einer (für Hibernate bekannter) Datenbank Tabelle gespeichert werden. Jeder QueryCahe kennt die Liste aller Tabellen, die in der Query verwendet wurden. Sobald Hibernate merkt, das eine der Tabellen in der Zwischenzeit geändert wurde, wird der gesamter Query-Cache Region invalidiert.

Beim Optimieren von Assoziationen geht es darum, eine möglichst effiziente Balance zwischen der Anzahl und Dauer von SQL Abfragen zu finden (Ausführungsdauer und Anzahl/Grösse der gelesenen Datensätze). Generell ist Lazy loading empfohlen (ist standard FetchMode in Hibernate), man kann das aber nicht pauschal für alle Abfragen verwenden. Eager loading und andere Strategien können in bestimmten Situationen bessere Ergebnisse liefern.
Typische Problemen sind:

  • N+1 Problem – kann mit FetchMode.JOIN, FetchMode.SELECT, FetchMode.SUBSELECT und BatchSize verbessert werden
  • Monster SQL (kartesischer Produkt bei Eager loading – zu viele JOIN’s + ein sehr langer ResultSet + Ausführungsdauer) – man vermeidet das mit FetchModel.SELECT oder BatchSize bzw. Lazzy loading
  • Zu viele Abfragen – suboptimale Applikation oder DAO Logik
  • Verwendung von Native SQL mit Parametern in SQL Code – stattdessen sollte man Parameter binding verwenden


Performance tuning tips for Hibernate and Java Persistence
Trully understanding the second level and query caches
Hibernate Improving performance
Hibernate Fetching Strategies examples


Oracle Performance Tuning Steps
Oracle Database Performance Tuning Guide

Veröffentlicht unter Java

PL/SQL Unit testing

Frameworks zum Unit-Testing von PL/SQL Programmen.

Name Version Licence Test style Link
utPLSQL 2.2 GPL Stored Procedures URL
Quest code tester ? proprietary declarative URL
PL/Unit ? proprietary PL/SQL packages URL
PLUTO – PL/SQL Unit Testing for Oracle 49 Artistic License/GPL PL/SQL classes URL
ruby-plsql-spec 0.2.1 free Ruby DSL URL
DBFit 1.1 GPL functional specifikation URL
DBUnit 2.4.8 Lesser GPL Java/XML URL
SQLDeveloper 3.0 proprietary Wizards, SQL URL

utPLSQL – entwickelt von Steven Feuerstein, ist ein bekanntestes und funktionsreichstes Framework für UnitTesting von PL/SQL Programmen. Es existieren Plugins für Build Tools wie Hudson oder Maven, die eine Integration in Build und Test Infrastruktur erleichtern. Leider wird dieses Framework in letzten Jahren praktisch nicht mehr weiterentwickelt, obwohl man noch viel machen könnte (z.B. bessere Tool Unterstützung, scaffolding usw.)

Quest Code Tester – ein weiteres Produkt von Steven Feuerstein (gestartet unter dem Namen Qute), wird heute von Firma Quest weiterentwickelt und verkauft, es existiert auch eine freeware Version von dem Produkt, die aber stark beschränkt ist, und kann deswegen nicht produktiv eingesetzt werden. Im Unterschied zum anderen Frameworks wird beim Quest Code Tester viel Wert auf Test Driven Development gelegt. Die Tests werden in deklarativer Form vor dem PL/SQL Code entwickelt.

PL/Unit – ist eine vereinfachte Version von dem utPLSQL, die aber in vielen fällen ausreichend ist. Leider ist das Projekt zwar frei, aber nicht opensource, sodass man keine eigenen Erweiterungen und Bugfixes implementieren kann.

PLUTO – PL/SQL Unit Testing for Oracle. Ein neuer Nachfolger von utPLSQL, im Unterschied zum Vorgänger verwendet es ein objektorientiertes Paradigma – jeder Test ist eine Ableitung von einem abstraktem Test Class. Das Framework ist sehr jung und leicht erweiterbar, aber funktional noch seinem Vorgänger unterlegen.

ruby-plsql-spec – ist eine Ruby DSL zum Entwickeln von PL/SQL Test. Die Vorteile einer DSL sind einfachere und lesbare Tests. Das Framework kann auch für Test Driven Development verwendet werden. Die Ruby Programmiersprache stellt aber den Haupt-Nachteil von dem Framework, weil man zusätzliche Sprache und Entwicklungs-Werkzeuge lernen muss, bevor man mit Test Development anfangen kann.

DBFit – eine Erweiterung von FitNesse Projekt (ein Acceptance test framework). Die Tests werden in Form von lesbarer Beschreibung der System-Funktonalität geschrieben (von nicht Programmierern), und werden dann mit Hilfe von “fixtures” in ausführbare Tests umgewandelt.

DBUnit – ein Datenbank-unabhängiges Java Framework, das für PL/SQL Unit-Testing verwendet werden kann. Ist populär in Java Welt, hat mehrere Erweiterungen z.B. für Maven, Spring, Eclipse usw. Die Tests werden als Kombination von XML Datasets und Java Code entwickelt.

SQL Developer – ein Unit-Testing Framework integriert ins SQL Developer, verwendet ein proprietäres Datenbank Repository. Die Tests werden mit Hilfe von Wirzards zusammengestellt und können dann manuell ausgeführt werden. Da das Framework auf proprietärem Repository basiert, kann es nicht immer leicht in die existierende Build Infrastruktur und Version Management eingebunden werden.

Datenbank unit testing best practise:

  • Eine private Datenbank Instanz für jeden Developer. Beim Entwickeln von Tests für Datenbanken ist sehr wichtig, dass der Zustand von dem Datenbank Schema und Daten während der Entwicklung sich nicht verändert. Deswegen, in Projekten mit mehreren Entwicklern, sollte jeder Entwickler in eigener privater Instanz arbeiten, um Konflikten mit anderen Entwicklern oder Programmen zu vermeiden.
  • Tests müssen unabhängig von Resultaten der anderen Tests sein. Am einfachsten kann man das erreichen, indem man vor jedem Test die Datenbank in initialen Zustand versetzt. Als mögliche Lösung kann man nach jedem Test die Datenbank Schema vollständig aufräumen, oder einfach keine Test Daten und Ergebnisse speichern (Rollback nach jedem Test).
  • Eine Cleanup Funktion. Es ist oft nötig oder unvermeidlich (automatisches commit beim DDL Operationen in Oracle), dass während der Test-Ausführung, Daten in der Datenbank gespeichert werden. Man sollte für diese Fälle eine Cleanup Funktion entwickeln, die das ganze Schema in Initialzustand versetzt, zum Beispiel durch das Einspielen von DB Dump, oder vollständiges Aufräumen der Inhalte aller Tabellen.
  • Kleine Datasets. Für die meisten Unit-Tests ist das vollständige Datenbank Inhalt nicht erforderlich. Stattdessen sollte man womöglich nur kleine, spezifische Datasets für jeden Test erstellen, was die Fehleranalyse erleichtert und einzelnen Tests unabhängig voneinander macht.
  • Gemeinsame Datasets. Viele Tests verwenden ähnliche Initialdaten, um die Entwicklung von solchen Tests zu vereinfachen, sollte man gemeinsame Datasets definieren, die von unterschiedlichen Tests wiederverwendet werden können. Die gemeinsame Datasets können auch vor der Ausführung von Test-Suits (die auf gemeinsamen Daten basieren), direkt in die Datenbank gespeichert werden, um die Test-Initialisierung zu verkürzen. Nach der Test-Suite Ausführung muss die Cleanup Funktion ausgeführt werden.

Weitere Links:

Presentation von Steven Feuerstein über UnitTesting von PL/SQL

Veröffentlicht unter Oracle

DB Schema documentation

“Documentation is like sex: when it is good, it is very, very good; and when it is bad, it is better than nothing.” Dick Brandon

Datenbank ist eine der Ressourcen in Software-Projekten, die immer gründlich und rechtzeitig dokumentiert werden soll.
Datenbank-Schema wird nicht so oft wie das Programmcode geändert (zumindest gut modelliertes Schema), das ist aber oft ein Grund, wieso ausgerechnet Datenbank Dokumention gar nicht oder nicht rechtzeitig gepflegt wird.
Dabei existiert es eine Reihe von Tools, mit denen automatisch aktuelle Dokumentation für den Team oder Projekt-Management/Stakeholder generiert werden kann.


  • Betriebsystem: Alle Betriebssysteme (Java)
  • Datenbanken: Unterstüzt alle gängige Datenbanken!
  • Output: Generiert sehr detaillierte verlinkte HTML Seiten, mit einfachen ER Diagrammen zum Überblicken von Tabellen-Relationen.
  • Ausführung: Command-line, SchemaSpyGUI (java client), Maven
  • Konfiguration: Sehr gut
  • Besonderheiten: Einfache Qualitäts-Prüfungen (Fehlender Index, falsher Datentyp etc.). Verliert in der Detallierung spezialisierten Tools wie SQLDeveloper, aber nicht viel.
  • Kosten: Opensource
  • Beispiel

Oracle SQL Developer

  • Betriebsystem: Alle Betriebssysteme (Auf Eclipse basis)
  • Datenbanken: Nur Oracle!
  • Output: sehr detallierte verlinkte HTML Seiten
  • Ausführung: Nur manuell aus SQL Developer
  • Konfiguration: Auswahl von DB Objekten und Detallierung
  • Besonderheiten: Aussehen änelt dem standartem Tabular-Ansicht von SQL Developer
  • Kosten: Frei
  • Dokumentation

Toad’s HTML Schema Doc Generator

  • Betriebsystem: Nur windows
  • Datenbanken: Oracle, DB2
  • Output: Verlinkte HTML seiten.
  • Ausführung: Nur manuell aus TOAD
  • Konfiguration: Auswahl von DB Objekten und Detalliereung
  • Kosten: Toad Lizenz


  • Betriebsystem: Nur Windows
  • Datenbanken: SQL Server, Oracle, MySQL, Postgresql, DB2
  • Output: HTML, CHM, Word
  • Ausführung: Windows GUI, command line
  • Konfiguration: Templates, Auswahl von DB Objekten und Detallierungsgrad
  • Besonderheiten: Gute Detallierung
  • Kosten: 100$


  • Betriebsystem: Nur Windows
  • Datenbanken: Alle gängige Datenbanken, manche sehr spezifische Datenbanken wie Teradata, Microsoft Integration Server
  • Output: HTML, CHM
  • Ausführung: Windows GUI, command line
  • Konfiguration: Auswahl von DB Objekten und Detallierungsgrad, rudimentär Aussehen vom generiertem Dokument (CSS, Logo, Header, Footer)
  • Besonderheiten: Sehr gute Detallierung für manche DB Objekte z.B. funktion call graph’s, ER Diagramme.
  • Kosten: 150$
  • Beispiel


  • Betriebsystem: Nur Windows
  • Datenbanken: SQL Server, MySQL, Access, Firebird
  • Output: HTML, RTF, Word, PDF
  • Ausführung: Windwos GUI, command line
  • Konfiguration: XSLT Templates, Auswahl von DB Objekten
  • Besonderheiten: XLST Templates
  • Kosten: 100$
  • Beispiel


  • Betriebsystem: Nur Windows
  • Datenbanken: Oracle und SQL Server!
  • Output: HTML, verlinkte Seiten, Word Dokument
  • Ausführung: nur GUI, manuell
  • Konfiguration: rudimintär
  • Besonderheiten:
  • Kosten: 100$
  • Beispiel

SQL Documentation Tool

  • Betriebsystem: Nur Windows
  • Datenbanken: SQL Server
  • Output: HTML, RTF, Plain Text
  • Ausführung: Windows GUI, command line
  • Konfiguration: rudimentär
  • Kosten: 50$
  • Beispiel

Diese Liste hat kein Anspruch auf die Vollständigkeit. Das sind Tools, die ich während einer Evaluierungs-Phase bei einem Software-Projekt analysiert habe.
Am Ende wurde SchemaSpy ausgewählt, wegen der Kosten, sehr guten Konfigurations-Möglichkeiten (Opensource, Java) und guten Ergebnisse für Oracle Datenbank. Dabei haben manche kommerzielle Produkte bessere Ergebnisse im Punkt “Detallierung” geliefert, waren aber nicht kostenlos und nicht so gut konfigurierbar/einsetzbar.

Veröffentlicht unter Oracle

Restore failed Oracle JMS queue messages

Wiederherstellung von durchgefallenen JMS Nachrichten

Angenommen wir haben eine Oracle JMS Queue mit dem Namen “QUEUE_NAME” und eine JMS Queue Tabelle “QUEUE_TABLE_NAME”, beim Erstellen von Queue hat Oracle automatisch eine Exception Queue angelegt mit dem Namen “AQ$_QUEUE_NAME_E” (und Exception Queue wurde gestartet)…

Aus verschiedenen Gründen kann es passieren, dass die Messages in Exception Queue landen, zum Beispiel wegen dem Fehler auf der Empfänger Seite. Nach N Versuchen die Nachricht zuzustellen, wid die Nachricht in die Exception Queue verschoben, die meistens von der gleichen Tabelle bedient wird (Oracle verwendet Feld Q_NAME in Queue Tabelle als Queue Identifikator, deswegen kann die gleiche Tabelle für mehrere Queues’s verwendet werden)

Die Nachrichten können aber sehr wichtig sein, und müssen auf jeden Fall zugestellt und abgearbeitet werden.
Am einfachsten kann man Nachrichten zurück in die normale Queue verschieben, in dem man die entsprechende Felder in der Queue Tabelle anpasst (mit dem folgendem Script)

set q_name = 'QUEUE_NAME',
    state = 0, 
    retry_count = 0, 
    exception_queue = null
where state = 3
  and q_name like 'AQ$_QUEUE_NAME_E';

Alternative Methode (sauberer, aber nicht immer möglich), ist die Verwendung von PL/SQL und DBMS_AQ Package. Dabei werden alle Nachrichten in der Schleife aus einem Queue gelesen (in unserem Fall aus Exception Queue) und in ein anderes Queue geschrieben (zurück in das normalle Queue).

  dequeue_options dbms_aq.dequeue_options_t;
  enqueue_options dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  message_handle RAW(32);
  message  SYS.AQ$_JMS_OBJECT_MESSAGE; -- Type of your message queue e.g. AQ$_JMS_MAP_MESSAGE

  deq_qname varchar2(50) := 'AQ$_QUEUE_NAME_E';
  enq_qname varchar2(50) := 'QUEUE_NAME';

  ex_no_messages exception;
  ex_dequeue exception;
  pragma exception_init(ex_no_messages, -25263);
  pragma exception_init(ex_dequeue, -25228);
  msg_count number := 0;
  dequeue_options.wait := DBMS_AQ.NO_WAIT;

    --Loop over all message in source queue
    dbms_aq.dequeue(queue_name         => deq_qname,
                    dequeue_options    => dequeue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_handle);
    -- Put message into destination queue
    dbms_aq.enqueue(queue_name         => enq_qname,
                    enqueue_options    => enqueue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_handle);
    msg_count := msg_count + 1;
    DBMS_OUTPUT.PUT_LINE('Processed ' || msg_count || ' messages');

  -- exception handling
  WHEN ex_no_messages THEN
    DBMS_OUTPUT.PUT_LINE('No of Messages Moved: ' || msg_count);
  WHEN ex_dequeue THEN
    DBMS_OUTPUT.PUT_LINE('No of Messages Moved: ' || msg_count);

Bei dieser Methodik muss der User das Recht auf DBMS_AQ Package besitzen und beide Queues müssen mit entsprechenden enqueu/dequeue Einstellungen gestartet sein (was auch nicht immer erwünscht ist). Ausserdem, muss man eventuell die geänderte Reihenvolge der Nachrichten berücksichtigen, weil die Nachrichten in diesem Fall ein neues “enque” Zeitstempel bekommen, was bei der Ersten Methodik nicht verändert wird.

Veröffentlicht unter Java, Oracle

Oracle JMS performance optimization

Oracle JMS verwendet polling (bei default 1 SQL Abfrage pro Sekunde) um neue Nachrichten in der Queue zu finden…

Dabei werden zwei system View’s abgefragt ALL_QUEUES und ALL_QUEUE_TABLES (sucht nach Queue Name und Queue Tabelle, die in JMS Konfiguration definiert wurden). Leider die Abfragen von System View’s mit Prefix ALL_ sind sehr langsam. Warum hat Oracle an dieser Stelle kein Namen Cache verwendet, ist mir nicht ganz klar, wahrscheinlich wird dadurch gewisse Dynamik ermöglicht …

Um die Abfragen zu beschleunigen, kann man lokale Tabellen mit gleichen Namen im User Schema anlegen (gecached). Dafür werden Administrator Rechte benötigt.

Man muss aber beachten, dass viele Datenbank-Tools (Reverse-Engineering Tools zum Beispiel) können auf die globale View’s zugreifen … Man soll auf die Benutzung von solchen Tools für diesen konkreten User verzichten, oder den Inhalt von lokalen Tabellen aktualisieren nach jeder Änderung von Queue Definitionen.

Vielen Dank an Maxim Bolshakov für den Tipp …

Veröffentlicht unter Java, Oracle