When you add a component with database connection properties to the Design section, the Database Configuration dialog box is opened. The dialog box gives you a central place to enter the most common parameters to set up a connection. More parameters are available in the Property section once the Database Configuration dialog is completed. This section explains all properties of a data provider component.
Click the Interface arrow to select one of the following types of database interfaces:
Sybase
Sybase Open Client must be installed on the same machine
as Sybase IQ ETL Development desktop and the ETL Server must be defined
in the sql.ini file. If the component is to
be sent to an ETL Server, then the ETL Server must also have access
to Open Client libraries.
SQLite Persistent
Sybase IQ ETL ships with a built-in, general purpose, relational database
that is based on SQLite (www.sqlite.org).
IBM DB/2
ODBC
The ODBC driver must be installed on the same machine
as Sybase IQ ETL Development desktop and a system data source name (DSN)
must be defined for the target. If the component is to be sent to an ETL Server,
then the ETL Server must also have access to the proper ODBC drivers
and DSN.
Oracle
Click the Host Name list and select one of the available host names.
If you are interfacing to a SQLite Persistent interface, you can enter a database file name.
Enter a valid database user and password combination for your connection if required.
The value provided for the password is encrypted and saved permanently to avoid repetitive entry.
Click Query to open the Query dialog box. (Refer to Chapter 5, “Advanced Concepts and Tools” for a detailed description of the Query Designer/Content Explorer.)
In the Database field, enter a database name, if required.
In the Schema field, enter a database schema name, if required.
Activate the standardize data format option.
When you activate the Standardize Data Format in a Data Provider component, incoming DATE and NUMBER information is automatically converted into a standardized format (Date: CY-M-D H:N:S.s; Example: 2005-12-01 16:40:59.123; Numbers with a '.' as decimal separator). This allows to automatically move and convert this format sensitive information between systems using different DATE and NUMBER information.
When activated in a Data Sink component, the component expects to receive all data in attributes of the datatypes DATE or NUMBER in the standardized data formats. The data sink component then automatically converts from the standardized data format to the native format of the connected database system.
Click Edit to open the Database Options dialog box and select the database options.
DB Option |
Default |
Description |
|---|---|---|
Show error location |
1 |
1 = yes 0 = no Database errors will include the position of the record within the result set, when Show error location = 1. |
Always use logon credentials |
0 |
Always use logon credentials. |
Extended connection option |
Extended connection option. |
|
Connect timeout |
0 |
Stops trying to connect after Connect timeout seconds. If set to 0, the connect will not time out |
Disconnect timeout |
10 |
Enforces disconnect from the database, if there was no reply from database n seconds after trying to disconnect. |
Treat numeric values as character |
0 |
1 = Treats numeric values as characters. |
Always Unicode |
0 |
Unicode will be forced (DB2). |
Isolation Level |
DEFAULT |
Database specific. |
Lock result set data |
0 |
Query tables will be locked. This is used to ensure that no data is written to the selected record set while the process in working on it. The selected record set is released when the last record from that set was fetched. This can be useful when dealing with data integrity concerns. |
LOB truncate size |
1024 |
LOB will be truncated when exceeding LOB truncate size. |
Numeric Support |
1 |
Support numeric values. |
Execution timeout |
0 |
Component will stop execution after a time interval in seconds (0 = no timeout.) |
Unicode support |
0 |
Support Unicode operation. |
Write rejected records to file |
File path for Reject Log. This and the following options are used to log records that have been rejected by the database on loading
|
|
Truncate reject log |
1 |
Log will be truncated on database connect. A value of 0 will append data to an existing log file. |
Write error code to reject log |
1 |
The database error code for each record will be written to the log. |
Write error text to reject log |
1 |
The database error text for each record will be written to the log. |
Write header to reject log |
0 |
If set to 1, a column header will be written to the log. |
Reject log column delimiter |
Tab |
The columns will be delimited by this character or string. |
Set the Read Block Size.
The Read Block Size option defines the number of records retrieved by the component within in a single step.
Use a fairly small number during simulations to accelerate the simulation step. Increase the number before you execute the project. Executing a project with a small number might have a negative effect on the overall performance of the system.
Set the Write Block Size.
The Write Block Size option defines the number of records to be written to the database in a single write operation. A component with a large Write Block Size will be waiting to receive as many records as defined before the data records are actually written to the database. To enforce writing after a simulation step, the number for Write Block Size should be equal or smaller than the Read Block Size of the previous component with a Read Block Size property.
Set up pre-processing SQL.
One or more SQL statements can be executed during the initialization of a component. Initialization is the first step each components passes when starting a simulation or when executing a project. When the project is initialized, all components of the project are initialized subsequently.
When using multiple statements, you can separate the
statements by using a semicolon.
Set up post-processing SQL.
One or more SQL statements can be executed when all components finished processing the project.
When using multiple statements, you can separate the
statements by using a semicolon.
To enter parameters