Versions Compared

Old Version 27

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 28

changes.mady.by.user Kalpana Malladi

Saved on

Key

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

In this article

...

  • Use the Informix JDBC driver version 4.50.4.1 or a higher version.
  • Configure the following URL properties in the account settings:
    • Url Property name: IFMXCONNECTION_CLEANER_THREADS
    • Url property value: 0

Account Settings

Image RemovedImage Added


Note

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

...

Field Name

Field Type

Description

Label*


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*


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

String/Expression

Specify the JDBC driver to use. Click the Upload icon to upload the JDBC driver that you want. Click Add  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).


JDBC Driver Class*


Default Value: [None]
Example: com.vertica.jdbc.Driver

String/Expression

Specify the JDBC Driver class name to use.


JDBC URL


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

String/Expression

Specify the JDBC URL to use.


Username


Default Value: N/A
Example: Snapuser

String/Expression

Specify the database username to use.


Password


Default Value: N/A
Example: 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: N/A
Example: Select 1

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.


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.


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: 60 
Example: 300

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


Checkout timeout*


Default Value: 10000
Example: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.

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

String

Specify a value for the property name.


Auto commit


Default Value: 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*


Default Value: 100
Example: 100

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


Batch size*


Default Value: 50
Example: 10

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.


...