DB Bulk Load Sybase IQ properties list

Interface

Specify the method or driver you want to use to connect to the data source.

Host Name

Specify the host where the Sybase IQ target is running.

Destination

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

Load Stage

Specify a Data File path or pipe name. The load stage file must reside on the same machine as the IQ Server.

If you use named pipes on UNIX or Linux, the ETL Server and the IQ Server must reside on the same machine. This is not a requirement for Windows.

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 (default) – to load records directly into the selected target table using the specified file path or pipe name.

• 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.

• Delete – to delete records from the target tables based on the keys in the incoming data. You can use the Key property to specify the target attributes to identify the records you want to delete.

  If you have selected the Delete function and the Truncate option, only Preprocessing and Postprocessing SQL are executed.

Truncate

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

Use IQ Client Side Load

Select the option to add records from files located on remote host machines into the Sybase IQ table, 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.

Load Stage (Server)

Specify the server path to the data file or, leave it empty when using a pipe.

If the Sybase IQ server needs to use a different path to the temporary data file than specified in the Load Stage property, you must enter it here.

Write Block Size

Specify the number of records to be written to the file or pipe in a single write operation.

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 source. Additionally, you must select an appropriate interface, and in some cases, specify an appropriate user ID and password.

Schema

Identifies the schema/owner you want to use as data source. The objects displayed will be restricted accordingly and new tables will be created in that schema.

Standardize Data Format

Select the option to convert incoming DATE and NUMBER information into a standard format that Sybase ETL can move between systems that support different formats.

Dates are converted into a format that includes the year, month, day, hour, minute, seconds, and fraction of a second: YYYY-MM-DD hh:mm:ss.s.

For example,

2005-12-01 16:40:59.123

Numbers are converted with a '.' as the decimal separator.

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”.

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.