About Domino
Domino Data LabKnowledge BaseData Science BlogTraining

Connect to IBM Netezza

You can connect to IBM Netezza using a Data Source or directly.

IBM Netezza Data Source

Your administrator must create a Data Source for this data store for you to use.

After your administrator has created the Data Source, you can work with it in your project as usual.

Direct connection

Connect directly to IBM Netezza from Domino. You must have network connectivity between Netezza and your Domino deployment.

Warning

Python and nzpy

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

Environment setup

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

This instruction assumes you already have pip installed.

RUN pip install nzpy

Credential setup

You must set the following Domino environment variables to store secure information about your Netezza connection

  • db_username

  • db_password

See Secure Credential Storage to learn more about Domino environment variables.

Usage

See the nzpy docs for detailed information about how to use the package. The following is an 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 following Dockerfile instruction to add RJDBC to your environment.

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

Credential setup

You must set the following Domino environment variables to store secure information about your Netezza connection

  • db_username

  • db_password

See 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>

The following 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)

Next steps