Versions Compared

Old Version 26

changes.mady.by.user Ritu Sachdeva

Saved on

compared with

New Version 27

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

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.
  • 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 name of the database which the account is to be connected to. The possible selections are

a Database to connect to using the account. The available options are: Auto detect, PostgreSQL, Redshift, MySQL, Oracle

and SQL Server. If PostgreSQL JDBC driver is used for Redshift database, the 'Auto detect' option will detect the database as PostgreSQL instead of Redshift. The behavior of JDBC Snap will be optimized for the selected database.
Example:  Oracle
Default value:  Auto detect

, 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

Note

When you select the Auto detect option for Database name, 'SELECT 1' is used by default as the query, but all databases do not support 'SELECT', in such as case you must specify a custom query to verify the database connection.


Test Query

Enter a custom test query to verify the database connection upon selecting Auto detect for Database name.

Example: 'select sr.subcontractor_id from service_records sr inner join partners p on sr.subcontractor_id = p.parnter_id where sr.customer_id = ' + $company_internal_id+ ' and sr.end_date is null and sr.service_description_id = 13'

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: 10

Max idle time

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

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: 100

Batch size

Required. Number of statements to execute at a time.
Example: 10
Default value: 50

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