Versions Compared

Old Version 33

changes.mady.by.user Siddharth Menon (Unlicensed)

Saved on

compared with

New Version 34

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

On this Page

Table of Contents
maxLevel2
absoluteUrltrue
excludeOlder Versions|Additional Resources|Related Links|Related Information

This account is used by the Snaps in the JDBC Snap Pack.

You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:

  • Your private project folder: This folder contains the pipelines that will use the account.
  • Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
  • The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.

Account Configuration

In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for a generic JDBC driver:

  1. If you have not already done so, upload the JDBC driver for this database as a file for the specific project.
  2. Click Create, then select JDBC > Generic Database Account.
  3. Supply an account label.
  4. Supply the account properties for your database. 
  5. (Optional) Supply additional information on this account in the Notes field of the Info tab.
  6. Click Apply.
Warning

Avoid changing account credentials while pipelines using them are in progress. This may lead to unexpected results, including locking the account.

Note
  • If you have several JDBC accounts, one to system A and one to system B, then you need to make sure all drivers can be loaded. If driver A fails to load for some reason, then driver B will always fail, even though it is a valid driver, as this is how the JDBC driver registry works. It loads all drivers and goes through them in sequence. If any driver fails on the way, all drivers loaded later will also fail. Once you fix the driver for the failing account, the JCC needs to be restarted.
  • To disable the mysql-cj-abandoned-connection-cleanup  thread that is generated by MySQL JDBC driver, use the latest version of MySQL (later than 8.0.22), and add -Dcom.mysql.cj.disableAbandonedConnectionCleanup=true to the jcc.jvm_options in the node properties under ‘Global Properties’.
  • If the Snap fails to connect to the database, it will retry three more times.

Account Types

Generic Database Account

Account Settings

Label

Required. User provided label for the account instance.

Account Properties

Required. 

JDBC Driver

Required. Select the JDBC driver to use. Type 3 and Type 4 JDBC drivers are only supported. 

Note
  • The JDBC driver can be uploaded through Designer or Manager and it is stored on a per project basis. That is, only users with access to that project will see JDBC drivers uploaded. To provide access to all users of your org, place the driver in the /shared project.
  • We recommend using the db2jcc.jar driver to use DB2 with your JDBC account.

Example: vertica-jdk5-6.1.2-0.jar

Default value: [None]

JDBC Driver Class

Required. The JDBC Driver class name to use.

Example: com.vertica.jdbc.Driver

Default value: [None]

JDBC Url

JDBC URL to use.

Example: jdbc:vertica://Snaplogic.com/database

Default value: [None]

Username

Database username to use.

Example: Snapuser

Default value: [None]

Password

Database password to use.

Example: Snapuser

Default value: [None]

Database name

Select a Database to connect to using the account. The available options are: Auto detect, PostgreSQL, Redshift, MySQL, Oracle, SQL Server 2012, SQL Server 2008, SAPHana, Apache Hive, DB2, SQLMX, Apache Derby, and 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 Snap is optimized for the selected database.

Example:  Oracle
Default value:  Auto detect

Test Query

Enter a custom Test Query to validate the database connection on selecting Auto detect for Database name.

Note

The Test Query must be effective as this executes multiple time 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.

Example: Select 1
Default value: Select 1
Note: Select 1 test query is supported only in H2, MySQL, PostgreSQL and SQLite databases.

Advanced PropertiesRequired. 

Min pool size

Required. Minimum number of idle connections a pool will maintain at a time.
Example: 0
Default value: 0
Note: If the size is set to non-zero, JCC restart is needed when the database account expires.

Max pool size

Required. Maximum number of idle connections a pool will maintain at a time.
Example: 10
Default value: 15

Max idle time

Required. Seconds a connection will exist in the pool before being destroyed.
Example: 300
Default value: 60 Minutes

Checkout timeout

Required. Number of milliseconds to wait for a connection to be available in the pool. Zero waits forever. After set time, then an exception will be thrown and the pipeline will fail.
Example: 10000
Default value: 10000

Number of helper threads

Required. Number of thread to help execute operations. Increasing the threads can improve performance.
Example: 3
Default value: 3

Url Properties

Properties to use in JDBC url.
Example: maxAllowedPacket | 1000
Default value: [None]

Auto commit

If selected, then batches are immediately committed after they execute. Therefore, only the current executing batch will be rolled back if the Snap fails.

If not selected, then a transaction is started for the Snap run and committed upon run success. The transaction will be rolled back if the Snap fails.
Default value: Selected

Fetch size

Required. Number of records to retrieve from the DB at a time.
Example: 100
Default value: 100Batch size
Required. Number of statements to execute at a time.
Example: 10
Default value: 50

On this Page

Table of Contents
maxLevel2
absoluteUrltrue
excludeOlder Versions|Additional Resources|Related Links|Related Information

This account is used by the Snaps in the JDBC Snap Pack.

You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:

  • Your private project folder: This folder contains the pipelines that will use the account.
  • Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
  • The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.


Account Configuration

