This connection type is via an ODBC link. Most data movement is performed using the ODBC connection.
Oracle can perform ODBC loads using Direct Path OCI and standard (single row inserts) via the scheduler. 
ODBC loads can have a number of source databases:

  • SQL Server
  • Oracle
  • DB2 for Linux, UNIX and Windows (the DB2 RED can build a warehouse in)
  • Teradata
  • Greenplum
  • Netezza
  • PDW
  • Hive
  • Sybase
  • MySQL
  • DB2/400 (the DB2 that runs on AS/400 machines - quite different to above)
  • MS Access
  • Excel

The connection object Properties window has the following fields:

General

Options

Description

Connection Name

Name used to label the connection within WhereScape RED. Typically for SQL Server, Oracle and DB2 this is Data Warehouse.For target databases like Greenplum, Netezza or PDW the Data Warehouse connection can be renamed to Repository.

Connection Type

Indicates the connection source type or the connection method, such as Database, ODBC, Windows, Unix. Select the ODBC connection type.

Database Type

Type of database such as DB2, Greenplum, Hive, Netezza, Oracle, SQL Server, Teradata. Default is (local).

ODBC Data Source Name (DSN)

ODBC Data Source Name (DSN) as defined in the Windows 32-bit ODBC Data Source Administrator.

Note

The ODBC Source Name defined in RED must be the same on all machines that use the corresponding connection.

WhereScape RED Metadata Connection Indicator

Distinguishes the special connection that identifies the WhereScape RED data warehouse/metadata repository. This option must be enabled for Data Warehouse/Metadata Repository type connections.

Note

There must only be one data warehouse/metadata connection in a WhereScapeRED repository.

ODBC

Options

Description

Database ID

Source Database Identifier (e.g. Oracle SID or TNS Name, Teradata TDPID) or Database Name (e.g. as in DB2 or SQL Server).

Work Directory

Windows directory used by WhereScape RED to create temporary files for minimal logged extracts. The directory must exist and allow write access. There must be a different work directory for each WhereScape RED Scheduler running on the same machine to avoid file conflicts. Typically C:\Temp or a sub-directory of C:\Temp is used.

Credentials

Note

If the source database does not support windows authentication, a username and password must be specified in the User ID and User Password fields below.


Options

Description

Extract User ID

Database User that has access to SELECT from the source system tables to extract data.

Extract User Password

Database Password to use with the Extract User ID to login to the source system to extract data.

Administrator User ID

Leave blank.

Administrator User Password

Leave blank.

Other

Options

Description

Default Schema for Browsing

Optional comma-delimited list of schemas for the browser pane filter. Enter the schema(s) you want the connection to browse by default on the right browser pane.

New Table Default Load Type

The default Load Type for new Load tables created using this connection. Select the desired default load type from Native ODBC, Integration Services load or ODBC load.

Note

The available options in this drop-down list is configured from Tools > Options > Available Load Types.

New Table Default Load Script Connection

The default Script Connection to use when a Script based load type is defined for a Load table object that is sourced from this connection.

New Table Default Load Script Template

The default Script Template to use when a Script based load type is defined for a Load table object that is sourced from this connection.

SSIS Connection String (OLEDB)

Connection string to be used by Microsoft SQL Server Integration Services (SSIS) to connect to the data source or destination. For details on how to use the wizard to build an SSIS Connection String, refer to Loading Data into RED Load Tables using SSIS.

Note

A connection string is typically composed of multiple property name/value pairs that are semi-colon delimited.

SSIS Connection String (SQLPDW)

PDW Database type connections only: Connection string to be used by Microsoft SQL Integration Services (SSIS) to connect to the data source or destination using the SQL Server Parallel Data Warehouse Connection Manager. The SSIS Connection String (SQLPDW) is used when the target is PDW and it must be set in order to process PDW loads.

Note

A connection string is typically composed of multiple property name/value pairs that are semi-colon delimited.

Staging database

PDW Database type connections only: The staging database to be used by Microsoft SQL Server Integration Services (SSIS) for loading to PDW.

Data Type Mapping Set

Mapping Set to use when converting from a source database data type to a destination database data type. Setting this field to (Default) makes RED automatically select the relevant mapping set, otherwise you can choose one of the standard mapping sets from the drop-down list or create a new set.

When Connection is an OLAP Data Source

This section of fields is only relevant and will only be visible from a Data warehouse connection (where the Data warehouse field is enabled). These fields are required so that the data warehouse can be used as a source for the Analysis Services cubes.

MSAS Connection String

Connection string to be used by Microsoft Analysis Services (MSAS) to connect to the data warehouse. For details on how to use the wizard to build the OLAP connection string, refer to OLAP Defining Data Source for the OLAP Cube.

Note

A connection string is typically composed of multiple property name/value pairs that are semi-colon delimited.

Connection Provider/Driver

Name of the Connection Provider/Driver to use to connect to the data warehouse database, when it is used as the data source for OLAP cubes.

set to SQLOLEDB.

set to MSDAORA.

set to IBMDADB2.

Data Warehouse Server

Data Warehouse Server Name, which is used when the data warehouse is used as the data source for OLAP cubes.

Data Warehouse Database ID

Data Warehouse Database Identifier (e.g. Oracle SID or TNS Name, Teradata TDPID) or Database Name (e.g. as in DB2 or SQL Server), which is used when the data warehouse is used as the data source for OLAP cubes.

Tip

Once the connection has been set up, you can right-click the connection in the middle pane or double click the connection name from the left pane to view or edit the connection's Properties.

Below are two examples of a Native ODBC Load.
If the source database supports windows authentication, it is not necessary to specify a username and password:
 
If the source database does not support windows authentication, a username and password must be specified:
 
 

  • No labels