Was this helpful?
JDBC Multimode
JDBC Multimode is a multimode connector that provides target connections to database repositories using existing JDBC drivers. JDBC driver is a software component that is used for connecting to relational databases, NoSQL databases, and other repositories.
You can quickly setup a multimode connector using the multimode wizard. For more information, see Setting Up Multimode Target Connector Using Multimode Wizard.
Note:  This connector appeared in DataConnect 11.6 and earlier versions as the JDBC connector. When you load an existing Map that was created when JDBC referred to a multimode connector, the target will automatically select JDBC Multimode connector.
Prerequisites
The JDBC driver must reside in the local filesystem.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target. The fields that are available depend on the connector you select. Multimode connectors are target only connectors. For a list of all parts for target connectors, see Specifying Target Connector, Parts, and Properties.
Specify the following connector parts on the Target tab to connect with the JDBC driver:
Connector 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.
Schema Selector
Shows a list of target tables from where you can select a table to define a schema.
Connector Properties
You can specify the following target properties:
Property
Description
Flush Frequency
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
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.
Update Null Fields
Allows you to send null value or empty strings to the server.
Default value is FALSE.
Show System Tables
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
Allows you to see the view names along with the table names, in the table list.
Default value is TRUE.
Batch Size
The number of queries to execute in one batch. This property is applicable only if the Enable Batch Processing property is TRUE.
Default value is 100.
Enable Batch Processing
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
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
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
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
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
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
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.
Supported Operations
JDBC Multimode connector supports only Insert operations.
Supported Data Types
The following data types are supported:
BIGINT
BIGINT UNSIGNED
BINARY
BIT
BLOB
BOOL
CHAR
DATE
DATETIME
DECIMAL
DOUBLE
DOUBLE PRECISION
FLOAT
INT
INT UNSIGNED
INTEGER
INTEGER UNSIGNED
IPV4
IPV6
LONG VARBINARY
LONG VARCHAR
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMINT UNSIGNED
MEDIUMTEXT
NUMERIC
REAL
SMALLINT
SMALLINT UNSIGNED
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYINT UNSIGNED
TINYTEXT
UUID
VARBINARY
VARCHAR
YEAR
If you are appending data to an existing table, then the data type of each field uses the data type in the selected table by default.
Note:  For a JDBC source being written into DB2, column sizes for text-type fields (char, varchar, etc.) should be specified in bytes, not characters. This is important when sending Unicode that contains characters outside the regular ASCII range. The strings are encoded using UTF-8, and the column width needs to be specified as the (max) number of bytes that will occur in the UTF-8 string.
Additional Information
If the connection is not established with the connector and the mapping is done, then the default data type for any non-text data type is "Text" and the default size is "1". After mapping, connecting to the target does not change the size. You must delete the fields and map them again for accurate mapping or modify individual field properties.
Therefore, it is recommended to perform all the mapping after the connection is established for both source and target.
Last modified date: 07/26/2024