Here we are connecting oracle to netizza same way we can connect oracle and mysql
Step1:
Ensure Unix ODBC Driver Manager is version 2.2.11 or higher.
[oracle@myhost-x1 ~]$
odbcinst --version
unixODBC 2.2.14
to check how to install unixODBC on linux you can check here
Step2:
Define or append below environment variables in user’s .bash_profile : highlighted
in yellow
[oracle@myhost-x1 ~]$ cat
.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
################ START: Entries at the time of ODBC installation
###############################
# User specific environment and startup
programs
export TMP=/d2/db1/oracle/temp
export TEMPDIR=/d2/db1/oracle/temp
export ORACLE_BASE=/d2/db1/oracle/apps
export
ORACLE_HOME=$ORACLE_BASE/product/db_1/11.2.0
export ORACLE_SID=DB1
export
LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/local/nz/lib:/usr/lib64
export LD_LIBRARY_PATH_32=$ORACLE_HOME/lib32
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/bin:/usr/lib64/:$ORACLE_HOME/OPatch:/usr/local/nz/bin
export
NZ_ODBC_INI_PATH=/etc
export
ODBCSYSINI=/etc
export
ODBCINI=/etc/odbc.ini
export PATH
################ END: Entries at
the time of ODBC installation ###############################
Step3:
- Go to the path $ NZ_ODBC_INI_PATH set
above in .bash_profile (i.e cd $ NZ_ODBC_INI_PATH).
- Login
through root .(
sudo su -) and Create odbc.ini using vi editor vi /etc/odbc.ini for server to be
connected, in which DSN is defined like below. Please point to the right
library file for ODBC.
[oracle@myhost-x1
~]$ cat /etc/odbc.ini
[ODBC Data Sources]
NZSQL = NetezzaSQL
[NZSQL]
Driver = /usr/local/nz/lib64/libnzodbc.so
Description = NetezzaSQL ODBC
Servername = 10.10.30.101
Port = 5480
Database = xyz
Username = abc
Password = mypassword
ReadOnly = false
ShowSystemTables = false
LegacySQLTables = false
LoginTimeout = 0
QueryTimeout = 0
DateFormat = 1
NumericAsChar = false
SQLBitOneZero = false
StripCRLF = false
securityLevel = preferredUnSecured
caCertFile =
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/odbc32v51
Trace=0
TraceDll=/opt/odbc32v51/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0
[oracle@myhost-x1 ~]$
Test note 1:
Now open
another PuTTY session and check you can connect to non- oracle database using
ODBC driver
[oracle@myhost-x1 ~]$
isql
-v NZSQL
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Till this
point the ODBC setup is complete, you can go ahead at oracle end.
Test note 2:
[oracle@myhost-n1 gateway]$ echo $ORACLE_HOME
/d1/db1/oracle/apps/product/11.1.0/gateway
[oracle@myhost-n1 gateway]$ pwd
/d1/db1/oracle/apps/product/11.1.0/gateway
[oracle@myhost-n1 gateway]$ ./bin/dg4odbc
Oracle Corporation --- THURSDAY APR 15 2010 06:37:17.144
Heterogeneous Agent Release 11.1.0.7.0 -
64bit Production Built with
Oracle Database Gateway for ODBC
Step 4: Prepare the 11g gateway heterogeneous
instance. In our case we have kept instance name to DG4ODBC.
[oracle@myhost-n1 admin]$ pwd
/d1/db1/oracle/apps/product/11.1.0/gateway/hs/admin
[oracle@myhost-n1
admin]$ cat initDG4ODBC.ora
# This is a sample agent init file that
contains the HS parameters that are
# needed for the Database Gateway for ODBC
HS_LANGUAGE=american_america.we8iso8859P1
#HS_FDS_TRACE_LEVEL = debug
HS_FDS_CONNECT_INFO
= NZSQL
HS_FDS_SHAREABLE_NAME
= /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set
ODBCINI = /home/oracle/nz/odbc.ini
#HS_FDS_CHARACTER_SEMANTICS=TRUE
#
# Environment variables required for the
non-Oracle system
#
#set <envvar>=<value>
HS_FDS_SUPPORT_STATISTICS=FALSE
Step 5: Create gateway listener as below. Take care in
specifying ENVS attribute.
trace_level_LISTENER_DB1=0
SID_LIST_LISTENER_DB1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /d1/db1/oracle/apps/product/db_1/11.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB1)
(ORACLE_HOME = /d1/db1/oracle/apps/product/db_1/11.2.0)
)
(SID_DESC =
(SID_NAME = DG4ODBC)
(ORACLE_HOME =/d1/db1/oracle/apps/product/11.1.0/gateway)
(ENVS = LD_LIBRARY_PATH=/usr/lib64/:/home/oracle/nz/lib:/d1/db1/oracle/apps/product/11.1.0/gateway/lib)
(PROGRAM = dg4odbc)
)
)
LISTENER_DB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aaaa.com)(PORT = 1522))
)
(ADDRESS_LIST
=
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
Start this listener and proceed
for remaining steps to be performed at 10g server only.
export ORACLE_HOME=:/d1/db1/oracle/apps/product/11.1.0/gateway
$ORACLE_HOME/bin/lsnrctl start LISTENER_DB1
Step 6: Create TNS entry for the 11g
gateway instance( DG4ODBC in our case)
Note: (HS=OK) should be after connect_data is
closed
DG4ODBC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS
= (PROTOCOL = TCP)(HOST = aaaa.com)(PORT = 1522))
)
(CONNECT_DATA =
(SID = dg4odbc)
)
(HS=OK)
)
Test note 3: now tnsping the gateway listener from database home to check request is reaching to gateway listener from db home. If ok then go ahead.
Step 7: connect to database user, from where connectivity to
non-oracle database is desired and create db link as below:
Create database link NZ connect to abc identified by “mypassword” using ‘DG4ODBC’;
Step 8: Check
connectivity oracle to natizza
select * from test@NZ
.
You should
be able to see data fetched from non-oracle database
If have issue, diagnosis logs can be seen from 11g home location:
$ORACLE_HOME/hs/admin/log
You need to uncomment #HS_FDS_TRACE_LEVEL
= debug and HS_FDS_TRACE_LEVEL = 4; restart 11g listener and query again.
General
issues:
- Driver
manager older version is some times. Installing newer versions help.
- Some
problems need compilation of Driver manager source code with some options like
taking flag for 64bit data etc.
- Some
problems require fix at Non Oracle database software end.
- Some
problems required change the ODBC driver software.
- Some
problems are fixed by changing the HS parameter in the initInstance.ora file of
gateway.
Real issue example
No comments:
Post a Comment