| | |||||
| Modified 07-DEC-2009 Type HOWTO Status PUBLISHED | |||||
In this Document
Goal
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5 - Release: to 10.2Linux x86
***Checked for relevance on 07-Dec-2009***
Goal
This note describes the setup of generic connectivity on Linux (32 bit) using DB2Connect and IBM ODBC driver to connect to a DB2 database.
Please be aware that HSODBC for Oracle on Linux 64bit is not available. You need to use Database Gateway for ODBC (DG4ODBC) 11g on this platform
Solution
How to Setup HSODBC (generic connectivity) on Linux using IBM ODBC driver included in DB2Connect.
This note is divided into three different parts:
Part I describes how to set up the DBConnect and IBM's ODBC driver
Part II describes the configuration process of HSODBC
Part III describes dedicated HSODBC/ DB2 ODBC errors
Part I: Setting up the ODBC driver
The odbc driver being used in this note is part of the DB2Connect package release 8.1 fixpack 12. The required ODBC Driver Manager is the driver manager from UnixODBC.
Please install the DB2Connect package according to the installation instructions from IBM.
The first step now is to configure the ODBC driver.
- Configuring the odbc.ini file:
The ODBC.INI file for the IBM ODBC driver included in DB2 Connect is very simple and requires ONLY the Driver path:
[toolsdb]
Driver = /home/db2inst1/sqllib/lib/libdb2.so
The ODBC DSN [toolsdb] refers to the database alias defined in the instance refered by by DB2INSTANCE parameter:
For example DB2INSTANCE environment variable is pointing to db2inst1, then connect as DB2 user (db2inst1) to this instance db2inst1 using utility "db2" and query the database aliases -> list database directory
db2 => list database directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = TOOLSDB
Database name = TOOLSDB
Node name = ZKU118
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
The Driver Manager unixODBC provides a test utility to test the ODBC connection to the DB2 database.
Make sure you have set DB2INSTANCE as environment variable to the DB2 database storing the network alias being refered in the ODBC.INI file.
In addition make sure environment variable ODBCINI is set and pointing to an odbc.ini file containing the db2 DSN.
Then test: isql -v <username> <password> <DSN>
Part II: How to configure HSODBC
(This section assumes that everything is done in the Oracle user account
that starts the listener!)
In general the following things must be configured:
1) listener
2) tnsnames
3) init<SID>.ora of the hs subsystem
4) environment
5) Oracle database
1) The listener needs a new SID entry like the following:
(SID_DESC =
(ORACLE_HOME = /oracle/product/10.2.0.1.0)
(SID_NAME = hsdb2udb)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/oracle/product/10.2.0.1.0/lib:/home/db2inst1/sqllib/lib:/usr/lib)
Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.
The LD_LIBRARY_PATH in the listener.ora contains the Oracle library directory (32 bit), the location of the ODBC Driver Manager (/usr/lib) and the DB2 library path.
The listener must be STOPPED and STARTED after changing the listener.ora! Please do not perform a restart of the listener.
2) The tnsnames.ora needs an entry for the HSODBC alias:
db2.de.oracle.com=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kgronau-pc)(PORT = 15102))
)
(CONNECT_DATA =
(SERVICE_NAME = hsdb2udb)
)
(HS = OK)
)
The domain of the tns alias can differ from the one used above (de.oracle.com),
depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com
But the important entry is the (HS=OK) key word. The (HS=OK) parameter must be outside the SID section and specifies that this connector uses the Oracle Heterogeneous Service Option.
Net Configuration Assistant/ Database Creation Assistant might remove entries like (HS=) from your tnsnames.ora file; so please make sure (HS=OK) is used!
After adding the tnsnames alias and stopping/starting the listener, a connectivity check is to use tnsping <alias>.
tnsping hsodbc
should come back with a successfull message.
3) init.ora of the gateway:
The SID to use HS functionality is called in this example hsdb2udb. There are some restrictions how to name the SID (described in the Net Administrators Guide in detail).
At this place only a short note: don't use dots in the SID and keep it short!
The SID is also relevant for the init.ora file of the gateway. The name of the
file is init<SID>.ora. In this example it is called inithsdb2udb.ora.
The file is located at $ORACLE_HOME/hs/admin.
It should contain the following entries:
#
# HS init parameters
#
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = toolsdb
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
set DB2INSTANCE=db2inst1
Short explanation of the parameters:
HS_FDS_CONNECT_INFO points to the ODBC DSN configured in PART I of this note.
HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library; in our case the unixODBC Driver Manager
The set ODBCINI=/InformixSDK/etc/odbc.ini points to the location of an odbc.ini file you want to use with this hsodbc configuration.
set DB2INSTANCE=db2inst1 points to the DB2 instance containing the database alias for the DB2 database.
4) Configuring the Oracle database
The only thing that must be done here is to create a database link:
connect with the username/password that has sufficient rights to create a
database link (i.e. system).
The syntax is:
create [public] database link <name>
connect to <UID> identified by <pwd> using '<tnsalias>';
In other words, to connect to the DB2 database the syntax must be:
CREATE DATABASE LINK db2
CONNECT TO "<valid DB2 user name>" IDENTIFIED BY "<valid password for this DB2 user>" USING 'db2.de.oracle.com';
The db link name is db2. Username and password must be in double quotes, because the username and password of the DB2 database might be case sensitive. 'db2.de.oracle.com' points to the alias in the tnsnames.ora file that calls the HS subsystem.
If everything is configured well, a select of a DB2 table or the DUAL table which is then translated by HSODBC should be successful:
select user from dual@db2;
...
Part III: Informix/HSODBC related errors
Common errors are coverd in a separate note:'
Note.234517.1 Ext/Pub How to Resolve Common Errors Encountered while using Transparent Gateways or Generic Connectivity
Problems that might occur using DB2 ODBC driver:
Problem 1:
Enabling ODBC tracing:
Solution 1:
ODBC tracing for the IBM ODBC driver is enabled by adding into the COMMON section of the db2cli.ini file:
[COMMON]
Trace=1
TraceFileName=/tmp/db2_odbc.trc
Problem 2:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_GetRows: errors.h (1094): ;
[unixODBC][IBM][CLI Driver] CLI0165E Error in row.
SQLSTATE=01S01[unixODBC][IBM][CLI Driver] CLI0002W Data truncated.
SQLSTATE=01004[unixODBC][IBM][CLI Driver] CLI0002W Data truncated.
SQLSTATE=01004 (SQL State: 01004; SQL Code: -99999)
ORA-02063: preceding 2 lines from HSDB2UDB
The Oracle database is using a UNICODE character set, the column content matches the column precison and the table contains at least one character, for example German letters with diaeresis like ÜÖÄ.
For example the column is defined as a char(2) and it contains for example the value 'ÖB'
Solution 2:
The DB2 ODBC driver is per default using the character set of the application for the connection; see ODBC trace file:
SQLDriverConnect( hDbc=0:1, hwnd=0:0, szConnStrIn="DSN=toolsdb;UID=db2;PWD=***",
cbConnStrIn=-3, szConnStrOut=&bfffd0e0, cbConnStrOutMax=1020, pcbConnStrOut=&bf
ffd0de, fDriverCompletion=SQL_DRIVER_NOPROMPT )
---> Time elapsed - +5.600000E-005 seconds
( DBMS NAME="DB2/NT", Version="08.02.0000", Fixpack="0x23010106" )
( Application Codepage=1208, Database Codepage=1252, Char Send/Recv Codepage=12
08, Graphic Send/Recv Codepage=1200 )
Codepage 1208 is a unicode codepage.
HSODBC uses the BYTE NLS_LENGTH_SEMANTICS to transfer values from the foreign database to Oracle; a char(2) is then represented by char (2 bytes).
In a unicode codepage the 'Ö' is represented by 2 bytes, 'B' in one byte = 3 bytes.
To avoid this behaviour the environment variable DB2CODEPAGE can be set to any 8 bit character set which will then intialize the ODBC connection using the character set specified by DB2CODEPAGE.
As HSODBC does not know how to interprete the characters fetched from the DB2 database, in addition to the DB2CODEPAGE the HS_LANGUAGE must be set in the init<hsodbc>.ora file:
...
set DB2INSTANCE=db2inst1
set DB2CODEPAGE=819
HS_LANGUAGE=american_america.we8iso8859P1
HS_LANGUAGE must be set to a character set which matches the DB2CODEPAGE character set. An overview of those matching characters can be found at:
http://www.borgendale.com/codepage/j2conv.htm
References
NOTE:234517.1 - How to Resolve Common Errors Encountered while using Transparent Gateways or Generic ConnectivityNOTE:374744.1 - Using a Gateway with a Unicode Oracle Database Increases the Column Precision Three Times for Certain Data Types
|
Products
| ||
Related 
0 ความคิดเห็น:
แสดงความคิดเห็น