Skip Headers
Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E12644-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

12 Hypersonic SQL

This chapter describes how to work with Hypersonic SQL in Oracle Data Integrator.

This chapter includes the following sections:

12.1 Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in an Hypersonic SQL database. Oracle Data Integrator features are designed to work best with Hypersonic SQL, including reverse-engineering, data integrity check, and integration interfaces.

12.1.1 Concepts

The Hypersonic SQL database concepts map the Oracle Data Integrator concepts as follows: A Hypersonic SQL server corresponds to a data server in Oracle Data Integrator. Within this server, one single Oracle Data Integrator physical schema maps to the database.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to Hypersonic SQL.

12.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 12-1 for handling Hypersonic SQL data. These KMs use Hypersonic SQL specific features. It is also possible to use the generic SQL KMs with the Hypersonic SQL database. See Chapter 4, "Generic SQL" for more information.

Table 12-1 Hypersonic SQL Knowledge Modules

Knowledge Module Description

CKM HSQL

Checks data integrity against constraints defined on a Hypersonic SQL table. Rejects invalid records in the error table created dynamically. Can be used for static controls as well as flow controls.

JKM HSQL Consistent

Creates the journalizing infrastructure for consistent journalizing on Hypersonic SQL tables using triggers. Enables consistent Changed Data Capture on Hypersonic SQL.

JKM HSQL Simple

Creates the journalizing infrastructure for simple journalizing on Hypersonic SQL tables using triggers.

SKM HSQL

Generates data access Web services for Hypersonic SQL databases.


12.2 Installation and Configuration

Make sure you have read the information in this section before you start using the Hypersonic SQL Knowledge Modules:

12.2.1 System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technology/products/oracle-data-integrator/index.html.

12.2.2 Technology Specific Requirements

There are no technology-specific requirements for using Hypersonic SQL in Oracle Data Integrator.

12.2.3 Connectivity Requirements

This section lists the requirements for connecting to a Hypersonic SQL Database.

JDBC Driver

Oracle Data Integrator is installed with a JDBC driver for Hypersonic SQL. This driver directly uses the TCP/IP network layer and requires no other installed component or configuration.

12.3 Setting up the Topology

Setting up the Topology consists of:

  1. Creating a Hypersonic SQL Data Server

  2. Creating a Hypersonic SQL Physical Schema

12.3.1 Creating a Hypersonic SQL Data Server

A Hypersonic SQL data server corresponds to an Hypersonic SQL Database connected with a specific Hypersonic SQL user account. This user will have access to the database via a physical schema in Oracle Data Integrator created under the data server.

Create a data server for the Hypersonic SQL technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining a Hypersonic SQL data server:

  1. In the Definition tab:

    • Name: Name of the data server that will appear in Oracle Data Integrator

    • Server: Physical name of the data server

    • User/Password: Hypersonic SQL user with its password (usually sa)

  2. In the JDBC tab:

    • JDBC Driver: org.hsqldb.jdbcDriver

    • JDBC URL: jdbc:hsqldb:hsql://<host>:<port>

      The URL parameters are:

      • <host>: Hypersonic SQL machine network name or IP address

      • <port>: Port number

12.3.2 Creating a Hypersonic SQL Physical Schema

Create a physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.

12.4 Setting Up an Integration Project

Setting up a project using the Hypersonic SQL database follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

It is recommended to import the following knowledge modules into your project for getting started with Hypersonic SQL:

Import also the Generic SQL KMs into your project. See Chapter 4, "Generic SQL" for more information about these KMs.

12.5 Creating and Reverse-Engineering a Hypersonic SQL Model

This section contains the following topics:

12.5.1 Create a Hypersonic SQL Model

Create a Hypersonic SQL Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

12.5.2 Reverse-engineer a Hypersonic SQL Model

Hypersonic SQL supports Standard reverse-engineering - which uses only the abilities of the JDBC driver.

To perform a Standard Reverse- Engineering on Hypersonic SQL use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

12.6 Setting up Changed Data Capture

The ODI Hypersonic SQL Knowledge Modules support the Changed Data Capture feature. See Chapter "Working with Changed Data Capture" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for details on how to set up journalizing and how to use captured changes.

Hypersonic SQL Journalizing Knowledge Modules support Simple Journalizing and Consistent Set Journalizing. The JKMs use triggers to capture data changes on the source tables.

Oracle Data Integrator provides the Knowledge Modules listed in Table 12-2 for journalizing Hypersonic SQL tables.

Table 12-2 Hypersonic SQL Journalizing Knowledge Modules

KM Notes

JKM HSQL Consistent

Creates the journalizing infrastructure for consistent journalizing on Hypersonic SQL tables using triggers. Enables consistent Changed Data Capture on Hypersonic SQL.

JKM HSQL Simple

Creates the journalizing infrastructure for simple journalizing on Hypersonic SQL tables using triggers.


12.7 Setting up Data Quality

Oracle Data Integrator provides the CKM HSQL for checking data integrity against constraints defined on a Hypersonic SQL table. See "Set up Flow Control and Post-Integration Control" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for details.

Oracle Data Integrator provides the Knowledge Module listed in Table 12-3 to perform a check on Hypersonic SQL.

Table 12-3 Check Knowledge Modules for Hypersonic SQL Database

Recommended KM Notes

CKM HSQL

Checks data integrity against constraints defined on a Hypersonic SQL table. Rejects invalid records in the error table created dynamically. Can be used for static controls as well as flow controls.


12.8 Designing an Interface

You can use Hypersonic SQL as a source, staging area or a target of an integration interface.

The KM choice for an interface or a check determines the abilities and performance of this interface or check. The recommendations in this section help in the selection of the KM for different situations concerning a Hypersonic SQL data server.

Oracle Data Integrator does not provide specific loading or integration knowledge modules for Hypersonic SQL. Use the Generic SQL KMs or the KMs specific to the other technologies used as source, target, or staging area.