Label*

Default Value: N/A
Example: Generic Database Account

String

N/A

Specify a unique label for the account.

Account properties*

The information required to create a connection to the database.

JDBC JARs

Use this field set to define JDBC Drivers. This field set consists of the JDBC Driver field.

JDBC Driver*

Default Value: N/A
Example: vertica-jdk5-6.1.2-0.jar

String/Expression

N/A

Specify the JDBC driver to use. Click the Upload icon to upload the required JDBC driver that must be used for the account.

JDBC Driver Class*

Default Value: [None]
Example: com.vertica.jdbc.Driver

String/Expression

N/A

Specify the JDBC Driver class name to use.

JDBC URL

Default Value: [None]
Example: jdbc:vertica://Snaplogic.com/database

String/Expression

N/A

Specify the JDBC URL to use.

Username

Default Value: N/A
Example: Snapuser

String/Expression

N/A

Specify the database username to use.

Password

Default Value: N/A
Example: nb*#!@09

String/Expression

N/A

Specify the database password to use.

Database name

Default Value: Auto detect
Example: Oracle

String/Expression

N/A

Choose a database to which the account must be connected. The available options are:

• Auto detect: If you select Auto detect, and if the Snap detects the target database as NetSuite, then the Limit rows field in the Generic JDBC - SelectJDBC—Select Snap is ignored.

• PostgreSQL

• Redshift

• NetSuite: If you select NetSuite, then the Limit rows field in the Generic JDBC - Select JDBC—Select Snap is ignored irrespective of whether you provide a value or notregardless of an existing or non-existing value.

• MySQL

• Oracle

• SQL Server 2012

• SQL Server 2008

• SAPHana

• Apache Hive

• DB2

• SQLMX

• Apache Derby

• Spark SQL

If you use PostgreSQL JDBC driver to connect to Redshift database, the Auto detect option automatically detects the PostgreSQL database instead of Redshift. The behavior of the JDBC Snaps is optimized for the selected database.

Test Query

Default Value: N/A
Example:

• SQL Query:

  • SELECT * FROM customers WHERE age > 30

• MongoDB Query:

  • db.customers.find({ age: { $gt: 30 } })

• Redshift Query:

  • SELECT customer_id, order_date, total_amount
    FROM sales
    WHERE total_amount > 1000
    ORDER BY order_date DESC
    LIMIT 10;

String/Expression

Activates on selecting Auto detect for Database name.

Specify a custom query to validate the database connection.

Configure SSH tunnel

Default Value: Deselected

Checkbox

N/A

Select this checkbox to connect to the database server through the SSH tunnel. After the operation is completed, the tunnel is closed.

Learn more about SSH Tunneling Testing with PostgreSQL.

SSH auth mode

Default Value: Password
Example: KeyFile

Dropdown list

N/A

Select an option to specify the mode for authenticating the user on the SSH tunnel. The available options are:

• Password: Uses the SSH hostname, username, and password for authentication.

• KeyFile (Private Key File): Uses the Private key file for authentication, which you can upload from SLDB into the account after setting up the SSH tunnel.

• KeyFile (Private Key String): Uses the Private key string for authentication, which you can upload from SLDB into the account after setting up the SSH tunnel.

SSH hostname

Default Value: N/A
Example: 127.0.0.1

String/Expression

N/A

Specify the IP address or the domain name of the SSH server to which you want to connect.

SSH username

Default Value: N/A
Example: SSHUser

String/Expression

N/A

Specify the SSH username that is authorized to connect to the database. This username is used as the default username when retrieving connections.

SSH password

Default Value: N/A
Example: <Encrypted>

String/Expression

Appears if SSH Auth Mode is Password.

Specify the password for the SSH username for connecting to the SSH tunnel. 

Private key file URL

Default Value: N/A
Example: postgres-ssh.pem

String/Expression

Appears if SSH Auth Mode is KeyFile (Private Key File).

Specify the location of the private key file.

The file can be in SLDB, on the host machine that is hosting the JCC, or at any other remote location. Click the File browser icon to upload the file from your local system. You can also upload the file using any protocol such as HTTPS, FTP, SLDB, and SFTP.

Private key*

Default Value: N/A
Example:

-----BEGIN RSA PRIVATE KEY-----

………………..

-----END RSA PRIVATE KEY-----

String/Expression

Appears if SSH Auth Mode is KeyFile (Private Key String).

Specify the private key for authentication.

Private key passphrase

Default Value: N/A
Example: 3x@mpl3_P@ssw0rd!

String/Expression

Appears if SSH Auth Mode is KeyFile.

Specify the passphrase that is to be used to decrypt the private key.

SSH port

Default Value: N/A
Example: 222

Integer/Expression

N/A

Specify the SSH port to connect to any of the following database servers:

• PostgreSQL

• Redshift

• NetSuite

• MySQL

• Oracle

• SQL Server 2012

• SQL Server 2008

• SAPHana

• Apache Hive

• DB2

• SQLMX

• Apache Derby

• Spark SQL

Advanced properties*

Min pool size*

Default Value: 0
Example: 0

Integer/Expression

N/A

Specify the minimum number of idle connections a pool will maintain at a time.

Max pool size*

Default Value: 15
Example: 10

Integer/Expression

N/A

Specify the maximum number of idle connections a pool will maintain at a time.

Max idle time*

Default Value: 60 
Example:100

Integer/Expression

N/A

Specify the number of minutes a connection must remain in the connection pool before being discarded.

Checkout timeout*

Default Value: 10000
Example:10000

Integer/Expression

N/A

Specify the number of milliseconds to wait for a connection to be available in the pool.

Url properties

Use this field set to define URL properties to use in the JDBC URL.

Url property name

Default Value: N/A
Example:

• maxAllowedPacket

• encrypt

• selectMethod

String

N/A

Specify a name for the URL property to be used by the account. For instance:

• If you want the account to use trust certificates, specify the Url property name as trustServerCertificate and the value as true.

• If you do not want the account to use encryption, specify the Url property name as encrypt and the value as false.

• If you want to specify the method for selecting and navigating data in the database, use the Url property name as selectMethod, and specify the value as cursor.

Url property value

Default Value: N/A
Example: 

• 1000

• false

• cursor

String

N/A

Specify a value for the URL property name.

Auto commit

Default Value: Selected

Checkbox

N/A

Select one of the options for this property to override the state of the Auto commit property on the account. The Auto commit at the Snap-level has three values: True, False, and Use account setting. The expected functionality for these modes are:

•  True - The Snap will execute with auto-commit enabled regardless of the value set for Auto commit in the Account used by the Snap.

•  False - The Snap will execute with auto-commit disabled regardless of the value set for Auto commit in the Account used by the Snap.

• Use account setting - The Snap will execute with Auto commit property value inherited by the Account used by the Snap.

Fetch size*

Default Value: 100
Example: 100

Integer/Expression

N/A

Specify the number of records to retrieve from the DB at a time.

Batch size*

Default Value: 50
Example: 10

Integer/Expression

N/A

Specify the number of query statements to execute at a time. SELECT queries are not batched.

• If the Batch Size is one, the query is executed as-is, that is the Snap skips the batch (non-batch execution).

• If the Batch Size is greater than one, the Snap performs the regular batch execution.