This topic describes the details of the connection Properties as they apply to the Database type connections.
When coming to the Data Warehouse from an OLTP source system, the connection defines the path to get to those source tables. A connection object must be defined for each source system.
The fields presented in the Connection Properties screen below, depends on the connection and database types selected.  See below for a description of all possible fields.
 
SQL Server: when running a SQL Server data warehouse it is possible to create database links (linked server) to other SQL Server instances and most of the other major databases, such as Oracle, DB2, etc.

  • Database link load - from a table in the current SQL Server database.
  • Database link load - from a table in another database on the same SQL Server instance - the OBDC Data Source Name must be defined in the Windows 32-bit ODBC Data Source Administrator and selected on the ODBC DSN field, as well as the Database ID that also needs to be populated in the Database ID field in the connection properties.
  • Database link load - through a linked server.
  • Integration Services load.

Oracle: using the Oracle database it is possible to create a database link to another Oracle database as standard, or to any other database by using Oracles Heterogeneous Services. Refer to Oracle Examples.

  • Database link load - from a table in the current Oracle database server (includes Oracle gateways and Oracle heterogeneous services).
  • Database link load - through a database link to another Oracle server.

DB2: using the DB2 database it is possible to create a database link load to another table in the same DB2 system or to another system via CLI. Refer to DB2 Examples.

  • Database link load - from another table in the same DB2 system (includes federation).
  • Database link load - via CLI from a DB2 table in another system.

Examples for each of RED's database type connection screens for SQL Server, Oracle, DB2 Greenplum, Netezza and PDW are provided below the connection properties description.
A Database Link Load through a linked server can have a number of source databases:

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

The connection Properties window has the following fields:

General

Options

Description

Connection Name

Name used to label the connection within WhereScape RED.

Connection Type

Indicates the connection source type or the connection method, such as Database, ODBC, Windows, Unix. Select the Database 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

Only required for the Data Warehouse/metadata repository connection. Leave this check box unselected.

Source System

Options

Description

Provider Name

Name of the connection provider/driver used to connect to the database.

Database ID

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

Database Host/Server

PDW Database type connections only: Used for sqlcmd specification.

Database Port

PDW Database type connections only: Used for sqlcmd specification.

Database Link Name


Optional name of a Database Link that is used to access the database.

If SQL Server and the database are on the same server as the data warehouse then no link needs to be defined and this field can be left blank.

If a database link already exists to this source database then enter that link name in this field. If no link exists then enter a link name. Under some circumstances Oracle insists that this link name is the same as the tnsnames entry for the database, so it may be good practice to use the source SID as the database link name. If this is a new link then see the notes later in this section on Creating a Database Link.Enter the defined SQL LINK name that points to the correct server, the link name does not need to be the same name as the server name.

Provider Name

Name of the connection provider/driver used to connect to the database.

Full Database Path Name

This field is used when you enter Microsoft.Jet.OLEDB.4.0 as the Provider Name, e.g. when the source database is from MS Access or Excel. Enter the full database path name.

Database Credentials

Options

Description

Extract User ID

Database User that has access to SELECT from the source system tables to extract data. For more information, refer to Remote View Extract - Oracle Databases only.

Extract User Password

The password of the data warehouse user. For SQL Server blank if a trusted login, or the server login password.

Teradata Wallet User ID/Teradata Wallet String

If connecting to a Teradata server, the Teradata Wallet log on method can be selected from the ODBC User Default drop-down menu and the Teradata Wallet User ID and String can be entered, instead of User/Password.

Administrator User ID

Database User ID to login, when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item (optional).

Administrator User Password

Optional Database User Password to login, when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item.

Other

Options

Description

Default Database for Browsing

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

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 as a source. Select the desired default load type from the list, e.g. Database Link Load, Script based load, Integration Services Load or Externally Loaded.

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.

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, if this option is enabled in the Flat File Load - Source screen's Properties.

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.

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.


  • No labels