JDBC Connectors

You are looking at an older version of the documentation. The latest version is found here.

A JDBC connector acts as a bridge across SQL semantic and data type differences between the Data Virtuality Server and a target RDBMS. The Data Virtuality Server has a range of specific connectors that target the most popular open-source and proprietary databases.

On this page, we give general information which is common for all JDBC connectors, and in the subpages, you will find detailed information for each connector.

Connector Configuration

To configure a connection, use the SYSADMIN.createConnection() procedure and specify all required connector properties in the call:

SQL

CALL SYSADMIN.createConnection('somedb', '<type name>', 'db=somedb,user-name=user,password=pwd');
CALL SYSADMIN.createDatasource('somedb', '<type name>', null, null);

Connection, translator, and data source properties for each connector are given in the relevant subpage. Please note that all properties are case-sensitive.

Data Source Properties

Data Source Properties Shared by All JDBC Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner

Name	Description	Default
`importer.autoCorrectColumnNames`	Replaces `.` in a column name with `_` as the period character is not supported by the Data Virtuality Server in column names	`TRUE`
`importer.defaultSchema`	Only for data sources that are also supported as Analytical Storage (SQLServer, MySQL, Oracle, PostgreSQL, Redshift); When the property is correctly set, `SELECT INTO`, `CREATE`, and `DROP TABLE` commands are enabled for that data source; Must point to the original schema name in the DBMS (e.g. `importer.defaultSchema=public`); Needs specifying where tables will be created or dropped in the source DBMS; Not meant to be used with Analytical Storage data source Please note that writing into a data source is only possible if this parameter is set.	Empty
`importer.enableMetadataCache`	Turns on metadata cache for a single data source even when the global option is turned off. Together with `importer.skipMetadataLoadOnStartup=true`, it allows using materialized views after server restart when the original source is unavailable	`FALSE`
`importer.excludeProcedures`	Case-insensitive regular expression that will exclude a matching fully qualified procedure name from import	Empty
`importer.excludeSchemas`	Comma-separated list of schemas (no `%` or `?` wildcards allowed) to exclude listed schemas from import. A schema specified in `defaultSchema` or `schemaPattern` will be imported despite being listed in `excludeSchemas`. Helps to speed up metadata loading	Oracle: SQL `APEX_PUBLIC_USER, DIP, FLOWS_040100, FLOWS_020100, FLOWS_FILES, MDDATA, ORACLE_OCM, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, XS$NULL, BI, HR, OE, PM, IX, SH, SYS, SYSTEM, MDSYS, CTXSYS` All others: empty
`importer.excludeTables`	Case-insensitive regular expression that will exclude a matching fully qualified table name from import. Does not speed up metadata loading Examples CODE `-- Exclude all tables in (source) schemas sys and INFORMATION_SCHEMA: importer.excludeTables=(.[.]sys[.].\|.[.]INFORMATION_SCHEMA[.].) -- You can also negate the defined patterns for the tables to be excluded. -- Exclude all tables except that ones starting with "public.br" and "public.mk": importer.excludeTables=(?!public\.(br\|mk)).*`	Empty
`importer.fetchSize`	Fetch size assigned to a resultset on loading metadata	No default value
`importer.importApproximateIndexes`	If set to `TRUE`, imports approximate index information	`TRUE`
`importer.importIndexes`	If set to `TRUE`, imports index/unique key/cardinality information	`FALSE`
`importer.importKeys`	If set to `TRUE`, imports primary and foreign keys	`FALSE`
`importer.importProcedures`	If set to `TRUE`, imports procedures and procedure columns. Overloaded procedures can be imported with an additional option `useProcedureSpecificName`. Please note that it is currently not possible to import procedures which use the same name for more than one parameter (e.g. same name for `IN` and `OUT` parameters). Such procedures can be excluded from import with the parameter `excludeProcedures`. Please note that it is not always possible to import procedure result set columns due to database limitations	`FALSE`
`importer.loadColumnsTableByTable`	Set to `TRUE` to force table by table metadata processing	`FALSE`/`TRUE` only for Netsuite and SAP Advantage Database Server
`importer.loadMetadataWithJdbc`	If set to `TRUE`, turns off all custom metadata load ways	`FALSE`
`importer.procedureNamePattern`	Procedure(s) to import. If omitted, all procedures will be imported. `%` as a wildcard is allowed: for example, `importer.procedurePatternName=foo%` will import `foo` , `foobar`, etc. W orks only in combination with `importProcedures`	Empty
`importer.quoteNameInSource`	If set to `FALSE`, directs the Data Virtuality Server to create source queries using unquoted identifiers	`TRUE`
`importer.renameDuplicateColumns`	If set to `TRUE`, renames duplicate columns caused by either mixed case collisions or `autoCorrectColumnNames` replacing `.` with `_`. The suffix `_n` where `n` is an integer will be added to make the name unique	`TRUE`
`importer.renameDuplicateTables`	If set to `TRUE`, renames duplicate tables caused by mixed case collisions. The suffix `_n` where `n` is an integer will be added to make the name unique	`TRUE`
`importer.replaceSpecSymbsInColNames`	If set to `TRUE`, replaces all special symbols (any symbols not in the `^A-Za-z0-9_` sequence) to the `_` symbol in column names of tables	`FALSE` / `TRUE` only for BigQuery
`importer.schemaPattern`	Schema(s) to import. If omitted or has "" value, all schemas will be imported. `%` as wildcard is allowed: for example, `importer.schemaPattern=foo%` will import `foo`, `foobar`, etc. To specify several schema names or/and patterns, values should be comma-separated and enclosed within double quotes: `importer.schemaPattern="schema1,schema2,pattern1%,pattern2%"`. For proper escaping of special characters depending on the type of data source, check Escaping special characters in schema names or use wildcards instead: `"[schema_name]"` can be rewritten as `"%schema%name%"`. Helps to speed up metadata loading	Empty
`importer.skipMetadataLoadOnStartup`	If set to `TRUE`, allows skipping metadata loading on server startup if there's cached metadata for the data source. . Together with `importer.enableMetadataCache=true`, it allows using materialized views after server restart when the original source is unavailable	`FALSE`
`importer.tableNamePattern`	Table(s) to import. If omitted, all tables will be imported. `%` as a wildcard is allowed: for example, `importer.tableNamePattern=foo%` will import `foo`, `foobar`, etc	Empty
`importer.tableTypes`	Comma-separated list (without spaces) of table types to import. Available types depend on the DBMS. Usual format: `"TABLE,VIEW"`. Other typical types are `"SYSTEM TABLE"`, `"GLOBAL TEMPORARY"`, `"LOCAL TEMPORARY"`, `"ALIAS"`, `"SYNONYM"`	Empty
`importer.useCatalogName`	If set to `TRUE`, uses any non-null/non-empty catalogue name as part of the name in source, e.g. `"catalogue"."table"."column"`, and in the Data Virtuality Server runtime name if `useFullSchemaName` is `TRUE`. If set to `FALSE`, will not use the catalogue name in either the name in source or the Data Virtuality Server runtime name. Should be set to `FALSE` for sources that do not fully support a catalogue concept, but return a non-null catalogue name in their metadata - such as HSQL	`TRUE` / `FALSE` only for Hive
`importer.useFullSchemaName`	If set to `FALSE`, directs the importer to drop the source catalogue/schema from the Data Virtuality Server object name so that the Data Virtuality Server fully qualified name will be in the form of `<model name>.<table name>`. Please note that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception	`TRUE`
`importer.useProcedureSpecificName`	If set to `TRUE`, allows the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name in the Data Virtuality Server. This option will only work with JDBC 4.0 compatible drivers that report specific names	`FALSE`
`importer.widenUnsignedTypes`	If set to `TRUE`, converts unsigned types to the next widest type. For example, SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte	`TRUE`

