Connection Part | Description |
|---|---|
JDBC URL | Full JDBC URL of the database that is recognized by the driver. The standard format is: jdbc:subprotocol:subname://hostname:port/database_name For example, for MySQL database: jdbc:mysql://localhost/sakila Note: This may vary based on the driver. |
User | User ID to access the database. |
Password | Password to access the database. |
Additional Properties | Any additional connection properties that must be appended to the connection string. For example, rewriteBatchedStatements=true. |
Driver Jar Location | Path of the driver Jar file, from where to load the JDBC driver. This path can be a folder on the file system or a jar file. In case of folder, all the jar files at the location are recursively added to the classpath for resolving the JDBC driver class. If this value is not specified, then the Java classpath of the DataConnect instance that is running is used to resolve the driver class. |
Driver Class | Fully qualified Java class name of a JDBC driver. If this value is not specified, then java.sql.DriverManager is used to resolve the JDBC URL. This will only work if any driver class was initialized when DataConnect was started and the driver registered itself with driver manager. Note: It is recommended to specify the driver class. |
Table/View | Table to be read from (Source Only). |
Query Statement | SQL query file (Source Only). |
Query File | SQL query statement (Source only). |
Table | Table to write into (Target only). |
Property | S/T | Description |
|---|---|---|
Flush Frequency | T | Number of records buffered in the memory before being written to the target connector. Default value is 0 (all records are written at once). When inserting many records, change the default to a higher value to improve performance. |
Batch Response | T | File to which the CRUD operation results are written. The format is one entry per record, indicating success or failure. If the operation fails, information about the cause of failure is returned. Default is to not write a batch response file. |
Show System Tables | S/T | Allows you to view all the tables created by the DBA. The system table names appear in the table list. Default value is FALSE. Note: This property is applicable only if you are logged onto the database as the database administrator. Only the DBA has access to System Tables. |
Show Views | S/T | Allows you to see the view names along with the table names, in the table list. Default value is TRUE. |
Batch Size | S/T | Source side: Number of source records that the connector caches before processing them in a map. Default value is 2000. Target side: Number of queries to execute in one batch. The Enable Batch Processing property must be set to TRUE when using this property. Default value is 100. |
Fetch Size | S | Indicates the number of rows to fetch in one go from the database. Default value is 1000. |
WhereStmt | S | Allows advanced users to construct the Where clause of the SQL query to filter the data based on a particular condition. It can be used as an alternative to writing a lengthy query statement. There is no default value for this property. Note: 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. |
Enable Batch Processing | T | Allows you to group related SQL statements into a batch and submit them in one go to the database server. This is a performance tuning option. Default value is TRUE. |
Transaction Isolation Level | S/T | Allows you to specify the isolation level that your database supports. The available options are: • Read Uncommitted: Permits Dirty Read, Non-repeatable Read, and Phantoms. • Read Committed: Permits Non-repeatable Read and Phantoms. Does not permit Dirty Read. • Repeatable Read: Permits Phantoms. Does not permit Dirty Read and Non-repeatable Read. • Serializable (default): Does not permit Dirty Read, Non-repeatable Read, and Phantoms. |
Cursor Type | S/T | The type of cursor to use for retrieving records from the source table. The available options are: • Forward Only (default): Cursor will only move in the forward direction. • Scrollable - Insensitive to changes: Cursor is scrollable but generally not sensitive to changes in the underlying data. • Scrollable - Sensitive to changes: Cursor is scrollable but sensitive to changes in the underlying data. |
Auto Commit | T | Controls whether the connection automatically commits changes as they are made by each SQL statement, instead of waiting until the end of the transaction. Default value is FALSE. If this property is set to TRUE, there is no way to roll back changes once they have been committed. |
Primary Key | T | Allows you to specify a list of field names that are used to make the primary key. Field names are delimited by commas. If the Primary Key property contains one or more field names, then these names are included in the SQL CREATE statement when the connector is in create table event. No default exists for this property. |
ConstraintDLL | T | Allows you to specify additional SQL data definition language statements to be executed after their target table is created. This is similar to the support provided for SQL pass-through in the SQL import connectors. Each line must be a valid SQL DDL statement. For example, consider the following statements: • CREATE UNIQUE INDEX index1 ON mytable (Field1 ASC) • CREATE INDEX index2 ON mytable (Field2, Field3) These statements would create two indices on the table mytable. The first one does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3. This property is executed only if the create table event is used for the target. If there are any errors, the errors are written to the error and event log file. If you want to ignore the DDL errors, you can continue with the transformation. |
CommitFrequency | T | Allows you to control how often data is committed to the database when the Auto Commit property is set to FALSE. Specifying a nonzero value indicates that data is committed to the database after inserting or updating specified number of records. Default value is 0 (all records are committed at the end, allowing rollback on error). When inserting many records, change the default to a higher value to reduce the number of log entries and to improve performance. |