JDBC - Connect_ETL - 9.13

Connection Overview

Through the DMX_JDBC_INI_FILE environment variable, Connect gains access to the JDBC configuration file, which you define. As per the JDBC driver properties outlined in the JDBC configuration file, Connect determines the JDBC driver class name and the Java class path to the class and dependent classes; establishes the connection with the DBMS; and connects to the source or target database, which is specified in the database connection URL.

JDBC Configuration File

Outlined within the JDBC configuration file are the JDBC driver class name and Java class path for locating the driver class and dependent classes for each DBMS. A separate section in the JDBC configuration file is required for each DBMS.

Format Requirements

The JDBC configuration file is organized in sections. Consider the following format requirements:

• A section header marks the beginning of each section and is specified by a string enclosed in square brackets ([]). The enclosed string specifies the name or alias of the DBMS. To establish a connection to the database in the DBMS, the DBMS name, which is enclosed within brackets([]) in the section header of the JDBC configuration file, must match the DBMS name specified in the second parameter of the database connection URL.
• Within each section, name-value pairs describe the properties of the JDBC driver. Unless otherwise stated, parameter names are case-insensitive and parameter values are case-sensitive. Each line can contain a maximum of one parameter description where the parameter value is separated from the parameter name by an equal sign (“=”). Extra spaces before and after the equal sign are ignored.

Consider the following parameters for accessing a DBMS through JDBC:

• DriverName - Mandatory - This mandatory parameter identifies the JDBC driver class name or Java class.
• DriverClassPath - Mandatory - This mandatory parameter identifies the Java class path that points to the JDBC driver. Use a semi-colon (;) to separate different entries in the path.
• SelectStatement and InsertStatement – Optional - If the query language used by the DBMS does not follow the SQL92 standard, these optional parameters enable you to provide custom queries for select and insert operations. When you provide these parameters, Connect uses the statement templates to create the appropriate select and insert statements. If either the SelectStatement or InsertStatement parameter is not defined, standard SQL is used for the corresponding read/write operation. If the right side of the equal sign is blank for the SelectStatement or InsertStatement parameter, the corresponding read/write operation is not supported. You can use the following place holders in the statement templates:
  • <columns> – location where the actual comma-separated columns should be placed.
  • <table> – location where the actual table name should be placed.
• IsSchemaSupported – Optional - This optional parameter ensures that Connect correctly identifies all specified database tables. Through JDBC calls, Connect can generally determine whether a DBMS supports a schema; however, certain DBMSs, such as Hive, do not return the values that are expected from certain JDBC calls. Under these circumstances, you can set the IsSchemaSupported parameter to ensure that all specified database tables are identified correctly. The values for this parameter can be either true or false; as an exception to the general rule, IsSchemaSupported parameter values are case insensitive.

For information on connecting to Hive through ODBC, see Connecting to Hive data warehouses.

• The character '#" marks the start of a comment, which continues until the end of the line. Comments are permitted anywhere within a JDBC configuration file.
• Empty lines are permitted anywhere within a JDBC configuration file.