Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) E12644-05 |
|
![]() Previous |
![]() Next |
This chapter describes how to work with Sybase IQ in Oracle Data Integrator.
This chapter includes the following sections:
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.
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.
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. |
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.
See the Sybase IQ documentation for more information.