Skip to main content
Feedback

Database V2 connection

The Database V2 connection represents a single account including login credentials. If you have multiple systems, you need a separate connection for each.

Connection tab

Connection URL - Refers to the Database URL (Uniform Resource Locator). It is mandatory to include database name in the connection URL. For example:

MySQL database - jdbc:mysql://localhost:3306/testdb.

  • localhost is the name of the server hosting your MySQL database. It can also be an IP address of the database server. Server can be on-premise/cloud (public or private).
  • 3306 is the port number.
  • testdb is the name of the database to connect to.

MS SQL database - jdbc:sqlserver://localhost:1433;databaseName=boomi

  • localhost is the server hosting the MS SQL database.
  • 1433 is the port number (default) of the SQL server.
  • databaseName is the name of the database to connect to.

PostgreSQL database takes one of the following forms:

  • jdbc:postgresql:database
  • jdbc:postgresql://host/database
  • jdbc:postgresql://host:port/database

Oracle database - jdbc:oracle:thin:@localhost:1521:xe.

  • localhost is the server name on which oracle is running. It can also be IP address.
  • 1521 is the port number.
  • xe is the Oracle service name.

Class Name - Class name of the database driver to be sent to the respective database.

info

Below are examples of class names:

  • Postgre: org.postgresql.Driver
  • MS SQL: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • MySql: com.mysql.jdbc.Driver
  • Oracle: oracle.jdbc.driver.OracleDriver

User Name - The database user on whose behalf the connection is made.

Password - The user's password.

Schema Name - Enter the database schema name to fetch the table names. The schema name must be specified either in Operation UI, Connection UI or Connection URL. If the Schema name is defined in all the above settings, the order of preference will be Operation UI, Connection UI and then the Connection URL.

Connection Timeout (ms) - Enter the maximum time (in milliseconds) that the connector can wait to complete the connection handshake with the database.

Read Timeout (ms) - Enter the maximum time (in milliseconds) that the runtime cluster or runtime cloud can spend attempting to read data from the database.

Connection Pooling Settings

This section describes how Integration manages connection pools:

  • A connection pool is identified by the connection component’s ID and its connection settings. This combination enables you to configure one component to talk to different databases. For example, you can define process extensions for connection settings so that one component can operate in separate connection pools.

  • When you change a connection’s primary settings on the Connection tab, such as user or password, a new connection pool is created when the connection is redeployed. Existing pools are updated with the new settings and continue to operate without interruption

  • When you change a connection’s secondary settings on the Connection Pool tab, such as maximum or minimum connections, the existing connection pool is updated the first time the new version of the connection is requested.

  • The connection pool does not initialize with the minimum number of idle connections by default. Instead, connections are created only when the first request is made.

  • If a maximum idle connection value is provided, the connector will use it; otherwise, it will default to 50 maximum idle connections.

  • Every 30 minutes, the integration monitors all connection pools used by the connector. If a connection pool has not been used for 30 minutes and has no active connections, it is considered a dead or surplus connection.

There are several ways you can change your pool settings:

  • To override the connection pool settings, change your connection settings and restart your basic Runtime.

  • To make changes immediately, change your connection settings and redeploy your process.

  • To make changes without restarting your basic runtime, for example, in extensions, make the changes. Then, leave the connection unused for 30 minutes in a process. The idle timeout generates a fresh connection pool the next time your process runs.

note

Most integrations do not require connection pooling. Consider pooling if you use low latency to speed up processes or if you execute a large number of statements during an integration, such as an SQL lookup map function with thousands of records that is integrated with Oracle databases.

Enable Connection Pooling (Optional) - Select the check box to use the connection pooling feature. If selected, then the connection pooling settings fields (listed below) will be displayed. If the check box is not selected, then the values defined in the below fields will not be considered for connection pooling.

Maximum Connections (Optional) - Enter the maximum number of connections allowed in the pool. The default is -1, which indicates an unlimited number of connections. You will receive an error, if you attempt to exceed the maximum.

Minimum Connections (Optional) - Enter the minimum number of connections allowed in the pool. The default is 0.

Maximum Idle Time (Optional) - Enter the maximum amount of time (in seconds) that a connection can remain open in an idle state. The default is 0.

When Exhausted Action (Optional) - Indicate whether to wait for a connection or immediately fail, when a connection pool is exhausted.

Maximum Wait Time (Optional) - Enter the maximum amount of time (in seconds) to wait for a connection from the pool. The default is 0.

Test Connection When Borrowing from Pool (Optional) - Select this option to verify if a connection is still valid while taking it from the pool.

Test Connection When Returning from Pool (Optional) - Select this option to test if a connection is still valid, when returning it back to the pool.

Test Idle Connections (Optional) - Select this option to test if the idle connections are still valid.

Validation Query (Optional) - Enter a simple SQL Statement to return a single row of data, if the connection is valid.

Connection Properties - Use the Add Property button to add additional connection properties, if required to establish connection to the database.

info

To optimize the performance of your Boomi Database V2 connector for the MySQL INSERT operation, set the rewriteBatchedStatements property to TRUE. For more details, refer to the blog post on Boomi Dev Docs.

note
  • PostgreSQL database — When passing multiple input documents, or using batching, make sure to add the property "autosave=always". This sets a savepoint before each query, and rolls back to that savepoint, in case of failure.
  • Oracle database — There are chances of getting TNS admin error while performing the Test Connection/ Import/ Process execution on the Public runtime cloud. To run the connector successfully on the Public runtime cloud, the oracle.net.tns_admin property must be defined as Key with Value set to the directory path of the tnsnames.ora file containing the DB connection details. Every time, you want to execute the process on the cloud, make sure the directory and the ORA file exist and you have required access permission on it. It is recommended to place the ORA file in the Work folder within the connector account of the cloud attachment as it is allowed with high security policy. Sample tnsnames.ora file content:
XE= 
  (DESCRIPTION —
    (ADDRESS_LIST = 
     (ADDRESS — (PROTOCOL — TCP) (HOST — 13.126.74.41) (PORT — 1521))
    )
  (CONNECT_DATA —
      (SERVICE_NAME = XE)
  )
)

Test Connection

You can test your connection settings before you use or save the connection in a process. The Test Connection ensures that your specified settings are correct, valid, and accessible. If the test connection is successful, you can save the connection. Otherwise, review and correct any incorrect settings, then test again.

Attention

You can only run the Test Connection against containers associated with your account (local runtimes, runtime clusters, and runtime clouds).

On this Page