JDBC connection string for Netezza

Question

Hi. I am using Netezza as my database for sqoop. What connection string should I use to make connections between sqoop and Netezza database? Thanks

Answer 1

Hi. The syntax of the connection string format to be used for Netezza is as follows:

jdbc:netezza://<HOST>:<PORT>/<DATABASE_NAME>

Refer to the below example:

jdbc:netezza://my_netezza_server_hostname:5480/my_database_name

Answer 2

HI..

I HOPE BELOW INFORMATION MAY HELP YOU-

To connect to a JDBC data source, you must create a connection string.

A Netezza JDBC connection string consists of the following parts in the following order:

• jdbc:netezza:// (mandatory).
• A host name (mandatory) and a port number (optional if you are using port 5480), followed by a forward slash (/). If you specify a port number, separate it from the host name with a colon (:).
• A database name followed by a semicolon (;) (mandatory).
• Property names and their values, specified as name=value pairs (optional). Separate a name=value pair from the next name=value pair by using a semicolon (;).

An example follows. In the example, main is the host name, and sales is the database name.

jdbc:netezza://main:5490/sales;user=admin;password=password;loglevel=2;logdirpath=C:\

The following table contains the properties and values that you can specify when creating the connection string. The properties and values are not case sensitive.

Table 1. Connection string properties and values

Property	Description
applicationName	Specifies the application name.
autocommit	Specifies whether autocommit applies to the connection. Valid values are true (the default) and false.
batchSize	Specifies the number of rows to be fetched in a cycle. The default is 256.
caCertFile	Specifies the name of the certificate authority (CA) file that is used to authenticate connections. If you use a CA for authentication, the name of the CA file must match the name that the security administrator defined for the database to which you are attempting to connect. For more information about security and authentication, see the IBM® Netezza System Administrator’s Guide.
clientHostName	Specifies the client host name.
clientUser	Specifies the client user.
database	Specifies the database name.
DatabaseMetaData	Specifies whether you can get metadata information that is not restricted to the current catalog and schema. Valid values are false (the default, meaning that the information is restricted to the current catalog and schema) and true.
description	Specifies the description for the data source.
dsn	Specifies a data source name. If you specify this property, the connection string properties and values in the nzjdbc.ini file that are associated with that data source are used, unless you specify those properties and values directly in the connection string. For example, if you specify the connection string jdbc:netezza//localhost/dsn=nzsql, the properties and values that are associated with the nzsql data source name in the nzjdbc.ini file are used, unless you specify the properties and values directly in the connection string. You can create the nzjdbc.ini file by using a GUI on Windows operating systems or command-line prompts on Linux and UNIX operating systems.
host	Specifies the name of the host to connect to.
ignoreUpdateCount	Specifies whether the JDBC driver is allowed to export more than two billion rows (the maximum int value). Valid values are off (the default) and on.
krbLoginModule	Specifies the entry module name of the set of Kerberos properties from the login.conf file. The JDBC driver uses the login module class to create authentication tickets.
krbSSODelegation	Specifies that if you configure an IBM Cognos Business Intelligence application in Kerberos SSO mode, the JDBC driver uses the authentication ticket that the Cognos product provides.
logDirPath	Specifies the log directory into which the log files are written.
loggerType	Specifies the type to be used for logging. Valid values are TEXT (the default) and HTML.
loginTimeout	Specifies the maximum wait time to make a connection. The default is infinite.
logLevel	Specifies the log level for the connection. The default is OFF.
port	Specifies the port number.
readonly	Specifies whether the connection is read only. Valid values are false (the default) and true.
securityLevel	Specifies the security level. Values are as follows: onlySecured Specifies that only connections that use SSL to encrypt data are accepted. This security level increases security but reduces performance somewhat, because of the necessity of encrypting the data stream. onlyUnsecured Specifies that only connections that do not use SSL to encrypt data are accepted. This security level can help increase performance but carries the risks that come with an unencrypted connection. preferredSecured Specifies that connections that use SSL are preferred but that a connection is not refused solely because it does not use SSL encryption. This value is the default. preferredUnsecured Specifies that connections that do not use SSL are preferred but that a connection is not refused solely because it uses SSL encryption.
schema	Specifies the name of the schema within the specified database on the Netezza® system. This property is used for Netezza systems that support multiple schemas within a database. If you do not specify a schema, you connect to the default schema of the database. ImportantCalling the setSchema method to specify a new schema causes all future queries, including those using previously created or prepared Statement objects, to use the new schema. This behavior is different from that documented in the Oracle JDBC specification.
user	Specifies the user name to connect to the database.
password	Specifies the password for authentication.

Answer 3

HI..

Netezza is one of the widely used MPP databases. You connect to it by using various methods and programming languages. Netezza supports ODBC, OLEDB, and JDBC drivers for connections. Connection to Netezza using a JDBC driver is easy and one of the widely used methods. In this article, we will check how to connect Netezza using JDBC driver and some working examples.

Netezza JDBC Driver

Netezza provides a JDBC driver, you can use that driver from any programming language that supports JDBC connections such as Java, Python, etc. You can download the JDBC driver from IBM to fix the central site. You should have a JDBC jar by the name nzjdbc.jar.

Alternatively, you will get software packages if you purchase Netezza or IBM PureData Systems for analytics.

Before going deep into using Netezza JDBC driver, you need to install jaydebeapi module into python. Though you can use any module that supports JDBC drivers, jaydebeapi is one of the easy module that I have been using.

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to databases using Java JDBC. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

pip install Jaydebeapi

Set CLASSPATH to Driver Location

As we have no other dependent jar for this Netezza JDBC driver, you can directly refer to this driver in your jaydebeapi module. Alternatively, you can export jar location to the CLASSPATH shell variable and run your python program without needing to set jar location in your module.

How to Connect Netezza using JDBC Driver?

In this section, we will discuss how can we connect Netezza using JDBC driver. I will be using python and jaydebeapi to execute the Netezza JDBC driver.

Once you have Netezza jar in place and installed the required modules, you are ready to access Netezza from within your Python program using a JDBC driver.

Note that, Netezza JDBC driver class name is “org.netezza.Driver“

Here is the code that can help you:

import jaydebeapi, os

dsn_database = "TESTDB"
dsn_hostname = "192.168.100.210"
dsn_port = "5480"
dsn_uid = "admin"
dsn_pwd = "password"
jdbc_driver_name = "org.netezza.Driver"
jdbc_driver_loc = os.path.join('D:\\Work\\Connections_Softwares\\Jar\\nzjdbc.jar')

sql_str = "select now()"

connection_string='jdbc:netezza://'+dsn_hostname+':'+dsn_port+'/'+dsn_database

url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

conn = jaydebeapi.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()

print(result[0])

Here is the sample output:

URL: jdbc:netezza://192.168.100.210:5480/TESTDB:user=admin;password=password
Connection String: jdbc:netezza://192.168.100.210:5480/TESTDB
('2018-12-02 06:08:32',)