This chapter describes how to work with IBM DB2 UDB in Oracle Data Integrator.
This chapter includes the following sections:
Oracle Data Integrator (ODI) seamlessly integrates data in an IBM DB2 UDB database. Oracle Data Integrator features are designed to work best with IBM DB2 UDB, including journalizing, data integrity checks, and mappings.
The IBM DB2 UDB concepts map the Oracle Data Integrator concepts as follows: An IBM DB2 UDB database 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 an IBM DB2 UDB database.
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 15-1 for handling IBM DB2 UDB data. These KMs use IBM DB2 UDB specific features. It is also possible to use the generic SQL KMs with the IBM DB2 UDB database. See Chapter 4, "Generic SQL" for more information
| Knowledge Module | Description |
|---|---|
|
IKM DB2 UDB Incremental Update |
Integrates data in an IBM DB2 UDB 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 identify 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 IBM DB2 UDB 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 DB2 UDB Slowly Changing Dimension |
Integrates data in an IBM DB2 UDB 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 IBM DB2 UDB 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 DB2 UDB Consistent |
Creates the journalizing infrastructure for consistent journalizing on IBM DB2 UDB tables using triggers. Enables Consistent Changed Data Capture on IBM DB2 UDB. |
|
JKM DB2 UDB Simple |
Creates the journalizing infrastructure for simple journalizing on IBM DB2 UDB tables using triggers. Enables Simple Changed Data Capture on IBM DB2 UDB. |
|
LKM DB2 UDB to DB2 UDB (EXPORT_IMPORT) |
Loads data from an IBM DB2 UDB source database to an IBM DB2 UDB staging area database using the native EXPORT / IMPORT commands. This module uses the EXPORT CLP command to extract data in a temporary file. Data is then loaded in the target staging DB2 UDB table using the IMPORT CLP command. 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 DB2 UDB database and your staging area is on a different DB2 UDB database. |
|
LKM File to DB2 UDB (LOAD) |
Loads data from a File to a DB2 UDB staging area database using the native CLP LOAD Command. Depending on the file type (Fixed or Delimited) this LKM will generate the appropriate LOAD script in a temporary directory. This script is then executed by the CLP and automatically deleted at the end of the execution. Because this method uses the native IBM DB2 loaders, it is more efficient than the standard LKM File to SQL when dealing with large volumes of data. Consider using this LKM if your source is a large flat file and your staging area is an IBM DB2 UDB database. |
|
LKM SQL to DB2 UDB |
Loads data from any ANSI SQL-92 standard compliant source database to an IBM DB2 UDB staging area. This LKM is similar to the standard LKM SQL to SQL described in Chapter 4, "Generic SQL" except that you can specify some additional specific IBM DB2 UDB parameters. |
|
LKM SQL to DB2 UDB (LOAD) |
Loads data from any ANSI SQL-92 standard compliant source database to an IBM DB2 UDB staging area using the CLP LOAD command. This LKM unloads the source data in a temporary file and calls the IBM DB2 native loader using the CLP LOAD command to populate the staging table. Because this method uses the native IBM DB2 loader, it is often more efficient than the LKM SQL to SQL or LKM SQL to DB2 UDB methods 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 an IBM DB2 UDB database. |
|
SKM IBM UDB |
Generates data access Web services for IBM DB2 UDB databases. See SKM SQL in Chapter 4, "Generic SQL" for more information. |
Some of the Knowledge Modules for IBM DB2 UDB use operating system calls to invoke the IBM CLP command processor to perform efficient loads. The following restrictions apply when using such Knowledge Modules:
The IBM DB2 UDB Command Line Processor (CLP) as well as the DB2 UDB Connect Software must be installed on the machine running the Oracle Data Integrator Agent.
The server names defined in the Topology must match the IBM DB2 UDB connect strings used for these servers.
Some DB2 UDB JDBC drivers require DB2 UDB Connect Software to be installed on the machine running the ODI Agent.
See the IBM DB2 documentation for more information.