TABLE OF CONTENTS
- Installing DBeaver on Windows
- Install DBeaver on macOS
- Download Drivers
- Connecting to Ocient DB
- Managing Ocient JDBC Driver Version
- Managing Large Query Return Sets
- Configuring Time Zones for DBeaver
- Collecting connection debug log
Introduction - DBeaver
DBeaver is a freely available open-source project that provides a UI for interfacing and interacting with SQL Databases. It is available for a range of Operating Systems, with this guide focusing on installing DBeaver on a Windows or macOS platform and configuring it to run against an Ocient Database.
Installing DBeaver on Windows
The latest Community Edition of DBeaver installer for Windows can be downloaded from https://dbeaver.io/files/dbeaver-ce-latest-x86_64-setup.exe. Other DBeaver download options are available on their download page at https://dbeaver.io/download.
The Community Edition is freely available and is the version that this guide focuses on.
Once it has been downloaded, double click on the executable file to start the installation.
- Select the installation language and click OK to start the installation process:
- Click on the Next button on the welcome screen:
- Review the presented license agreement and click on “I Agree” to start the installation process.
- Select if the software should be installed for current user or all users of the system and click Next. The previous two steps will be presented again if the installation option for “all users” is selected.
- Choose the components to be installed and click Next. At minimum “DBeaver Community” should be selected.
- Update the Destination folder location if desired and click Next.
- Update the Start Menu folder if desired and click Next.
- The software installation will now start, and progress bar will be displayed.
- An option to create a desktop icon will be presented once the installation completes. Click on the Finish button to close the installer.
Install DBeaver on macOS
The following section details the steps required to download DBeaver on the macOS platform.
DBeaver can be downloaded from the DBeaver download page, dbeaver.io/download/.
The Community Edition is freely available and is the version that this guide focuses on.
Select the Disk Image File (dmg) option, and begin to download it:
Once it has been downloaded, open the DMI, wait for macOS to verify the contents, and then drag the DBeaver application into the macOS Applications folder:
Close the installer and unmount the DBeaver Disk Image File.
DBeaver can then be launched.
Ocient driver needs to be in installed before DBeaver can be used to access Ocient database. The following procedure walks through launching DBeaver for the first time and performing initial configuration.
- The following screen is displayed the first time DBeaver is launched. It is not necessary to create a sample database to access Ocient cluster.
- Once the DBeaver is launched, select “Driver Manager” under Database Menu.
- Type “Ocient” in the Driver Manager search bar, select Ocient and then click on the Edit button:
- Click on “Download/Update” to install the latest version of Ocient JDBC driver.
- Click on the Download button in the launched window to start the driver installation process. Click on the “OK” button once the driver Download completes.
- Click on the “Connections properties” tab followed by right clicking in the empty area and selecting Add new property.
- Enter “tls” as the property name and click OK.
- Click under the value column next to the “tls” property and type “on” in the value field
- Click OK to close the “Edit Driver” window.
Connecting to Ocient DB
- Launch DBeaver if not already running, navigate to “Database” menu and select “New Database Connection”.
- Select the “All” tab, type “Ocient” in the search bar, select “Ocient” and click on Next.
- Enter values in “Generic JDBC Connection Settings” window and click on the “Test Connection” button. A password prompt will appear if the “Save password locally” box was not checked.
Host: IP Address or FQDN (Fully Qualified Domain Name) of Ocient SQL node.
Database/Schema: Name of database.
Username: Username with access to the database.
- If connecting from behind a proxy server, add “force” property in the “Driver properties” tab as below:
- Navigate to the “Driver properties” page and click on the ‘+’ icon towards bottom left of the tab.
- Set the Property Name as “force” and click on the OK button.
- The newly added property will show up under the “User Properties” section in the “Driver properties” tab. Click on the space next to the property name in the Value column and enter “true” as the value and then click on the “Test Connection” button.
- The following screen will be displayed if a successful connection is made to the database.
- Click on the “Finish” button to close “Generic JDBC Connection Settings” window. The just created database connection will now show up in the left-hand side navigation menu.
- Double click on the database name and enter password if needed to connect to Ocient DB via DBeaver.
Managing Ocient JDBC Driver Version
It’s possible that by following the above steps a version of the Ocient JDBC Driver is installed that is not current, and more steps are needed to correct the Ocient JDBC Driver that DBeaver uses.
Over time new versions of the JDBC Driver will be released and the recommended version guidance may shift.
The following steps provide instructions for locating a different numbered version of the Ocient JDBC Driver, and then configuring DBeaver to use that version.
There are 2 methods to managing the version of Ocient JDBC Driver being used; the first is to allow DBeaver to automatically search out drivers and apply them, and the alternative is to download a version and manually configure DBeaver to use it. These are referred to as the automatic process and manual process hereafter.
In all circumstances it is preferred that the management of the Ocient JDBC Driver be done via the automatic process as this is simpler to maintain; the alternative steps for the manual process are provided for instances when, for whatever reason, the automatic process cannot be used.
Automatic Process: Using DBeaver to Manage Ocient JDBC Driver Version
By following these steps DBeaver will automatically locate, download, and apply an Ocient JDBC Driver version:
- From DBeaver select Database -> “Driver Manager”
- Select Ocient from the list of Databases, and click “Edit …”
- Select “Download/Update”
- In the “Version” drop down ensure the required version is selected, and click “Download”
- Ensure the appropriate version of the Driver is selected and click “OK” through the rest of the screens.
- DBeaver should now be set to use that version of the driver.
Manual Process: Downloading a Different Ocient JDBC Driver
If the above automatic process does not work, the following manual process details the steps required to manually download a driver and configure DBeaver to use it.
Key releases of the Ocient JDBC Driver are released and archived on Maven. However only specific bundles for each release will work.
All of the Ocient JDBC releases are stored on Maven, to make them centrally accessible. Follow these steps to download a specific release:
- Navigate to the Maven page for Ocient’s JDBC Driver:
- Locate the version required in the list, and click the version number link
- On the “Files” entry, click the “View All” link:
- Download the version that includes dependencies:
- Example for release 1.88:
Manual Process: Configuring DBeaver to use a Different Ocient JDBC Driver
With a different version of the Ocient JDBC Driver downloaded the next step is to configure DBeaver to use that driver file.
- From DBeaver, disconnect any active connections that may have been established.
- Right click the Database entry in the Database Navigator panel, and select “Edit Connection”
- Select the “Edit Driver Settings” button:
- The Driver version will be in the “Libraries” list.
- Select “Add File” and navigate to the version downloaded in the previous steps.
- Select the old version and click “Delete”
- Click “OK”
- DBeaver will now use this version of the driver for the configured connections.
Managing Large Query Return Sets
DBeaver is capable of running a range of queries and has scripting capabilities for numerous use cases of driving queries against a Database, but there are some additional considerations that should be made if queries being executed will yield very large result sets.
Configuring DBeaver’s MaxRow limit
There are several ways that limits intended to control the maximum result set an individual query can return can be managed. The Ocient Database, for instance, has limits built into the Service Classes, and it’s possible to pass other limits through the JDBC Driver.
One other limit that needs to be lifted is in DBeaver itself. By default, DBeaver limits the payload response to 200 rows, so if there is a plan to exceed this value, this limit should be adjusted to something more appropriate.
To adjust this:
- Open the main DBeaver Preferences Window
- Select Editors -> Data Editor from the navigation panel on the left
- Adjust the value stored in the “ResultSet fetch size”
- Finally Apply and Close the new setting.
Output Query Result Set to a File
By default, DBeaver will display the results of queries in the UI, under a “Results” tab. This is again, suitable for eye-balling results, but less useful when dealing with queries that return many rows of data.
It is possible to configure DBeaver to output the results directly to a formatted file, such as CSV, without displaying the data internally.
To do this, once the query is written in DBeaver, follow these steps:
- Right click the query, and select Execute -> Export from Query
- A wizard appears with numerous options for how to export the data. Follow the wizard selecting the options appropriate to the use case.
- On the final screen of the Wizard note the output location and output name pattern.
- Click “Start”
- The query will execute, and a file will appear at the location noted in step 3
Configuring Time Zones for DBeaver
When using DBeaver against a Database that is bucketing on a time key, such as Ocient is in this use case, it may be critical to configure DBeaver to use the same time zone as the Ocient database is when testing application queries.
The simplest way to prevent DBeaver from doing additional formatting and conversion on the date and time is to disable this option from the Preferences:
- Go to DBeaver -> Preferences
- Navigate on the tree on the panel on the left to Editors -> Data Editor -> Data Formats
- Check the box labelled “Disable date/time formatting
- Click Apply and Close
Collecting connection debug log
The connection logs are helpful in troubleshooting connection issues. The connection logs can be collected by setting the “logfile” and “loglevel” Driver properties for a driver.
- Right click on the connection for which connection logs are needed and click on “Edit Connection”
- Click on the “Driver Properties” tab:
- Set logfile as absolute path of the file where you want the logs stored and set loglevel to DEBUG and click OK.
- Double click on the connection to connect. Once the connection is established/failed, close DBeaver and provide the log file to support.