7 Using SQL*Loader, SAP, and Code Template Mappings

Adding SAP Tables to the Mapping

To add an SAP table to a mapping:

On the Mapping Editor, drag and drop the required SAP table onto the Mapping Editor canvas.

The editor places a Table operator on the mapping canvas to represent the SAP table.

Setting the Loading Type

Use the Property Inspector to set the SQL*Loader properties for the tables in the mapping.

To set the loading type for an SAP source table:

1. On the Mapping Editor, select the SAP source table. The Property Inspector displays the properties of the SAP table.

2. Select a loading type from the Loading Type list. With ABAP code as the language for the mapping, the SQL*Loader code is generated as indicated in Table 7-1.

   Table 7-1 SQL*Loader Code Generated in ABAP

   Loading Type	Resulting Load Type in SQL*Loader
   INSERT	APPEND
   CHECK/INSERT	INSERT
   TRUNCATE/INSERT	TRUNCATE
   DELETE/INSERT	REPLACE
   All other types	APPEND

   
   

Setting Configuration Properties for the Mapping

Perform the following steps to configure a mapping containing SAP tables:

• Use the Configuration tab to define the code generation language as described in "Setting the Language Parameter".

• Set ABAP specific parameters, and the directory and initialization file settings in the Configuration tab as described in "Setting the Runtime Parameters".

Setting the Language Parameter

The Language parameter enables you to choose the type of code you want to generate for a mapping. For mappings containing SAP source tables, Oracle Warehouse Builder automatically sets the language parameter to ABAP. Verify that this parameter has been set to ABAP.

Setting the Runtime Parameters

With the Language set to ABAP, expand the Runtime Parameters node in the Configuration tab to display settings specific to ABAP code generation.

Some of these settings come with preset properties that optimize code generation. Oracle recommends that you retain these settings, as altering them may slow the code generation process.

The following Runtime parameters are available for SAP mappings:

• Background Job: Select this option to run the ABAP report as a background job in the SAP system. Enable this option for the longer running jobs. Foreground batch jobs that run for a long duration are considered hanging in SAP after a certain time. Therefore, it is ideal to run a background job for such extracts.

• File Delimiter for Staging File: Specifies the column separator in a SQL data file.

• Data File Name: Specifies the name of the data file that is generated when the ABAP code for the mapping is run in the SAP system.

• SQL Join Collapsing: Specifies the following hint, if possible, to generate ABAP code.

  SELECT < > INTO < > FROM (T1 as T1 inner join T2 as T2) ON <condition > 
  

  The default setting is TRUE.

• Primary Foreign Key for Join: Specifies the primary key to be used for a join.

• ABAP Report Name: Specifies the name of the ABAP code file generated by the mapping. This is required only when you run a custom function module to run the ABAP code.

• SAP System Version: Specifies the SAP system version number to which you want to deploy the ABAP code. For MySAP ERP and all other versions, select SAP R/3 4.7. Different ABAP code is required for versions before 4.7.

• Staging File Directory: Specifies the location of the directory in the SAP system where the data file generated by ABAP code resides.

• SAP Location: Specifies the location of the SAP instance from where the data can be extracted.

• Use Select Single: Indicates whether Select Single is generated, if possible.

• Nested Loop: Specifies a hint to generate nested loop code for a join, if possible.

Setting the Join Rank

You must set the Join Rank parameter only if the mapping contains the Joiner operator, and you want to explicitly specify the driving table. Unlike SQL, ABAP code generation is rule-based. Therefore, you must design the mapping so that the tables are loaded in the right order. Or you can explicitly specify the order in which the tables must be joined. From the Configuration tab, expand Table Operators, and then for each table, specify the Join Rank. The driving table must have the Join Rank value set to 1, with increasing values for the subsequent tables.

You can also let Oracle Warehouse Builder decide the driving table and the order of joining the other tables. In such cases, do not enter values for Join Rank.

Limitations of Using Certain Prebuilt Code Templates

• When you use ICT_ORACLE_INCR_UPD_MERGE, sequences are not supported.

• When you use ICT_SQL_CONTROL_APPEND in a mapping, an ORDER BY clause associated with this CT does not work. No error message is displayed during the execution of a CT mapping containing this CT. However, the rows are not ordered as specified in the ORDER BY property.

• When you use Incremental Update Integration CTs, the Loading Type property of the target Table operator should be set to either INSERT_UPDATE or UPDATE_INSERT. Also, the target Table operator must have Unique key or Primary key defined on it.

• ICT_SQL_TO_SQL_APPEND uses two different credentials (one credential to the source schema and another credential to the target schema as defined in the location) to perform the loading. As a result, the map can be run successfully without a permission problem.

