Connection Part | Description |
|---|---|
JDBC URL | Full JDBC URL of the database as 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 JDBC URL |
Password | Password to access the JDBC URL |
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 folder on the file system or a jar file. In case of folder, all the jar files at the location is recursively added to the classpath for resolving the JDBC driver class. Note: 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). |
Property | Source(S)/Target(T) | Description |
|---|---|---|
Batch Size | S/T | Number of source records that the connector caches before processing them in a map. Default value is 2000. |
Fetch Size | S | Indicates the number of rows to fetch in a single call to the server. Default value is 1000. |
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. |
Show System Tables | S/T | If set to True, it allows you to view all the tables created by the DBA. The system table names appear in the table list. The 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 | 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. |
Transaction Isolation Level | S/T | Allows you to specify the isolation levels when reading from or writing to a database table with JDBC. 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 value): 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. |
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). If you are inserting many records, then changing the default to a higher value may improve performance. |
Batch Response | T | File to which CRUD operation results are written. The format is one entry per record, indicating success or failure. If the operation fails, information about cause of failure is returned. Default is to not write a batch response file. |
Update Null Fields | T | If set to True, nulls or empty strings are sent to the server. Default value is False. |
Enable Batch Processing | T | Batch processing groups related SQL statements into a batch and submits them in one call to the database server. This is a performance tuning option. Default value is True. |
Auto Commit | T | Allows you to automatically commit changes as they are made by each SQL statement, instead of waiting until the end of the transaction. If this property is set to True, there is no way to roll back changes once they have been made. Default value is False. |