This chapter describes how to work with Oracle Changed Data Capture Adapters as well as with Attunity Stream in order to integrate changes captured on legacy sources using Oracle Data Integrator.
This chapter includes the following sections:
Section 25.5, "Creating and Reverse-Engineering an Attunity Stream Model"
Section 25.6, "Designing an Interface Using the LKM Attunity to SQL"
Oracle Changed Data Capture Adapters offer log-based change data capture (CDC) for enterprise data sources such as CICS, VSAM, Tuxedo, IMS DB, and IMS TM. Captured changes are stored in a storage called Staging Area (which is different from the Oracle Data Integrator interfaces' staging areas).
Attunity Stream is part of the Attunity Integration Suite (AIS) and provides the same features as the Oracle Changed Data Capture Adapters. In this section, we will refer to both products as Attunity Stream.
The Attunity Stream Staging Area contains the Change Tables used by Attunity Stream to store changes captured from the sources. It maintains the last position read by Oracle Data Integrator (This is the Attunity Stream Context, which is different from the Oracle Data Integrator Context concept) and starts at this point the next time a request from Oracle Data Integrator is received. The Change Tables are accessed through Attunity Stream Datasources.
Oracle Data Integrator uses Attunity Stream datasources as a sources of integration interfaces. They cannot be used as target or staging area. Journalizing or data quality check is not possible on this technology.
The Attunity Stream concepts map the Oracle Data Integrator concepts as follows: One Workspace within an Attunity Agent (or Daemon) listening on a port corresponds to one data server in Oracle Data Integrator. Within this Daemon, each Datasource (or Datasource/Owner pair) corresponds to one ODI Physical Schema. In each datasource, the Change Tables appear as ODI Datastores in an ODI model based on the Attunity technology.
Oracle Data Integrator provides the LKM Attunity to SQL for handling Attunity Stream data. The KMs use Attunity Stream specific features.
The Oracle Data Integrator CDC Knowledge Module provides integration from Attunity Stream Staging Areas via a JDBC interface. It is able to:
Read Attunity Stream data from Attunity Stream Data Sources.
Load this Attunity Stream data into an ANSI SQL-92 compliant database used as a staging area in Oracle Data Integrator staging area.
Handle the Attunity Stream Context to ensure consistent consumption of the changes read.
Using the data provided in the Attunity staging area, the Oracle CDC KM cleans the working environment (dropping temporary tables), determines and saves Attunity Stream Context information, loads the journalized data into the collect table and purges the loaded data from the journal.
Note:
Although Attunity Stream is used to capture changes in source systems, it is used as a regular JDBC source (only an LKM is used). The Oracle Data Integrator journalizing framework (JKM) is not used for this technology.Make sure you have read the information in this section before you start using the Oracle Knowledge Modules:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html.
Please review the Attunity Streams or Oracle Changed Data Capture Adapters documentation for the requirements and instruction for installing and setting up Streams for your source servers.
In order to use the Attunity Stream technology, you must first install the Attunity drivers in the drivers directory of your Oracle Data Integrator installation and restart ODI. See "Add Additional Drivers and Open Tools" in the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator.
The driver files include the following: nvjdbc2.jar, nvapispy2.jar, nvlog2.jar.
Setting up the Topology consists in:
An Attunity Stream data server corresponds to the server and workspace storing the Attunity Stream datasources.
Create a data server for the Attunity Stream technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining an Oracle data server:
In the Definition tab:
User: User profile to connect the workspace. If you are using anonymous access or specifying the user and password on the URL, leave this field and the JDBC Password field empty.
Password: Master password for the user profile.
In the JDBC tab:
JDBC Driver: com.attunity.jdbc.NvDriver
JDBC URL: jdbc:attconnect://<host_name>:<port>/<workspace> [;AddDefaultSchema=1][;<parameter>=<value>]
You can use in the URL the properties listed in:
Table 25-1 JDBC Attunity Driver Properties
| Option | Description |
|---|---|
|
<host_name> |
Name of the machine running the Attunity daemon |
|
<port> |
Port that the daemon listens to |
|
<workspace> |
Daemon's workspace. Default is |
|
AddDefaultSchema=1 |
This parameter specifies that a schema shows the default owner name |
|
<parameter>=<value> |
Any parameter available for the JDBC driver. Note that it is not needed to specify the datasource using the DefTdpName driver parameter, as Oracle Data Integrator accesses the change tables using the full qualified syntax: |
For more information on the JDBC URL connection details, see the Oracle Application Server CDC Adapters Installation Guide.
Create an Attunity Stream physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
This physical schema represents the Attunity Stream datasource from which you want to read the changed data. While defining the physical schema, the list of datasources and owners available for your workspace is displayed, provided that the data server is correctly configured. Public is displayed if no datasources and owners exist.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
Setting up a project using the Attunity Stream follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
It is recommended to import the LKM Attunity to SQL into your project for getting started with Attunity Stream.
This section contains the following topics:
Create an Attunity Stream Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Attunity Stream supports standard reverse-engineering. Standard reverse-engineering returns the change tables stored in the datasource as datastores. The change tables contain some CDC header columns in addition to the data columns used for integration. These columns include timestamps, table_name, operation, transactionID, context, and so forth. See the Attunity Stream documentation for more information on the header columns content.
To perform a Standard Reverse-Engineering on Oracle use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Oracle Data Integrator uses Attunity Stream datasources as a sources of integration interfaces. They cannot be used as target or staging area. Journalizing or data quality check is not possible on this technology.
To create an integration interface, which loads Attunity Stream data into your Oracle Data Integrator integration project, run the following steps:
Create an integration interface with Attunity Stream source datastores.
Create joins, filters and mappings as usual. Note that joins between change tables are not allowed on the source. They should be performed on the interface's staging area.
In the Flow tab of the interface, select the source set containing the source change table(s) and select the LKM Attunity to SQL.
Set the KM options as follows:
DELETE_TEMPORARY_OBJECTS - Set this option to No, if you wish to retain temporary objects (files and scripts) after integration.
PK_LIST – Specify the list of source columns that holds the primary key of the journalized table. Use SQL syntax and separate with a comma (,) each column name without prefixing it by the table alias, for example ORDER_ID, CUSTOMER_ID
Note:
When running an interface using this LKM, the changes are consumed from the change table. This KM does not support reading twice the same change.