• ICT_SQL_CONTROL_APPEND uses a single credential (the credential to connect to the target schema) to perform the loading. In other words, the security behavior is similar to existing Oracle Warehouse Builder PL/SQL mapping. As a result, if the target schema has not been granted the permission to access the source schema, an "Insufficient privileges" error is reported.

  In general, Oracle Data Integrator Knowledge Modules (KMs) with "multiple connections" property set to true in its KM falls into the first category described above. Please refer to Oracle Data Integrator documentation for details.

Execution View Menu and Toolbars

When you select the Execution View tab, the Design Center displays an additional menu called Execution and an Execution toolbar. Use these to:

• Create and delete execution units

• Define default execution units

• Associate code templates with execution units

Creating Execution Units

Create an execution unit for a group of operators for which code generation and execution are controlled by a specific code template.

To create an execution unit:

1. In the Execution View of the mapping, select the operators to group into an execution unit.

   You can do this by drawing a rectangle around the operators. Hold down the mouse button at the top of the first operator, drag the mouse so that you cover the required operators and then release the mouse button. Alternatively, hold down the Ctrl key and click the headers of all the operators to include in an execution unit.

2. From the Execution menu, select Create Execution Unit. Or click Create Execution Unit on the Execution View toolbar.

   Oracle Warehouse Builder creates an execution unit for the selected operators and assigns a default name to it. To rename the execution unit, right-click the name and select Open Details. In the Edit Execution Unit dialog box, enter the new name for the execution unit.

To associate a code template with an execution unit:

1. If the Code Template panel is not displayed, from the View menu, select Code Template.

   The Code Templates tab is displayed in the Log window panel.

2. In the Execution View tab of the Mapping Editor, select the execution unit with which you want to associate a code template.

   See Also:

   "How Oracle Warehouse Builder Displays Code Templates that Can be Associated with Execution Units"

3. In the Code Templates tab, use the list to select the code template with which the selected execution unit should be associated.

   The code templates displayed in the list depend on the source and target platforms of the operators in the selected execution unit. They also depend on the nature of the execution unit. For example, you can associate a Load CT with an execution unit that does not contain any operators bound to data objects. You can associate an Integration CT with an execution unit that contains one target operator bound to a repository data object.

Adding Operators to an Execution Unit

To add operators to an execution unit:

1. In the Execution View of the Mapping Editor, select both the execution unit and the operators to add to the execution unit.

   You can select multiple objects by holding down the Ctrl key while selecting objects.

2. From the Execution menu, select Add Operator to Execution Unit. Or click Add Operator to Execution Unit on the Execution View toolbar.

Adding Operators to Multiple Execution Units

An operator may appear in multiple execution unit, such as when it is a target in one execution unit and a source in another.

For example, you have a Table operator cust_tab in the execution unit CUST_EXEC_UNIT. You can copy cust_tab to another execution unit EX_UNIT_2 (which exists) by selecting both cust_tab and EX_UNIT_2 and then clicking Add Operator to Execution Unit. A copy of cust_tab is added to EX_UNIT_2.

Consider the Table operator cust_tab, which is a target in the execution unit CUST_EXEC_UNIT. Select cust_tab and then click Create Execution Unit to create an execution unit containing a copy of cust_tab. The label of this copy must be <cust_tab>, using the angle brackets as a visual cue that the operator appears in multiple execution units.

Removing Operators from an Execution Unit

To remove operators from an execution unit:

1. In the Execution View of the Mapping Editor, select the operator or operators to remove from an execution unit.

2. From the Execution menu, select Remove Operator from Execution Unit. Or click Remove Operator from Execution Unit on the Mapping Editor toolbar.

   The selected operators are removed from the execution unit and displayed separately in the Execution View.

   Note:

   You cannot remove an operator from an execution unit when it is the only operator in the execution unit.

Removing Execution Units

When you remove an existing execution unit, the operators that were part of the execution unit are not associated with any execution unit. You must associate these operators with other execution units, if required.

If you remove execution units, you must regenerate and deploy updated code for the mapping before your changes take effect.

To remove an execution unit:

1. In the Execution View of the Mapping Editor, select the execution unit to remove.

2. From the Execution menu, select Remove Execution Unit. Or click Remove Execution Unit on the Execution View toolbar.

   The execution unit is removed. Any operators that were contained in this execution unit are displayed individually, instead of grouped under the execution unit.

Creating Default Execution Units

You can create a default set of execution units by clicking Default Execution Units or by selecting Default Execution Units from the Execution menu. Oracle Warehouse Builder first removes all existing execution units and then creates a new set of execution units such that all operators are assigned to some execution unit.

The operators are assigned to execution units based on factors such as their location. Operators that are at a different location are assigned to different execution units. If all operators are at the same location, then the default may consist of a single execution unit containing all operators.

The names of the default execution units depend on whether the location associated with the operators in the execution units is known. When the execution unit location (location associated with operators in the execution unit) is known, the name of the default execution unit is set to the location name followed by "_EU". If the location associated with the execution unit is not known, then the execution unit name starts with "MAP_EX_UNIT_",

Default Code Template for An Execution Unit

