Configuring Connections in Aginity Pro¶
Aginity Pro allows you to set up connections to a variety of different database platforms. In order to do this, click the + symbol next to the “Connections” header in the left navigation pane of Aginity Pro. You will be presented with a list of existing connections, as well as options for adding another connection. Clicking the “New Connection” button will allow you to enter the necessary information for the connection you wish to create.
Below is a quick start video of creating connections in Aginity Pro
Aginity Pro uses JDBC to connect to database platforms. It is recommended you refer to the JDBC guides for our supported platforms to learn about specific variables to use within the Advance Parameters section of Aginity Pro. We will provide links to that documentation where available.
The video below provides a more detailed explanation of how to use advanced parameters to illicit the desired behavior and successful connection to the database platform.
The first step in configuring a connection is to download the JDBC driver for the platform you will be using. In most cases, we will provide access to the drivers which have been certified to work with Aginity Pro. In some cases such as when the vendor does not allow us to distribute or you want to use a different version, you will have to manually add the JDBC driver.
The video below provides a quick illustration of how to manage drivers.
To get started, in the Edit or Add Connection box you will click on Edit Drivers as shown below
As shown in the image below, if we are allowed to to download drivers you will be provided a link to do so and then be presented with one or more drivers to use and your done.
In some cases you may get an error message if your organization does not allow you to download drivers (jar files) typically supplied through a Maven repository.
In this case please review the section below Manually Add Drivers.
Manually Add Drivers¶
If you have to manually add a driver it starts with first acquiring the JDBC jar for that platform and saving it somewhere on your machine. Once you acquire the JDBC jar you can upload it to Aginity Pro.
To do this you will hit the Edit Driver or Edit Drivers Panel section as shown above and you will be presented with the following screen.
You can name the Driver anything you want and provide a description.
The class name if not provided must be looked up typically in the JDBC reference manual from the platform vendor.
Finally if you click on the Add File button you will navigate to where you saved the Jar file and then click Save Changes. You are now ready to configure your connection.
Setting up Netezza Connections¶
You can set up a connection to a Netezza (IBM PureData for Analytics) appliance here. Simply populate the requested data into the form.
Netezza does not allow us to distribute their drivers so you must obtain it from your DBA or directly from IBM Fix Central.
Using SSL with Netezza Connections¶
Aginity Pro supports SSL when setting up a Netezza connection. While setting up your Netezza connection simply select the level of security to enable from the dropdown selector. Your choices include:
Only Unsecured - Specify this option when you want an unsecured connection to the Netezza system. If the Netezza system requires a secured connection, the connection is rejected.
Preferred Unsecured - This argument is the default value. Specify this option when you would prefer an unsecured connection, but you accept a secured connection if the Netezza system requires one.
Preferred Secured - Specify this option when you want a secured connection to the Netezza system, but you accept an unsecured connection if the Netezza system is configured to use only unsecured connections.
Only Secured- Specify this option when you want a secured connection to the Netezza system. If the Netezza system accepts only unsecured connections, or if you are attempting to connect to a Netezza system that is running a release before release 4.5, the connection is rejected.
For more information on SSL refer to the link Netezza SSL
The connection timeout will tell the session when to disconnect. Typically set this to 10 minutes or 3600 seconds.
Setting up Redshift Connections¶
Using SSL Mode with Redshift Connections¶
Aginity Pro supports SSL when setting up a Redshift connection. While setting up your Redshift connection simply select the level of security to enable from the dropdown selector. Your choices include:
- Disable - SSL is disabled and the connection is not encrypted.
- Prefer - SSL is used if the server supports it. Amazon Redshift supports SSL, so SSL is used when you set sslmode to prefer.
- Allow - SSL is used if the server requires it.
- Require - SSL is required.
For more information on Redshift SSL refer to this link
Setting up Snowflake Connections¶
You can set up a connection to a Snowflake here. Simply populate the requested data into the form.
A virtual warehouse, often referred to simply as a “warehouse”, is a cluster of compute resources in Snowflake. A warehouse provides the required resources, such as CPU, memory, and temporary storage, to perform the following operations in a Snowflake session. If you do not know which specific Warehouse to use speak to your Snowflake DBA.
This is a security role that may have been setup for you to use. You can leave this field blank if you do not know what Role to connect as.
For Snowflake most connection detail is handled through the command line additions to the JDBC URI. You can supply additional JDBC parameters using the Advanced Properties section.
Here is the link to other configurable parameters supported within Snowflake JDBC’s driver
Setting up Hive Connections¶
You can set up a connection to a Hive databases here. Simply populate the requested data into the form.
You can choose either Hive 2 or Spark SQL syntax when executing against Hive databases. You can choose either value in this drop down.
Setting up Kerberos connections¶
In a lot of cases organizations require the use of Kerberos with Hive so by clicking on the radio button for Use Kerberos for authentication you will be prompted to enter the following:
- Service name
The value of these parameters are typically set by your Hadoop administrator and it is best to work with them to obtain the appropriate values.
Setting up SQL Server Connections¶
You can set up a connection to Microsoft SQL Server databases here. Simply populate the requested data into the form.
We only support SQL Server compatibility levels 150 and 140 which is SQL Server 2017 and greater.
Setting up DB2 Warehouse Server Connections¶
You can set up a connection to IBM DB2 warehouse databases here. Simply populate the requested data into the form.
Setting up Greenplum and Postgres Connections¶
You can set up a connection to Greenplum and Postgres databases here. Simply populate the requested data into the form. Greenplum and Postgres share the same drivers so we will speak of them together.
Choosing Subprotocol (Only for Greenplum)¶
You can choose either generic Postgres or Pivotal Greenplum SQL syntax when executing against Greenplum databases. You can choose either value in this drop down.
Import and Exporting Connections¶
A new feature in Aginity Pro is the ability to import and export your connection templates and share them with other users. A good deal of inefficiency with SQL IDE’s is the time it takes to get connected. No longer. With the connection templates you can save all the relevant detail minus the login credentials and distribute them through an easy import and export feature.
To get started have a DBA or power user establish the connection details once and export the connection as shown in the image below.
You will be presented with a selection list of connections as shown below. Select the connections you want to export and click Export selected
It is important to either check the radio button include sensitive data or to uncheck it. This will include the username and password or exclude it from the connection export.
You will be presented with a file menu window to name and pick the folder location for the export file as shown below.
Once you have exported the connections you can send them to Aginity Pro users and have them import them. To do that choose the Import Connections menu item as shown below.
You will be presented with a file selection. Choose the appropriate Aginity connection package.
By default Aginity packages for both Catalog and Connections are prefaced with .aginitypkg file types.
Once you choose a file select the connections you want to import and choose Import Selected.