En esta ubicación se debe generar un initXXX.ora con el nombre que declaremos en el listener, para no complicar las cosas es conveniente poner el mismo nomre tanto en el listener como en el tnsnames.
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/hs/admin/initDB2DATABASE.ora
#This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_FDS_CONNECT_INFO = ODBC_DSN
HS_FDS_CONNECT_INFO = prueba
HS_FDS_TRACE_FILE_NAME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/hs/admin/DB2DATABASE.log
HS_FDS_CONNECT_INFO = DB2DATABASE
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
set DB2INSTANCE=is400
listener.ora
Para la configuración del listener se debe usar una entrada como la que se usa para el "extproc" pero en PROGRAM se debe poner hsodbc.
# listener.ora Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB2DATABASE)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM =hsodbc)
(ENVS = LD_LIBRARY_PATH = /opt/ibm/iSeriesAccess/lib:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
tnsnames.ora
En el tnsnames agregamos la entrada que en nuestro caso al igual que en el listener llamamos DB2DATABASE.
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srvpdf)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
DB2DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =localhost )(PORT=1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB2DATABASE)
)
(HS=OK)
)
odbc.ini
[prueba]
Description = iSeries Access ODBC Driver
Driver = iSeries Access ODBC Driver
System = xxx.xx.3.2
UserID = xxxxxx
Password = xxxxxx
Naming = 0
DefaultLibraries = NOMBRE
Database = NOMBRE
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 0
MaxFieldLength = 32
BlockFetch = 1
BlockSizeKB = 128
ExtendedColInfo = 0
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0
odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[iSeries Access ODBC Driver]
Description = iSeries Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
UsageCount = 1
CPTimeout =
CPReuse =
System = 192.181.13.2
User = username
Password = password
NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2 = the following Driver64/Setup64 keywords will provide that support.
Threading = 2
DontDLClose = 1
ODBC Driver
-bash-3.2$ cd /opt/ibm/iSeriesAccess/lib
-bash-3.2$ ls -ltr
total 2260
-r-xr-xr-x 1 root root 443939 Apr 5 2008 libcwbxda.so
-r-xr-xr-x 1 root root 94504 Apr 5 2008 libcwbrc.so
-r-xr-xr-x 1 root root 16636 Apr 5 2008 libcwbodbcs.so
-r-xr-xr-x 1 root root 729572 Apr 5 2008 libcwbodbc.so
-r-xr-xr-x 1 root root 998060 Apr 5 2008 libcwbcore.so
luego de hacer todo esto me encontre con un error porque esta apuntando al driver en vez de apuntar al ODBC en el archivo de confuguración en el ORACLE_HOME/hs/admin
SQL> /
*select * from display@db2database*
***
ERROR en línea 1:
ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este
mensaje:
[Generic Connectivity Using ODBC][C077] Could not find symbol 'SQLAllocConnect'
in dynamic library
[C079] Failed to load dynamic library
'/opt/ibm/iSeriesAccess/lib/libcwbodbc.so'
ORA-02063: 3 lines precediendo a DB2DATABASE
en el parámetro
lo apunte a
HS_FDS_SHAREABLE_NAME = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
que es el driver del ODBC
luego lo reapunte a
HS_FDS_SHAREABLE_NAME = /usr/local/etc/libodbc.so
