Connected – Oracle to Netezza

The atmosphere is tense as Oracle collapses into a pool of sweat.
“One” … “Two” ….  “Three” – Oracle is back on his feet. Oracle tries to regain his balance as he dances around the ring.Determined to end this match, Oracle approaches his opponent. Left Jab, Right Jab, Upper Cut .. Netezza is up against the ropes again. Oracle delivers a fatal blow and now Netezza is on the ground.

Although there is various battles between Oracle and Netezza, the fact remains that many corporations run various flavours of databases. In a heterogeneous environment there will always be a need for data to be shared between various databases.

One possible option of sharing data is made possible by DG4ODBC – Database Gateway for ODBC

What is DG4ODBC ?

DG4ODBC uses third party ODBC drivers to make connections to non-Oracle data sources.

I will briefly explain the setup of the DG4ODBC driver on a Aix 64 bit server.

Step 1: Install ODBC driver for Netezza

Create a filesystem/directory to install the Netezza ODBC driver.

We will install the driver in a filesystem called /u01/app/Netezza/ODBC

Obtain the Netezza ODBC driver installation files from your Netezza Administrator.

Copy the installation files to /u01/app/Netezza/ODBC

Sample listing of the Netezza installation files.


ls -ltr

-rwx—— 1 oracle dba 12851 Dec 24 14:46 unpack*
-rw-r—– 1 oracle dba 125832881 Dec 24 14:46 npsclient.6.1.P1.tar.Z


./unpack npsclient.6.1.P1.tar.Z

——————————————————————————-IBM Netezza — NPS AIX Client 6.1.P1
(C) Copyright IBM Corp. 2002, 2011 All Rights Reserved.
——————————————————————————-Where should the NPS AIX Client be unpacked? [npsclient.6.1.P1.tar.Z] /u01/app/Netezza/ODBC0% 25% 50% 75% 100%
|||||||||||||||||||||||||||||||||||||||||||||||||||Unpacking complete.

create a soft link:


cd /u01/app/Netezza/lib64
ln -s libnzodbc.a libnzodbc.so

Step 2: Setup your environment

Add the following environmental variables to your user profile.

export NZ_ODBC_INI_PATH=/u01/app/Netezza/ODBC
export LIBPATH=/u01/app/Netezza/ODBC/lib64
export ODBCINI=/u01/app/Netezza/ODBC/odbc.ini

After modifying your profile, logout and login again. Confirm that the environmental variables have been setup correctly.

Step 3: Setup and Test the ODBC driver

You can get a sample odbc.ini file from /u01/app/Netezza/ODBC/lib


cd /u01/app/Netezza/ODBC

create a odbc.ini file with the following contents:

[ODBC Data Sources]
NZ_TESTDS = NetezzaSQL[NZ_TESTDS]
Driver = /u01/app/Netezza/ODBC/lib/lib64/libnzodbc.so
Description = NetezzaSQL ODBC
Servername = NetezzaTest
Port = 5480
Database = dev
Username = guest
Password = password
ReadOnly = false
ShowSystemTables = false
LegacySQLTables = false
LoginTimeout = 0
QueryTimeout = 0
DateFormat = 1
NumericAsChar = false
SQLBitOneZero = false
StripCRLF = false

A description of each of the parameters defined in the odbc.ini file can be found in the odbc sample file.

For security purposes change the permission of odbc.ini


chmod 700 odbc.ini

To test your ODBC driver and setup –  run nzodbcsql passing the datasource as a parameter.


cd /u01/app/Netezza/ODBC/bin64

./nzodbcsql NZ_TESTDS

NZODBCSQL – program to test Netezza ODBC driverUsage : [Max-rows]
1 – Data Source Name – mandatory
2 – Maximum rows to display – optional; default 100
Type ‘quit’ or ‘\q’ or CTRL-D or ENTER at the prompt to quit
NOTE: Max 100 rows are displayed (for selects)Driver version : ‘Release 6.1 (P-1) [Build 18855]‘
NPS version : ’07.00.0000 Release 7.0 [Build 25936]‘
Database : ‘DEV’nzodbc > select count(*) from admin.test_table;COUNT
——-
16Rows Returned : 1

nzodbc > quit

Step 4: Configure gateway init file in your Oracle database home


cd $ORACLE_HOME/hs/admin

The sample file is called: initdg4odbc.ora

Extract of the sample file:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC#
# HS init parameters
#
HS_FDS_CONNECT_INFO =
HS_FDS_TRACE_LEVEL = <trace_level> HS_FDS_SHAREABLE_NAME =#
# ODBC specific environment variables
#
set ODBCINI= /u01/app/Netezza/ODBC#
# Environment variables required for the non-Oracle system
#
set =

Copy the provided sample file and create a new init file.


cp initdg4odbc.ora initDG4ODBC.ora

Contents of  the new initDG4ODBC.ora file:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC#
# HS init parameters
#
HS_FDS_CONNECT_INFO = NZ_TESTDS
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /u01/app/Netezza/ODBC/lib64/libnzodbc.so#
# ODBC specific environment variables
#
set ODBCINI=/u01/app/Netezza/ODBC/odbc.ini#
# Environment variables required for the non-Oracle system
#
#set =

Step 5: Configure Oracle Net Listener for Gateway

The Database communicates with the Gateway using Oracle Net.

Sample:

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=gateway_sid)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib)
)
)

Add the following entry to the listener.ora file.

(SID_DESC =
(SID_NAME = DG4ODBC)
(ORACLE_HOME = /main/software/oracle/11.2.0.3)
(ENV=”LIBPATH=//u01/app/Netezza/ODBC/lib64:/u01/app/oracle/db/11.2.0.3/lib”)
(PROGRAM=dg4odbc)
)

Step 6: Configure tnsnames.ora

Add the following entry to the tnsnames.ora file:

DG4ODBC.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

HOST is the server where the Oracle database is located.

Step 7:  Start and Stop the listener

Stop and start the listener. Note: listener must be stopped and started if you make any changes to the configuration files.

. oranev
lsnrctl stop
lsnrctl start

Step 8: Create a database link  

Create a private database link.

sql> create database link netezza connect to "guest" indentified by "password" using 'DG4ODBC';

Step 9: Test the database link  

sql> select * from admin.test_table@netezza;
sql> select count(*) from admin.test_table@netezza;

For more information on DG4ODBC refer to the Oracle® Database Gateway for ODBC User’s Guide 11g Release 2 (11.2)

http://docs.oracle.com/cd/E11882_01/gateways.112/e12070.pdf

Author: Nitesh

Oracle Certified Master

Featured Posts Slider Photo Credit : atlnav (creative commons license)