[How To] Setup the Compass JDBC Driver

Hey all,

Time to revisit some of the [How To] tutorials and I've seen quite a few members on the forum as well as meeting some of you ask how you can start using the Compass JDBC driver to experiment with development setup and querying the Data Lake. We'll take a look through how and where to access the download, setting up our authorized app in the ION API Gateway, and using a universal SQL editor to start querying our Data Lake.

Also, be sure to take a look @ KB 2103864 (Setup and configure the Infor Compass JDBC Driver) if you're looking to grab resources directly from the Infor Support Portal.

Downloading the JDBC Driver

Editor's Note: Before we get started, note that the Compass JDBC driver is only visible & accessible for download if you've purchased Infor entitlements for data egress out of the Cloud. Because the driver is ultimately moving data out of our cloud boundaries, data moved out is metered against this license. If you don't see the Compass JDBC driver available for download, reach out to your account rep and they can get you sorted out.

Let's jump over to the Infor Support Portal, log-in, and navigate over to Products > Downloads. A new tab should open to the Product Download Center. You'll see the GIF below probably has more download selections than you have available but you'll want to drill down to Infor SaaS Add-Ons > Technology > Infor OS (formerly Xi Platform) > Infor Compass JDBC Driver.

Just simply click the link on the .zip file and you'll begin your download. The .zip includes:

Copyright documentation (Copyright.html)
Third party license documentation (DataLakeThirdPartyLicense.html)
Compass JDBC .jar file (infor-compass-jdbc-{version}.jar)

Because much of the platform is server-side, the JDBC interface is fairly thin and isn't updated with every Infor OS Cloud release. You'll often see, as is the case at the time of writing, that the version of the JDBC downloaded was released in December of 2019. We publish updated copies of the JDBC when new authentication options or client-side code is released.

I would recommend creating a new directory somewhere on your machine and copy the .jar file over to this new directory. For example:

Windows Users: C:Infor Data Lake
Mac Users: ~/Library/Infor Data Lake

Setting up our Authorized App

The JDBC doesn't use Basic Authentication (username and password) to authenticate against the Data Lake. Rather, we use a credentials file generated from the Infor ION API Gateway that impersonates our IFS user account. That means we'll have to create a new authorized app in the gateway and download a credentials .ionapi file built around a service account.

Navigate over to the App Switcher and select the Infor ION API application. In the left-hand navigation menu, select Authorized Apps and click the + to create a new application.

Name*: Infor Compass JDBC Driver
Type: Backend Service
Description: Infor Compass JDBC Driver

* You must use this name format when creating your authorized app. If you don't name this correctly, this may cause issues when trying to authenticate against the Data Lake.

Once you save your authorized app, select the Download Credentials button and hit the Create Service Account slider before entering in your IFS Username. Select the DOWNLOAD button and you'll now have the Infor Compass JDBC Driver.ionapi in your notebook's Download Folder.

Cut or copy your new .ionapi credentials file over to your C:Infor Data Lake or ~/Library/Infor Data Lake directory

Configuring a Universal SQL Manager

Now, we've just got to download and/or configure your universal SQL manager of choice to connect to the Data Lake using the .jar & .ionapi files we've bundled together in our folder. There's a lot of options out there -

DBeaver - https://dbeaver.io/ (my personal preference)
SQuirreL - http://squirrel-sql.sourceforge.net/
SQL Workbench - https://www.sql-workbench.eu/

Keep in mind these are open source, 3rd party tools and Infor makes no claims around support, configurability, and so forth regarding usage of them.

We're going to be using DBeaver during this [How To] as it has a similar "look and feel" to other SQL products out there like Microsoft SQL Management Studio or Oracle SQL Navigator. We'll be creating our own custom driver reference within DBeaver that will connect back to the Infor Data Lake.

Let's launch DBeaver and from the application's menu, select Database and then Driver Manager. Select New and use the following information to enter in your driver details:

Name: Infor Data Lake (or anything of your choice)
Driver Type: Generic
Class Name: com.infor.idl.jdbc.Driver
URL Template*: jdbc:infordatalake://{tenantname}
Libraries:
- Select the Add File button and select your JDBC driver path C:Infor Data Lakeinfor-compass-jdbc-{version}.jar
  ~/Library/Infor Data Lake/infor-compass-jdbc-{version}.jar
- Select the Add Folder button and select the "Infor Data Lake" folder that stores your .jar and .ionapi file

Select the OK button to complete set-up of your new driver.

Selecting the .jar file step may be more obvious than the reason why we're also selecting the parent directory hosting these files. By adding in the folder, DBeaver now knows which files and folders are being used to coordinate authentication on your behalf over to the server.

* Remember, you've set-up a service account within a specific tenant that'll be used to authenticate your session against that tenant. If your tenant name is "ACME", then your URL template would be jdbc:infordatalake://ACME

So, we've set up our driver and DBeaver now recognizes how to connect to the Data Lake. Let's go ahead and actually test to see whether we can get a connection to the Data Lake.

In DBeaver's Database Navigator pane on the left, select Create > Connection and filter for your new driver name. If you followed the naming convention above, filtering for "Infor Data Lake" should work here. If you don't see your driver listed as an option, make sure you're driver filter on the left is set to All and not Popular.

Select the Infor Data Lake and select the Next button. You'll see your JDBC URL already populated. Because we're using .ionapi to authenticate, you do not need to enter in a username or password. Simply select the Finish button.

Querying the Data Lake & Additional Notes

