Agent Parameters - Connections

Note:

For all the connections, the downloaded Driver can be added to the default location:

server.driversLocation=$ORACLE_HOME/agent/drivers

Or to a custom location that you have to specify in agent.properties. Example:

server.driversLocation=/custom/driver/directory

To use a JDBC driver that is not bundled with Veridata product, refer to the Veridata 26c Certification Matrix. Open the Veridata Drivers tab, download the certified version of JDBC driver and add to this location.

This information is also available in the agent.properties.sample.

# The server.driversLocation property is the directory
# containing the JDBC driver jar file(s). The JDBC drivers
# included with the Veridata agent will be retrieved from the
# standard locations.
# The path is relative to the Veridata agent
# deployment directory. OR An absolute path to the directory containing
# drivers can be specified.
server.driversLocation=/scratch/nextgen/vdtInstall/agent/drivers

• IBM Db2 for i

• IBM Db2 for LUW

• IBM Db2 for z/OS

• Apache Hive

• MariaDB

• Microsoft SQL Server

• MySQL

• Oracle Database

• PostgreSQL

• Snowflake

• Sybase Adaptive Server Enterprise

• Teradata Vantage

• SingleStore

• MongoDB

• Databricks

IBM Db2 for i

Db2 i sample database connection URL

LocationName is usually the i server name.

database.url=jdbc:veridata:db2://DB2HOST:446;LocationName=<DATABASE_NAME>
server.jdbcDriver=vddb2-5.1.4.jar

Settings for using the JTOpen driver for Db2 i

database.url=jdbc:as400:DB2HOST
server.jdbcDriver=jt400Native.jar

IBM Db2 for LUW

Db2 LUW sample database connection URL

database.url=jdbc:veridata:db2://localhost:50000;DatabaseName=sample
server.jdbcDriver=vddb2-5.1.4.jar

IBM Db2 for z/OS

DB2 z/OS sample database connection URL

database.url=jdbc:veridata:db2://localhost:447;LocationName=DB2
server.jdbcDriver=vddb2-5.1.4.jar

Apache Hive

Hive sample database connection URL for Apache Hive2 JDBC Driver

database.url=jdbc:hive2://localhost:10000
server.jdbcDriver=hive-jdbc-3.1.2-standalone.jar

MariaDB

MariaDB sample JDBC URL with SSL

database.url=jdbc:mariadb://localhost:3306/
database?sslMode=verify-full&serverSslCert=/path/to/cert.pem
server.jdbcDriver=mariadb-java-client-3.5.2.jar

MariaDB sample JDBC URL without SSL

database.url=jdbc:mariadb://localhost:3306/database
server.jdbcDriver=mariadb-java-client-3.5.2.jar

You can download the Driver from https://mariadb.com/kb/en/installing-mariadb-connectorj/.

Microsoft SQL Server

SQL Server sample database connection URL for SSL/TLS

database.url=jdbc:sqlserver://localhost:1443;databaseName=<dbName>;encrypt=false;trustServerCertificate=false;
server.jdbcDriver=mssql-jdbc-11.2.0.jre17.jar

SQL Server database connection URL

database.url=jdbc:veridata:sqlserver://localhost:1433
server.jdbcDriver=vdsqlserver-6.0.0.jar

MySQL

MySQL sample database connection URL

database.url=jdbc:mysql://localhost:3306/<db_name>?serverTimezone=UTC&
zeroDateTimeBehavior=CONVERT_TO_NULL&sessionVariables=sql_mode='PAD_CHAR_TO_FULL_LENGTH'
server.jdbcDriver=mysql-connector-j-8.3.0.jar

MySQL sample JDBC URL for MySQL Server Authentication via server certificate

(For MySQL version 8.0.12 and earlier):

database.url=jdbc:mysql://localhost:3306?useSSL=true&verifyServerCertificate=true
server.jdbcDriver=mysql-connector-j-8.3.0.jar

For MySQL version 8.0.13 and later:

database.url=jdbc:mysql://localhost:3306?sslMode=<VERIFY_CA or VERIFY_IDENTITY>
server.jdbcDriver=mysql-connector-j-8.3.0.jar

Oracle Database

Oracle sample database connection URL

To connect to Non-CDB databases:

database.url=jdbc:oracle:thin:@localhost:1521:orcl

To connect to CDB databases:

database.url=jdbc:oracle:thin:@localhost:1521/CDB_service_name

To connect to only one PDB:

database.url=jdbc:oracle:thin:@localhost:1521/PDB_service_name

Oracle OCI bequeath database connection URL

The OCI libraries must be available and the JDBC driver must match the OCI libraries.

database.url=jdbc:oracle:oci:@FREE
server.jdbcDriver=ojdbc11-23.9.0.25.07.jar

Oracle sample database connection URL for SSL/TLS

database.url=jdbc:oracle:thin:@tcps://<host>:<port>/<service name>?wallet_location=<wallet directory path>
database.url=jdbc:oracle:thin:@tcps://localhost:2484/service_name?wallet_location=/path/WALLET
server.jdbcDriver=ojdbc11-ojdbc11-23.9.0.25.07.jar

