Skip Headers
Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E12644-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

11 Teradata

This chapter describes how to work with Teradata in Oracle Data Integrator.

This chapter includes the following sections:

11.1 Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in an Teradata database. Oracle Data Integrator features are designed to work best with Teradata, including reverse-engineering, data integrity check, and integration interfaces.

11.1.1 Concepts

The Teradata database concepts map the Oracle Data Integrator concepts as follows: A Teradata server corresponds to a data server in Oracle Data Integrator. Within this server, a database maps to an Oracle Data Integrator physical schema.

Oracle Data Integrator uses Java Database Connectivity (JDBC) and Teradata Utilities to connect to Teradata database.

11.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 11-1 for handling Teradata data. These KMs use Teradata specific features. It is also possible to use the generic SQL KMs with the Teradata database. See Chapter 4, "Generic SQL" for more information.

Table 11-1 Teradata Knowledge Modules

Knowledge Module Description

CKM Teradata

Checks data integrity against constraints defined on a Teradata table. Rejects invalid records in the error table created dynamically. Can be used for static controls as well as flow controls.

IKM File to Teradata (TTU)

This IKM is designed to leverage the power of the Teradata utilities for loading files directly to the target. See Section 11.8.2, "Support for Teradata Utilities" for more information.

IKM SQL to Teradata (TTU)

Integrates data from a SQL compliant database to a Teradata database target table using Teradata Utilities FastLoad, MultiLoad, TPump or Parallel Transporter. See Section 11.8.2, "Support for Teradata Utilities" for more information.

IKM Teradata Control Append

Integrates data in a Teradata target table in replace/append mode.

IKM Teradata Incremental Update

Integrates data in a Teradata target table in incremental update mode.

IKM Teradata Slowly Changing Dimension

Integrates data in a Teradata target table used as a Type II Slowly Changing Dimension in your Data Warehouse.

IKM Teradata to File (TTU)

Integrates data in a target file from a Teradata staging area in replace mode. See Section 11.8.2, "Support for Teradata Utilities" for more information.

IKM Teradata Multi Statement

Integrates data in Teradata database target table using multi statement requests, managed in one SQL transaction. See Using Multi Statement Requests for more information.

IKM SQL to Teradata Control Append

Integrates data from an ANSI-92 compliant source database into Teradata target table in truncate / insert (append) mode.

This IKM is typically used for ETL configurations: source and target tables are on different databases and the interface's staging area is set to the logical schema of the source tables or a third schema.

LKM File to Teradata (TTU)

Loads data from a File to a Teradata staging area database using the Teradata bulk utilities. See Section 11.8.2, "Support for Teradata Utilities" for more information.

LKM SQL to Teradata (TTU)

Loads data from a SQL compliant source database to a Teradata staging area database using a native Teradata bulk utility. See Section 11.8.2, "Support for Teradata Utilities" for more information.

RKM Teradata

Retrieves metadata from the Teradata database using the DBC system views. This RKM supports UNICODE columns.


11.2 Installation and Configuration

Make sure you have read the information in this section before you start using the Teradata Knowledge Modules:

11.2.1 System Requirements and Certifications

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.

11.2.2 Technology Specific Requirements

Some of the Knowledge Modules for Teradata use the following Teradata Tools and Utilities (TTU):

  • FastLoad

  • MultiLoad

  • Tpump

  • FastExport

  • Teradata Parallel Transporter

The following requirements and restrictions apply for these Knowledge Modules:

  • Teradata Utilities must be installed on the machine running the Oracle Data Integrator Agent.

  • The server name of the Teradata Server defined in the Topology must match the Teradata connect string used for this server (without the COP_n postfix).

  • It is recommended to install the Agent on a separate platform than the target Teradata host. The machine were the Agent is installed should have a very large network bandwidth to the target Teradata server.

  • The IKM File to Teradata (TTU) and LKM File to Teradata (TTU) support a File Character Set Encoding option specify the encoding of the files integrated with TTU. If this option is unset, the default TTU charset is used.
    Refer to the "Getting Started: International Character Sets and the Teradata Database" Teradata guide for more information about character set encoding.

See the Teradata documentation for more information.

11.2.3 Connectivity Requirements

This section lists the requirements for connecting to a Teradata Database.

JDBC Driver