The names of the data source properties are case sensitive.

Using a materialized view when the original source is not available at the time the server starts is possible since v2.4.29

Data Source Properties Specific for Some Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner

Name	Description	Default	Connector
`importer.loadAllOracleColumnComments`	Set to `FALSE` to prevent importing column comments during data source creation	`TRUE`	Oracle Database
`importer.loadAllOracleTableComments`	Set to `FALSE` to prevent importing table comments during data source creation	`TRUE`	Oracle Database
`importer.trimColumnNames`	For Hive 0.11.0 and later, metadata for the `DESCRIBE` command could be inappropriately returned with padding. Set to `TRUE` to trim white spaces from column names	`FALSE`	Hive
`importer.useDatabaseMetadata`	For Hive 0.13.0 and later, the normal JDBC DatabaseMetaData facilities are sufficient to perform an import. Set to `TRUE` to use the normal JDBC import logic. Set to `FALSE` to use native Hive methods to import metadata. When `TRUE`, `trimColumnNames` has no effect	`FALSE`	Hive

Escaping Special Characters in Schema Names

Characters that are forbidden for Oracle schema names and can't be escaped: ", /, &, {, }.

All other special characters can be used as they are or should be escaped depending on the type of data source:

"	forbidden	\"	\"	\"
\	\\\\	\\\\\\	\\\\\\	\\\\\\
_	/_	\\\\_	\\\\_	\\\\_
,	\\,	\\,	\\,	\\,
%	/%	\\\\%	\\\\%	\\\\%
[	[	[	[[]	[
'	''	''	''	''''

Usage

With the Data Virtuality Server SQL, the source can be queried as if the tables and procedures were local to the Data Virtuality Server system.