PostgreSQL

Postgresql sample database connection URL for native driver

database.url=jdbc:postgresql://localhost:5432/target
server.jdbcDriver=postgresql-42.7.4.jar

Postgresql sample database connection URL for SSL/TLS

database.url=jdbc:postgresql://<host>:5432/postgres?sslmode=verify-ca&sslrootcert=<crt_path>/<crt_filename>
server.jdbcDriver=postgresql-42.7.4.jar

Snowflake

Prerequisite to start the Snowflake agent

Veridata Agent uses the Snowflake Arrow format by default to retrieve query results, offering significantly better performance than the JSON format.

Add the following lines to $VERIDATA_DEPLOYMENT_HOME/VAOH.sh:

JAVA_OPTS="$JAVA_OPTS --add-opens=java.base/java.nio=ALL-UNNAMED"
JAVA_OPTS="$JAVA_OPTS -Xmx6g -XX:MaxDirectMemorySize=2g"
export JAVA_OPTS

Veridata 23c requires JDK 17.

The --add-opens JVM parameter is officially documented as a solution to ensure compatibility between the Arrow format and JDK 17+.

Example JVM parameter for an 8 GB table:

MaxDirectMemorySize ~ CLIENT_RESULT_CHUNK_SIZE(Snowflake JDBC parameter, default: 160 mb) x CLIENT_PREFETCH_THREAD (Snowflake JDBC parameter, default value 4) x 2.5 (decompression overhead)

Xmx ~ MaxDirectMemorySize + half of the table size

Adjust MaxDirectMemorySize and Xmx based on your specific use case.

To switch to JSON format:

1. Comment out or remove the Arrow-related lines from VAOH.sh

2. Append the following parameter to the Snowflake database.url in the agent.properties file:

   &JDBC_QUERY_RESULT_FORMAT=JSON

3. Restart the Veridata agent

Snowflake database connection URL

database.url=jdbc:snowflake://hostname?db=db_name&warehouse=sf_basic_dt_wh&role=accountadmin
server.jdbcDriver=snowflake-jdbc-3.27.0.jar

Sybase Adaptive Server Enterprise

Sybase database connection URL

JDBCBehavior=0 is required to repair Sybase UNITEXT columns.

database.url=jdbc:veridata:sybase://localhost:5000;ApplicationName=VeriAgent;MaxPooledStatements=20;
JDBCBehavior=0
server.jdbcDriver=vdsybase-5.1.4.jar

Teradata Vantage

Teradata database connection URL

database.url=jdbc:teradata://localhost/DBS_PORT=1025,CHARSET=UTF8
server.jdbcDriver=terajdbc4.jar

SingleStore

SingleStore database connection URL

database.url=jdbc:singlestore://host:port/dbname?user=<dbuser>&password=<dbpassword>
server.jdbcDriver=singlestore-jdbc-client-1.1.5.jar

MongoDB

MongoDB database connection URL

database.url=mongodb://<username>:<password>@localhost:27017
server.jdbcDriver=mongodb-driver-sync-5.4.0.jar mongodb-driver-core-5.4.0.jar bson-5.4.0.jar

Databricks

Prerequisite to start the Databricks Agent

The Databricks JDBC driver uses the Apache Arrow format to enable high-performance data transfer, especially for large query results through a feature called Cloud Fetch.

Default Behavior

• Arrow-based fetch is enabled by default in the JDBC driver.

• This is the recommended configuration for optimal performance. Enabling Arrow Support in Agent

To enable Arrow fetch, add the following lines to the VAOH.sh file located in the agent deployment directory:

JAVA_OPTS="--add-opens=java.base/java.nio=ALL-UNNAMED"
export JAVA_OPTS

Disabling Arrow Fetch (Optional)

To disable Arrow Fetch:

1. Remove the following lines from VAOH.sh:

   JAVA_OPTS="--add-opens=java.base/java.nio=ALL-UNNAMED"
   export JAVA_OPTS

2. Add the EnableArrow=0 property to the database.url in agent.properties Example (Arrow Disabled)

database.url=jdbc:databricks://localhost:443/default;transportMode=https;ssl=1;httpPath=sql/protocolv1/o/3321;AuthMech=3;EnableArrow=0;

Note: Any changes made to VAOH.sh require an agent restart.

Databricks database connection URL

database.url=jdbc:databricks://<serverhostname>:<port>/<schema>;[property1]=[value];[property2]=[value];

Example

database.url=jdbc:databricks://localhost:443/default;transportMode=https;ssl=1;httpPath=sql/protocolv1/o/3321;AuthMech=3;

server.jdbcDriver=databricks-jdbc-3.0.7.jar

For additional configuration details, refer to the official documentation:

https://docs.databricks.com/aws/en/integrations/jdbc-oss/configure#authenticate

Proxy Configuration (If Connection Fails)

If you are unable to connect to a Databricks instance, add the following proxy parameters to the agent.properties file:

• database.proxyHost

• database.proxyPort

• database.useProxy