Oracle Data Integrator uses the Teradata JDBC Driver to connect to a Teradata Database. The Teradata Gateway for JDBC must be running, and this driver must be installed in your Oracle Data Integrator installation. You can find this driver at:

http://www.teradata.com/DownloadCenter/Group48.aspx

11.3 Setting up the Topology

Setting up the Topology consists of:

  1. Creating a Teradata Data Server

  2. Creating a Teradata Physical Schema

11.3.1 Creating a Teradata Data Server

A Teradata data server corresponds to a Teradata Database connected with a specific Teradata user account. This user will have access to several databases in this Teradata system, corresponding to the physical schemas in Oracle Data Integrator created under the data server.

11.3.1.1 Creation of the Data Server

Create a data server for the Teradata 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 a Teradata data server:

  1. In the Definition tab:

    • Name: Name of the data server that will appear in Oracle Data Integrator

    • Server: Physical name of the data server

    • User/Password: Teradata user with its password

  2. In the JDBC tab:

    • JDBC Driver: com.teradata.jdbc.TeraDriver

    • JDBC URL: jdbc:teradata://<host>:<port>/<server>

      The URL parameters are:

      • <host>: Teradata gateway for JDBC machine network name or IP address.

      • <port>: gateway port number (usually 7060)

      • <server>: name of the Teradata server to connect

11.3.2 Creating a Teradata Physical Schema

Create a Teradata 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.

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.

11.4 Setting Up an Integration Project

Setting up a project using the Teradata database 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 following knowledge modules into your project for getting started with Teradata:

11.5 Creating and Reverse-Engineering a Teradata Model

This section contains the following topics:

11.5.1 Create a Teradata Model

Create a Teradata Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

11.5.2 Reverse-engineer a Teradata Model

Teradata supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering, which uses a RKM to retrieve the metadata from Teradata database using the DBC system views.

In most of the cases, consider using the standard JDBC reverse engineering for starting. Standard reverse-engineering with Teradata retrieves tables and columns.

Preferably use customized reverse-engineering for retrieving more metadata. Teradata customized reverse-engineering retrieves the tables, views, columns, keys (primary indexes and secondary indexes) and foreign keys. Descriptive information (column titles and short descriptions) are also reverse-engineered.

Standard Reverse-Engineering

To perform a Standard Reverse-Engineering on Teradata use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

Customized Reverse-Engineering

To perform a Customized Reverse-Engineering on Teradata with a RKM, use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to the Teradata technology:

  1. In the Reverse tab of the Teradata Model, select the KM: RKM Teradata.<project name>.

  2. Set the REVERSE_FKS option to true if you want to reverse-engineer existing FK constraints in the database.

  3. Set the REVERSE_TABLE_CONSTRAINTS to true if you want to reverse-engineer table constrains.

  4. Set REVERSE_COLUMN_CHARACTER_SET to true if you want to reverse-engineer VARCHAR and CHAR for a Unicode database as CHAR()CHARACTER SET UNICODE or VARCHAR()CHARACTER SET UNICODE respectively, regardless of the use of CHARACTER SET UNICODE clause at table creation.

The reverse-engineering process returns tables, views, columns, Keys (primary indexes and secondary indexes) and Foreign Keys. Descriptive information (Column titles and short descriptions) are also reverse-engineered

Note that Unique Indexes are reversed as follows:

  • The unique primary index is considered as a primary key.

  • The primary index is considered as a non unique index.

  • The secondary unique primary index is considered as an alternate key

  • The secondary non unique primary index is considered as a non unique index.

You can use this RKM to retrieve specific Teradata metadata that is not supported by the standard JDBC interface (such as primary indexes).

11.6 Setting up Data Quality

Oracle Data Integrator provides the CKM Teradata for checking data integrity against constraints defined on a Teradata table. See "Set up Flow Control and Post-Integration Control" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for details.

Oracle Data Integrator provides the Knowledge Module listed in Table 11-2 to perform a check on Teradata.

Table 11-2 Check Knowledge Modules for Teradata Database

Recommended KM Notes

CKM Teradata

Checks data integrity against constraints defined on a Teradata table. Rejects invalid records in the error table created dynamically. Can be used for static controls as well as flow controls.

This KM supports the following Teradata optimizations:

  • Primary Indexes

  • Statistics


11.7 Designing an Interface

