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 Master Index
Master Index
Go to Feedback page
Contact Us

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

17 Sybase IQ

This chapter describes how to work with Sybase IQ in Oracle Data Integrator.

This chapter includes the following sections:

17.1 Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in a Sybase IQ database. Oracle Data Integrator features are designed to work best with Sybase IQ, including data integrity check and integration interfaces.

17.2 Concepts

The Sybase IQ concepts map the Oracle Data Integrator concepts as follows: A Sybase IQ server corresponds to a data server in Oracle Data Integrator. Within this server, a schema maps to an Oracle Data Integrator physical schema.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to a Sybase IQ database.

17.3 Knowledge Modules

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

Table 17-1 Sybase IQ Knowledge Modules

Knowledge Module Description

CKM Sybase IQ

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

Consider using this KM if you plan to check data integrity on a Sybase IQ database.

IKM Sybase IQ Incremental Update

Integrates data in a Sybase IQ target table in incremental update mode. This IKM creates a temporary staging table to stage the data flow. It then compares its content to the target table to guess which records should be inserted and which others should be updated. It also allows performing data integrity check by invoking the CKM.

Inserts and updates are done in bulk set-based processing to maximize performance. Therefore, this IKM is optimized for large volumes of data.

Consider using this IKM if you plan to load your Sybase IQ target table to insert missing records and to update existing ones.

To use this IKM, the staging area must be on the same data server as the target.

IKM Sybase IQ Slowly Changing Dimension

Integrates data in a Sybase IQ target table used as a Type II Slowly Changing Dimension in your Data Warehouse. This IKM relies on the Slowly Changing Dimension metadata set on the target datastore to figure out which records should be inserted as new versions or updated as existing versions.

Because inserts and updates are done in bulk set-based processing, this IKM is optimized for large volumes of data.

Consider using this IKM if you plan to load your Sybase IQ target table as a Type II Slowly Changing Dimension.

To use this IKM, the staging area must be on the same data server as the target and the appropriate Slowly Changing Dimension metadata needs to be set on the target datastore.

LKM File to Sybase IQ (LOAD TABLE)

Loads data from a File to a Sybase IQ staging area database using the LOAD TABLE SQL command.

Because this method uses the native LOAD TABLE command, it is more efficient than the standard "LKM File to SQL" when dealing with large volumes of data. However, the loaded file must be accessible from the Sybase IQ machine.

Consider using this LKM if your source is a large flat file and your staging area is a Sybase IQ database.

LKM SQL to Sybase IQ (LOAD TABLE)

Loads data from any ANSI SQL-92 standard compliant source database to a Sybase IQ staging area database using the native LOAD TABLE SQL command.

This LKM unloads the source data in a temporary file and calls the Sybase IQ LOAD TABLE SQL command to populate the staging table. Because this method uses the native LOAD TABLE, it is often more efficient than the LKM SQL to SQL method when dealing with large volumes of data.

Consider using this LKM if your source data located on a generic database is large, and when your staging area is a Sybase IQ database.


17.4 Specific Requirements

Some of the Knowledge Modules for Sybase IQ use the LOAD TABLE specific command. The following restrictions apply when using such Knowledge Modules.

See the Sybase IQ documentation for more information.