This chapter describes how to work with Hypersonic SQL in Oracle Data Integrator.
This chapter includes the following sections:
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 mappings.
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.
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 12-1for 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 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. |
|
SKM HSQL |
Generates data access Web services for Hypersonic SQL databases. |
Make sure you have read the information in this section before you start using the Hypersonic SQL Knowledge Modules:
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.
There are no technology-specific requirements for using Hypersonic SQL in Oracle Data Integrator.
This section lists the requirements for connecting to a Hypersonic SQL Database.
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.
Setting up the Topology consists of:
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:
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)
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
Create a physical schema using the standard procedure, as described in "Creating a Physical Schema" in Administering Oracle Data Integrator.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" in Administering Oracle Data Integrator and associate it in a given context.
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:
CKM HSQL
Import also the Generic SQL KMs into your project. See for more information about these KMs.
This section contains the following topics:
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.
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.
Oracle Data Integrator provides the CKM HSQL for checking data integrity against constraints defined on a Hypersonic SQL table. See "Flow Control and Static Control" in Developing Integration Projects with Oracle Data Integrator for details.
Oracle Data Integrator provides the Knowledge Module listed in Table 12-2to perform a check on Hypersonic SQL.
You can use Hypersonic SQL as a source, staging area or a target of a mapping.
The KM choice for a mapping or a check determines the abilities and performance of this mapping 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 KMs or the KMs specific to the other technologies used as source, target, or staging area.