19 Sybase IQ
This chapter describes how to work with Sybase IQ in Oracle Data Integrator.
Attention:
This chapter applies only to Data Integration Platform Cloud.This chapter includes the following sections:
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.
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.
Knowledge Modules
Oracle Data Integrator provides the Knowledge Modules (KM) listed in the below table 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 5, Generic SQL for more information.
Note:
Generic SQL KMs for Update, Incremental Update and Merge cannot be used to incrementally load or update Sybase IQ target table. Use IKM Sybase IQ Incremental Update.Table 19-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 Because this method uses the native 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 This LKM unloads the source data in a temporary file and calls the Sybase IQ 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. |
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.
-
The file to be loaded by the
LOAD TABLE
command needs to be accessible from the Sybase IQ machine. It could be located on the file system of the server or reachable from a UNC (Unique Naming Convention) path or mounted from a remote file system. -
UNC file paths are supported but not recommended as they may decrease performance.
-
For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.
To know more about the supported data types, refer to Sybase IQ documentation.