In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for a generic JDBC driver:

  1. If you have not already done so, upload the JDBC driver for this database as a file for the specific project.
  2. Click Create, then select JDBC > Generic Database Account.
  3. Supply an account label.
  4. Supply the account properties for your database. 
  5. (Optional) Supply additional information on this account in the Notes field of the Info tab.
  6. Click Apply.
Warning

Avoid changing account credentials while pipelines using them are in progress. This may lead to unexpected results, including locking the account.


Note
  • If you have several JDBC accounts, one to system A and one to system B, then you need to make sure all drivers can be loaded. If driver A fails to load for some reason, then driver B will always fail, even though it is a valid driver, as this is how the JDBC driver registry works. It loads all drivers and goes through them in sequence. If any driver fails on the way, all drivers loaded later will also fail. Once you fix the driver for the failing account, the JCC needs to be restarted.
  • To disable the mysql-cj-abandoned-connection-cleanup  thread that is generated by MySQL JDBC driver, use the latest version of MySQL (later than 8.0.22), and add -Dcom.mysql.cj.disableAbandonedConnectionCleanup=true to the jcc.jvm_options in the node properties under ‘Global Properties’.
  • If the Snap fails to connect to the database, it will retry three more times.


Account Encryption

Standard Encryption

If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.


Enhanced Encryption

If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected for this account.

Account:

  • High: Password
  • Medium + High: Username, password
  • Low + Medium + High: Username, password, JDBC UR

Auto Commit with Execute Snaps

For a DB Execute Snap, assume that a stream of documents enters 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 batches 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.

Active Directory authentication

SnapLogic supports Active Directory authentication for SQL server for driver JAR version mssql-jdbc-6.2.2-jre8.jar. Ensure that you have installed mssql-jdbc-6.2.2-jre8.jar file. Also, use the following configurations in Account Settings to configure Active Directory authentication:

SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. The prerequisites are as follows:

  • In the account settings, add the following to Url property name and Url property value:


    Url property name
    Url property value
    IntegratedSecurityTrue
    AuthenticationSchemeJavaKerberos


  • In the account settings, enter your Active Directory Username and Password.

Connecting to Cassandra Database Using Cassandra Simba Driver

To connect to Cassandra database using the Cassandra Simba driver ensure that the appropriate Simba JAR files are installed based on the version of JDK in the Snaplex node. Contact your Org admin to know the JDK in the Snaplex node. To see which JAR version is to be used, see JDBC Install Guide for details.

 Aside from the JAR files, use the following configurations in the Account Settings when using the Cassandra Simba driver:

  • JDBC Driver class:  "com.simba.cassandra.jdbc42.Driver"
  • JDBC URL: jdbc:cassandra://<host>:<port>;AuthMech=1;UID=<user id>;PWD=<password>;DefaultKeyspace=<database name>
  • Database name: Auto detect


Note
titleFor JDK 8 and Above
  • For JDK 8 and above, use version 4.2 of the Cassandra Simba Driver
  • Of the JARs included with version 4.2, do not include the following since they are already provided as part of the Snap pack:
    • asm-5.0.3.jar
    • guava-16.0.1.jar
    • jackson-annotations-2.6.0.jar
    • jackson-core-2.6.3.jar
    • jackson-databind-2.6.3.jar
    • joda-time-2.9.1.jar
    • metrics-core-3.1.2.jar
    • snappy-java-1.1.2.6.jar
    • slf4j-api-1.7.7.jar
    • netty-transport-4.0.37.Final.jar
    • netty-handler-4.0.37.Final.jar
    • netty-common-4.0.37.Final.jar
    • netty-codec-4.0.37.Final.jar
    • netty-buffer-4.0.37.Final.jar

Examples

This section provides examples of JDBC connection details for different sources. Note that specifics may vary based on operating system or database version.

Teradata

  • JDBC Driver Jar: terajdbc4_13.jar, tdgssconfig_13.jar
  • JDBC Driver Class: com.teradata.jdbc.TeraDriver
  • JDBC URL: jdbc:teradata://<host>:<port>/TMODE=ANSI,SHARSET=UTF8,DBS_PORT=1025

SAP Hana

  • JDBC Driver Jar: ngdbc.jar
  • JDBC Driver Class:  com.sap.db.jdbc.Driver
  • JDBC URL: jdbc:sap://<host>:<port>/?currentschema=<your HANA Schema> 

Sybase

  • JDBC Driver Jar: jconn4.jar
  • JDBC Driver Class: com.sybase.jdbc4.jdbc.SybDriver
  • JDBC URL: dbc:sybase:Tds:<host>:<port2048>/<database> 

JDBC ODBC Bridge

  • JDBC Driver Jar: 
  • JDBC Driver Class: sun.jdbc.odbc.JdbcOdbcDriver
  • JDBC URL: jdbc:odbc:ODBCConnectionName

Hive

Varies based on version.

See https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBC for general information on Hive JDBC Drivers for HiveServer2.

Informix

Image Modified

You will need to log into Informix with your IBM account to be able to download the Informix JDBC drivers and upload them into SnapLogic.

  

Insert excerpt
JDBC Snap Pack
JDBC Snap Pack
nopaneltrue