Migrating from Netezza: an SQL Script to help you import data from Netezza into Exasol’s analytics database
Whether you’re planning to migrate away from Netezza and are exploring different migration paths or you’ve already decided to migrate data across to us, we’ve built an SQL script (that you can review or use) to automatically import data across from Netezza into our analytics database.
The script extracts the metadata from the and creates the appropriate statements automatically so that you don’t have to worry about table names, column names and types. Before you can get started, you will need to add the IBM Netezza JDBC driver to our analytics database and test the connection.
Add the IBM Netezza JDBC driver to our analytics database
Since Netezza ran out of support in June 2019, the JDBC-driver (nzjdbc3.jar) can no longer be found on IBM’s official JDBC download page. However, the driver can still be found within your Netezza distribution, under the following path:
nz/kit.version_number/sbin/nzjdbc3.jar (e.g. nz/kit.126.96.36.199/sbin/nzjdbc3.jar)
In order to add the driver to the Exasol analytics database, log into your EXAOperations, select the ‘Software’-, then the ‘JDBC Drivers’ tab.
Click Add, then specify the following details:
- Driver Name: Netezza
- Main Class: org.netezza.Driver
- Prefix: jdbc:netezza:
- Disable Security Manager: Check this box
After clicking Apply, you’ll see the newly added driver’s details on the top section of the driver list. Select the Netezza driver by locating the nzjdbc3.jar and upload it. When you’ve done this, the .jar file should be listed in the files column for the IBM Netezza driver.
The standard port for Netezza is 5480.
The Connection-String should look like the following: “jdbc:netezza://’host_ip’:’port’/Database-Name” (User-ID, Password) (e.g. jdbc:netezza://127.0.0.1:5480/SYSTEM, User-ID: ADMIN, Password: Password)
Testing the connection
To test the connectivity of our analytics database to your Netezza instance, create the following connection in your SQL-client:
CREATE OR REPLACE CONNECTION <name_of_connection> TO 'jdbc:netezza://<host_name>:<port>' USER '<netezza_username>' IDENTIFIED BY '<netezza_password>';
You need to have CREATE CONNECTION privilege granted to the user in order to do this.
Test the connectivity with a simple query like:
SELECT * FROM ( IMPORT FROM JDBC AT netezza_connection STATEMENT 'SELECT 1 as "sucessfully_connected" from _v_dual ' );
Migrating the data
For the actual data migration, please see script netezza_to_exasol.sql (this will take you to our GitHub repository). Other migration scripts, including for DB2, are also available: https://github.com/exasol/database-migration
Other useful links for you:
This gives you a more comprehensive view on the key technical considerations and steps to take to migrate – based on the real-life experience of customers who’ve migrated from Netezza to our analytics database.
This whitepaper provides a high level view on migration path options and a comparison between our analytics database and IBM IIAS.
By Julian Fischer