Connecting to IBM Netezza from Domino




Overview

This article describes how to connect to IBM Netezza from Domino.




Python and nzpy

Domino recommends the nzpy package for interacting with Netezza from Python.


Environment setup

Use the Dockerfile instruction below to add nzpy to your environment.

This instruction assumes you already have pip installed.

USER root
RUN pip install nzpy
USER ubuntu

For a basic introduction to modifying Domino environments, watch this tutorial video.


Credential setup

There are several environment variables you should set up to store secure information about your Netezza connection. Set the following as Domino environment variables on your user account:

  • db_username

  • db_password

Read Environment variables for secure credential storage to learn more about Domino environment variables.


Usage

Read the nzpy docs for detailed information on how to use the package. Below is a simple example for connecting to Netezza with nzpy where:

  • you have set up environment variables noted above with the db_username and db_password

  • you’ve replaced ‘my.host.name’ with the host name for your machine

  • you’ve replaced ‘my.database.name’ with the database name for your machine

import nzpy
import os
import pandas as pd

hostname = 'my.host.name'
database_name = 'my.database.name'
port = 5480
username = os.environ['db_username']
password = os.environ['db_password']

def query_db(sql):
   conn = nzpy.connect(user=username, password=password, host=hostname, port=port, database=database_name)
   with conn.cursor() as cursor:
      cursor.execute(sql)
      df = cursor.fetchall()
      return df

sql_cmd = """

SELECT
 *
FROM
 table

"""

df_cmd = query_db(sql_cmd)

df_cmd



R and RJDBC

Domino recommends the RJDBC package and nzjdbc jar for interacting with Netezza from R.


Environment setup

Use the Dockerfile instruction below to add RJDBC to your environment.

USER root
RUN R -e 'install.packages("RJDBC")'
USER ubuntu

For a basic introduction to modifying Domino environments, watch this tutorial video.


Credential setup

There are several environment variables you should set up to store secure information about your Netezza connection. Set the following as Domino environment variables on your user account:

  • db_username

  • db_password

Read Environment variables for secure credential storage to learn more about Domino environment variables.


Usage

Test if the nzjdbc jar is working as expected by running the below in a workspace terminal

java -jar <path.to.the.nzjdbc.jar> -t -h my.host.name -p 5480 -u <username> -db <database_name>

Below is a simple example for connecting to Netezza with RJDBC and nzjdbc jar where:

  • nzjdbc jar is present in the project files (/mnt/nzjdbc3)

  • you have set up environment variables noted above with the db_username and db_password

  • you’ve replaced ‘my.host.name’ with the host name for your machine

  • you’ve replaced ‘my.database.name’ with the database name for your machine

library(RJDBC)
drv <- RJDBC::JDBC(driverClass = "org.netezza.Driver", classPath = "/mnt/nzjdbc3.jar")
conn <- dbConnect(drv, 'jdbc:netezza://my.host.name:5480/my.database.name;logLevel=2',Sys.getenv('db_username'), Sys.getenv('db_password'))
dbListTables(conn)