Problem with Information Integrator/Federated Datase - Oracle

[Michael Rudolph]

Hi newsgroup,

I have an issue with the configuration of a DB2 federated database
(WebSphere Information Integrator) in conjunction with the relational
wrapper for Oracle on AIX. DB2 seems to not use the existing tnsnames.ora.

The Oracle client is installed and the connection to the Oracle database
using tnsping and sqlplus is possible (for the db2instance user). For
that I changed the environment for the db2instanceuser and added the
following to ~/.profile:
export ORACLE_HOME=/applications/oracle/CURRENT
export PATH=$ORACLE_HO ME/bin:$PATH
export PATH=/home/oracle/net/admin:$PATH
export DB2_DJ_INI=/home/db2instanceuser/sqllib/cfg/db2dj.ini
As mentioned in the manual the file db2dj.ini is filled with the correct
ORACLE_HOME and TNS_ADMIN (Optional, but to be on the save side) variable.
$ cat /home/db2instanceuser/sqllib/cfg
ORACLE_HOME=/applications/oracle/CURRENT
TNS_ADMIN=/home/oracle/net/admin
Im confident that this config file is used:
$ db2set -all
[e] DB2COMM=tcpip
[e] DB2_DJ_INI=/home/db2instanceuser/sqllib/cfg/db2dj.ini
[g] DB2_EEE_LICENSE _POLICY=xxx
[g] DB2SYSTEM=myful lyqualifiedhost namehere
[g] DB2ADMINSERVER= db2das00

The creation of the wrapper, server and user mapping works fine since
db2 don't yet speak to the oracle database.
db2 => CREATE WRAPPER oracle LIBRARY 'libdb2net8.a'
db2 => CREATE SERVER TEST TYPE ORACLE VERSION '9i' WRAPPER ORACLE
OPTIONS(ADD NODE 'REMOTEDB')
db2 => CREATE USER MAPPING FOR RUDOLPH SERVER TEST OPTIONS (ADD
REMOTE_AUTHID 'someuser', ADD REMOTE_PASSWORD 'somepass')

But once a connection to the remote database will be established (e.g.
create nickname or a passthru sql) DB2 seems to not use the existing
tnsnames.ora:

db2 => CREATE NICKNAME TEST.ACCOUNTS FOR REMOTEDB.TEST.A CCOUNTS
DB21034E The command was processed as an SQL statement because it
was not a valid Command Line Processor command. During SQL
processing it returned:
SQL1822N Unexpected error code "12154" received from data source
"ATLAS".Associa ted text and tokens are "ORA-12154: TNS:could not
resolve service name". SQLSTATE=560BD

It does not matter wether the "REMOTEDB" exists in tnsnames.ora or not,
the same errormessage appears.
db2diag.log is filled with the following statements when the above query
is executed:
------------------------------------------------------------------------
2006-02-08-10.57.31.278723 +060 E25888C656 LEVEL: Error (OS)
PID : 43186 TID : 1 PROC : db2fmp (54506) 0
INSTANCE: db2instance NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:130
CALLED : OS, -, dlopen OSERR: ENOENT (2)
RETCODE : ECF=0x9000001A=-1879048166=ECF_ FILE_DOESNT_EXI ST
File doesn't exist
MESSAGE : Attempt to load specified library failed.
DATA #1 : Library name or path, 9 bytes
db2dsproc
DATA #2 : shared library load flags, PD_TYPE_LOAD_FL AGS, 4 bytes
2
DATA #3 : String, 52 bytes
A file or directory in the path name does not exist.

