tOracleCDCOutput - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

tOracleCDCOutput properties

Component family

Databases/Oracle

 

Function

This component replicates data changes from an XStream inbound server to a target table using CDC.

Purpose

This component allows you to synchronize data changes in the Oracle XStream CDC mode.

Basic settings

Property of the CDC connection

Either Built-in or Repository.

 

 

Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.

 

 

Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.

 

Use an existing connection

Select this check box and in the Component List click the relevant connection component to reuse the connection details you already defined.

Note

When a Job contains the parent Job and the child Job, if you need to share an existing connection between the two levels, for example, to share the connection created by the parent Job with the child Job, you have to:

  1. In the parent level, register the database connection to be shared in the Basic settings view of the connection component which creates that very database connection.

  2. In the child level, use a dedicated connection component to read that registered database connection.

For an example about how to share a database connection across Job levels, see Talend Studio User Guide.

 

DB Version

Select the version of the Oracle database being used.

 

Local Service Name

Specify the local service name of the Oracle database.

 

Oracle schema

Specify the name of the Oracle schema.

 

Username and Password

Specify the user authentication data of the Oracle database.

To enter the password, click the [...] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.

 

Schema and Edit schema

A schema is a row description, it defines the number of fields to be processed and passed on to the next component. The schema is either Built-in or stored remotely in the Repository.

Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.

Click Edit schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

  • View schema: choose this option to view the schema only.

  • Change to built-in property: choose this option to change the schema to Built-in for local changes.

  • Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion. If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the [Repository Content] window.

 

 

Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.

 

 

Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.

 

Input field

Select an input field from the drop-down list.

 

Inbound server name

Specify the XStream inbound server name.

 

Set data table schema

Select this check box to specify the schema of the CDC table.

 

Set target table

Select this check box to specify the table to which data changes from the inbound server will be replicated.

Advanced settings

Acknowledge interval(seconds)

Specify the value for the OCI_ATTR_XSTREAM_ACK_INTERVAL attribute for the inbound server. The default value is 30 seconds.

For more information about this attribute, see http://docs.oracle.com/cd/E18283_01/server.112/e16545/xstrm_oci_intro.htm#CEGDICBB.

 

tStatCatcher Statistics

Select this check box to collect the log data at component level.

Global Variables 

NB_LINE: the number of rows processed. This is an After variable and it returns an integer.

ERROR_MESSAGE: the error message generated by the component when an error occurs. This is an After variable and it returns a string. This variable functions only if the Die on error check box is cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl + Space to access the variable list and choose the variable to use from it.

For further information about variables, see Talend Studio User Guide.

Usage

This component is used as an output component. It requires an input component and Row > Main link as input.

Scenario: Extracting and synchronizing data changes using XStream mode

This scenario describes a Job that inserts data into an empty source table PERSON in one Oracle database on which the XStream outbound server is configured, then extracts and synchronizes the data changes into an empty target table PERSON_BAK in another Oracle database on which the XStream inbound server is configured using Oracle CDC XStream mode, and finally displays the data in the source and target tables on the console.

Prerequisites: The Oracle database connections dedicated to the XStream outbound and inbound servers have been set up in the Repository tree view in the Studio, and the CDC has been added to the empty source and target tables. For more information about how to set up an Oracle database connection and configure CDC in Oracle XStream mode, see Talend Studio User Guide.

Dropping and linking the components

  1. Create a new Job and add the following components by typing their names in the design workspace or dropping them from the Palette: a tFixedFlowInput component, a tOracleOutput component, a tOracleCDC component, a tOracleCDCOutput component, two tOracleInput components, and two tLogRow components.

  2. Connect tFixedFlowInput to tOracleOutput using a Row > Main link.

  3. Do the same to connect tOracleCDC to tOracleCDCOutput, the first tOracleInput to the first tLogRow, and the second tOracleInput to the second tLogRow.

  4. Connect tFixedFlowInput to tOracleCDC using a Trigger > On Subjob Ok connection.

  5. Do the same to connect tOracleCDC to the first tOracleInput and the first tOracleInput to the second tOracleInput.

Configuring the components

Inserting data into the source table

  1. Double-click tFixedFlowInput to open its Basic settings view.

  2. Click the [...] button next to Edit schema to open the schema editor.

    Click four times the [+] button to add four columns: id and age of the integer type, name and sex of the string type.

    Click OK to close the schema editor and accept the propagation prompted by the pop-up dialog box.

  3. Select Use Inline Content(delimited file) in the Mode area, and then enter the following input data in the Content field:

    1;Ford;Male;25
    2;Rose;Female;30
    3;Sabrina;Female;28
    4;Teddy;Male;32
    5;Kate;Male;35
  4. Double-click tOracleOutput to open its Basic settings view.

  5. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

  6. In the Table field, enter the name of the table into which the input data will be inserted. In this example, it is PERSON.

  7. Click the [...] button next to Edit schema to open the schema editor to ensure the schema has been synced up.

Extracting and synchronizing data changes to the target table

  1. Double-click tOracleCDC to open its Basic settings view.

  2. Set Property of the CDC connection to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

  3. Select LCR Object from the Generate LCR type drop-down list.

    In the Table using CDC field, enter the name of the source table from which changes made to data are to be captured. In this example, it is PERSON.

    In the Outbound server name field, enter the XStream outbound server name. In this example, it is xout.

  4. Click the [...] button next to Edit schema to open the schema editor, and you will see that the schema has been set by default.

  5. Click Advanced settings to open its view.

    In the Idle timeout(seconds) field, enter the number of seconds of idle time that the outbound server waits for an LCR before terminating the call to get or receive an LCR stream. Considering the network latency and the database response rate, the idle time is set to 60 seconds in this example.

  6. Double-click tOracleCDCOutput to open its Basic settings view.

    Set Property of the CDC connection to Repository and select the connection metadata corresponding to the Oracle database on which the XStream inbound server is configured, Oracle12_XStreamIn in this example. The database version and other connection details information will be completed automatically.

  7. In the Inbound server name field, enter the XStream inbound server name. In this example, it is xin.

    Select the Set data table schema check box and specify the schema of the CDC table. In this example, it is TALEND.

    Select the Set target table check box and specify the table to which data changes from the inbound server will be replicated. In this example, it is PERSON_BAK.

Retrieving data from the source and target table

  1. Double-click the first tOracleInput to open its Basic settings view.

  2. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

    Set Schema to Repository and select the source table from the Repository. In this example, it is PERSON.

  3. In the Query field, enter the following SQL statement to retrieve all the data from the source table:

    select * from PERSON
  4. Double-click the second tOracleInput to open its Basic settings view.

  5. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream inbound server is configured, Oracle12_XStreamIn in this example. The connection type, database version and other connection details information will be completed automatically.

    Set Schema to Repository and select the target table from the Repository. In this example, it is PERSON_BAK.

  6. In the Query field, enter the following SQL statement to retrieve all the data from the target table:

    select * from PERSON_BAK
  7. Double-click the first tLogRow to open its Basic settings view.

    In the Mode area, select Table (print values in cells of a table) for a better display of the result.

  8. Do the same to configure the second tLogRow.

Saving and executing the Job

  1. Press Ctrl + S to save the Job.

  2. Press F6 to run the Job.

    As shown above, the input data is inserted into the source table and then the data changes are synchronized into the target table.