Specifying Data Target Options

Apache Hive and Cloudera ImpalaApache Hive, Cloudera Impala, and Salesforce.com targets only have the default Extended Bulk Load Utility option, as shown in the following image.

Note: For the best performance when loading Apache Hive or Cloudera Impala, it is recommended that the DataMigrator Server be located on a data node or Edge Node (with the Hadoop client installed) of your Hadoop cluster.

However, it is possible to use DataMigrator to load data into Hadoop, even when the servers are not co-located. In this case, the DataMigrator Server will create a temporary file and use FTP to copy it to the Hadoop node. This requires that an FTP server is available on the Hadoop cluster, and in addition for use with Impala REXEC daemon.

Greenplum targets have the following additional options, as shown in the following image.

Column Delimiter

Delimiter characters between fields in the intermediate flat file that is loaded into the database. The delimiter can be up to four characters and can be specified as the following:

• TAB. A tab character. This value is the default.
• a - A. A character string. For example, ~.
• 0x nn. A hex code. For example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

Escape Character

Specifies the single character that is used for escape sequences, such as ,, or @;. Also used for escaping data characters that might otherwise be interpreted as row or column delimiters.

Column Enclosure

Characters used to enclose each alphanumeric value in the intermediate flat file that is loaded into the database. The enclosure consists of up to four printable characters. The most common enclosure is one double quotation mark. Numeric digits, symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-), and 0x09 (Horizontal Tab), 0x0A (Line Feed), or 0x0D (Carriage return) cannot be used in the enclosure sequence. In order to specify a single quotation march as the enclosure character, you must enter four consecutive single quotation marks.

Note: When a non-default value is used, the intermediate file is created as the Greenplum term CSV, instead of what is called TEXT.

Global Settings

These options are only available when BULKLOAD=ON and apply to all new flows using Greenplum as the target.

Note: ALIAS_CASE controls the case of column names when new tables are created. Greenplum users may want to select Enforce lower case.

Informix targets have the following additional options, as shown in the following image.

Column Delimiter

Delimiter characters between fields in the intermediate flat file that is loaded into the database. The delimiter can be up to four characters and can be specified as the following:

• TAB. A tab character. This value is the default.
• a - A. A character string. For example, ~.
• 0x nn. A hex code. For example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

Jethro targets have the following additional options, as shown in the following image.

Column Delimiter

Delimiter characters between fields in the intermediate flat file that is loaded into the database. The delimiter can be up to four characters and can be specified as the following:

• TAB. A tab character. This value is the default.
• a - A. A character string. For example, ~.
• 0x nn. A hex code. For example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

MS SQL Server ODBC targets have the following additional options, as shown in the following image.

Packet size to send

The packet size in bytes.

PostgreSQL targets have the following additional options, as shown in the following image.

Column Delimiter

Delimiter characters between fields in the intermediate flat file that is loaded into the database. The delimiter can be up to four characters and can be specified as the following:

• TAB. A tab character. This value is the default.
• a - A. A character string. For example, ~.
• 0x nn. A hex code. For example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

Escape Character

Specifies the single character that is used for escape sequences, such as ,, or @;. Also used for escaping data characters that might otherwise be interpreted as row or column delimiters.

Column Enclosure

Characters used to enclose each alphanumeric value in the intermediate flat file that is loaded into the database. The enclosure consists of up to four printable characters. The most common enclosure is one double quotation mark. Numeric digits; symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-); and 0x09 (Horizontal Tab), 0x0A (Line Feed), or 0x0D (Carriage return) cannot be used in the enclosure sequence. In order to specify a single quotation march as the enclosure character, you must enter four consecutive single quotation marks.

Note: When a non-default value is used, the intermediate file is created as the PostgreSQL term CSV, instead of what is called TEXT.

Global Settings

These options are only available when BULKLOAD=ON and apply to all new flows using PostgreSQL as the target.

Note: ALIAS_CASE controls the case of column names when new tables are created. PostgreSQL users may want to select Enforce lower case.

Sybase ASE and Sybase IQ targets have the following additional options, as shown in the following image.

Column Delimiter

Is the delimiter character(s) used between fields. It is used by the intermediate flat file used to load the database.

The delimiter can be up to four characters and can be specified as:

TAB. A tab character. This is the default value.

a - A. A character string. For example, ~.

0x nn. A hex code, for example, 0x44 (a comma) or 0x0D0A (a return and a linefeed).

Row Delimiter

Is the delimiter character used between records. It is used by the intermediate flat file to the load the database. The row delimiter can be specified in the same manner as the (field) delimiter, except that character comma (,) is not permitted.

Activate Server Log

This parameter is available for Sybase IQ only.

Activate log information about integrity constraint violations and the types of violations. With this option two types of LOG files could be written into a temporary space in the configuration directory: MESSAGE LOG file etlblk.msg and ROW LOG file etlblk.log.

