JDBC Connectors

for use with Oracle 9i or later

Sequences may be used with the Oracle connector. A sequence may be modelled as a table with a name in the source of DUAL and columns with the name in the source set to <sequence name>.[nextval|currentval]. You can use a sequence as the default value for insert columns by setting the column to auto increment and the name in the source to <element name>:SEQUENCE=<sequence name>.<sequence value> . A rownum column can also be added to any Oracle physical table to support the rownum pseudo-column. A rownum column should have a name in the source of rownum . These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage.

Oracle-specific translator properties:

OracleSuppliedDriver indicates that the Oracle-supplied driver (typically prefixed by ojdbc) is being used. Defaults to true. Set to false when using DataDirect or other Oracle JDBC drivers.

for use with SQL Server 2000 or later. A SQL Server JDBC driver version 2.0 or later (or compatible e.g. JTDS 1.2 or later) should be used.

JtdsDriver indicates that the open source JTDS driver is being used. Defaults to false.

for use with Sybase version 12.5 or later and Sybase IQ

JtdsDriver indicates that the open-source JTDS driver is being used. Defaults to false.

Teradata specific translator properties:

supportsRandFunction enables pushing down to source for RAND() function. Defaults to false.

Sets a template to convert WHERE clauses on strings.

comparisonStringConversion=%s
-- no conversion will be applied

comparisonStringConversion=binary %s
-- WHERE binary someStringExpression LIKE someOtherStringExpression

comparisonStringConversion=(%s COLLATE Latin1_General_CS_AS)
-- WHERE (someStringExpression COLLATE Latin1_General_CS_AS LIKE someOtherStringExpression)

MS SQL Server:
(%s COLLATE Latin1_General_CS_AS)

MySQL, SingleStore (former MemSQL):

binary %s

others:
%s

DatabaseTimeZone

The time zone of the database. Used when fetchings date, time, or timestamp values.

The system default time zone

DatabaseVersion

The specific database version. Used to further tune pushdown support.

Automatically detected by the server through a data source JDBC driver, if possible

If true, all identifiers in the final query (that is being generated by a translator to be sent directly to the target DBMS) will be quoted.

Sets a template to convert ORDER BY clauses on strings.

OrderByStringConversion=%s
-- no conversion will be applied

OrderByStringConversion=(binary %s)
-- ORDER BY (binary someStringExpression)

OrderByStringConversion=cast((%s) as varchar(2000) ccsid 1208)
-- ORDER BY cast((someStringExpression) as varchar(2000) ccsid 1208)

DB2 for AS/400:
cast((%s) as varchar(2000) ccsid 1208)

DB2 for zOS:
cast((%s) as varchar(2000) ccsid ASCII)