2006-02-08-10.57.31.279102 +060 E26545C696 LEVEL: Error (OS)
PID : 43186 TID : 1 PROC : db2fmp (54506) 0
INSTANCE: db2instance NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:140
CALLED : OS, -, dlopen OSERR: ENOENT (2)
RETCODE : ECF=0x9000001A=-1879048166=ECF_ FILE_DOESNT_EXI ST
File doesn't exist
MESSAGE : Attempt to load specified library augmented with object name
failed.
DATA #1 : Library name or path, 16 bytes
db2dsproc(shr.o )
DATA #2 : shared library load flags, PD_TYPE_LOAD_FL AGS, 4 bytes
262146
DATA #3 : String, 52 bytes
A file or directory in the path name does not exist.
------------------------------------------------------------------------

Not sure what this means and what file is missing, but the path in the
db2dj.ini is correct and db2dsproc exists and its path is in $LIBPATH
If I try to create a wrapper using the graphical Command Center the
output in db2diag is different:
Directly after wrapper creation db2diag gives the same input as the
above diag-message except PID and timestamp.
Additionaly a popup appears where I can set the environment variables
ORACLE_HOME, TNS_ADMIN and so on, non of this parameters is filled in
the popup but they should already have values assigned through
db2dj.ini. If this settings will be set, nothing changes in the
following actions.

I also tried djxlinkand restarted the db2instance.

Versions used:
AIX 5.2 (oslevel -r: 5200-04)
DB2 8.2.0 ("DB2 v8.1.1.72", "s040914", "U498350", FixPak "7"), AFAIK no
APARs/Fixes installed
WII 8.2
Do you have any hint what is configured wrong?

With kind regards
Michael Rudolph

[Serge Rielau]

Michael Rudolph wrote:

Hi newsgroup,

I have an issue with the configuration of a DB2 federated database
(WebSphere Information Integrator) in conjunction with the relational
wrapper for Oracle on AIX. DB2 seems to not use the existing tnsnames.ora.

The Oracle client is installed and the connection to the Oracle database
using tnsping and sqlplus is possible (for the db2instance user). For
that I changed the environment for the db2instanceuser and added the
following to ~/.profile:
export ORACLE_HOME=/applications/oracle/CURRENT
export PATH=$ORACLE_HO ME/bin:$PATH
export PATH=/home/oracle/net/admin:$PATH
export DB2_DJ_INI=/home/db2instanceuser/sqllib/cfg/db2dj.ini
As mentioned in the manual the file db2dj.ini is filled with the correct
ORACLE_HOME and TNS_ADMIN (Optional, but to be on the save side) variable.
$ cat /home/db2instanceuser/sqllib/cfg
ORACLE_HOME=/applications/oracle/CURRENT
TNS_ADMIN=/home/oracle/net/admin
Im confident that this config file is used:
$ db2set -all
[e] DB2COMM=tcpip
[e] DB2_DJ_INI=/home/db2instanceuser/sqllib/cfg/db2dj.ini
[g] DB2_EEE_LICENSE _POLICY=xxx
[g] DB2SYSTEM=myful lyqualifiedhost namehere
[g] DB2ADMINSERVER= db2das00

The creation of the wrapper, server and user mapping works fine since
db2 don't yet speak to the oracle database.
db2 => CREATE WRAPPER oracle LIBRARY 'libdb2net8.a'
db2 => CREATE SERVER TEST TYPE ORACLE VERSION '9i'
WRAPPER ORACLE OPTIONS(ADD NODE 'REMOTEDB')
db2 => CREATE USER MAPPING FOR RUDOLPH SERVER TEST OPTIONS (ADD
REMOTE_AUTHID 'someuser', ADD REMOTE_PASSWORD 'somepass')

But once a connection to the remote database will be established (e.g.
create nickname or a passthru sql) DB2 seems to not use the existing
tnsnames.ora:

db2 => CREATE NICKNAME TEST.ACCOUNTS FOR REMOTEDB.TEST.A CCOUNTS
DB21034E The command was processed as an SQL statement
because it was not a valid Command Line Processor command.
During SQL processing it returned:
SQL1822N Unexpected error code "12154" received from
data source "ATLAS".Associa ted text and tokens are
"ORA-12154: TNS:could not resolve service name".
SQLSTATE=560BD