For Sybase IQ:

• Yes. Writes both MESSAGE LOG and ROW LOG files: etlblk.msg and etlblk.log.
• No. The LOG files are not written. This is the default value.

allow_read_client_file = on
allow_write_client_file = on

This can be confirmed by using the following Sybase stored procedure:

sp_iqcheckoptions

If these permissions are not set, the flow will fail and display the following error:

FOC1261 PHYSICAL INTERFACE NOT FOUND FOR SUFFIX SQLSYB. MODULE   
NAME : dbodbc12_r 

1010 targets have the following additional options, as shown in the following image.

Column Delimiter

Delimiter characters between fields in the intermediate flat file that is loaded into the database. The delimiter can be up to four characters and can be specified as the following:

• TAB. A tab character. This value is the default.
• a - A. A character string. For example, ~.
• 0x nn. A hex code. For example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

The Teradata Extended Bulk Load Utility uses TPT (Teradata Parallel Transporter) to load data.

Teradata targets have the following additional options, as shown in the following image.

Rejected records maximum number

The maximum number of rejected records that can be stored in one of the error tables before the job is terminated. If unspecified, the default is unlimited.

TDP Id

The Teradata Director Program Id.

Account Id

The Account Id used to access the database.

Data error table

Name of table to contain records that were rejected because of data conversion errors, constraint violations, or AMP configuration changes. This must be a new table name. The default is the database default.

Index violation table

Name of table to contain records that violated the unique primary index constraint. This must be a new table name. The default is the database default.

The Target Options section of the Properties pane for relational targets contains the following additional options:

Connection

Is the connection for the data target. For a relational data target, this is a database server. For ODBC, this is a data source.

Table

Is the name of data target table.

Keys

Is the names of key columns in order. This option is only available for new targets.

Prior to Load Options:

These options are only available for existing targets.

No changes

Does not delete the records already in a data target.

Delete all rows from table

Deletes all rows and creates a database log.

Truncate table

Deletes all rows from the table, but does not generate a database log. This is a faster option than using Delete all rows from table.

The Target Load Options include the following load options:

Load Type:

Note: Although Load Type appears in every data target, it is set on a per flow basis. Changing the type in one target will reset it for all targets.

Insert/Update

Specifies the behavior of DataMigrator while loading data.

Insert/Update options can be set on a per target basis.

If you select this option, you can set a behavior when duplicate records are found from the If the record exists drop-down menu.

• Include the record. Allows the relational database target to handle duplicates.

  Note: If you select Include the record, the record is passed directly to the relational database, which determines whether to accept it or not. If inserting a record would result in a duplicate key, the RDBMS will reject it due to a Unique Index constraint violation and return an error. Processing continues even if such errors occur, up to the number of errors specified under Stop processing after __ DBMS errors in the General properties of the flow.

• Reject the record. Issues a SELECT command against the target table to see if a record exists with the key values. If there are no key columns, DataMigrator screens all columns. If the record is found, the record is rejected.
• Update the existing record. Updates records if the key value on the input record, or entire record if there are no keys, are found in the table. All non-key values are updated in the target.
• Delete the existing record. Deletes the record if the key on the input record (or entire record if no keys) is found in the table.

  If you select Update the existing record or Delete the existing record from the If the record exists drop-down menu, you can also set behavior when the record does not exist using the If the record does not exist drop-down menu:

• Include the record. Includes the key in the data target.
• Reject the record. Does not include the key in the data target.

  Note: The Reject, Update, and Delete options can adversely affect performance because they determine the existence of a key value on an incoming record before performing the specified action. This is done by issuing a SELECT command against the target table, then waiting for the response. If one of these actions is required, try to limit the volume of records in the incremental change. These actions will perform best if there are unique keys on the table.

• Commit every row(s). Specifies the row interval to commit or write transactions to the database.

Insert records from memory

When you specify this load type, you need to specify:

• Commit every row(s). Specifies the row interval to commit or write transactions to the database.
• Block size. Specifies how many records you want to process at a time.

Note: The value for Commit every rows should be a multiple of the Block size, for example, 10000 and 2000.

Bulk load utility via a disk file

These options will depend on your target type.

Extended bulk load utility

Uses a database loader program with additional capabilities. For more information, see Extended Bulk Load Utility Options.

Slowly Changing Dimensions

This option is only available for existing relational targets.

Enables you to load a data target with column values that change over time. There are two column types. Type I simply overwrites the value. Type II allows you to track activity before and after the change. Type II changes are handled either using an activation flag or an end date/begin date. When you update a Type II column, you do not actually change its value. Instead, you update the activation flag to inactive, or the end date to the current date. You then add a new record with the new value, and the activation flag on by default or the begin date set.