MS SQL Server:
(cast(cast(%s as varchar) as varbinary)

MySQL, SingleStore (former MemSQL):
(binary %s)

PostgreSQL:
(%s COLLATE \"C\")

others:
%s

• true for MySQL, PostgreSQL, Redshift, Greenplum, Oracle, MS SQL Server
• false for the others

supportsNativeQueries

Forces a translator to issue a native() system procedure which can be used to pass native queries directly to an underlying DBMS.

select x.* 
from table
  ( 
    call "dwh.native"("request" => 'select query, pid, elapsed, substring from svl_qlog order by starttime desc limit 200') 
  ) w
  , ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x;

More usage examples

• true for MySQL, PostgreSQL, Redshift, Snowflake, Oracle, MS SQL Server; Exasol, Vertica, Bigquery, SingleStore (former MemSQL), Azure
• false for the others

If false, Data Virtuality Server doesn't generate aliases in ORDER BY clause.

True to indicate that the source supports aliases in ORDER BY clause.

• true for PostgreSQL
• others: if OrderByStringConversion is not set, the same as supportsOrderBy. if OrderByStringConversion is set, default for supportsOrderByAlias is false

If false, Data Virtuality Server doesn't push down ORDER BY clause if it contains at least one expression of string type.

True to indicate that the source supports strings in ORDER BY clause.

• false for MS SQL Server
• for others: same as supportsOrderBy

True to trim trailing whitespace from fixed-length character strings. Note that Data Virtuality Server only has a string or varchar type that treats trailing whitespace as meaningful.

false

UseBindVariables

True to indicate that PreparedStatements should be used and that literal values in the source query should be replaced with bind variables. If false only LOB values will trigger the use of PreparedStatements.

true

UseCommentsInSourceQuery

This will embed a / comment / leading comment with session/request id in the source SQL query for informational purposes

false

MaxPreparedInsertBatchSize

The max size of a prepared insert batch.

2048

UseStreamingResults

By default MySQL and PostgreSQL JDBC drivers try to load all results into memory. With big tables, there might be performance problems and sometimes it might result in OutOfMemory.

This option is used to have better memory usage by the JDBC driver and, in the case of MySQL, to quicker transfer of large data sets. It is recommended to set this option to true if OutOfMemory problems are experienced when reading large datasets.

However, such connections should be used only for reading data, since enabling streaming breaks the normal transactional behaviour.

true

replaceNullCharsWith

PostgreSQL,

Redshift,

Greenplum,

BigQuery

Values: bulkInsertSingleThread, bulkInsertMultiThread

bulkInsertSingleThread: Bulk inserts in a single thread.
bulkInsertMultiThread: Bulk inserts in multiple threads.

The legacy value insertMultiThread still works for backward compatibility.

Value: s3Load

Uploads data as files on Amazon S3 storage. Translator properties "region", "keyId", and "secretKey" properties should be specified for the mode to work properly

The value is case-insensitive so s3Load and S3LOAD are the same values.

Value: blob_azure

Uploads data as files on Azure Blob storage. Translator properties "storageAccountName", "storageAccountKey" and "sasToken" should be specified for the mode to work properly

Values: blob, standard

SingleStore (former MemSQL),

MySQL 

Values:   bulkInsertSingleThread , bulkInsertMultiThread , standard

bulkInsertSingleThread - sends batches in a single thread
bulkInsertMultiThread - sends batches in multiple threads
standard - single data processing/no batches (slow)

Values:  bulkInsertSingleThread, bulkInsertMultiThread, standard

bulkInsertSingleThread - sends batches in a single thread
bulkInsertMultiThread - sends batches in multiple threads
standard - single data processing/no batches

Values: CLOUDSTORAGE (default), STREAMING, INSERTS

• CLOUDSTORAGE mode uses a Google Cloud Storage bucket to upload data. The bucket can be configured via bucketName or bucketPrefix translator parameters. If no bucketName is specified the bucket will be created
• STREAMING mode streams data through the Big Query API
• INSERTS mode uses JDBC prepared statements for inserting data

Value: a number

Sets the size limit of a temp file in bytes.

SingleStore (former MemSQL),

MySQL,

MS SQL

Value:   a number

Sets the approximate size in bytes for batches created.  

SingleStore (former MemSQL): 1048576

MySQL: 1039597

Value: a number

Specifies the maximum number of uploader threads.

SingleStore (former MemSQL),

MySQL,

MS SQL

Value: a number.

The number of threads for the bulkInsertMultiThread mode.

MS SQL

Value: boolean.

When set to true update is commited only when all data is loaded

Value: a path to a folder (relative or absolute).

Specifies the folder to be used for creating temporary files instead of the system configured one.

Value: boolean.

For blob uploadMode only

Value: a bucket name.

Only for "s3Load"-uploadMode.
Specifies a bucket name to upload data files to. If it's specified, the specified bucket is assumed to exist (no creation/deletion operations performed). The region specified for the bucket must be the same as the region specified via "region"-translator property. If no "bucketName" is specified, a bucket will be created and deleted after the operation is finished.

Value: a bucket name.

Only for default (CLOUDSTORAGE) uploadMode.
Specifies a bucket name to upload data files to. If it's specified, the specified bucket is assumed to exist (no creation/deletion operations performed). The region specified for the bucket must be the same as the region specified via "region"-translator property. If no "bucketName" is specified, a bucket will be created and deleted after the operation is finished.

Value: a bucket prefix.

Only for "s3Load"-uploadMode.
Specifies a prefix of a bucket name to upload data files to. The name of the bucket created for uploading will start with the specified prefix. The bucket will be deleted after the operation is finished.

Value: a bucket prefix.

Only for default (CLOUDSTORAGE) uploadMode .
Specifies a prefix of a bucket name to upload data files to. The name of the bucket created for uploading will start with the specified prefix. The bucket will be deleted after the operation is finished.

Value: boolean.

Specifies if the bucket set in bucketName parameter should be created if it does not exist

Value: a region.

Only for "s3Load"-uploadMode.
Specifies a region endpoint.

iamRole, awsAccountId

Enables compression of temporary files before uploading them to S3. May be disabled for environments with high network bandwidth in order to save some CPU power but this will increase the disk usage.

Redshift, Azure,
PostgreSQL 

Redshift: false

Azure, PostgreSQL: true

As Redshift and Vertica measure varchar size in bytes, not chars, and stores strings in UTF-8 encoding, a char may be 1 to 4 bytes long. You can specify the percentage by which the byte size will exceed the original char size.
For instance, if in an original DBMS there's a varchar(32) column (32 chars), the actual data of 32 chars long may occupy 32 bytes for English alphabet or 128 bytes for Chinese hieroglyphs. If a user knows that the data contain English chars only, he may not specify it at all and the resulting column in the target will be varchar(32) (bytes this time). If a user uses the Chinese language for the column, he should specify the value of 300 for the property and it means that it's necessary to add 300 percent to the original field length (32 + 300 % = 128).

Also, there's a special value: 65535 which makes every varchar to be of 65535 bytes long.

Value:  any ASCII character except NULL

Only for "s3Load"-uploadMode.

This property enables the loading of data into VARCHAR columns even if the data contains invalid UTF-8 characters. If it's specified, ACCEPTINVCHARS option will be passed to the S3 COPY command and each invalid UTF-8 character will be replaced with a string of equal length consisting of the character specified. For example, if the replacement character is '^', an invalid three-bytruncateStringste character will be replaced with '^^^'.

If acceptInvChars is not specified, an error will be thrown whenever an invalid UTF-8 character is encountered.

Keep temporary files after uploading.

The maximum length of a column name. The d efault value relies on underlying DBMS.

Five chars of defined maximum length will be reserved for internal purposes and cannot be used for column-identifier.

MySQL: 64,
PostgreSQL: 63,
SQL Server: 128,
Oracle: 30,
Redshift: 127

The property sets a length threshold for strings to be converted to clobs when the 'createStringsAsClobs' is set to true. String columns with length less than the threshold would not be converted to clobs.

Example:  
webhdfsEndpoint="webhdfs://myhdfsnode:50070"

Important: the hostname should be the node name of the Hadoop node which runs WebHDFS service. It is not possible to use the IP address or some alternative DNS name. If the node name does not resolve on Data Virtuality server, please add node name with the correct IP address to the host file on the Data Virtuality server.

possible values: TEXTFILE, PARQUET

Example:
tableType=PARQUET

Azure default endpoints protocol

Snowflake: for blob_azure uploadMode only

Azure Storage Account name

Azure: for blob uploadMode only

Snowflake: for blob_azure uploadMode only

Azure Storage Account key

Snowflake: for blob_azure uploadMode only

Azure: for blob uploadMode only

Value: boolean.

When set to true creates and drops are performed using JDBC statements instead of API

When set to true UNIQUEIDENTIFIER SQL Server data type is always cast to CHAR(36) SQL Server data type. Otherwise, no implicit casting is applied to the UNIQUEIDENTIFIER SQL Server data type.

If set to false, please, use explicit casting of UNIQUEIDENTIFIER SQL Server data type to VARCHAR(36) when using it in window functions

• 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 allowed for that data source
• must point to the original schema name in the DBMS (e.g. importer.defaultSchema=public)
• specify where tables will be created or dropped in the source DBMS
• property is not meant to be used with Analytical Storage data source

Oracle:

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

A case-insensitive regular expression that when matched against a fully qualified table name will exclude it from import.

-- 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

The fetch size assigned to a resultset on loading metadata.

no default value

importer.importApproximateIndexes

True to import approximate index information.

true

importer.importProcedures

True to import procedures and procedure columns. Overloaded procedures can be imported with an additional option useProcedureSpecificName.

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-parameter). Such procedures can be excluded from import with the parameter excludeProcedures.
Note, that it is not always possible to import procedure result set columns due to database limitations. 

false

importer.quoteNameInSource

False to direct Data Virtuality Server to create source queries using unquoted identifiers.

true

Comma-separated list - without spaces - of table types to import. Depends on DBMS, which types are available. Usually use "TABLE,VIEW".

Other typical types are "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

False will direct 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>

Note: that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception.