Skip to end of banner
Go to start of banner

Generic Database Account

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 52 Current »

In this article

Overview

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

Overview of settings with default values

Prerequisites

None.

Limitations

None.

Known Issues

Generic JDBC Snaps connecting to the Informix database through the Generic 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

Account Settings

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon (): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon (): Indicates that you can add fields in the field set.

  • Remove icon (): Indicates that you can remove fields from the field set.

Field Name

Field Type

Field Dependency

Description

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.

  • Type 3 and Type 4 JDBC drivers are only supported. Type 4 JDBC driver is recommended. For more information on JDBC driver versions and downloads, see 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).

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 - Select Snap is ignored.

  • PostgreSQL

  • Redshift

  • NetSuite: If you select NetSuite, then the Limit rows field in the Generic JDBC - Select Snap ignored irrespective of whether you provide a value or not.

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

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

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.

The username must be valid to set up the data source.

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

Ensure that there are no port conflicts.

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.

If the size is set to non-zero, JCC restart is needed when the database account expires.


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.

If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.

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.

  • '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.

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.

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.

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

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.

Snap Pack History

 Click here to expand...

Release 

Snap Pack Version

Date

Type

  Updates

May 2024

main26341

Stable

Updated and certified against the current SnapLogic Platform release.

February 2024

436patches25244

Latest

  • Fixed an issue with the Generic JDBC-Select, Insert, Update, and Table List Snaps that incorrectly displayed the Schema Name suggestions with the Cassandra driver connection.

  • Fixed an issue with the Generic JDBC-Select Snap that displayed an empty result with the MariaDB driver connection for output 1.

February 2024

main25112

Stable

Added the following new Snaps to the JDBC Snap Pack:

November 2023

435patches24781

Latest

The database connection error message in the Generic Database Account is improved for clarity and readability.

November 2023

main23721

Stable

Updated and certified against the current SnapLogic Platform release.

August 2023

main22460

Stable

Updated and certified against the current SnapLogic Platform release.

May 2023

main21015

 

Stable

The Generic Database Account now supports the SSH Tunneling connection. You can now encrypt the network connection between your client and the database server, ensuring a highly secure connection.

February 2023

main19844

 

Stable

Upgraded with the latest SnapLogic Platform release.

November 2022

main18944

 

Stable

The Generic JDBC - Insert Snap previously took a longer time to execute and was aborted after a few minutes, which affected the performance of the Snap. The Snap's performance is optimized now and takes lesser time for execution.

September 2022

430patches17894

 

Latest

The Generic JDBC Snaps connecting to the DB2 database now take lesser time to execute thereby improving the performance.

August 2022

main17386

 

Stable

Enhanced the Generic Database account Support dynamic values for Account, Advanced, and URL properties.

4.29 Patch

429patches16588

 

Latest

  • Fixed an issue with JDBC Select and JDBC Insert Snaps where the Snaps did not load suggestions for the Table property. Now the Snaps load the suggestions correctly.

  • Enhanced the Generic Database account to support dynamic values for Account properties, Advanced properties, and URL properties so that you can pass these values through Pipeline parameters.

4.29

main15993

 

Stable

Enhanced the Generic Database Account in the JDBC Snap pack to include Netsuite as an option for the Database name field to connect to the NetSuite database. While configuring the account, if you select Netsuite or Autodetect (if the Snap detects the target database as NetSuite) then the Limit rows field in the Generic JDBC - Select Snap is ignored.

4.28

main14627

 

Stable

Enhanced the following Snaps to support DML (Data Manipulation Language) operations (CREATE, ALTER, INSERT, and SELECT) when using AWS Athena database.

4.27

main12833

 

Stable

4.26 

main11181

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.25 Patch

425patches11008

 

Latest

  • Fixed an issue with Generic JDBC - Select Snap where if there are URL properties in the account, the unPooledConnection does not use the properties when creating a connection. This causes the auto-detect functionality to use the wrong database name.

  • Fixed an issue with MySQL and MariaDB in Generic JDBC - Update Snap when a table is incorrectly reported as not being found.

4.25

main9554

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.24 Patch

4.24patches8903

 

Latest

Fixes the Pipeline execution failure issue when using the JDBC Snaps with a Microsoft Access account by setting the correct quotation handler for the configured account.

4.24

main8556

Stable

Enhances the Generic JDBC - Select Snap to return only the fields (provided in the Output Fields) in the output schema (second output view), through a new check box Fetch Output Fields In Schema. If the Output Fields property is empty all the columns are visible.

4.23 Patch

423patches8027

 

Latest

Fixes the multiple connection issue in the JDBC Snap Pack that occurs when the Auto Detect field is enabled for each execution of the Snap by storing a copy of the database name.

4.23 Patch

423patches7800

 

Latest

Fixes an issue with JDBC account through a Test Query option that establishes a database connection upon using Auto detect option for the Database name.

4.23

main7430

 

Stable

  • Fixes an issue with the JDBC account that displays a connection is closed error when connecting with the SQL server.

  • Fixes an issue with the JDBC Snaps that fail while displaying the javax.management.MalformedObjectNameException: Invalid character '=' in value part of property error repeatedly when there is “=“ or ”:” in the JDBC URL connection.

4.22 Patch 

422patches7246

 

Latest

Fixes the file descriptor error in the Snap Pack by removing logging exception which was causing log file spam.

4.22 Patch

422patches6772

 

Latest

Fixes the issue with Auto detect in Generic JDBC - Execute Snap by implementing the JDBC Auto detect feature to use the correct database name.

4.22

main6403

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.21 Patch

421patches6272

 

Latest

Fixes the issue where Snowflake SCD2 Snap generates two output documents despite no changes to Cause-historization fields with DATE, TIME and TIMESTAMP Snowflake data types, and with Ignore unchanged rows field selected.

4.21 Patch

421patches6144

 

Latest

Fixes the following issues with DB Snaps:

  • The connection thread waits indefinitely causing the subsequent connection requests to become unresponsive.

  • Connection leaks occur during Pipeline execution.

4.21 Patch

MULTIPLE8841

 

Latest

Fixes the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends.

4.21

snapsmrc542

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.20 Patch

jdbc8783

 

Latest

Fixes the Generic JDBC Execute Snap that adds the value of the previous column's field to a NULL field when SELECT queries having duplicate column names are run on the PostgreSQL database.

4.20

snapsmrc535

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.19 Patch 

db/jdbc8405

 

Latest

Fixes an issue with the Generic JDBC - Update Snap wherein the Snap is unable to perform operations when:

  • An expression is used in the Update condition property.

  • Input data contain the character '?'.

4.19

snaprsmrc528

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.18

snapsmrc523

 

Stable

Added support for JDBC account Active Directory authentication for SQL server for driver JAR version mssql-jdbc-6.2.2-jre8.jar.

4.17

ALL7402

 

Latest

Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.

4.17

snapsmrc515

 

Latest

  • Fixes an issue with the Generic JDBC Execute Snap wherein the Snap would send the input document to the output view even if the Pass through field is not selected in the Snap configuration. With this fix, the Snap sends the input document to the output view, under the key original, only if you select the Pass through field.

  • Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview check box.

4.16 Patch

MULTIPLE7123

 

Latest

Added two new properties, Number of retries and Retry interval, to Execute, Insert, Select, and Update Snaps that enables you to handle retry attempts during a connection failure.

4.16

snapsmrc508

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.15

snapsmrc500

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.14

snapsmrc490

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.13 Patch

jdbc5231

 

Latest

Added binding information for DB2 database connection on the Generic JDBC Snap Pack to ensure that pipelines correctly validate and execute.   

4.13 Patch

jdbc5229

 

Latest

Added test connection query for SQLMX database in the JDBC Accounts to check if the database connection is valid.

4.13

snapsmrc486

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.12 Patch

MULTIPLE4744

 

Latest

Added support to execute multiple queries in a single batch for Snowflake, this patch affects Generic JDBC Execute. 

4.12

snapsmrc480

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.11

snapsmrc465

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.10

snapsmrc414

 

Stable

Adds Auto commit property to the Select and Execute Snaps at the Snap level to support overriding of the Auto commit property at the Account level.

4.9.0 Patch

jdbc3242

 

Latest

Addresses an issue in JDBC Execute Snap with functions not working (fixes by executing commit() at the end of the processing query).

4.9.0 Patch

jdbc3205

 

Latest

Fixes auto-commit issue for PostgreSQL SELECT query.

4.9.0 Patch 

jdbc3133

 

Latest

Fixed error handling of the limit clause by adding Apache Derby support.

4.9.0 Patch

jdbc3069

 

Latest

Fixed an issue regarding connection not closed after login failure; Expose autocommit for "Select into" statement in PostgreSQL Execute Snap and Redshift Execute Snap.

4.9

snapsmrc405

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.8.0 Patch

jdbc2801

 

Latest

Addressed an issue with Generic JDBC - Execute Snap holding the resultset in memory, exhausting memory and causing a core dump.

4.8.0 Patch

jdbc2753

 

Latest

Potential fix for JDBC deadlock issue.

4.8

snapsmrc398

 

Stable

  • Updated the default value for the property Min pool size to 0. 

  • Info tab added to accounts.

  • Database accounts now invalidate connection pools if account properties are modified and login attempts fail.

4.7 Patch

jdbc2195

 

Latest

Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.

4.7

snapsmrc382

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.6

snapsmrc362

 

Stable

  • Enhanced Generic JDBC Execute Snap to fully support SQL statements with/without expressions & SQL bind variables.

  • Resolved an issue in Generic JDBC Insert Snap that caused unexpectedly failures on Snowflake and connecting to DB2.

  • Resolved an issue in Generic JDBC Select Snap that caused errors with Teradata database instance.

  • Resolved an issue in Generic JDBC Select Snap with Snowflake account that caused an error when auto-commit was not selected.

  • Resolved an issue in Generic JDBC Select Snap with Snowflake account that caused an error when a merge statement was used in the Snap.

Known Issue:

  • JDBC Insert: "The Maximum Possible Row Length in the Table is too Large" error message: You will see this message if trying to insert data into Teradata that is larger than Teradata can handle (rows are limited to approximately 64,000 bytes). Workaround: Use smaller default column size, e.g., 32000 bytes.

4.5.1

snapsmrc344

 

Stable

Resolved an issue with JDBC Select failing with a syntax error when special characters were used.

4.5

snapsmrc344

 

Stable

  • Verified Generic JDBC Snaps connections to Hive. See JDBC Account for configuration information. SELECT and INSERT are supported. SnapLogic does not support UPDATE, DELETE, or MERGE due to limitations in the drivers. 

  • Resolved an issue in Generic JDBC Insert Snap that occurred when inserting records in batches. 

  • Resolved an issue in Generic JDBC Insert Snap that occurred because of an SQL execution (internal) error.

  • Resolved an issue in Generic JDBC Select Snap that occurred because of an SQL query execution. 

4.4.1

N/A

 

Stable

  • Added expression support to the Where clause property for Select Snaps.

  • Resolved an issue with the JDBC Select Snap failing with Teradata because of a Limit Offset.

  • Resolved an issue with JDBC Execute failing to execute a create or replace table statement with Snowflake.

  • Resolved issues with JDBC Insert Snap:

    • Failing to create a table when Create table if not present is selected.

    • An exception occurred when attempting to insert record containing date columns into Teradata.

4.4

N/A

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.3.2

N/A

 

Stable

Resolved an issue with Generic JDBC Update updating null into database when an invalid string value is provided as input for time datatype.

4.3.1

N/A

N/A

Stable

  • New! Generic JDBC SELECT Snap

  • New! Generic JDBC UPDATE Snap

  • Feature: JDBC Snaps - add a new property 'Database name' to JDBC Account

  • Defect: JDBC Execute Snap fails in preview for Oracle timestamp data type

  • Defect: JDBC Insert to Snowflake throws an error

  • Defect: Generic JDBC Execute with JDBC4OLAP drivers to connect to Essbase resulted in com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to parse content type: null

4.2.2

N/A

N/A

Stable

  • Ignore empty result added to Execute Snap. The option will not write any document to the output view for select statements producing no results.

  • Addressed an issue with idle timeouts.

  • Resolved an issue in JDBC Execute throwing an exception, unable to get driver.

4.2.1

N/A

N/A

Stable

  • Resolved Generic JDBC Execute not working with value substitution.

  • Resolved JDBC Execute unable to read CLOB columns via jTDS/JDBC.

Related Content

  • No labels

Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.