Versions Compared

Old Version 26

changes.mady.by.user Sandhya Sriram

Saved on

compared with

New Version 27

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Updated for SNAP-9086

In this article

...

Note

Field names marked with an asterisk ( * ) are mandatory.


Default

Field Name

Field Type

Description

Label*

String

Specify a unique label for the account.


Default Value: N/A
Example: Generic Database Account

String

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

*String

*


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

String/Expression

Specify the JDBC driver to use. Click the Upload Image Modifiedicon to upload the JDBC driver that you want. Click Add Image Modified to add a row for specifying a JDBC driver. Specify each driver in a separate row.

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

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


JDBC Driver Class*

String

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 String

Driver class name to use.


JDBC URL


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

Username

String/Expression

Specify the

database username to

JDBC URL to use.


Username


Default Value:

 N

 N/A
Example: Snapuser

Password
String/Expression

Specify the database

password

username to use.


Password


Default Value: N/A
Example:

k

nb*#!@09

String/Expression

Specify the database password to use.


Database name


Default Value: Auto detect
Example: Oracle

String/Expression

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

  • Auto detect

    Info

    If you select Autodetect 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

    Info

    If you select Netsuite then the Limit rows field in the Generic JDBC - Select Snap is 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:

Auto detect
Test Query

N/A
Example:

Oracle

Select 1

String/Expression

Activates on

selecting on selecting 

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: Select 1


Advanced properties*

Min pool size*


Default Value: 0
Example: 0

Integer/Expression

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


Default Value: 0
Example: 0




Note

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/ExpressionSpecify the maximum number of idle connections a pool will maintain at a time.


Max idle time*


Default Value:

15

60 
Example:

10

300

Max idle time*
Integer/ExpressionSpecify the number of seconds a connection will exist in the pool before being destroyed.


Checkout timeout*


Default Value:

60 
Checkout timeout*

10000
Example:

300

10000

Integer/ExpressionSpecify the number of milliseconds to wait for a connection to be available in the pool. A timeout of zero makes the Snap wait infinitely.  After a certain time, an exception is thrown and the Pipeline fails.
Default Value: 10000
Example:10000

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

Url property name


Default Value: N/A
Example: maxAllowedPacket

String

Specify the name for the URL property.


Url property value


Default Value: N/A
Example:

maxAllowedPacketUrl property value

 1000

String

Specify a value for the property name.


Auto commit


Default Value:

N/A
Example: 1000Auto commit

Selected

Checkbox

Select this checkbox to commit a batch immediately after the batch executes. So, only the current executing batch will be rolled back if the Snap fails.
If you deselect, then a transaction is started for the Snap run and committed upon run success. The transaction is rolled back if the Snap fails

.Fetch size*

Fetch size*


Default Value:

Selected

100
Example: 100

Integer/ExpressionSpecify the number of records to retrieve from the DB at a time.


Batch size*


Default Value:

100

50
Example:

100

10

Batch size*

Integer/Expression

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.


Info

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

Default Value: 50
Example: 10



...

Insert excerpt
JDBC Snap Pack
JDBC Snap Pack
nopaneltrue

...