It does not matter wether the "REMOTEDB" exists in tnsnames.ora or not,
the same errormessage appears.
db2diag.log is filled with the following statements when the above query
is executed:
------------------------------------------------------------------------
2006-02-08-10.57.31.278723 +060 E25888C656 LEVEL: Error (OS)
PID : 43186 TID : 1 PROC : db2fmp (54506) 0
INSTANCE: db2instance NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:130
CALLED : OS, -, dlopen OSERR: ENOENT (2)
RETCODE : ECF=0x9000001A=-1879048166=ECF_ FILE_DOESNT_EXI ST
File doesn't exist
MESSAGE : Attempt to load specified library failed.
DATA #1 : Library name or path, 9 bytes
db2dsproc
DATA #2 : shared library load flags, PD_TYPE_LOAD_FL AGS, 4 bytes
2
DATA #3 : String, 52 bytes
A file or directory in the path name does not exist.

2006-02-08-10.57.31.279102 +060 E26545C696 LEVEL: Error (OS)
PID : 43186 TID : 1 PROC : db2fmp (54506) 0
INSTANCE: db2instance NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:140
CALLED : OS, -, dlopen OSERR: ENOENT (2)
RETCODE : ECF=0x9000001A=-1879048166=ECF_ FILE_DOESNT_EXI ST
File doesn't exist
MESSAGE : Attempt to load specified library augmented with object name
failed.
DATA #1 : Library name or path, 16 bytes
db2dsproc(shr.o )
DATA #2 : shared library load flags, PD_TYPE_LOAD_FL AGS, 4 bytes
262146
DATA #3 : String, 52 bytes
A file or directory in the path name does not exist.
------------------------------------------------------------------------

Not sure what this means and what file is missing, but the path in the
db2dj.ini is correct and db2dsproc exists and its path is in $LIBPATH
If I try to create a wrapper using the graphical Command Center the
output in db2diag is different:
Directly after wrapper creation db2diag gives the same input as the
above diag-message except PID and timestamp.
Additionaly a popup appears where I can set the environment variables
ORACLE_HOME, TNS_ADMIN and so on, non of this parameters is filled in
the popup but they should already have values assigned through
db2dj.ini. If this settings will be set, nothing changes in the
following actions.

I also tried djxlinkand restarted the db2instance.

Versions used:
AIX 5.2 (oslevel -r: 5200-04)
DB2 8.2.0 ("DB2 v8.1.1.72", "s040914", "U498350", FixPak "7"), AFAIK no
APARs/Fixes installed
WII 8.2

Comments form the federated team:
What version of the Oracle client are you using? We've recently hit an
Oracle bug that produces that exact error on Oracle clients 9.2.0.5 and
up and Oracle
10.1.0.4 clients and up. Oracle has recently fixed the problem and has
started to release patches on the 9.2.0.7 and 10.2.0.1 levels of their
client. So far, they have published patches on the following OSs:

AIX5L based systems (64 bit)
HP-UX 64 bit
Linux x86/Linux x86 64 bit
Solaris SPARC 64 bit

You can download the patches by searching for 3807408 on the "Patches
and Updates" tab of Oracle's Metalink web pages (you will need to logon
first):
https://metalink.oracle.com/metalink...50820341868001

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Michael Rudolph]

Serge Rielau wrote:

Comments form the federated team:
What version of the Oracle client are you using? We've recently hit an
Oracle bug that produces that exact error on Oracle clients 9.2.0.5 and
up and Oracle
10.1.0.4 clients and up. Oracle has recently fixed the problem and has
started to release patches on the 9.2.0.7 and 10.2.0.1 levels of their
client. So far, they have published patches on the following OSs:

AIX5L based systems (64 bit)
HP-UX 64 bit
Linux x86/Linux x86 64 bit
Solaris SPARC 64 bit

