| Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) Part Number E12644-07 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) Part Number E12644-07 |
|
|
PDF · Mobi · ePub |
This chapter describes how to work with Teradata in Oracle Data Integrator.
This chapter includes the following sections:
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.
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.
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. |
Make sure you have read the information in this section before you start using the Teradata Knowledge Modules:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html.
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.
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:
Setting up the Topology consists of:
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.
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:
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
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
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.
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:
CKM Teradata
IKM File to Teradata (TTU)
IKM SQL to Teradata (TTU)
IKM Teradata Control Append
IKM Teradata Incremental Update
IKM Teradata Multi Statement
IKM Teradata Slowly Changing Dimension
IKM Teradata to File (TTU)
IKM SQL to Teradata Control Append
LKM File to Teradata (TTU)
LKM SQL to Teradata (TTU)
RKM Teradata
This section contains the following topics:
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.
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:
In the Reverse tab of the Teradata Model, select the KM: RKM Teradata.<project name>.
Set the REVERSE_FKS option to true if you want to reverse-engineer existing FK constraints in the database.
Set the REVERSE_TABLE_CONSTRAINTS to true if you want to reverse-engineer table constrains.
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).
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:
|
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.
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.
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.
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:
|
|
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:
This KM support the following Teradata optimizations:
|
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:
|
|
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:
|
|
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:
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:
|
|
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:
|
|
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:
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:
|
|
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.
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:
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.
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.
In the Flow tab, select one of the Source Sets, by clicking its title. The Property Inspector opens for this object.
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.
Optionally, modify the KM options.
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:
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.
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.
In the Flow tab, select one of the Source Sets.
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.
Optionally, modify the KM options.
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.
Optionally, modify the options.
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 |
This section describes the specific optimizations for Teradata that are included in the Oracle Data Integrator Knowledge Modules.
This section includes the following topics:
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.
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.
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.
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.
|
 Copyright © 2011, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
