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, depend 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 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.
Examples for each of RED's database-type connection screens for SQL Server, DB2 Greenplum, Netezza, and PDW are provided below the connection properties description.
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, or Unix. Select the Database connection type. |
Database Type | Type of database, the default is (local). |
ODBC Data Source Name (DSN) | ODBC Data Source Name (DSN) as defined in the Windows 32-bit ODBC Data Source Administrator. 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 |
|---|---|
Database Host/Server |
|
Database Port |
|
Database ID | Database identifier or database name. |
Database Link Name | Optional name of a Database Link that is used to access the database.
|
Advanced Connect Parameters
Options | Description |
|---|---|
Session User ID | Session User ID used for Advanced Connect. |
Session Password | Session Password used for Advanced Connect. |
Connection String | Used when establishing a connection, accepts automatic replacements of RED Tokens. |
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 is 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. The available options in this drop-down list are configured from Home > 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 using the OLE DB Connection Manager. |
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. |
Default Transform Function Set | Function Set that is selected by default in the Transformation dialogs. |
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.