This chapter describes how to work with Sybase AS Enterprise in Oracle Data Integrator.
This chapter includes the following sections:
Oracle Data Integrator (ODI) seamlessly integrates data in a Sybase AS Enterprise database. Oracle Data Integrator features are designed to work best with Sybase AS Enterprise, including journalizing and integration interfaces.
The Sybase AS Enterprise concepts map the Oracle Data Integrator concepts as follows: An Sybase AS Enterprise database corresponds to a data server in Oracle Data Integrator. Within this server, a database/owner pair maps to an Oracle Data Integrator physical schema.
Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to a Sybase AS Enterprise database.
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 16-1 for handling Sybase AS Enterprise data. These KMs use Sybase AS Enterprise specific features. It is also possible to use the generic SQL KMs with the Sybase AS Enterprise database. See Chapter 4, "Generic SQL" for more information.
Table 16-1 Sybase ASE Knowledge Modules
| Knowledge Module | Description |
|---|---|
|
IKM Sybase ASE Incremental Update |
Integrates data in a Sybase Adaptive Server Enterprise 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 Adaptive Server Enterprise 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 ASE Slowly Changing Dimension |
Integrates data in a Sybase Adaptive Server Enterprise 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 Adaptive Server Enterprise 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. |
|
JKM Sybase ASE Consistent |
Creates the journalizing infrastructure for consistent journalizing on Sybase Adaptive Server Enterprise tables using triggers. Enables Consistent Set Changed Data Capture on Sybase Adaptive Server Enterprise. |
|
JKM Sybase ASE Simple |
Creates the journalizing infrastructure for simple journalizing on Sybase Adaptive Server Enterprise tables using triggers. Enables Simple Changed Data Capture on Sybase Adaptive Server Enterprise. |
|
LKM SQL to Sybase ASE |
Loads data from any SQL compliant database to Sybase Adaptive Server Enterprise. This KM uses the ODI Agent to read selected data from the database and write the result into the target temporary table created dynamically.When using this KM on a journalized source table, the Journalizing table is first updated to flag the records consumed and then cleaned from these records at the end of the interface.This Knowledge Module is NOT RECOMMENDED when using LARGE VOLUMES. Other specific modules using Bulk utilities (SQL*LOADER, BULK INSERT...) or direct links (DBLINKS, Linked Servers...) are usually more efficient. |
|
LKM SQL to Sybase ASE (BCP) |
Loads data from any SQL compliant database to a Sybase Adaptive Server Enterprise staging area database using the BCP (Bulk Copy Program) utility. This LKM unloads the source data in a temporary file and calls the Sybase BCP utility to populate the staging table. Because this method uses the native BCP utility, 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 Adaptive Server Enterprise database. |
|
LKM Sybase ASE to Sybase ASE (BCP) |
Loads data from a Sybase Adaptive Server Enterprise source database to a Sybase Adaptive Server Enterprise staging area database using the native BCP out/BCP in commands. This module uses the native BCP (Bulk Copy Program) command to extract data in a temporary file. Data is then loaded in the target staging Sybase Adaptive Server Enterprise table using the native BCP command again. This method if often more efficient than the standard "LKM SQL to SQL" when dealing with large volumes of data. Consider using this LKM if your source tables are located on a Sybase Adaptive Server Enterprise instance and your staging area is on a different Sybase Adaptive Server Enterprise instance. |
Some of the Knowledge Modules for Sybase Adaptive Server Enterprise use the BCP specific loading utility. The following restrictions apply when using such Knowledge Modules:
The BCP utility as well as the Sybase Adaptive Server Enterprise Client must be installed on the machine running the Oracle Data Integrator Agent.
The server names defined in the Topology must match the Sybase Adaptive Server Enterprise Client connect strings used for these servers.
White spaces in server names defined on the Client are not supported.
The target staging area database must have option "select into/bulk copy"
Execution can remain pending if the file generated by the BCP program is empty.
For performance reasons, it is often recommended to install Oracle Data Integrator Agent on the target server machine.
See the Sybase Adaptive Server Enterprise documentation for more information.