Generic JDBC Database Account

Overview

You can use this account type to connect JDBC Snaps with data sources that use the Generic JDBC Database Account .

Known issues

Generic JDBC Snaps connecting to the Informix database through the Generic JDBC Database Account can cause thread leaks because of JDBC driver implementations.

Workaround: To avoid these thread leaks, do the following:
  • Use the Informix JDBC driver 4.50.4.1 version or a later version.
  • Configure the following URL properties in the account settings:
    • Url Property name: IFMXCONNECTION_CLEANER_THREADS
    • Url property value: 0
Note: Breaking Change

Your existing pipelines that use JDBC Snaps to integrate with Oracle or Redshift databases using the bundled Oracle or Redshift JDBC drivers will stop functioning with the 438patches28052.

Workaround

You must manually upload the Oracle or Redshift JDBC driver in the JDBC Database account to run your pipelines successfully.

Download the drivers:

OJDBC: Maven Repository: com.oracle.database.jdbc » ojdbc6 » 11.2.0.4

Redshift: Maven Repository: com.amazon.redshift » redshift-jdbc42 » 2.1.0.11

Account settings



Legend:
  • Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field / Field set Type Description
Label String

Required. Specify a unique label for the account.

Default value: N/A

Example: Generic Database 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 String Required. Specify the JDBC driver to use. Click on the Upload icon to upload the required JDBC driver that must be used for the account.
Note:

  • Type 3 and Type 4 JDBC drivers are only supported. We recommend you to use Type 4 JDBC driver.

    Learn more: JDBC driver versions and downloads https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads

  • You can upload the JDBC drivers through Designer or Manager and it is stored on a per project basis. That is, only users with access to that project can view the uploaded JDBC drivers. To provide access to all users of your org, place the driver in the /shared project.

  • We recommend you to use the db2jcc.jar driver for DB2 with your JDBC account. The DB2 version 11.5 supports only JDBC 4.0 Driver (db2jcc4.jar) and 11.1 version supports JDBC 3.0 driver (db2jcc.jar) and JDBC 4.0 Driver (db2jcc4.jar).

Default value: N/A

Example: vertica-jdk5-6.1.2-0.jar
JDBC Driver Class String/Expression Required. Specify the JDBC Driver class name to use.

Default value: None.

Example: com.vertica.jdbc.Driver
JDBC URL String/Expression

Specify the JDBC URL to use.

Default value: None.

Example: jdbc:vertica://Snaplogic.com/database
Username String/Expression

Specify the database username to use.

Default value: N/A

Example: Snapuser
Password String/Expression Specify the database password to use.

Default value: N/A

Example: nb*#!@09
Database name Dropdown list

Choose a database to which the account must be connected.

The available options are:
  • Auto detect: If you select Auto detect and the Snap detects the target database as NetSuite, then the Limit rows field in the Generic JDBC - Select is ignored.
  • PostgreSQL
  • Redshift
  • NetSuite: If you select Netsuite, the Limit rows field in the Generic JDBC—Select Snap is ignored regardless of an existing or non-existing value.
  • MySQL
  • Oracle
  • SQL Server 2012
  • SQL Server 2008
  • SAPHana
  • Apacha Hive
  • DB2
  • SQLMX
  • Apache Derby
  • Spark SQL
Note:
  • 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.
  • To connect to popular databases such as Snowflake, PostgreSQL, MySQL, SQL Server, or Oracle we recommend that you use the dedicated Data Snaps available in the SnapLogic application. This will address all your database integration needs and help optimize performance.

Default value: Auto detect

Example: Oracle
Test Query String/Expression

Activates on selecting Auto detect for Database name.

Specify a custom query to validate the database connection.

Note:

  • The Test Query must be effective as this executes multiple times during the lifecycle of a connection. The efficiency of a Test Query directly affects the efficiency of the Pipeline execution. All databases do not support all test queries. Hence, use a query that supports your database.

  • Select 1 test query is supported only in H2, MySQL, PostgreSQL, and SQLite databases.

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;
Configure SSH tunnel Checkbox 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 with PostgreSQL

Default status: Deselected
SSH auth mode Dropdown list Required. 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 to 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 to the account after setting up the SSH tunnel.

Default value: Password

Example: KeyFile
SSH hostname Integer/Expression Required. Specify the IP address or the domain name of the SSH server to which you want to connect.

Default value: N/A