You can download the patches by searching for 3807408 on the "Patches
and Updates" tab of Oracle's Metalink web pages (you will need to logon
first):
https://metalink.oracle.com/metalink...50820341868001

Cheers
Serge

Hi Serge,

Thanks for this hint, at the moment we are using Oracle Client 9.2.0.5.
I will get my account for Metalink tomorrow, I will try again and report.

Michael

[Michael Rudolph]

Michael Rudolph wrote:

Serge Rielau wrote:

Comments form the federated team:
What version of the Oracle client are you using? We've recently hit
an Oracle bug that produces that exact error on Oracle clients 9.2.0.5
and up and Oracle
10.1.0.4 clients and up. Oracle has recently fixed the problem and
has started to release patches on the 9.2.0.7 and 10.2.0.1 levels of
their client. So far, they have published patches on the following OSs:

AIX5L based systems (64 bit)
HP-UX 64 bit
Linux x86/Linux x86 64 bit
Solaris SPARC 64 bit

You can download the patches by searching for 3807408 on the "Patches
and Updates" tab of Oracle's Metalink web pages (you will need to
logon first):
https://metalink.oracle.com/metalink...50820341868001

Cheers
Serge

Hi Serge,

Thanks for this hint, at the moment we are using Oracle Client 9.2.0.5.
I will get my account for Metalink tomorrow, I will try again and report.

Michael

Hi all,

I switched the client back to 9.2.0.3 and everything is working fine
now. So you seems to be right with your assumptions. Sadly I am using
AIX on 32 bit, Oracle does not offer this patch for this OS.
Additionally the patch and bug description does not fit very well to my
environment (only to "( and other characters in passwords and 64 bit
environments).
Perhaps I have to wait until a fixed version is out, the security crew
is not very amused with outdated software with security holes in a
productive environment.

Does a PMT make sense in this case? It seems to be a known bug but I
does not find something about it on IBM.com

Cheers
Michael

[Michael Rudolph]

Michael Rudolph wrote:

Hi all,

I switched the client back to 9.2.0.3 and everything is working fine
now. So you seems to be right with your assumptions. Sadly I am using
AIX on 32 bit, Oracle does not offer this patch for this OS.
Additionally the patch and bug description does not fit very well to my
environment (only to "( and other characters in passwords and 64 bit
environments).
Perhaps I have to wait until a fixed version is out, the security crew
is not very amused with outdated software with security holes in a
productive environment.

Does a PMT make sense in this case? It seems to be a known bug but I
does not find something about it on IBM.com

Cheers
Michael

I got in contact with some IBMers. Oracle support replied to them, that
the patch for AIX 5L will patch both 32bit and 64bit libraries. So it
should be just fine once applying 9.2.0.7 fixpack and the special patch.
The write-up for the special patch is very general and they think they
must have fixed a few similar customer problems using the same patch.
The fix has been tested and verified.

Cheers
Michael

[Michael Rudolph]

Michael Rudolph wrote:

Michael Rudolph wrote:

Hi all,

I switched the client back to 9.2.0.3 and everything is working fine
now. So you seems to be right with your assumptions. Sadly I am using
AIX on 32 bit, Oracle does not offer this patch for this OS.
Additionally the patch and bug description does not fit very well to
my environment (only to "( and other characters in passwords and 64
bit environments).
Perhaps I have to wait until a fixed version is out, the security crew
is not very amused with outdated software with security holes in a
productive environment.

Does a PMT make sense in this case? It seems to be a known bug but I
does not find something about it on IBM.com

Cheers
Michael

I got in contact with some IBMers. Oracle support replied to them, that
the patch for AIX 5L will patch both 32bit and 64bit libraries. So it
should be just fine once applying 9.2.0.7 fixpack and the special patch.
The write-up for the special patch is very general and they think they
must have fixed a few similar customer problems using the same patch.
The fix has been tested and verified.

Cheers
Michael

See the following technote:
http://www-1.ibm.com/support/docview...=utf-8&lang=en