You can use Teradata as a source, staging area or a target of an integration interface. It is also possible to create ETL-style integration interfaces based on the Teradata technology.

The KM choice for an interface or a check determines the abilities and performance of this interface or check. The recommendations in this section help in the selection of the KM for different situations concerning a Teradata data server.

11.7.1 Loading Data from and to Teradata

Teradata can be used as a source, target or staging area of an interface. The LKM choice in the Interface Flow tab to load data between Teradata and another type of data server is essential for the performance of an interface.

11.7.1.1 Loading Data from Teradata

Use the Generic SQL KMs or the KMs specific to the other technology involved to load data from a Teradata database to a target or staging area database.

For extracting data from a Teradata staging area to a file, use the IKM File to Teradata (TTU). See Section 11.7.2, "Integrating Data in Teradata" for more information.

11.7.1.2 Loading Data to Teradata

Oracle Data Integrator provides Knowledge Modules that implement optimized methods for loading data from a source or staging area into a Teradata database. These optimized Teradata KMs are listed in Table 11-3. In addition to these KMs, you can also use the Generic SQL KMs or the KMs specific to the other technology involved.

Table 11-3 KMs for loading data to Teradata

Source or Staging Area Technology KM Notes

File

LKM File to Teradata (TTU)

Loads data from a File to a Teradata staging area database using the Teradata bulk utilities.

Because this method uses the native Teradata utilities to load the file in the staging area, 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 a Teradata database.

This KM support the following Teradata optimizations:

  • Statistics

  • Optimized Temporary Tables Management

SQL

LKM SQL to Teradata (TTU)

Loads data from a SQL compliant source database to a Teradata staging area database using a native Teradata bulk utility.

This LKM can unload the source data in a file or Named Pipe and then call the specified Teradata utility to populate the staging table from this file/pipe. Using named pipes avoids landing the data in a file. This LKM is recommended for very large volumes.

Consider using this IKM when:

  • The source data located on a SQL compliant database is large

  • You don't want to stage your data between the source and the target

  • Your staging area is a Teradata database.

This KM support the following Teradata optimizations:

  • Support for Teradata Utilities

  • Support for Named Pipes

  • Optimized Temporary Tables Management


11.7.2 Integrating Data in Teradata

Oracle Data Integrator provides Knowledge Modules that implement optimized data integration strategies for Teradata. These optimized Teradata KMs are listed in Table 11-4. In addition to these KMs, you can also use the Generic SQL KMs.

The IKM choice in the Interface Flow tab determines the performances and possibilities for integrating.

Table 11-4 KMs for integrating data to Teradata

KM Notes

IKM Teradata Control Append

Integrates data in a Teradata target table in replace/append mode. When flow data needs to be checked using a CKM, this IKM creates a temporary staging table before invoking the CKM.

Consider using this IKM if you plan to load your Teradata target table in replace mode, with or without data integrity check.

To use this IKM, the staging area must be on the same data server as the target Teradata table.

This KM support the following Teradata optimizations:

  • Primary Indexes and Statistics

  • Optimized Temporary Tables Management

IKM Teradata Incremental Update

Integrates data in a Teradata 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 guess 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 Teradata 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.

This KM support the following Teradata optimizations:

  • Primary Indexes and Statistics

  • Optimized Temporary Tables Management

IKM Teradata Multi Statement

Integrates data in Teradata database target table using multi statement requests, managed in one SQL transaction

IKM Teradata Slowly Changing Dimension

Integrates data in a Teradata 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 Teradata 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.

This KM support the following Teradata optimizations:

  • Primary Indexes and Statistics

  • Optimized Temporary Tables Management

This KM also includes a COMPATIBLE option. This option corresponds to the Teradata engine major version number. If this version is 12 or above, then a MERGE statement will be used instead of the standard INSERT then UPDATE statements to merge the incoming data flow into the target table.

IKM Teradata to File (TTU)

Integrates data in a target file from a Teradata staging area in replace mode. This IKM requires the staging area to be on Teradata. It uses the native Teradata utilities to export the data to the target file.

Consider using this IKM if you plan to transform and export data to a target file from your Teradata server.

To use this IKM, the staging area must be different from the target. It should be set to a Teradata location.

This KM support the following Teradata optimizations:

  • Support for Teradata Utilities

IKM File to Teradata (TTU)

This IKM is designed to leverage the power of the Teradata utilities for loading files directly to the target. It is restricted to one file as source and one Teradata table as target.

Depending on the utility you choose, you'll have the ability to integrate the data in either replace or incremental update mode.

Consider using this IKM if you plan to load a single flat file to your target table. Because it uses the Teradata utilities, this IKM is recommended for very large volumes.

To use this IKM, you have to set the staging area to the source file's schema.

This KM support the following Teradata optimizations:

  • Primary Indexes and Statistics

  • Support for Teradata Utilities

  • Optimized Temporary Tables Management.

IKM SQL to Teradata (TTU)

Integrates data from a SQL compliant database to a Teradata database target table using Teradata Utilities TPUMP, FASTLOAD OR MULTILOAD.

This IKM is designed to leverage the power of the Teradata utilities for loading source data directly to the target. It can only be used when all source tables belong to the same data server and when this data server is used as a staging area (staging area on source). Source data can be unloaded into a file or Named Pipe and then loaded by the selected Teradata utility directly in the target table. Using named pipes avoids landing the data in a file. This IKM is recommended for very large volumes.

Depending on the utility you choose, you'll have the ability to integrate the data in replace or incremental update mode.

Consider using this IKM when:

  • You plan to load your target table with few transformations on the source

  • All your source tables are on the same data server (used as the staging area)

  • You don't want to stage your data between the source and the target

To use this IKM, you have to set the staging area to the source data server's schema.

This KM support the following Teradata optimizations:

  • Primary Indexes and Statistics

  • Support for Teradata Utilities

  • Support for Named Pipes

  • Optimized Temporary Tables Management

IKM SQL to Teradata Control Append

Integrates data from an ANSI-92 compliant source database into Teradata target table in truncate / insert (append) mode.

This IKM is typically used for ETL configurations: source and target tables are on different databases and the interface's staging area is set to the logical schema of the source tables or a third schema. See Section 11.7.3, "Designing an ETL-Style Interface" for more information.


Using Slowly Changing Dimensions

For using slowly changing dimensions, make sure to set the Slowly Changing Dimension value for each column of the target datastore. This value is used by the IKM Teradata Slowly Changing Dimension to identify the Surrogate Key, Natural Key, Overwrite or Insert Column, Current Record Flag, and Start/End Timestamps columns.

Using Multi Statement Requests

Multi statement requests are typically enable the parallel execution of simple interfaces. The Teradata performance is improved by synchronized scans and by avoiding transient journal.

Set the KM options as follows:

  • Interfaces using this KM must be used within a package:

    • In the first interface of the package loading a table via the multi-statement set the INIT_MULTI_STATEMENT option to YES.

    • The subsequent interfaces loading a table via the multi-statement must use this KM and have the INIT_MULTI_STATEMENT option set to NO.

    • The last interface must have the EXECUTE option set to YES in order to run the generated multi-statement.

  • In the STATEMENT_TYPE option, specify the type of statement (insert or update) for each interface.

  • In the SQL_OPTION option, specify the additional SQL sentence that is added at the end of the query, for example QUALIFY Clause.

Note the following limitations concerning multi-statements:

  • Multi-statements are only supported when they are used within a package.

  • Temporary indexes are not supported.

  • Updates are considered as Inserts in terms of row count.

  • Updates can only have a single Dataset.

  • Only executing interface (EXECUTE = YES) reports row counts.

  • Journalized source data not supported.

  • Neither Flow Control nor Static Control is supported.

  • The SQL_OPTION option applies only to the last Dataset.

11.7.3 Designing an ETL-Style Interface

See "Working with Integration Interface" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for generic information on how to design integration interfaces. This section describes how to design an ETL-style interface where the staging area is on a Teradata database or any ANSI-92 compliant database and the target on Teradata.

In an ETL-style interface, ODI processes the data in a staging area, which is different from the target. Oracle Data Integrator provides two ways for loading the data from a Teradata or an ANSI-92 compliant staging area to a Teradata target:

Depending on the KM strategy that is used, flow and static control are supported.

Using a Multi-connection IKM

A multi-connection IKM allows integrating data into a target when the staging area and sources are on different data servers.

Oracle Data Integrator provides the following multi-connection IKM for handling Teradata data: IKM SQL to Teradata Control Append. You can also use the generic SQL multi-connection IKMs. See Chapter 4, "Generic SQL" for more information.