Equally familiar to most SQL management interfaces, you'll now see you have a connect to the Infor Data Lake within DBeaver's Navigator pane. You can start expanding the tree to see a list of all of the Tables within Data Catalog and even start querying the tables themselves, providing a relational view on top of your lake. All of the same queries you can run within Compass UI and APIs can be equally done here. Note that Views, Stored Procedures, and functions are not supported by the Data Lake at this time.

The Compass UI within Infor OS is an excellent tool for prototyping queries but there are limitations including a 100 record default limit at this time. Having the JDBC driver is a useful tool for developers and applications to begin working with the Data Lake and provide more robust dataset retrieval than is possible through the Compass browser interface.

I'll leave a final comment here - always remember that your .ionapi file is tied to the service account that's impersonating a user registered within IFS. The JDBC driver shouldn't be distributed to normal, everyday functional & power users because this is a heavily-oriented developer deliverable. I would certainly recommend that for any user that needs to use the JDBC driver (colleague developers), they follow the same steps for their own .ionapi generation

Find more posts tagged with

Infor Data Fabric

Comments

Legacy Contributor

Hi Mike, thanks for the instructions! I was wondering do you have some suggestions to resolve "Null Connection Returned"? I am not sure what's supposed to go into the Website URL of the driver. mingle-portal.inforcloudsuite.com/tenantname? I appreciate your help as this is very new to me.

Maggie

Legacy Contributor

Hey Maggie,

What client are you using to try and connect to Data Lake via Compass? Rather than adding in a fully qualified URL, you should be entering in jdbc:infordatalake://tenantname

It may be easier to follow along with a video tutorial so check out www.youtube.com/watch

Legacy Contributor

Hi Mike, great write-up!

One question -- i've noticed that when browsing data lake objects/tables using both Squirrel and DBeaver, the table doesn't seem to appear in the object listing until after it is initially queried. Do we need to run a query against each table to get it to show in the table/object listing?

For example, using Compass UI in ION Desk, I see the ln_cisli310 table in the object listing, and I can autogenerate a query for it. I haven't actually run the query yet. In DBeaver, i don't see the ln_cisli310 table listed yet. Once I run the query in Compass UI, I then refresh the object/table listing and the table shows up in the default listing. It seems like it takes a query in order for the object to show up in the object listing pane.

One other thing I found is that even if the table/object is not showing up in DBeaver or Squirrel's object browser, I can still run a query against it, and then afterwards refresh the listing in DBeaver/Squirrel, and it appears.

Thanks!

Legacy Contributor

Disregard above question. We're now seeing all objects show up in the object browser in Squirell and Dbeaver, without manual intervention. Thanks!

Legacy Contributor

Hey there, rpow!

We've identified a bug that snuck out with Infor OS 2020-09 where we aren't always retrieving a full list of tables and columns. This month's 2020-11 update fixes that. It's not a driver issue so you won't need to update or swap out the driver

You're still able to query any objects you have in Data Lake even if you don't see the table name listed in your client. Unfortunately, a minor silly issue that impacted retrieving the complete list of tables that you can query.

Legacy Contributor

Hi Mike, Thanks for the detailed instructions. I've followed all the steps, but when I'm trying to connect it gave me the below error.

ClassCastException: attempting to castjar:file:/C:/Infor%20Data%20Lake/infor-compass-jdbc-2020-09.jar!/com/infor/idl/shaded/javax/ws/rs/client/ClientBuilder.class to jar:file:/C:/Infor%20Data%20Lake/infor-compass-jdbc-2020-09.jar!/com/infor/idl/shaded/javax/ws/rs/client/ClientBuilder.class

Legacy Contributor

I had gotten this at one point as well. What resolved it for me if I recall correctly was that the URL I had set was for a tenant that was not same as the tenant where the .ionapi file was generated out of. If you are using multiple tenants (TRN, TST, PRD), maybe double check that the URL and the credentials file are for the same tenant.

Hope that helps!

Legacy Contributor

@rpowers is on to something. If working with DBeaver, this is unfortunately one area where they cannot balance multiple tenant configurations. Other universal SQL tools like SQuirreL and SQL Workbench/j have better class loader handling and you can define multiple tenant connections without incident.

If using DBeaver, you'll typically need to swap out the tenant in your connection as well as the .ionapi file being used in the defined connection anytime you want to switch tenants you're querying.

What DBeaver provides in usability and elegant experience, they make up for in reduced functionality in some cases

Legacy Contributor

I got this error also. Here is how I fixed

The .ionapi file that gets downloaded from Infor has the incorrect filename. you need to remove the underscore from the beginning of the filename:

NO: “_Infor Compass JDBC Driver.ionapi”

YES: “Infor Compass JDBC Driver.ionapi”

Hope this is helpful.

Legacy Contributor

Thank you for this "manual", great stuff

tdouglass

Hi, this article is great, but we are trying to get the etl/extract process automated through an Azure Data FActory or Logic App application and it has not been successful. We can get to work manually in postmaster but not automated in other tools. Any suggestions? Many websites/utubes but not specifically for Azure Data Factory.

Rene Bottern

Have you looked at "Infor ION ETL for DataLake"?

This Tool allows you to extract specific Tables to a local SQL instance, which then can be queried (performance is also great that way).

tdouglass

Yes. We were trying save the cost of bringing up a new database on the cloud and server for the repository, since we already had Azure Data Factory and have some other ETL there, knowing we would have to write the code to get the most recent variation. We were going to pilot both ways.

Rene Bottern

If you are planning on extracting to a separate Database instance, JDBC is NOT the way to go, since it's extremely slow and also have record limitations.
Either use the ETL API's or get the ETL Tool I mentioned above, it works great for me, since it extracts incrementally (meaning only changes since last run) so the extract can be run every 15 minutes if necessary.

Copyright © 2025 Infor. All rights reserved.