SOA: February 2011

Note: Below steps are tried and configured in a Linux 64 bit environment

It’s been while I was trying to setup connecting to Oracle 8i database from ODI 11g. One of our target applications uses 8i databases even though it’s a decade old upgradeJ. I have found out that Oracle will provide support to only 2 versions backwards of its current version. So the maximum version the ODI 11g supports is Oracle 9i database. Even if we use the JDBC drivers we cannot connect it.

Below is the database compatibility matrix the Oracle currently supports.

Server Version

Client Version

11.2.0

11.1.0

10.2.0

10.1.0

9.2.0

9.0.1

8.1.7

8.1.6

8.1.5

8.0.6

8.0.5

7.3.4

11.2.0

Yes

ES #7

ES #5

No #3

11.1.0

Yes

ES #7

ES #6

ES #5

No #3

10.2.0

ES #7

ES #5

Was

No #3

10.1.0(#4)

ES #6

Was

Was #2

No #3

9.2.0

ES #5

Was

No #1

9.0.1

Was

8.1.7

Was

8.1.6

Was

8.1.5

Was

8.0.6

Was

8.0.5

Was

7.3.4

Was

Key:

Yes	Supported
ES	Supported but fixes only possible for customers with Extended Support .
Was	Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible.
No	Has never been Supported

Also below is the list of JDK drivers that we have tried to test the connectivity between Oracle 8i and 11g. We have placed below drivers in $ORACLE_HOME/agent/drivers path and added into additional_path.txt file before testing the connectivity. We found that it’s mandatory to have Ojdbc6.jar in the drivers’ directory of the agent for the agent to start.

JDK Version	OJDBC Driver Version	Issue
JDK 1.6	Ojdbc6.jar Ojdbc.14.jar Ojdbc5.jar Classes12.zip Classes12.jar	Invocation exception 7. ODI Agent failing to start due to older version.
JRocket 1.6	Ojdbc6.jar	Invocation exception 7.
JDK 1.4	Any version	Unable to install the agent.

Since none of the JDBC driver is compatible we planned to go with ODBC drivers.

Follow below steps to configure ODBC in Linux Environment

· Install Oracle 10.2.0.4 client – Administrator

· After installing the client copy the file $ORACLE_HOME/odbc/lib/libsqora.so.10.1 to
$ORACLE_HOME/lib

· Download ODBC Driver from below path

http://www.unixodbc.org/

· gunzip unixODBC*.tar.gz and tar xvf unixODBC*.tar

· Install the UNIXODBC driver version 2.2.12 (certified version) as follows
./configure --prefix=/u02/unixODBC-2.2.12 --exec-prefix=/u02/unixODBC-2.2.12 --enable-gui=no
CFLAGS="-DBUILD_REAL_64_BIT_MODE -DSIZEOF_LONG=8 -fshort-wchar"
Where the /u02/unixODBC-2.2.12 is the location of the unixodbc driver.
Please note that we are building the unixodbc driver using the "BUILD_REAL_64_BIT_MODE" flag which is recommended on the 64-bit platforms and used to build the ODBC 10.2.0.4 on x64bit platform.

· Set the testing environment
       export DM_HOME=/u02/unixODBC-2.2.12
       export PATH=$DM_HOME/bin:$PATH
       export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$DM_HOME/lib:$LD_LIBRARY_PATH
       export TNS_ADMIN=/usupport/etc

· Make the following changes in SQLConnect.c
You need to do some manual modification to the SQLConnect.c file to avoid errors due to setting the "BUILD_REAL_64_BIT_MODE" flag. Those changes will be sent to UNIXODBC.org to incorpeorate them in the new version of the driver.

v Go to DriverManager source i.e where you have untared the Driver Manager source
i.e $cd unixODBC-2.2.12/DriverManager
In SQLConnect.c: __connect_part_two(), initialise the local variable 'supported' to SQL_TRUE.
Here is the difference of the source.
bash-2.03$ diff SQLConnect_orig.c SQLConnect.c
1814c1814
< SQLUSMALLINT supported;
---
> SQLUSMALLINT supported = SQL_TRUE;
1842c1842
< SQLUSMALLINT supported;
---
> SQLUSMALLINT supported = SQL_TRUE;
v Rebuild the Driver Manager
$ make all
--> This command compiles the SQLConnect.c file and rebuilds the libodbc.so.1.0 in .libs
directory.
v Replace the Driver Manager shared libraries libodbc.so.1.0.0/libodbc.so with the newly built binaries
$cp .libs/libodbc.so* $DM_HOME/lib/
Where DM_HOME is the location where we have installed the Driver Manager.

· The following files are set.
odbcinst.ini
------------
[ODBC]
Trace = No
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = No
[ODBC10204]
Description = Oracle 10g R2 ODBC driver.
Driver = /u02/app/oracle/product/10.2.0.4/lib/libsqora.so.10.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
odbc.ini
--------
[ODBC10204] (This is your DSN which you will put in your ODI topology)
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = T
DisableDPM = F
DisableMTS = T
Driver = ODBC10204
DSN = V1021U

EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = V1021U (This name is the service name of the database you want to connect. This has to be present in the tnsnames.ora file)
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = scott
Password = tiger
.odbc.ini
---------
[ODBC10204] -- (This is your DSN which you will put in your ODI topology)
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = T
DisableDPM = F
DisableMTS = T
Driver = ODBC10204
DSN = V1021U

EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = V1021U
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = scott
Password = tiger
isql -v ODBC10204 scott tiger

· Add below lines in profile file

export DM_HOME=/u02/unixODBC-2.2.12
export PATH=$DM_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$DM_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/usupport/etcBottom of Form

Below is the ODI Topology Screenshot which is configured for this DSN

SOA

Feb 1, 2011

Connecting to Oracle 8i database from ODI 11g