12 Using Code Templates to Load and Transfer Data

As Warehouse Builder loads and transforms data from many different database platforms and systems, the technology used to access and load these different data sources needs to be flexible, extensible, and efficient. Oracle Warehouse Builder solves this problem using code templates (CTs).

CTs are extensible components that enable efficient loading, transformation, or integration of data for a specific data source or target. They can be used in a mapping to perform specific tasks. For example, the task can be extracting data from a source and loading it into a target table.

This chapter contains the following topics:

About Code Templates
Working with Code Templates

About Code Templates

CTs are components of Oracle Warehouse Builder's open connector technology. CTs contain the knowledge required by Warehouse Builder to perform a specific set of tasks against a specific system or set of systems. Combined with a connectivity layer such as JDBC, CTs define an open connector that performs defined tasks against a system, such as connecting to this system, extracting data from the system, transforming the data, or checking and integrating the data.

Open connectors provide a combination of:

Connection options such as JDBC
Correct syntax, such as SQL, for the specific platform
Control over the creation and deletion of objects such as the temporary and work tables, views, and triggers
Data processing and transformation strategies
Data movement options (create target table, insert, delete, update)

There are different types of CTs available for performing different tasks.Warehouse Builder contains the following types of CTs:

Load Code Template: To load data from a source database into a staging area.
Integration Code Template: To efficiently transform data from staging area to the target tables, generating optimized native SQL for the given database.
Control Code Template: To check for errors in source data.
Change Data Capture Code Template: To capture changed data in source objects.
Oracle Target Code Template: To deploy and execute a map within a CT framework. It supports the rich set of transformation operators available in mappings including match/merge, name and address and so on.
Function Code Template: To deploy functions, packages, and procedures.

With CTs, you thus get templates to perform specific tasks in an efficient manner. For example, to load data from a DB2 database and move it to a SQL Server database using CTs:

Create a mapping with a source table and a target table
Use a Load CT (LCT) to load data from the DB2 source table
Use an Integration CT (ICT) to move data to the SQL Server target table

When you execute this mapping, the loading and transfer of data from DB2 to SQL Server is managed by the LCT and the ICT.

You cannot use CTs in customary mappings. A CT can be used only in a special type of mapping called code template mappings. You can create code template mappings from the Template Mappings node in the Projects Navigator.

To use CT in a mapping, you must encapsulate the objects in a code template mapping into units called execution unit. See "Using Code Templates" for more details.

You can either import predefined CTs from Oracle Data Integrator 10g (Knowledge Modules) or create new CTs based on your specific requirements.

Load Code Template

LCTs enable you to load data from a remote data source to the staging area. They support a variety of data sources. These include:

Oracle
DB2
SQL Server
File

You can also add new data sources. By default, an LCT must be associated with an execution unit that contains a source table object.

Integration Code Template

ICTs are used to integrate data from staging tables into a target database. Based on the loading method and the type of target database, you can use one of the existing set of ICTs. The following rules are applicable when using ICTs.

The input to an ICT must come from an LCT
An ICT must be associated with a target database (through an execution unit).

Control Code Template

Control CTs provide data quality checks to ensure data integrity. These include checking for key constraints as well as other user-defined data loading rules. CCTs can be used to check for data constraints in existing tables as well as while loading new data into tables. CCTs are associated with execution units in maps. For details about implementing data quality rules in ETL, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Change Data Capture Code Template

The CDC CTs enable you to capture only those changes that have been made to a data object since the last import. CDC CTs control the change capture data processing and are associated with tables and modules.

Oracle Target Code Template

Oracle Target CTs can contain all of the regular mapping operators such as match/merge, name/address, dimension, and so on.

Function Code Template

A Function CT is used to deploy functions, packages, and procedures. Function CTs are associated with functions, packages, and procedures.

Predefined Code Templates

Warehouse Builder contains certain predefined CTs that you can directly use in Warehouse Builder.

These predefined CTs are also installed in the Warehouse Builder repository and are listed in the Globals Navigator. To view the predefined CTs from the Globals Navigator, expand the Public Code Templates node and then expand the BUILT_IN_CT node. You can directly use these predefined code templates without having to import them.

For a list of all the predefined code templates shipped with Warehouse Builder, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Note:

The predefined code templates available in Warehouse Builder were imported from the Knowledge Modules available in Oracle Data Integrator 10g. These code template files are available at OWB_ORACLE_HOME/owb/misc/CodeTemplates.

Working with Code Templates

To use CTs in Warehouse Builder, perform the following steps:

Create a CT folder to store a CT, as described in "Creating a Code Template Folder".
Create or import a CT, as described in "Creating a Code Template".
Create a mapping under Template Mappings, define execution units within the map, and associate CTs to those execution units, as described in "Using Code Templates".

Creating a Code Template Folder

You must have a container in Warehouse Builder to hold CTs. This is known as a CT folder.

To create a CT folder:

From the Projects Navigator, right-click Code Templates and click New Code Template Folder to open the Create Code Template Folder dialog box.
Provide a name for the new folder and click OK.

The newly created CT folder is available in the Projects Navigator. You can also view all the available types of CTs under the newly created folder.

Creating a Code Template

You can create any of the available types of CTs under a CT folder. For some types of CTs such as Load, Integrate, and Change Data Capture, you can either import an existing CT from Oracle Data Integrator or create a new one. For Oracle Target CTs and Function CTs, you must create a new CT. The process to create any type of CT is the same as described for LCT.

To create a new LCT:

Right-click Load and click New Load CT.
The Create Load Code Template dialog box is displayed.
Enter a name and an optional description for the CT and click OK.

The newly created CT is displayed under Load.

You can now specify the tasks between the Start and End tasks.

Importing a Code Template

To import CTs:

Select a Code Template Folder into which you want to import the CTs.
Select File, then Import, and then Code Template.

The Import Code Template Wizard is displayed.
Click Next to open the Select Files page.
Click Browse and select the source directory containing the CTs to be imported.

The available CTs are listed under Directory Files in the Select Files page.
Select the CTs and move them to the Files to Import list.
Click Next to open the Name Code Templates page.

Here, you can either provide new names for the CTs or retain the current name.
Click Next to open the Summary page, and then click Finish.

Warehouse Builder automatically inserts imported CTs under the appropriate type. For example, if you import an LCT, it gets added under Load in the Projects Navigator.

Note:

You do not have to import predefined CTs, as these are already available in the repository and can be used directly.

Tasks in a Code Template

A CT consists of a series of predefined tasks that are broadly classified as:

JDBC: To process JDBC statements.
Jython: To process Jython statements.
Runtime API: To provide access to Oracle Data Integrator 10g tools such as OdiOSCommand.
OS: To provide access to the operating system.
Jacl: To provide access to native tcl script that is generated for a CT mapping.

For example, an LCT that is used to load data from SQL to Oracle consists of the following tasks:

Start_Task
1. Validate KM options: Jython task
2. Drop work table: JDBC task
3. Create work table: JDBC task
4. Lock journalized table: JDBC task
5. Load data: JDBC task
6. Analyze work table: JDBC task
7. Cleanup journalized table: JDBC task
8. Drop work table: JDBC task
End_Task

Using Code Templates

CTs are used in a code template mapping. To use CTs:

Create a template mapping module.
Create a map under the module.
Use the CT in the mapping.

Creating a Template Mappings Module

To create a Template Mappings module:

From the Projects Navigator, right-click Template Mappings and select New Mapping Module.

The Create Module Wizard is displayed.
In the Name and Description page, provide a name and description (optional).
In the Connection Details page, either select an existing location or click Edit to open the Edit Agent Location dialog box and provide the connection details for the agent. See "Specifying an Agent for Template Mappings" for more details.
In the Summary page, verify the information you provided and click Finish.

The newly created template mapping module is now available under the Template Mappings node.

Specifying an Agent for Template Mappings

Template mappings get executed in a control center agent. By default, Warehouse Builder contains a default agent location, which like the default control center, refers to the control center agent on the same host as the control center service.

Starting and Stopping the Control Center Agent

To use the default agent, ensure that the control center agent (CCA) is running.

Windows On Windows, start the CCA instance by navigating to OWB_ORACLE_HOME/owb/bin/win32 and running the file ccastart.bat. When you run this file the first time, you will be prompted to enter a password for the oc4jadmin user.

To stop the CCA, run the ccashut.bat script located in the OWB_ORACLE_HOME/owb/bin/win32 directory. The script will prompt you to enter the oc4jadmin user password.

UNIX On UNIX, start the CCA instance by running the file ccastart located in the OWB_ORACLE_HOME/owb/bin/unix directory.

To stop the CCA, run the ccashut file located in the OWB_ORACLE_HOME/owb/bin/unix directory. The script will prompt you to enter the oc4jadmin user password.

Edit Agent Location Dialog Box

You must specify the following details in the Edit Agent Location:DEFAULT AGENT dialog box:

User: oc4jadmin
Password: The password set while invoking the ccastart file.

Host: Host where the agent resides
Port: 23791
Port Type: RMI
Instance: Can be left blank
Application Name: Name of the application where the mapping gets deployed. Enter the value jrt to deploy it to the default CCA that is installed with Warehouse Builder.
HTTP Port: 8888

Note:

Ensure that the CCA instance is running before you set the agent location.

Creating a Mapping Under the Code Template Mapping

To create a mapping under the code template mapping module:

Right-click the Template Mapping module and click New Mapping.
In the Create Mapping dialog box, provide a name for the mapping.

The newly created mapping is available under Template Mapping module.

Using Code Templates in the Mapping

For mappings under a code template mapping, Warehouse Builder provides two different views to work with, Logical View and Execution View.

In the logical view, you can insert the various components of the mapping. This includes the source and target operators (tables), as well as the transformation operators. You can also define the data flow from the source to the target, along with the transformation operators that define how the data is transformed before it is loaded into the target.

In the execution view, you must define execution units for the components within the mapping. An execution unit is a module that holds the various mapping operators.

For example, if your mapping has a DB2 source table, an expression operator, and an Oracle target table, then you can bind the source table to an execution unit DB2_SOURCE_TABLE, and the expression and the target table to another execution unit DB2_to_OracleTable, as shown in Figure 12-1.

Figure 12-1 Execution Units in a Map

Description of "Figure 12-1 Execution Units in a Map"

After you bind the components of the mapping to execution units, you must associate a CT with each of the execution units. In the current example, you must associate DB2_SOURCE_TABLE to an LCT that loads data from a DB2 source (LCT_SQL_TO_ORACLE). DB2_to_OracleTable must be associated with an ICT that retrieves and loads data into an Oracle table (ICT_ORACLE_INCR_UPD). For more information about how to bind the components of a mapping to execution units, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Note that execution units containing certain transformation operators cannot be bound to LCTs and ICTs. See Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more information about operators not supported in LCTs and ICTs.

For more information about different types of CTs, and about creating and using CTs in mappings, see the Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.