See Table 11-5 for more information on when to use a multi-connection IKM.

To use a multi-connection IKM in an ETL-style interface:

  1. Create an integration interface with an ANSI-92 compliant staging area and the target on Teradata using the standard procedure as described in "Creating an Interface" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section describes only the ETL-style specific steps.

  2. In the Definition tab of the Interface Editor, select Staging Area different from Target and select the logical schema of the source tables or a third schema.

  3. In the Flow tab, select one of the Source Sets, by clicking its title. The Property Inspector opens for this object.

  4. Select an LKM from the LKM Selector list to load from the source(s) to the staging area. See Table 11-5 to determine the LKM you can use.

  5. Optionally, modify the KM options.

  6. In the Flow tab, select the Target by clicking its title. The Property Inspector opens for this object.

    In the Property Inspector, select an ETL multi-connection IKM from the IKM Selector list to load the data from the staging area to the target. See Table 11-5 to determine the IKM you can use.

Note the following when setting the KM options of the IKM SQL to Teradata Control Append:

  • If you do not want to create any tables on the target system, set FLOW_CONTROL=false. If FLOW_CONTROL=false, the data is inserted directly into the target table.

  • If FLOW_CONTROL=true, the flow table is created on the target or on the staging area.

  • If you want to recycle data rejected from a previous control, set RECYCLE_ERROR=true and set an update key for your interface.

Using an LKM and a mono-connection IKM

If there is no dedicated multi-connection IKM, use a standard exporting LKM in combination with a standard mono-connection IKM. The exporting LKM is used to load the flow table from the staging area to the target. The mono-connection IKM is used to integrate the data flow into the target table.

Oracle Data Integrator supports any ANSI SQL-92 standard compliant technology as a source and staging area of an ETL-style interface. The target is Teradata.

See Table 11-5 for more information on when to use the combination of a standard LKM and a mono-connection IKM.

To use an LKM and a mono-connection IKM in an ETL-style interface:

  1. Create an integration interface with an ANSI-92 compliant staging area and the target on Teradata using the standard procedure as described in "Creating an Interface" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section describes only the ETL-style specific steps.

  2. In the Definition tab of the Interface Editor, select Staging Area different from Target and select the logical schema of the source tables or a third schema.

  3. In the Flow tab, select one of the Source Sets.

  4. In the Property Inspector, select an LKM from the LKM Selector list to load from the source(s) to the staging area. See Table 11-5 to determine the LKM you can use.

  5. Optionally, modify the KM options.

  6. Select the Staging Area. In the Property Inspector, select an LKM from the LKM Selector list to load from the staging area to the target. See Table 11-5 to determine the LKM you can use.

  7. Optionally, modify the options.

  8. Select the Target by clicking its title. The Property Inspector opens for this object.

    In the Property Inspector, select a standard mono-connection IKM from the IKM Selector list to update the target. See Table 11-5 to determine the IKM you can use.

Table 11-5 KM Guidelines for ETL-Style Interfaces with Teradata Data

Source Staging Area Target Exporting LKM IKM KM Strategy Comment

ANSI SQL-92 standard compliant

ANSI SQL-92 standard compliant

Teradata

NA

IKM SQL to Teradata Control Append

Multi-connection IKM

Recommended to perform control append

Supports flow control.

ANSI SQL-92 standard compliant

Teradata or any ANSI SQL-92 standard compliant database

Teradata or any ANSI SQL-92 standard compliant database

NA

IKM SQL to SQL Incremental Update

Multi-connection IKM

Allows an incremental update strategy with no temporary target-side objects. Use this KM if it is not possible to create temporary objects in the target server.

The application updates are made without temporary objects on the target, the updates are made directly from source to target. The configuration where the flow table is created on the staging area and not in the target should be used only for small volumes of data.

Supports flow and static control

ANSI SQL-92 standard compliant

Teradata or ANSI SQL-92 standard compliant

Teradata

LKM SQL to Teradata (TTU)

IKM Teradata Incremental Update

LKM + standard IKM

 

ANSI SQL-92 standard compliant

Teradata

Teradata

LKM SQL to Teradata (TTU)

IKM Teradata Slowly Changing Dimension

LKM + standard IKM

 

ANSI SQL-92 standard compliant

