Content

Step 1. - Access your software and documentation

Implementing your Optim solution for use with Teradata requires the following:
--IBM Optim Solution v7.3 Installation pack
--IBM Infosphere Federation Server v9.5 or 9.7
--Teradata client v2.5 or v2.6

You can download your Optim solution from Passport Advantage by following instructions in the download document. For documentation on your Optim solution, see the DOC directory on the Windows installation media or the Information Data Management (IDM) Information Center available from the IDM Support Site.

If you download DB2 for Linux, UNIX, and Windows from Passport Advantage, follow the directions in the download document.

For complete DB2 documentation, including installation instructions, see the DB2 Multiplatform Tools Information Center or the DB2 Tools Library.

Complete documentation for Federation Server is in the Federation Server Library.

Step 2. - Review the base architecture

The following graphic provides an overview of the environment for using your Optim solution with Teradata.

Step 3. - Review the installation prerequisites

To implement your Optim solution for use with Teradata,

--Download the Optim solution or insert the Optim installation disk and follow the prompts in the installation launchpad. Install and configure the Optim server and, if you need access to archived data, install the Open Data Manager (ODM) component. For detailed information about the installation requirements and steps, see the documentation for your Optim solution.

--Install the Teradata client (include TeraGSS, FastLoad, MultiLoad, CLIV2, PIOM, and BTEQ components) on the Optim server.

--Install Federation Server on the Optim server.

Step 4. - Configure the local DNS (host file)

After installing the Teradata ODBC driver on the Teradata client, create a system DSN and include the IP address or hostname of the Teradata server, a default userid and password, a default starting database, and data source name. Use default values for the remaining settings.

Step 5. - Define the federated connection

To define the connection, use the DB2 Control Center to:

1. Create a wrapper for the Teradata database.

CREATE WRAPPER TERA LIBRARY 'db2teradata.dll'; OPTIONS (ADD DB2_FENCED 'Y');

2. Create a Server Definition. Using the Discover feature, select the Teradata node, which must be defined previously in etc/hosts (see IBM InfoSphere Federation Server Configuration Guide for Federated Data Sources for more details).

CREATE SERVER TERA TYPE TERADATA VERSION '2.6' WRAPPER TERA OPTIONS (ADD NODE 'TERA', PUSHDOWN 'Y', DB2_MAXIMAL_PUSHDOWN 'Y');

3. Alter User Mapping.

CREATE USER MAPPING FOR DB2ADMIN SERVER TERA OPTIONS (ADD REMOTE_AUTHID 'TEST', ADD REMOTE_PASSWORD 'TEST');

4. Create a schema for the Nicknames. As a best practice, create a local schema to match the remote schema on the Teradata system. When nicknames are created they have the Teradata owner.tablename values, allowing SQL references from Teradata to remain unchanged.

CREATE SCHEMA PROD AUTHORIZATION DB2ADMIN;

5. Create Nicknames. Your Optim solution uses nicknames to reference tables within Teradata.

CREATE NICKNAME PROD.DEPARTMENT FOR TERA.PROD.DEPARTMENT;

6. Add foreign keys as needed, because nicknames do not automatically expose relationships on the Teradata tables. The Optim solution will use these keys defined to process a business object (data model).

ALTER NICKNAME PROD.DEPARTMENT ADD CONSTRAINT FEMPNO FOREIGN KEY (SALESMAN_EMPNO) REFERENCES PROD.EMPLOYEE (EMPNO) NOT ENFORCED ENABLE QUERY OPTIMIZATION;

Step 6. - Configure Optim to use the Teradata Loaders

1. In Optim Personal Options, on the Load Tab, click the box for Teradata and specify file paths for FastLoad and MultiLoad.

2. In any Load Request for Teradata, select a Teradata-specific file type.

3. Complete the Load Request and execute.

Refer to Optim documentation for more details.

Step 7. - Define Optim Object Definitions

Using Optim:
--Define the Federation Server to the Optim solution as a DB2 Linux, UNIX, Windows DB Alias.
--Define Access Definitions that reference nicknames.
--Define additional Optim relationships as needed.

Step 8. - Open Data Manager (Data Growth only)

With the use of Open Data Manager (ODM), archived data and data from the Teradata system can be combined in a Federation Server query or view. In addition, you can use ODM to access archived data directly. To access data from an online table and an archived table with a single query, you must use a view. The VIEW does not contain filters or other qualifiers, but is a generic UNION of the two tables. For example:
RTC 15345