Setting Up a JDBC or ODBC Connection from Domino (Including Informatica Connections)

katie.shakmankatie.shakman Administrator, Domino Posts: 226 admin

You can use Domino to connect to essentially any data source that is accessible from a Linux machine. For a few types of connections, such as Informatica JDBC (Java Database Connection) and ODBC (Open Database Connection), as well as Informatica data sources that use these connection formats, you may need some guidance on what setup steps to take before trying to connect to your data source. This guide aims to give you a brief, easy-to-follow set of steps to get up and running:

  1. Determine the programming language you'll primarily use to interact with the data source in your Domino project, e.g. Python, R, SAS, MATLAB, etc.
  2. Identify the package that your chosen language uses for JDBC/ODBC. In the case of Python, a common choice is pyodbc, but there are other options available.
  3. If you are using Informatica, you will likely need to obtain a driver file named infadsjdbc.jar from your Informatica administrator or account owner. (See pg 55 of this guide for more information about this jar file and how it is used in the Linux configuration steps for JDBC/ODBC.)
  4. If you are using Informatica, host the jar file from Step 3 in a location that is accessible to Domino's compute environment build process. A good choice is web location that can be accessed with a presigned URL or a public project within your Domino deployment (you can make the project private after the compute environment is built). If those options are unsuitable, speak with your Domino account team for other options to make the jar file into the compute environment.
  5. Edit your compute environment (or Duplicate the compute environment you use and customize your own). You will need to add the appropriate packages for your language to your compute environment's Dockerfile Instructions. For instance, if you want to use Python you would add the following to add pyodbc: `RUN pip install pyodbc`. Note that if you are building off of a Domino Analytics Distribution (or DAD base image), it will usually have pyodbc and other common drivers/db connection packages preinstalled.
  6. If you are using Informatica you will also need to add the jar and the jaydebeapi package as follows: `RUN java –jar infadsjdbc.jar && pip install JayDeBeApi`
  7. Click Build at the bottom of your compute environment to apply the changes. Make sure that the build completes successfully.
  8. Set your project to use your new compute environment (or make sure it's using the one you edited), and test the connection from an interactive workspace in the project. For example, to connect to an Informatica SQL service using Python you could open a Jupyter workspace and try code similar to the following:
import jaydebeapi
conn = jaydebeapi.connect("com.informatica.ds.sql.jdbcdrv.INFADriver",
                         "jdbc:informatica:sqlds//@<YOUR-DATABASE-URL>",
                          {'user':'xxxxxx','password':'xxxxxxx'}, "infadsjdbc.jar",)
curs = conn.cursor()

Did this help you? Do you have questions or suggestions? Please let us know below.

Looking for Impala, Snowflake, or MSSQL? Check out these guides as well:


Katie

Field Data Scientist @ Domino

Sign In or Register to comment.