Create a JDBC Connection

A JDBC Connection is used to connect and get or set data from or to a MySQL database or PostgreSQL database in Hero Platform_.

Create a Connection to a JDBC in Hero Platform_

    1. Open Hero Platform_.

    2. Open Integration from the navigation menu and select Connections.
        

   3. Click Create New Connection.
        

   4. Give the Connection a name and select JDBC from the drop-down list for Connection type.
        

   5. The JDBC details screen pops up with fields that need to be configured.
       The first field is labeled Database type:     

DB2

    6. Select DB2 for the database type.

    7. Configure your DB2 settings:

  • Host
  • Port
  • Database name
  • Username
  • Password

         

     8. Click Test connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

MS Sql

  6. Select MS Sql for the database type.

  7. Configure your MS Sql settings:

         

     8. Click Test Connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

MySQL

    6. Select MySQL for the database type.

    7. Configure your MySQL settings:

         

     8. Click Test connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

Hero Platform_ MySQL Connections use the UTC time zone. If the MySQL server has a time zone other than UTC, users can add the following property to the Connection:

Property nameValue
serverTimezone<Your system's timezone>

Example:

Oracle

     6. Select Oracle to connect to an Oracle database.

     7. Configure your Oracle settings:

  • Host
  • Port
  • SID
  • Username
  • Password

         

     8. Click Test Connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

PostgreSQL

     6. Select PostgreSQL to connect to a PostgreSQL database. 

     7. Configure your PostgreSQL settings:

         

     8. Click Test Connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

Qubole

     6. Select Qubole to connect to an Qubole database.

     7. Configure your Qubole settings:

  • Connection string
  • Password

        

     8. Click Test connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

Redshift

     6. Select Redshift to connect to a Redshift database.

     7. Configure your Redshift settings:

    • Host
    • Port
    • Database name
    • Username
    • Password

         

   8. Click Test connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.

Snowflake

Creating a Snowflake Connection requires first installing it's latest JDBC driver.

     6. Select Snowflake to connect to a Snowflake database.

     7. Configure your Snowflake settings:

  • Snowflake account name
  • Select your platform (AWS or Azure)
  • Select the region for your platform.
  • Username
  • Password

         

   8. Click Test connection. A confirmation message is displayed if the connection has been established with Hero Platform_.
         

     9. Click OK to finish saving the Connection in Hero Platform_.


Advanced -  Fine-Tuning a Connection

Each Connection type has its own properties for fine-tuning. Visit vendor specific documentation for their properties and values.

Hero Platform_ also offers a set of properties to fine-tune the connection pool for advanced users and use-cases.

Please don't set these properties unless you are experienced with connection pools.

Reach out to Automation Hero for support.

The following properties are exposed for users to set: 

  • pool.connectionTimeout
  • pool.idleTimeout
  • pool.keepaliveTime
  • pool.maxLifetime
  • pool.maximumPoolSize
  • pool.initializationFailTimeout
  • pool.validationTimeout

Additional information about these properties can be found on: https://github.com/brettwooldridge/HikariCP

Troubleshooting

Problem: Your Flow executions are exceeding the number of connections to your database. 

  • Cause: A Flow execution creates (at most) as many connections to the database as the number of database operations it runs. This means that each database Input/Output/Lookup running can have an active connection, though in practice it could be 30%-70% fewer. The number of database connections is then multiplied by parallelism of the Flow execution. Connections are pooled (i.e., reused) and new connections are created on demand.
  • Solution: Reduce the number of active database connections used in the Flow execution or increase the number of connections allowed to the database.


Problem: When using a MySQL Connection, I see the error: `Communications link failure`

  • Solution: It is possible that the MySQL server has a connection limit that has been reached. Ask your database administrator to increase the limit. 
  • Additional possible causes and solutions:
    • IP address or hostname in JDBC URL is wrong.
      • Verify and test them with ping.
    • Hostname in JDBC URL is not recognized by local DNS server.
      • Refresh DNS or use IP address in JDBC URL instead.
    • Port number is missing or wrong in JDBC URL.
      • Verify it based on my.cnf of MySQL DB.
    • DB server is down.
      • Start the DB.
    • DB server doesn't accept TCP/IP connections.
      • Verify if mysqld is started without the --skip-networking option.
    • Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
      • Disable firewall and/or configure firewall/proxy to allow/forward the port


Problem: When using a MySQL Connection, I see the error: `Deadlock found when trying to get lock; try restarting transaction`



Search