Oracle TimesTen In-Memory Database

27.1 Introduction

The Oracle TimesTen In-Memory Database (TimesTen) provides real-time data management. It provides application-tier database and transaction management built on a memory-optimized architecture accessed through industry-standard interfaces. Optional data replication and Oracle caching extend the product to enable multi-node and multi-tier configurations that exploit the full performance potential of today's networked, memory-rich computing platforms.

Oracle TimesTen In-Memory Database is a memory-optimized relational database. Deployed in the application tier, TimesTen operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication.

TimesTen supports a variety of programming interfaces, including JDBC (Java Database Connectivity) and PL/SQL (Oracle procedural language extension for SQL).

27.1.1 Concepts

The TimesTen concepts map the Oracle Data Integrator concepts as follows: An Oracle TimesTen In-Memory Database instance corresponds to a data server in Oracle Data Integrator. Within this database instance, the database/owner pair maps to an Oracle Data Integrator physical schema. A set of related objects within one database corresponds to a data model, and each table, view or synonym will appear as an ODI datastore, with its attributes, columns and constraints.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to an Oracle TimesTen In-Memory Database ODBC DSN.

27.1.2 Knowledge Modules

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

Table 27-1 TimesTen KMs

Knowledge Module	Description
IKM TimesTen Incremental Update (MERGE)	Integrates data from staging area into a TimesTen target table using TimesTen JDBC driver in incremental update mode. For example, inexistent rows are inserted; already existing rows are updated.
LKM SQL to TimesTen	Loads data from an ANSI SQL-92 source to a TimesTen staging table using the TimesTen JDBC driver.
LKM File to TimesTen (ttBulkCp)	Loads data from a file to a TimesTen staging table using ttBulkCp utility.

27.2 Installation and Configuration

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

27.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/technetwork/middleware/data-integrator/documentation/index.html

27.2.2 Technology Specific Requirements

Some of the Knowledge Modules for TimesTen use the ttBulkCp utility.

The following requirements and restrictions apply for these Knowledge Modules:

The host of the ODI Agent running the job must have the TimesTen Client utilities installed (TTBULKCP)
Data transformations should be executed on the staging area or target
The correct ODBC entry must be created on the agent machine:
- Client DSN: A Client DSN specifies a remote database and uses the TimesTen Client. A Client DSN refers to a TimesTen database indirectly by specifying a hostname, DSN pair, where the hostname represents the server machine on which TimesTen Server is running and the DSN refers to a Server DSN that specifies the TimesTen database on the server host.
- Server DSN: A Server DSN is always defined as a system DSN and is defined on the server system for each database on that server that will be accessed by clientapplications. The format and attributes of a server DSN are very similar to those of a Data Manager DSN.

27.2.3 Connectivity Requirements

This section lists the requirements for connecting to a TimesTen database.

To be able to access Microsoft Excel data, you need to:

Install the TimesTen ODBC Driver

Microsoft Excel workbooks can only be accessed through ODBC connectivity. The ODBC Driver for TimesTen must be installed on your system.

Declare a TimesTen ODBC Data Source

An ODBC data source must be defined for each Microsoft Excel workbook (.xls file) that will be accessed from ODI. ODBC datasources are created with the Microsoft ODBC Data Source Administrator. Refer to your Microsoft Windows operating system documentation for more information on datasource creation.

JDBC Driver

Oracle Data Integrator uses the TimesTen JDBC driver to connect to a TimesTen database. This driver must be installed in your Oracle Data Integrator drivers directory.

ODI Agent

The ODI Agent running the job must have the TimesTen JDBC Driver and ODBC driver installed and configured.

27.3 Setting up the Topology

Setting up the Topology consists of:

27.3.1 Creating a TimesTen Data Server

A TimesTen data server corresponds to a TimesTen database.

27.3.1.1 Creation of the Data Server

Create a data server for the TimesTen technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining a TimesTen 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: TimesTen user with its password
In the JDBC tab:
- JDBC Driver: org.TimesTen.Driver
- JDBC URL: jdbc:timesten:direct:dsn=<DSNname>
  
  where DSNname is the name of an ODBC datasource configured on the machine running the agent

Note:

Note that Oracle Data Integrator will have write access only on the database specified in the URL.

27.3.2 Creating a TimesTen Physical Schema

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

27.4 Creating and Reverse-Engineering a TimesTen Model

This section contains the following topics:

27.4.1 Create a TimesTen Model

Create a TimesTen Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.

27.4.2 Reverse-engineer a TimesTen Model

TimesTen supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering.

In most of the cases, consider using the standard JDBC reverse engineering for starting.

Consider switching to customized reverse-engineering if you encounter problems with the standard JDBC reverse-engineering process due to some specificities of the TimesTen JDBC driver.

Standard Reverse-Engineering

To perform a Standard Reverse-Engineering on TimesTen use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.

Customized Reverse-Engineering

To perform a Customized Reverse-Engineering on TimesTen with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to the TimesTen technology:

In the Reverse Engineer tab of the TimesTen Model, select the KM: RKM SQL (Python).<project name>.

The reverse-engineering process returns tables, views, attributes, Keys and Foreign Keys.

27.5 Setting up Data Quality

Oracle Data Integrator provides the CKM SQL for checking data integrity against constraints defined on a TimesTen table. See Flow Control and Static Control in Developing Integration Projects with Oracle Data Integrator for details.

See Generic SQL for more information.

27.6 Designing a Mapping

You can use TimesTen 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 TimesTen data server.

27.6.1 Loading Data from and to TimesTen

TimesTen can be used as a source, target or staging area of a mapping. The LKM choice in the Loading Knowledge Module tab to load data between TimesTen and another type of data server is essential for the performance of a mapping.

27.6.1.1 Loading Data from TimesTen

Use the Generic SQL KMs or the KMs specific to the other technology involved to load data from a TimesTen database to a target or staging area database.

For extracting data from a TimesTen staging area to a TimesTen table, use the IKM TimesTen Incremental Update (MERGE). See Loading Data from TimesTen for more information.

27.6.1.2 Loading Data to TimesTen

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into a TimesTen database. These optimized TimesTen KMs are listed in Table 27-2. In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.

Table 27-2 KMs for loading data to TimesTen

Source or Staging Area Technology	KM	Notes
SQL	LKM SQL to TimesTen	Loads data from an ANSI SQL-92 source to a TimesTen staging table using the TimesTen JDBC driver.
File	LKM File to TimesTen (ttBulkCp)	Loads data from a file to a TimesTen staging table using ttBulkCp utility.

27.6.2 Integrating Data in TimesTen

Oracle Data Integrator provides Knowledge Modules that implement optimized data integration strategies for TimesTen. These optimized TimesTen KMs are listed in Table 27-3. In addition to these KMs, you can also use the Generic SQL KMs.

The IKM choice in the Integration Knowledge Module tab determines the performances and possibilities for integrating.

Table 27-3 KMs for integrating data to TimesTen

KM	Notes
IKM TimesTen Incremental Update (MERGE)	Integrates data from staging area into a TimesTen target table using TimesTen JDBC driver in incremental update mode. For example, inexistent rows are inserted; already existing rows are updated.