The fields on the Source tab for Native ODBC loads are described below. Oracle and DB2 Source screen examples can also be found at the bottom of this topic.
SQL Server Source Screen:

Fields

Description

Load Type

Method of loading data into the table. The available options are dependent on the Source Connection. Defaults to the 'Default Load Type' of the Source Connection. Can be specified via the Properties screen.

Source Connection

The connection that identifies the source database. Can be specified via the Properties screen.

General

Fields

Description

Select Distinct Values

Include the DISTINCT keyword in the SQL SELECT statement.

Source Schema

Schema within the source database where the source table resides.

Derive Source Tables(s) and Source Columns

Derive the Source Table(s) and Source Column(s) properties (of this screen) from the source details of this table's columns.

Note

The existing property values will be overwritten.


Source Table(s)

Name of the table or tables that the data is sourced from.

Override Source Column/Transformations

Ignore the source and transformation details of this table's columns and instead use the override details specified below.

Where and Group By Clauses

Optional SQL SELECT WHERE-clause and/or GROUP BY-clause. Parameter names can be specified using $Pparameter_name$ (with leading $P and trailing $), which are replaced at run-time by the parameter's value.

Tip

This is where you can build a statement to handle change data.

Parameter values can be in-line replaced and included in the 'Where' clause. Prefix the parameter name with a '$P' and add a trailing '$'. For example, if we have a parameter called SALES_LOCATION_CODE we could construct a statement, such as WHERE location_code = '$PSALES_LOCATION_CODE$' AND region_code = 'NY'. When this statement is executed, the value of the parameter will replace the parameter name. For example if the parameter was set to 'New York' then the statement would execute as: WHERE location_code = 'New York' AND region_code = 'NY'.
Clicking the SQL button opens the Where and Group By Clauses editor which provides control settings to assist you in constructing the SQL statement.

Override Load SQL

Optional SQL statement to load data into the table, which overrides all other properties. The specified SQL will be executed instead of generated SQL. For a linked database specify a complete INSERT statement. For an ODBC source specify only the SELECT statement.

Source View Name

Oracle only: Name of the database view to be created by WhereScape RED (if necessary) in the source system that will be used to select the source data. Refer to Remote View Extract for details.

Native ODBC Load

Fields

Description

Available Load Types

File Loader utility/mechanism to use to load the generated extract file.

Field Delimiter

Character that separates the fields within each record of the generated extract file. The default value is a | character (pipe). This should be changed if pipes are possible in the source data.

Unicode Extract File

Data will be loaded via a Unicode format file. The default is unchecked and this field is not available FOR DB2.

SQL*Loader Options

Oracle Only - use this field to optionally specify an Oracle SQL*Loader OPTIONS clause and/or WHEN clause. Multiple options can be specified via the OPTIONS clause by separating each option with a comma. The property can be used to specify that SQL*Loader uses the efficient Direct Path method to load data into Oracle and/or a WHEN clause to selectively load rows based on a condition.
Examples: OPTIONS(DIRECT=TRUE) specifies that SQL*Loader uses the Direct Path load methodWHEN (3) = 'Y' specifies that only rows where column 3 contains the value Y are loaded

Note

Use of the "not" symbol  !  in the WHEN statement is only supported for Linux/UNIX file loads. This symbol is not supported for Windows based loads.

BULK INSERT Maximum Errors

SQL Server Only - Maximum number of errors accepted before a load failure is triggered. The default for this option is 0.

BULK INSERT Additional Options

SQL Server Only - Optional comma-delimited list of options to control the behavior of the SQL Server BULK INSERT command. For example the following options will respectively lock the table; set the batch  size to 100,000 rows; and fail the load when the first error occurs: TABLOCK, BATCHSIZE=100000.

Populate Load Parameters

Populate any load-related WhereScape RED parameters, which may be used for validation purposes.

Oracle and DB2 Source screen examples:

Oracle Source Screen:
 
DB2 Source Screen:

  • No labels