Property | ST | Description |
|---|---|---|
CodePage | ST | Translation table that determines which encoding to use for reading and writing data. The default is ANSI, the standard in the US. |
CommitFrequency | T | Allows you to control how often data is committed to the database when the AutoCommit property is set to False. The default value is zero that is, the data is committed at the end of the transformation, allowing rollback on error. This is the slowest setting. When performing large transformations, this is not practical as it may produce too many transaction log entries. Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records. |
ConstraintDDL | T | Allows you to specify some additional SQL data definition language statements to be executed after the target table is created. This is similar to the support provided for SQL pass-thru in the SQL import connectors. Each line must be a valid ODBC DDL statement. For example, the following statements create two indices on the table mytable. The first index does not allow duplicates and the index values are stored in ascending order. The second is a compound index on Field2 and Field3. CREATE UNIQUE INDEX index1 ON mytable (Field1 ASC) CREATE INDEX index2 ON mytable (Field2, Field3) ConstraintDDL is executed only if the replace mode is used for the target. If there are errors, they are written to the error and event log file. During transformation, the Transformation Error dialog box appears. You can ignore the DDL errors and continue the transformation. ConstraintDDL also supports an escape mechanism that allows you to specify DDL in the native SQL of the database management system. Any statement that is preceded by an ampersand (@) is sent straight to the DBMS. The following statement is a DDL statement for creating a primary key for the table mytable. @CREATE INDEX pk_mytable ON mytable (Field1, Field2) WITH PRIMARY Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. To create a primary key, use native SQL. Note: This option only works in REPLACE mode. |
CursorType | S | The type of cursor to use for retrieving records from the source table. The choices are Forward Only, Static, and Dynamic. The default setting is Forward Only. For more information, see Cursors. |
DataTypes | S | Data types of the fields in the target table |
MaxDataLength ST | ST | The maximum data length for long data types. The default is one megabyte, or 1,048,576 bytes. You can reset this number as needed, based on the available memory and target requirements. Some ODBC drivers can only handle limited data lengths. If you choose an application (ODBC) source or target connector and the default setting is not 1 MB, then the integration platform has preset the default for that particular ODBC driver. Do not set the MaxDataLength property any higher when this happens. |
PrimaryKey | ST | Allows you to specify a list of field names that are used to make the primary key. The field names are delimited by commas. If the PrimaryKey property contains one or more field names, these names are included in the SQL CREATE statement when the connector is in replace mode. When using the PrimaryKey property, the ODBC driver must support integrity enhancement facility (IEF). Only advanced ODBC drivers support this. |
Synonyms | ST | If set to true, allows you to see synonyms. The alias names appear in the table list along with the tables. Default is false. |
SystemTables | ST | If set to true, this property allows you to see all tables created by the DBA in the database. The system table names appear in the table list. Default is false. Note: This property is applicable only if the user is logged onto the database as the database administrator. Only the DBA has access to system tables. |
TransactionIsolation | ST | Allows you to specify any one of five different isolation levels when reading from or writing to a database table with ODBC. The default is Serializable. The ANSI SQL 2 standard defines three ways in which serializability of a transaction may be violated: P1 (Dirty Read), P2 (Nonrepeatable Read), and P3 (Phantoms). The four isolation levels are as follows: • READ_UNCOMMITTED - Permits P1, P2, and P3. • READ_COMMITTED - Permits P2 and P3. Does not permit P1. • REPEATABLE_READ - Permits P3. Does not permit P1 and P2. • SERIALIZABLE - Does not permit P1, P2, or P3. For details about transaction isolation levels, see the Microsoft ODBC SDK documentation. |
WhereStmt | S | Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement. You may use this to instruct the SQL database server to filter the data based on a particular condition before sending it to the integration platform. There is no default value for this property. Note: This property is not applicable when the source connection is a query statement or file. This property enables data filtering when you select a table. |
UseCursors | T | Allows you to turn cursor support on or off. The default is False. When UseCursor is true and the specified ODBC driver does not support cursor inserts, the SQL INSERT mode of adding records is used instead. For exports, cursor support is supposed to enhance the performance of record insertion. This appears to be the case for desktop databases but not for database servers, which execute prepared queries about as quickly as they handle cursor inserts. Some drivers require that the target table be indexed to enable positioned updates (cursors). The PrimaryKey and ConstraintDDL properties address this issue for the ODBC export (see details above). |
Views | ST | If set to True, this property allows you to see the view names in the table list along with the table names. Default is True. Note: This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode. |