• Updates for Type I. Enables you to specify how updates will affect Type I fields. The options are Change all rows or Change only active rows. Change all rows is the default value.
• Commit every row(s). Specifies the row interval to commit or write transactions to the database.

IUD Processing

Enables you to load a data target with only the records that have changed. This feature is an optional, add-on component to DataMigrator. When this option is selected, the Prior to Load option is eliminated.

IUD/Slowly Changing Dimensions

This load type is a combination of the previous two load types. The Insert/Update/Delete indications in the CDC logs are not applied directly to the target. Instead, the SCD Types of the target are used and deleted records in the source are marked as inactive in the target.

The Target Options section of the Properties pane for FOCUS/FDS or XFOCUS targets contains the following additional options:

Data File

Is the name of the data file pointed to by the synonym.

On IBM z/OS, to create a file in HFS (hierarchical file system), enter the name. To create a dataset, enter the name as

//’qualif.tablename.FOCUS’

where:

qualif

Is a fully qualified location.

tablename

Should match the synonym name.

Keys

Is the number of key columns. This option is only available for new targets.

Prior to Load Options:

These options are only available for existing targets.

No changes

Does not drop (delete) the data target.

Drop Table

Drops and recreates the data target.

Load Type

Specifies the method DataMigrator uses to load data.

Insert/Update

Since DataMigrator uses Insert/Update to load FOCUS/FDS or XFOCUS targets, you can set a behavior when duplicate records are found from the If the record exists drop-down menu. These options can be set on a per target basis.

• Include the record. Includes the duplicate record in the data target.
• Reject the record. Issues a SELECT command against the target table to see if a record exists with the key values. If there are no key columns, DataMigrator screens all columns. If the record is found, the record is rejected.
• Update the existing record. Updates records if the key value on the input record, or entire record if there are no keys, are found in the table. All non-key values are updated in the target.
• Delete the existing record. Deletes the record if the key on the input record (or entire record if no keys) is found in the table.

  If you select Update the existing record or Delete the existing record from the If the record exists drop-down menu, you can also set behavior when the record does not exist using the If the record does not exist drop-down menu:

  • Include the record. Includes the key in the data target.
  • Reject the record. Does not include the key in the data target.
  • Commit every row(s). Specifies the row interval to commit or write transactions to the database.

DataMigrator can create a new XML document with name/value pairs. For more information on any other structures, see Creating a Data Flow Using a Target Based on a Predefined XML Schema.

Note: In order to create an XML document, a Server Administrator must first configure an adapter for XML.

The Target Options section of the Properties pane for XML targets contains the following additional options:

Data File

Is the name of the XML document described by the synonym. DataMigrator will also create an XML Schema Definition with the same name as the data file and an extension of .xsd.

Load Type

Is set to Insert/Update for XML documents. Note that no updates are currently performed.

Records

Is the name of the top level element.

Record

Is the name of the row level element.

Column

Is the name of the column level element.

The Target Options section of the Properties pane for delimited flat file targets contains the following additional options:

Note: Delimited Flat File will only appear as a target type if it is configured on the server.

Connection

Select the connection, FTP or SFTP server, where the data should be written.

Data File

Is the name of the data file pointed to by the synonym.

On IBM z/OS, to create a file in HFS (hierarchical file system), enter the name. To create a dataset, enter the name as

//’qualif.tablename.DATA’

where:

qualif

Is a fully qualified location.

tablename

Should match the synonym name.

Code Page

Specifies a code page for the target data.

Field Delimiter

Is the delimiter character(s) used between fields. The delimiter can be up to 30 characters. It can be specified as:

TAB. A tab character. This is the default.

a. A character string, for example ~.

0xnn. A hex code, for example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

Header

Inserts column headings as the first row of data and surrounds the column names with the character specified in the Enclosure field. When set to Yes, the FIELDNAME is used for the header.

You can change this to use the TITLE as the header instead. To do so, expand the Adapters folder, right-click Delimited Flat File (CSV/TAB), and click Change Settings. From the HEADER drop-down menu, select Build header based on field title and click Save.

Enclosure

This character is used to surround the column headings when Header is set to Yes.

The Target Options section of the Properties pane for flat file targets contains the following additional options:

Connection

The connection configured for the selected adapter.

Data File

Is the name of the data file pointed to by the synonym.

On IBM z/OS, to create a file in HFS (hierarchical file system), enter the name. To create a dataset, enter the name as

//’qualif.tablename.DATA’

where:

qualif

Is a fully qualified location.

tablename

Should match the synonym name.

Code Page

Specifies a code page for the target data.

Prior to Load Options:

This option is only available for existing targets.

No changes

Does not delete the data target.

Delete File

Deletes and recreates the data target.

The Target Options section of the Properties pane for formatted file targets contains the following additional options:

Connection

The connection configured for the adapter. The default value is <local>.

Format

Is the format of the target data.

Data File

Is the name of the data file pointed to by the synonym.