Example: 127.0.0.1
SSH username String/Expression Required. Specify the SSH username that is authorized to connect to the database. This username is used as the default username when retrieving connections.
Note: The username must be valid to set up the data source.

Default value: N/A

Example: SSHUser
SSH password String/Expression Appears if SSH Auth Mode is Password.

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

Default value: N/A

Example: <Encrypted>
Private key file URL 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.

Default value: N/A

Example: postgres-ssh.pem
Private key String/Expression Appears if SSH Auth Mode is KeyFile (Private Key String).

Specify the private key for authentication.

Default value: N/A

Example:

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

………………..

-----END RSA PRIVATE KEY----
Private key passphrase String/Expression Appears if SSH Auth Mode is KeyFile.

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

Default value: N/A

Example: 3x@mpl3_P@ssw0rd!
SSH port Integer/Expression Required. 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
Note: Ensure that there are no port conflicts.

Default value: N/A

Example: 222
Advanced properties
Min pool size Integer/Expression Required. Specify the minimum number of idle connections a pool should maintain at a time.
Note: If the size is set to non-zero, JCC restart is needed when the database account expires.

Default value: 0

Example: 0
Max pool size Integer/Expression Required. Specify the maximum number of idle connections a pool should maintain at a time.

Default value: 15

Example: 10
Max lifetime (minutes) Integer/Expression Required. Specify the number of minutes a connection must remain in the connection pool before being discarded.

Default value: 60

Example: 100
Checkout timeout (milliseconds)* Integer/Expression Required. Specify the number of milliseconds to wait for a connection to be available in the pool.
Note: If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.

Default value: 1000

Example: 10000
Url properties Use this field set to define URL properties to use in the JDBC URL.
Url property name String 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.

Default value: N/A

Example:

  • maxAllowedPacket

  • encrypt

  • selectMethod
Url property value String Specify a value for the URL property name.

Default value: N/A

Example:
  • 1000
  • false
  • cursor
Auto commit Checkbox 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.

Note:

  • 'Auto commit' may be enabled for certain use cases if PostgreSQL jdbc driver is used in either Redshift, PostgreSQL or generic JDBC Snap. But the JDBC driver may cause out of memory issues when Select statements are executed. In those cases, “Auto commit" in Snap property should be set to ‘False’ and the Fetch size in the “Account setting" can be increased for optimal performance.

  • For a DB Execute Snap, assume that a stream of documents are passed to the input view of the Snap and the SQL statement property has JSON paths in the WHERE clause. If the number of documents are large, the Snap executes in more than one batch rather than executing one per each document. Each batch would contain a certain number of WHERE clause values. If Auto commit is turned on, a failure would only roll back the records in the current batch. If Auto commit is turned off, the entire operation would be rolled back. For a single execute statement (with no input view), the setting has no practical effect.

Warning: Behavior of DML Queries in Database Execute Snap when auto-commit is false

DDL queries used in the Database Execute Snap will be committed by the Database itself, regardless of the Auto-commit setting.

When Auto commit is set to false for the DML queries, the commit is called at the end of the Snap's execution.

The Auto commit needs to be true in a scenario where the downstream Snap does depend on the data processed on an Upstream Database Execute Snap containing a DML query.When the Auto commit is set to the Use account setting on the Snap, the account level commit needs to be enabled.

Default status: Selected
Fetch size Integer/Expression Required. Specify the number of records to retrieve from the DB at a time.

Default value: 100

Example: 100
Batch size Integer/Expression Required. 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.

Note: If you are using AWS Athena database, the Batch size must be set to 1.

Default value: 50

Example: 10

Troubleshooting

Error Reason Resolution
Caused by: java.lang.AbstractMethodError Snap displays this error when the endpoint database driver lacks an implementation for the isValid() or any other method. To address this issue, consider the following troubleshooting methods:

  • Check database driver version: Ensure that you are using the latest version of the target database driver. Outdated drivers might lack support for certain methods.

  • Check documentation: Refer to the official documentation of the target database driver. Check if the isValid() method is supported and if any specific configurations are required.

  • Driver compatibility: Verify the compatibility of the database driver with the specific database version you are using. Incompatibility can lead to missing implementations for certain methods.

  • Alternative health check mechanisms: Explore alternative mechanisms to validate the database connection health, such as executing a test query or utilizing other available methods provided by the driver.

  • Community forums and support: Visit the official database driver community forums or seek support.