ANSI SQL-92 standard compliant

Teradata

LKM SQL to Teradata (TTU)

IKM SQL to Teradata (TTU)

LKM + standard IKM

If no flow control, this strategy is recommended for large volumes of data


11.8 KM Optimizations for Teradata

This section describes the specific optimizations for Teradata that are included in the Oracle Data Integrator Knowledge Modules.

This section includes the following topics:

11.8.1 Primary Indexes and Statistics

Teradata performance heavily relies on primary indexes. The Teradata KMs support customized primary indexes (PI) for temporary and target tables. This applies to Teradata LKMs, IKMs and CKMs. The primary index for the temporary and target tables can be defined in these KMs using the PRIMARY_INDEX KM option, which takes the following values:

  • [PK]: The PI will be the primary key of each temporary or target table. This is the default value.

  • [NOPI]: Do not specify primary index (Teradata 13.0 & above only).

  • [UK]: The PI will be the update key of the interface. This is the default value.

    • <Column list>: This is a free PI based on the comma-separated list of column names.

    • <Empty string>: No primary index is specified. The Teradata engine will use the default rule for the PI (first column of the temporary table).

Teradata MultiColumnStatistics should optionally be gathered for selected PI columns. This is controlled by COLLECT_STATS KM option, which is set to true by default.

11.8.2 Support for Teradata Utilities

Teradata Utilities (TTU) provide an efficient method for transferring data from and to the Teradata engine. When using a LKM or IKM supporting TTUs, it is possible to set the method for loading data using the TERADATA_UTILITY option.

This option takes the following values when pushing data to a Teradata target (IKM) or staging area (LKM):

  • FASTLOAD: use Teradata FastLoad

  • MLOAD: use Teradata MultiLoad

  • TPUMP: use Teradata TPump

  • TPT-LOAD: use Teradata Parallel Transporter (Load Operator)

  • TPT-SQL-INSERT: use Teradata Parallel Transporter (SQL Insert Operator)

This option takes the following values when pushing data FROM Teradata to a file:

  • FEXP: use Teradata FastExport

  • TPT: use Teradata Parallel Transporter

When using TTU KMs, you should also take into account the following KM parameters:

  • REPORT_NB_ROWS: This option allows you to report the number of lines processed by the utility in a Warning step of the integration interface.

  • SESSIONS: Number of FastLoad sessions

  • MAX_ALLOWED_ERRORS: Maximum number of tolerated errors. This corresponds to the ERRLIMIT command in FastLoad/MultiLoad/TPump and to the ErrorLimit attribute for TPT.

  • MULTILOAD_TPUMP_TYPE: Operation performed by the MultiLoad or TPump utility. Valid values are INSERT, UPSERT and DELETE. For UPSERT and DELETE an update key is required in the interface.

For details and appropriate choice of utility and load operator, refer to the Teradata documentation.

11.8.3 Support for Named Pipes

When using TTU KMs to move data between a SQL source and Teradata, it is possible to increase the performances by using Named Pipes instead of files between the unload/load processes. Named Pipes can be activated by setting the NP_USE_NAMED_PIPE option to YES. The following options should also be taken into account for using Named Pipes:

  • NP_EXEC_ON_WINDOWS: Set this option to YES if the run-time agent runs on a windows platform.

  • NP_ACCESS_MODULE: Access module used for Named Pipes. This access module is platform dependant.

  • NP_TTU_STARTUP_TIME: This number of seconds for the TTU to be able to receive data through the pipe. This is the delay between the moment the KM starts the TTU and the moment the KM starts to push data into the named pipe. This delay is dependant on the machine workload.

11.8.4 Optimized Management of Temporary Tables

Creating and dropping Data Integrator temporary staging tables can be a resource consuming process on a Teradata engine. The ODI_DDL KM option provides a mean to control these DDL operations. It takes the following values:

  • DROP_CREATE: Always drop and recreate all temporary tables for every execution (default behavior).

  • CREATE_DELETE_ALL: Create temporary tables when needed (usually for the first execution only) and use DELETE ALL to drop the temporary table content. Temporary table are reused for subsequent executions.

  • DELETE_ALL: Do not create temporary tables. Only submit DELETE ALL for all temporary tables.

  • NONE: Do not issue any DDL on temporary tables. Temporary tables should be handled separately.