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_HOME/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=myfullyqualifiedhostnamehere
    [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.ACCOUNTS
    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".Associated 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_EXIST
          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_FLAGS, 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_EXIST
          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_FLAGS, 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