If you do not explicitly associate a code template with an execution unit, during code generation, Oracle Warehouse Builder assigns a default code template. The default code template depends on the platform of the target operators. You can define default code templates for each platform.

For example, you can define a default Load CT, Integration CT, and Oracle Target CT for the Oracle Database platform. When you do not assign a code template to an execution unit that contains operators referencing Oracle Database objects, Oracle Warehouse Builder Code Generator performs the following steps:

• Identifies the type of code template that is necessary for that particular execution unit

• Retrieves the default code template that should be assigned to the execution unit using the platform of the location with which the execution unit is associated

• Assigns the retrieved code template to the execution unit

If no default code templates are defined for a particular platform, Oracle Warehouse Builder picks a code template from the available code templates and assigns it to the execution unit. It then updates the platform definition and assigns the selected code template as the default code template definition for that platform.

A default code template can be assigned to an execution unit only if the CT mapping containing the code template and the default code template belong to the same project.

How Oracle Warehouse Builder Displays Code Templates that Can be Associated with Execution Units

When associating a CT with execution units, the CTs displayed in the UI list are filtered by the source and the target platforms. For example, if the execution unit containing your source tables is an Oracle database, the CTs available for selection for this execution unit are ones that can be used for Oracle sources.

In certain simple mappings, you may create a single execution unit that contains the source and target operators. In this case, the list of available Integration CTs is limited by the following:

• Source Platform

• Platform

• The value set for the Multi-Connections property of the CT

Sample Code Generated for CT Mappings

Following are some code examples from a code template and its associated metadata script, showing the substitution. The following code block displays a single procedural step of a code template, creating a temporary flow table.

proc 3_CREATE_FLOW_TABLE_I__main {  }
{
# Input Flow Parameters
  variable SRC_LOCATION
  variable TGT_LOCATION
  variable KM_PARAMS
  variable LOG_LEVEL
  variable INSERT
  variable UPDATE
  variable COMMIT
  variable SYNC_JRN_DELETE
  variable FLOW_CONTROL
  variable RECYCLE_ERRORS
  variable STATIC_CONTROL
  variable TRUNCATE
  variable DELETE_ALL
  variable CREATE_TARG_TABLE
  variable FLOW_TABLE_OPTIONS
  variable ANALYZE_TARGET
  variable OPTIMIZER_HINT

# Output parameters
  variable EXIT_CODE 
  variable RETURN_VALUE {}

# Global variables
  global errorInfo
  global g_iud

        set g_iud ""
        set tgt_stmt [process "create table <%=snpRef.getTable(\"L\", \"INT_NAME\", \"W\")%>\n(\n<%=snpRef.getColList(\"\", \"\\t\[COL_NAME\]\\t\[DEST_WRI_DT\] NULL\", \",\\n\", \"\", \"\")%>,\n\tIND_UPDATE \tchar(1)\n)\n<%=snpRef.getUserExit(\"FLOW_TABLE_OPTIONS\")%>"]
        puts $tgt_stmt
        execjdbc $tgt_stmt "TGT_AC" "$TGT_LOCATION" "" "true" "false" "false"
}

Notice that a target SQL statement, variable tgt_stmt, is assigned an actual SQL statement which is then run using the execjdbc procedure call. The execjdbc tcl procedure makes a Java call to run the statement through JDBC. The target statement is a string produced by the process procedure. The <%...%> delimiters require special processing to substitute required components into the SQL string. The snpRef tag is the prefix for a substitution method callout. snpRef.getTable is replaced by the actual table name. snpRef.getColList is another universal method to retrieve the list of table columns participating in the DML. In addition to snpRef, odiRef is supported (as in Oracle Data Integrator 10.2).

The substitution method (snpRef) calls are completed by an Oracle Warehouse Builder Tcl module which extracts the associated data from a variable of the metadata script. The following is an example of a metadata script section showing the table name and column list:

set M1_params {
  {CKM_CALL ""}
  {COLUMN_GENERIC_DATATYPE "NUMERIC VARCHAR"}
  {COLUMN_LIST "EMPLOYEES.EMPLOYEE_ID EMPLOYEES.LAST_NAME"}
  {COLUMN_LIST_ALIAS "EMPLOYEE_ID LAST_NAME"}
  {COLUMN_LIST_DATATYPE "NUMBER(6) VARCHAR2(25)"}
  {EXECUTION_UNIT_NAME "EX_UNIT_2"}
  {FROM_LIST "EMPLOYEES"}
  {FROM_LIST_ALIAS "EMPLOYEES"}
  {HAS_JRN "0"}
  {INSERT_COLUMN_LIST "EMPID ENAME"}
  {IS_DISTINCT "FALSE"}
  {JOURNAL_IN_CURRENT_SCHEMA "false"}
  {JOURNAL_IN_SOURCE_SCHEMA "false"}
  {JOURNAL_IN_STAGING_AREA "false"}
  {JRN_FILTER ""}
  {JRN_METHOD "NONE"}
  {JRN_TABLE "."}
  {KM_NAME "KM_IKM_ORACLE_INCREMENTAL_UPD"}
  {MAP_NAME "MAPPING_2"}
  {SELECT_STATEMENT "SELECT EMPLOYEES.EMPLOYEE_ID EMPLOYEE_ID,
         EMPLOYEES.LAST_NAME LAST_NAME
FROM
  EMPLOYEES  EMPLOYEES"}
  {SQL_STATEMENT "INSERT INTO TGT(EMPID, ENAME)
  (SELECT EMPLOYEES.EMPLOYEE_ID EMPLOYEE_ID,EMPLOYEES.LAST_NAME LAST_NAME
FROM
  EMPLOYEES  EMPLOYEES
  )
;"}
  {TARGET_COLUMN_DATATYPE "NUMBER(15) VARCHAR2(100)"}
  {TARGET_COLUMN_LIST "EMPID ENAME"}
  {TARGET_GENERIC_DATATYPE "NUMERIC VARCHAR"}
  {TARGET_NAME "TGT"}
}

Viewing Execution Results by Using the Results Tab

When you run a CT mapping using the Design Center, a new Results tab is created in the Log window to display the CT mapping execution results. The Results tab contains a node called Execution that contains the execution results. This displays details about the number of rows inserted, updated, and deleted during the CT mapping execution.

Viewing Execution Results by Using the Audit Information Panel

The Audit Information panel enables you to view additional details about the CT mapping execution. You can also view results for previous executions of the CT mapping. To display the Audit Information panel, from the View menu, select Audit Information.

Expand the location node to which the CT mapping is deployed to view the jobs for this CT mapping. Because you can run a CT mapping multiple times, each execution is represented by a job. Each job contains a node for execution units, which in turn contains a node for steps. Details such as number of rows inserted, updated, deleted, and selected are displayed. Use the Execute Statement tab to view the statement used to run a step.

While running the CT mapping or Change Data Capture process, the Audit Information panel displays the job and its child nodes, such as Execution Units (for CT mapping only), Tasks, and Steps, as they are being run. The status for each job is also displayed. You can filter tasks for a Job in the Audit Information panel based on the status (such as Skipped, Warnings, and so on). The default is set to Filter None, which means that no filtering is performed. You can also sort Jobs by Timestamps.

Use the Message tab to view any message during the execution of the steps, such as an exception from the JDBC driver during the execution of a JDBC task.

Modes of Operation for the Audit Information Panel

The Audit Information panel can display audit information in the following modes:

• If a CT mapping is selected in the Projects Navigator, only the execution audit for that CT mapping is displayed.

• If you select a Function CT in the Projects Navigator, only execution audit information for the selected CT is displayed.

• If you select a module containing CDC tables in the Projects Navigator, the audit information for CDC is displayed.

• If you select an agent in the Locations Navigator, the execution audits for all the jobs run in that agent (including CT mapping, CDC execution, and CT function deployment) are displayed.

Steps to Perform Change Data Capture Using CDC CTs

Performing Change Data Capture includes the following steps:

1. "Selecting the Objects for Change Data Capture"

2. "Creating the Mapping that Loads Changes"

3. "Deploying the Change Data Capture Solution"

4. "Starting the Change Data Capture Process"

5. "Adding a Subscriber to the Change Data Capture Process"

Selecting the Objects for Change Data Capture

You can perform Change Data Capture for any source or target for which code templates are available. The first step in performing CDC is to identify and select the objects for which you want to capture changes.

To specify the objects for which data changes must be captured:

1. If you have not done so, create a module and import the source objects whose data changes you want to capture.

   In this example, the source schema SRC contains the ORDERS table whose changes you want to capture. Create an Oracle module called SRC_MOD, whose location points to the SRC schema, and import the ORDERS table into this module.

2. Double-click the SRC_MOD module to display the Edit Module dialog box.

3. In the left panel, click CDC Code Template. On the CDC Code Template page, in the Code Template list, select the code template that you imported to perform Change Data Capture.

   In this example, select PUBLIC_PROJECT/BUILT_IN_CT/JCT_ORACLE_SIMPLE.

4. In the left panel, click CDC Tables. On the CDC Tables page, use the buttons to move tables whose changes must be captured from the Available section to the Selected section.

   In this example, move the ORDERS table from the Available list to the Selected list.

The ORDERS table is now set up for Change Data Capture.

Creating the Mapping that Loads Changes

Use the following steps to create the CT mapping that loads changes.

1. If you have not done so, create a template mapping module to contain the CT mapping that performs Change Data Capture.

   Ensure that the location of this template mapping module is associated with the agent to which the CT mapping must be deployed.

   In this example, the template mapping module called CDC_MAP_MOD is associated with the DEFAULT_AGENT location. This location represents the OC4J server installed with Oracle Warehouse Builder.

2. Under the template mapping module created in Step 1, create a CT mapping that contains the ETL logic for performing CDC.

   The mapping created in this example is called CDC_LOAD_ORDERS_MAP.

3. Drag and drop the source object whose changes you want to capture.

   In this example, drag and drop the ORDERS table from the SRC_MOD module onto the Mapping Editor canvas.

4. Select the operator representing the source object. In the Property Inspector, under the Change Data Capture node, select Enable.

   Notice that the ORDERS Table operator contains three additional attributes: JRN_SUBSCRIBER, JRN_FLAG, and JRN_DATE.

5. In the Property Inspector, under the Change Data Capture node, set the Change Data Capture Filter property to define the condition that is used to select changed data for a particular subscriber.

6. Drag and drop the table that stores the changed data onto the canvas.

   In this example, the table ORDERS_CHANGE in the SRC_MOD module stores the changes to the ORDERS table.

7. Map the attributes from the source table, whose changes you want to capture, to the corresponding attributes in the target table that stores changed data.

   In this example, map the order_Id, order_status, order_mode, and JRN_DATE attributes from the ORDERS table to order_id, order_status, order_mode, and change_date attributes, respectively, in the ORDERS_CHANGE table.

8. In the Execution View of the CT mapping, create the execution units to associate with code templates.

   In this example, select the ORDERS and ORDERS_CHANGE operators and click Create Execution Unit on the Execution View toolbar.

   Or, select ORDERS and ORDERS_CHANGE and select Create Execution Unit from the Execution menu.

9. If the Code Templates panel is not displayed in the Log window, from the View menu, select Code Templates.

10. Select the Integration/Load Code Template tab in the Code Templates panel.

11. In the Code Template for EX_UNIT1 field, select the code template to be used.

    In this example, select JCT_ORACLE_SIMPLE as the code template used to perform the ETL.

12. Validate the mapping and rectify any errors.

Deploying the Change Data Capture Solution

After you set up the tables for Change Data Capture and create the CT mapping that loads changes from the source table, deploy the Change Data Capture solution using the following steps:

1. If you have not done so, start the Control Center Agent as described in "Starting the Control Center Agent (CCA)".

2. Deploy the target table that stores the changed data.

   In this example, deploy the ORDERS_CHANGE table using either the Design Center or Control Center Manager.

3. Deploy the CT mapping that loads changed data into the target table using the Design Center or Control Center Manager.

   If you get an error saying application exists, go to the Control Center Manager, select Replace as the Deployment Option, and deploy the mapping.

   In this example, deploy the CDC_ORDERS_LOAD_MAP mapping using the Design Center or Control Center Manager.

Starting the Change Data Capture Process

When you start the Change Data Capture process, Oracle Warehouse Builder creates triggers on the source table for which you want to capture changes and on the target table. For your CT mapping to run successfully, ensure that you grant the required privileges on the source schema to the user performing CDC.

To start the capture process, in the Projects Navigator, right-click the module containing the source table for which you want to capture changes, select Change Data Capture, then Start.

In this example, right-click SRC_MOD, select Change Data Capture, then Start.

A new tab is displayed in the Message Log containing messages about the CDC operation. During this process, Oracle Warehouse Builder generates DDL scripts to create triggers on the source table and deploys the DDL scripts.

Adding a Subscriber to the Change Data Capture Process

After you start the Change Data Capture process, you must add a subscriber to the capture system. The subscriber consumes the changes generated by the Change Data Capture process.

To add a subscriber:

1. In the Projects Navigator, right-click the module containing the source table, select Change Data Capture, then Subscribe.

   The Add Subscriber dialog box is displayed containing the list of current subscribers.

   In this example, right-click SRC_MOD, select Change Data Capture, then Subscribe.

2. In the Subscriber Name column of the Subscriber to add section, enter a subscriber name.

   In this example, enter Ord_subscriber in the Subscriber Name column.

3. Click OK to close the Add Subscriber dialog box.

   The Log window displays a new panel for the Subscribe action that lists the actions being performed for the Subscribe action and the results of the actions. If this log displays errors, rectify them and then run the steps to add a subscriber.

Testing the Change Data Capture Process

After you set up your Change Data Capture process, you can optionally test this system to verify that changes to the source are being captured and made available to the subscriber.

To test your Change Data Capture system:

1. In SQL*Plus, log in to the schema that contains the source table for which you want to capture changes.

   In this example, log in to the SRC schema.

2. Insert a row into the source table.

   In this example, insert a row into the ORDERS table.

3. In the Control Center Manager, select the CT mapping that performs Change Data Capture and click Start.

   In this example, start the CDC_LOAD_ORDERS_MAP mapping.

The row that you just added should be inserted into the target table.

The Job Details panel displays the details about each operation being performed in the CT mapping.

Performing Change Data Capture Actions in Oracle Warehouse Builder

After you set up your Change Data Capture solution, you manage the change capture process using predefined actions. If you publish the module or table with CDC tracking as a Web service, then the actions defined in these objects are contained in the generated Web service.

To perform these actions, you right-click the module or table with CDC tracking, select Change Data Capture, and use the following actions available here: Start, Drop, Subscribe, Unsubscribe, Extend Window, Purge Data, Lock Subscriber, and Unlock Subscriber. This section describes the CDC actions that you can perform.

Actions applicable depend on the type of Change Data Capture you are performing.

Starting the Change Data Capture 

After you set up the source object for Change Data Capture, right-click the object and select Start to begin capturing changed data. For more details, see "Starting the Change Data Capture Process".

Stopping a Change Data Capture System 

The "Drop" action stops the Change Data Capture process and drops the capture objects.

To stop a Change Data Capture process, in the Projects Navigator, right-click the data object or module, select Change Data Capture, then Drop. Oracle Warehouse Builder displays a prompt asking to stop the capture and drop capture objects. Click Yes.

Subscribing to a Change Data Capture System 

The "Subscribe" action enables you to add a subscriber to the Change Data Capture system. For more information about adding subscribers, see "Adding a Subscriber to the Change Data Capture Process".

Removing a Subscriber from a Change Data Capture System 

The "Unsubscribe" action enables you to remove a subscriber from the Change Data Capture system. Once you remove a subscriber, change data that is being captured is not available to the subscriber.

To remove a subscriber:

1. In the Projects Navigator, right-click the data object or module from which you want to remove a subscriber and, from the Change Data Capture menu, select Unsubscribe.

   The Remove Subscriber dialog box is displayed. The Current Subscribers section contains the list of current subscribers of the Change Data Capture system.

2. Select the subscriber you want to remove from the Current Subscriber section and use the buttons to move the subscriber to the section titled Subscribers to remove.

3. Click OK.

   The Log window displays the details of the action being performed. Check this log to verify that the Unsubscribe action succeeded.

Extending the Change Data Capture Window

Use the "Extend Window" action to extend the subscription window to receive a new set of change data. To extend the window and receive the latest changed data, right-click the module or table in the Projects Navigator, select Change Data Capture, and then Extend Window. Oracle Warehouse Builder displays a prompt asking to extend the window. Click Yes.

Purging Capture Data 

After subscribers consume the change data that they subscribed to, use the "Purge Data" action to remove the change data from the capture tables. To purge capture data, right-click the module or table in the Projects Navigator, select Change Data Capture, and then Purge Data. Oracle Warehouse Builder displays a prompt asking to purge all capture data. Click Yes.

Locking a Subscriber 

The "Lock Subscriber" operation enables you to lock a subscriber so that a subscriber-specific snapshot of the changes in the consistency window can be taken. The snapshot includes all the changes within the consistency window that have not yet been consumed by the subscriber.

To lock a subscriber:

1. In the Projects Navigator, right-click the data object or module from which you want to lock a subscriber, select Change Data Capture, then Lock Subscriber.

   The Lock Subscriber dialog box is displayed. The Current Subscribers section contains the list of current subscribers of the Change Data Capture system.

2. Select the subscriber you want to lock from the section titled Current Subscriber and use the buttons to move the subscriber to the section titled Subscribers to lock.

3. Click OK.

   The Log window displays the details of the action being performed. Check this log to verify that the Lock Subscriber action succeeded.

Unlocking a Subscriber 

Use the "Unlock Subscriber" action to commit the changes that where locked during the Lock Subscriber operation for the subscriber.

To unlock a subscriber:

1. In the Projects Navigator, right-click the data object or module from which you want to unlock a subscriber, select Change Data Capture, then Lock Subscriber.

   The Unlock Subscriber dialog box is displayed. The Current Subscribers section contains the list of current subscribers of the Change Data Capture system.

2. Select the subscriber you want to unlock from the section titled Current Subscriber and use the buttons to move the subscriber to the section titled Subscribers to lock.

3. Click OK.

   The Log window displays the details of the action being performed. Check this log to verify that the Unlock Subscriber action succeeded.

Steps to Log Constraint Violations While Loading Data Into a Target Table

Checking data constraints using Control CTs includes the following steps:

1. "Creating the Source Module and Importing Source Objects"

2. "Creating the Code Template Mapping that Extracts Data, Checks Data Integrity, and Loads Data into an Oracle Target"

Creating the Source Module and Importing Source Objects

Because the source is a flat file, you create a flat file module in the Design Center and import the flat file into this module.

To create a source module and import the source flat file:

1. In the Projects Navigator, right-click the Files node and select New Flat File Module.

   The Create Module Wizard is displayed. Use this wizard to create a flat file module.

   For more information about creating flat file modules, see Oracle Warehouse Builder Sources and Targets Guide.

2. Right-click the flat file module that you created and select New File.

   The Create Flat File Wizard is displayed. Use this wizard to define and sample the source flat file.

   For more information about creating flat files and sampling them, see Oracle Warehouse Builder Sources and Targets Guide.

Creating the Code Template Mapping that Extracts Data, Checks Data Integrity, and Loads Data into an Oracle Target

1. If you have not done so, create a template mapping module to contain the CT mapping that performs the data integrity check.

   Ensure that you set the location details of this mapping module to the Agent to which the mapping must be deployed.

   This example uses a mapping module called CKM_MAP_MOD that is associated with the DEFAULT_AGENT location. This location points to the OC4J server installed with Oracle Warehouse Builder.

2. Create a CT mapping that contains the logic for extracting, checking, and loading data.

   In this example, create a CT mapping called EMP_LOAD_CKM_MAP.

3. Drag and drop the source file from the source File source module onto the Mapping Editor canvas.

   In this example, drag and drop the file EMP.dat from the File module onto the canvas.

4. Drag and drop the target table onto the canvas.

   In this example, drag and drop the EMP_TGT operator onto the canvas.

   The Table operator properties, in the Property Inspector, contain a node called Control CT. All existing constraints and data rules are displayed in the properties under this section. Use the properties in this group to define how data rules and integrity constraints should be applied.

5. Map the source attributes to the corresponding target attributes.

6. In the Execution View of the mapping, perform the following tasks:

   • Create an execution unit for Flat File operator and associate this execution unit with the LCT_FILE_TO_ORACLE_EXTER_TABLE code template.

   • Create an execution unit containing the target table. Associate this execution unit with the CCT_ORACLE code template.

7. Validate and generate the CT mapping. In the Projects Navigator, right-click the CT mapping and select Generate.

8. Deploy the CT mapping. In the Projects Navigator, right-click the CT mapping and select Deploy.

9. Run the CT mapping. In the Projects Navigator, right-click the CT mapping and select Start.

   The records that violate constraints defined on the EMP_TGT table are not loaded into the target. These records are written to the error table associated with the target table.

Creating the Source Module and Importing Source Objects

In the Projects Navigator, create a source module and its associated location. Import the source objects into this module.

In this example, create an XML module that represents the XML source data and import the CHANNEL and COUNTRY tables. The location associated with this XML module should point to the XML source. Create an Oracle module whose location points to the SH sample schema in the Oracle Database. Import the CUSTOMERS, TIMES, and SALES tables into this module.

For more information about creating modules, see Oracle Warehouse Builder Sources and Targets Guide.

Creating the Target Module and Target Table

1. If you have not done so, create an Oracle module to store the target table.

2. Under this Oracle module, create the target table that stores the transformed data. Right-click the Tables node, select New Table and use the Table Editor to define the table.

3. Generate the target table by right-clicking the table name and selecting Generate. Rectify generation errors, if any.

4. Deploy the target table by right-clicking the table name and selecting Deploy.

In this example, the module WH_TGT contains the target table SUMMARY_SALES.

Creating the CT Mapping that Transforms Source Data Using Oracle Target CTs

1. If you have not done so, create a template mapping module to contain the CT mapping that performs the required data transformation.

   Ensure that you set the location details of this mapping module to the agent to which the mapping must be deployed.

   In this example, a mapping module called ETL_MAP_MOD is associated with the DEFAULT_AGENT location. This location points to the OC4J server installed with Oracle Warehouse Builder.

2. Create a CT mapping to contain the required ETL logic for extracting, transforming, and loading data.

   In this example, create a mapping called LOAD_SUMMARY_SALES_MAP.

3. Drag and drop the source tables onto the Mapping Editor canvas.

   In this example, drag and drop the CHANNEL and COUNTRY tables from the XML source module and the CUSTOMERS, TIMES, and SALES tables from the Oracle module.

4. Drag and drop the operators that you must use to perform the required data transformation. Connect the operator attributes.

   In this example, you add the following operators:

   • A Joiner operator to join the data in the source tables. Set the Join Condition property for the Joiner operator to define how the source tables should be joined. In this example, you use the columns that are common between a pair of tables to join data from those tables.

   • An Aggregator operator to aggregate the output of the Joiner operator. Data is aggregated based on the CHANNEL_DESC and COUNTRY_ISO_CODE attributes and the SQL function CUBE is leveraged to perform aggregation.

     Thus, in the Group by clause of the Aggregator operator, specify the following:

     CUBE(INGRP1.CHANNEL_DESC,INGRP1.COUNTRY_ISO_CODE)

5. Drag and drop the target table onto the canvas.

   In this example, drag and drop the SUMMARY_SALES table onto the canvas.

6. Create the data flows between the source and transformation operators. Map the transformed output to the target table.

   In this example, the tables CHANNEL, CUSTOMERS, COUNTRY, TIMES, and SALES are mapped to the Input groups of the Joiner operator. The Output group of the Joiner operator is mapped to the Aggregator operator. The output of the Aggregator operator is mapped to the target table SUMMARY_SALES.

7. In the Execution View of the CT mapping, create the execution units required to perform the data transformation.

   Your CT mapping should look like the one in Figure 7-6.

   In this example, perform the following:

   • Create an execution unit for the CHANNEL table. Associate this execution unit with the LCT_SQL_TO_SQL code template.

   • Create an execution unit for the COUNTRY table. Associate this execution unit with the LCT_SQL_TO_SQL code template.

   • Create an execution unit containing the tables CUSTOMERS, SALES, and TIMES, the Joiner operator, the Aggregator operator, and the SUMMARY_SALES table. Associate this execution unit with the Oracle Target CT DEFAULT_ORACLE_TARGET_CT.

8. Validate and generate the CT mapping. In the Projects Navigator, right-click the CT mapping and select Generate.

9. Deploy the CT mapping. In the Projects Navigator, right-click the CT mapping and select Deploy.

10. Run the CT mapping to extract data from the XML and Oracle source, transform it, and load it into the Oracle target table. In the Projects Navigator, right-click the CT mapping and select Start.

Figure 7-6 displays the Execution View of the CT mapping that enables you to perform the required data transformation using Oracle Target CTs.

Figure 7-6 Mapping That Uses Oracle Target CTs to Transform Source Data

Description of "Figure 7-6 Mapping That Uses Oracle Target CTs to Transform Source Data"

Steps to Extract Data from IBM DB2, Transform Data, and Load it into an Oracle Database

To extract data from IBM DB2, transform the data, and then load the data into an Oracle Database:

1. "Create the Source Module"

2. "Create the Target Module and Target Table"

3. "Create the CT Mapping that Extracts, Transforms, and Loads Data"

Create the Source Module

In the Projects Navigator, create a DB2 module that represents the source data. The location associated with this module should point to the DB2 database containing the source objects.

In this example, create a DB2 module whose location points to the DB2 database containing the ORDERS and ORDER_DETAILS tables.

For more information about creating a DB2 module, see Oracle Warehouse Builder Sources and Targets Guide.

Create the Target Module and Target Table

Use the following steps to create the target module and the target table.

1. If you have not done so, create an Oracle module to store the target table.

2. Under this Oracle module, create the target table that stores the transformed data. Right-click the Tables node, select New Table, and use the Table Editor to define the table.

3. Generate the target table by right-clicking the table name and selecting Generate. Rectify generation errors, if any.

4. Deploy the target table by right-clicking the table name and selecting Deploy.

In this example, the module WH_TGT contains the target table ORDERS_AGG_CUST.

Create the CT Mapping that Extracts, Transforms, and Loads Data

Use the following steps to create the CT mapping that extracts data from DB2 tables, transforms it, and then loads it into and Oracle Database table.

1. If you have not done so, create a template mapping module to contain the CT mapping that performs the required ETL.

   Ensure that you set the location details of this mapping module to the agent to which the mapping must be deployed.

   In this example, a mapping module called ETL_MAP_MOD is associated with the DEFAULT_AGENT location. This location points to the OC4J server installed with Oracle Warehouse Builder.

2. Create a mapping to contain the required ETL logic for extracting, transforming, and loading data.

   In this example, create a mapping called LOAD_DB2_TO_ORACLE_MAP.

3. Drag and drop the source tables from the DB2 source module onto the Mapping Editor canvas.

   In this example, drag and drop the ORDERS and ORDER_DETAILS tables from the DB2 module source module.

4. Drag and drop the operators that you must use to perform the required data transformation. Connect the operator attributes.

   In this example, you add the following operators:

   • A Joiner operator to join the data in the ORDERS and ORDER_DETAILS tables. Set the Join Condition property for the Joiner operator.

   • An Aggregator operator to aggregate the output of the Joiner operator. Aggregate the data based on the CUSTOMER_ID attribute.

5. Drag and drop the target table onto the canvas.

   In this example, drag and drop the ORDERS_TGT operator onto the canvas.

6. Map the transformed output to the target table.

7. In the Execution View of the mapping, perform the following:

   • Create an execution unit for the ORDERS and ORDER_DETAILS operators. Associate this execution unit with the LCT_SQL_TO_ORACLE code template.

   • Create an execution unit containing the Joiner, Aggregator, and ORDERS_AGG_CUST table. Associate this execution unit with the ICT_ORACLE_INCR_UPD code template.

8. Validate and generate the CT mapping. In the Projects Navigator, right-click the CT mapping and select Generate.

9. Deploy the CT mapping. In the Projects Navigator, right-click the CT mapping and select Deploy.

10. Run the CT mapping to extract data from the source DB2 tables, transform it, and load it into the Oracle target table. In the Projects Navigator, right-click the CT mapping and select Start.

Figure 7-7 displays the Execution View of the mapping LOAD_DB2_TO_ORACLE_MAP.

Figure 7-7 Mapping to Extract Data from IBM DB2, Transform Data, and Load it into Oracle Database

Description of "Figure 7-7 Mapping to Extract Data from IBM DB2, Transform Data, and Load it into Oracle Database"