IQ Loader File via Load Table properties list

Interface

Specify the method or driver you want to use to connect to the target IQ database. The supported interfaces are Sybase and ODBC.

Host Name

Specify the host where the Sybase IQ target is running.

Destination

Click to select the destination table from a set of existing tables.

Text Source

Identifies the text file you want to use as the data source. From the Properties window, click the Text Source icon, select the file, and specify the format. See“Working with the Text Source property window”.

Row Delimiter

Specify how each row is delimited:

• LF (Line feed)

• CR (Carriage return)

• CRLF (Carriage return followed by a line feed)

Alternatively, you can enter a different delimiter.

Column Delimiter

Specify how columns are delimited:

• Tab

• Comma

• Semicolon

Alternatively, you can enter a different delimiter.

User and Password

Specify an authorized database user and a password to protect the database against unauthorized access.

Key

Select target key attributes to identify the records for the selected function.If no key is selected, the interface works with the primary key information, which is delivered from the DB host. An error is displayed if no primary key information is available.

Function

Select one of these load functions:

• Insert – to load records directly from the file into the selected target table.

• Upsert – to update the existing records and insert the new records. When you select Upsert, the existing records are replaced and not updated on an attribute level. You can use the Key property to specify the target attributes to identify the records you want to update.

  The Upsert function is ignored:

  • If you are using Generate Load Script. The LOAD TABLE script is generated as used for the Insert function.

  • If you have selected the Truncate option. All records are removed from the target table before data is loaded. The component ignores the Upsert function and instead performs the Insert function, which loads all attributes to the target table.

Use IQ Client Side Load

Select the option to to bulk load data from files located on remote host machines into a target IQ database, using the LOAD TABLE statement.

Load Script

The Load Table Statement is generated on runtime based on the component settings, if this property is empty.

To use a customized script, right-click the component, and select Generate Load Script. After you generate the script, you can click Load Script and edit the script.

Truncate

Select the option to remove all records from the destination table before the load.

Pre-processing SQL

Click the Pre-processing SQL icon to create a query that runs during component initialization.

Queries can include one or more SQL statements. If you use multiple statements, separate them with a semicolon (;).

Post-processing SQL

Click the Post-processing SQL icon to create a query that runs after all components execute.

Queries can include one or more SQL statements. If you use multiple statements, separate them with a semicolon (;).

Database

Specify the database you want to use as data target. The database is used together with the specified user name, password, and host name.

Schema

Specify an owner to filter the table catalog.

Database Options

Click the Database Options icon to set options that override performance defaults and control the behavior of some transactions.

See “Database connection settings”.

Null Indicator

Specify the string that represents null values in the source file.

Skip Rows

Specify the number of rows to skip at the beginning of the input file for a load. The default is 0.

Parallel format

Select this option when all columns including the last, are delimited by a single ASCII character, to allow the LOAD TABLE command to run in parallel.

Strip

Select the option if you want trailing blanks to be stripped from values before they are inserted. This applies only to variable-length non-binary data.

Byte Order

Specify the byte ordering during reads. This option applies to all binary input fields. If none are defined, this option is ignored. Sybase ETL always reads binary data in the format native to the machine it is running on (default is NATIVE). You can also specify:

• HIGH when multibyte quantities have the high order byte first.

• LOW when multibyte quantities have the low order byte first.

Block Size

Specify the default size in bytes in which input should be read.

Limit

Specify the maximum number of rows you want to insert into the table. The default is 0 for no limit.

ON File Error

Specify the action Sybase IQ should take when an input file cannot be opened, either because it does not exist or because you have incorrect permissions to read the file. For all other reasons or errors, it aborts the entire insertion. You can specify one of the following options:

• ROLLBACK aborts the entire transaction (the default).

• FINISH finishes the insertions already completed and ends the load operation.

• CONTINUE returns an error but only skips the file to continue the load operation. You cannot use this option with partial-width inserts.

Word Skip

Allows the load to continue when it encounters data longer than the limit specified when the word index was created.

IQ Lock Table in Exclusive Mode

Select the option to lock the target table and prevent it from being updated by concurrent transactions. When an exclusive table lock is applied, no other transaction can execute queries or perform any updates against the locked table. The IQ Lock Table in Exclusive Mode option also queues multiple projects that load the same table in Sybase IQ.

Wait Time for IQ Lock Table

Specify the maximum blocking time that the project should wait, before acquiring an Exclusive lock.

Specify the time argument in the format hh:nn:ss.sss. If you do not enter a time argument, the server waits indefinitely until an Exclusive lock is available or an interruption occurs. When you specify “00:00:00.000” as the time argument, an Exclusive lock is acquired as soon as the project starts.

Use IQ Multiplex

Select the option to support multiplex execution by using multiple writers to load data into the IQ database.