DB2 and PostgreSQL federation

[mitrofun63]

Hi, All

In the first place sorry for mu english.

I'm trying to integrate DB2 9.1.3 (with WebSphere Federation Server
Relational Wrappers installed) and PostgreSQL 7.4 on SLES9 box

I have configured the unixODBC library for access to Postgre database,
is worked normal (test connect and select from test table via tool /
usr/bin/isql is fine)

And next i'm trying to create the ODBC-wrapper in my DB2 database
via :

create wrapper odbc options (module '/usr/lib/unixODBC/
libodbcdrvcfg2S .so.1.0.0')

but this is fail with error :

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Unable to load
the
fencible wrapper library".) SQLSTATE=58004

DB2 InfoCenter say's : "The MODULE wrapper option specifies the full
path of the library that contains the ODBC Driver Manager."

I'm assume what /usr/lib/unixODBC/libodbcdrvcfg2S .so.1.0.0 is the same
library.
May be am wrong ?

Please help me for this situation and may be provide right links about
integration DB2 and PostgreSQL (or other databases) on Linux with ODBC-
wrapper.

Thank's.

[Serge Rielau]

mi********@gmai l.com wrote:

Hi, All

In the first place sorry for mu english.

I'm trying to integrate DB2 9.1.3 (with WebSphere Federation Server
Relational Wrappers installed) and PostgreSQL 7.4 on SLES9 box

I have configured the unixODBC library for access to Postgre database,
is worked normal (test connect and select from test table via tool /
usr/bin/isql is fine)

And next i'm trying to create the ODBC-wrapper in my DB2 database
via :

create wrapper odbc options (module '/usr/lib/unixODBC/
libodbcdrvcfg2S .so.1.0.0')

but this is fail with error :

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Unable to load
the
fencible wrapper library".) SQLSTATE=58004

DB2 InfoCenter say's : "The MODULE wrapper option specifies the full
path of the library that contains the ODBC Driver Manager."

I'm assume what /usr/lib/unixODBC/libodbcdrvcfg2S .so.1.0.0 is the same
library.
May be am wrong ?

Please help me for this situation and may be provide right links about
integration DB2 and PostgreSQL (or other databases) on Linux with ODBC-
wrapper.

I've checked with the federated team and they ask for a PMR to be
opened. L1 support gives you a run around refer them to me.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

mi********@gmai l.com wrote:

>Hi, All

In the first place sorry for mu english.

I'm trying to integrate DB2 9.1.3 (with WebSphere Federation Server
Relational Wrappers installed) and PostgreSQL 7.4 on SLES9 box

I have configured the unixODBC library for access to Postgre database,
is worked normal (test connect and select from test table via tool /
usr/bin/isql is fine)

And next i'm trying to create the ODBC-wrapper in my DB2 database
via :

create wrapper odbc options (module '/usr/lib/unixODBC/
libodbcdrvcfg2 S.so.1.0.0')

but this is fail with error :

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Unable to load
the
fencible wrapper library".) SQLSTATE=58004

DB2 InfoCenter say's : "The MODULE wrapper option specifies the full
path of the library that contains the ODBC Driver Manager."

I'm assume what /usr/lib/unixODBC/libodbcdrvcfg2S .so.1.0.0 is the same
library.
May be am wrong ?

Please help me for this situation and may be provide right links about
integration DB2 and PostgreSQL (or other databases) on Linux with ODBC-
wrapper.

I've checked with the federated team and they ask for a PMR to be
opened. L1 support gives you a run around refer them to me.

Cheers
Serge

Some comments from backstage:
unixODBC's driver manager lib's name is libodbcinst.a. Sometimes the
datasource driver will ask to find the driver manager name in
"libodbcinst.so ", you can do this:
In unixODBC/lib/,
ar -x libodbcinst.a -->this will archive the
"libodbcinst.so .1" out
ln -s libodbcinst.so. 1 libodbcinst.so
Once I tested with odbc wrapper to access mysql via unixODBC, when I
issue below statement when create wrapper.

create wrapper odbcwrapper library 'libdb2rcodbc.a ' options (MODULE
'/home2/sunyunf/mysqlstuff/mysql-connector-odbc-3.51.17-aix5.2-powerpc-32bit/lib/libmyodbc3.so', DB2_FENCED
'N')
Here is the example of use unixODBC access mysql, hope it will be helpful.

create wrapper odbcwrapper library 'libdb2rcodbc.a ' options (MODULE
'/home2/sunyunf/unixODBC/lib/libodbc.so', DB2_FENCED 'N')
DB20000I The SQL command completed successfully.

create server odbcserver type odbc version 3 wrapper odbcwrapper options
(node 'mysql', dbname 'test', PUSHDOWN 'Y', DB2_MAXIMAL_PUS HDOWN 'Y',
DB2_IUD_ENABLE 'Y',DB2_ONE_REQ UEST_PER_CONNEC TION 'N' )
DB20000I The SQL command completed successfully.

create user mapping for user server odbcserver options ( REMOTE_AUTHID
'Login', REMOTE_PASSWORD 'Passw0rd' )
DB20000I The SQL command completed successfully.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[mitrofun63]

Some comments from backstage:

unixODBC's driver manager lib's name is libodbcinst.a. Sometimes the
datasource driver will ask to find the driver manager name in
"libodbcinst.so ", you can do this:

In unixODBC/lib/,
ar -x libodbcinst.a -->this will archive the
"libodbcinst.so .1" out
ln -s libodbcinst.so. 1 libodbcinst.so

Once I tested with odbc wrapper to access mysql via unixODBC, when I
issue below statement when create wrapper.

create wrapper odbcwrapper library 'libdb2rcodbc.a ' options (MODULE
'/home2/sunyunf/mysqlstuff/mysql-connector-odbc-3.51.17-aix5.2-powerpc-32bi*t/lib/libmyodbc3.so', DB2_FENCED
'N')

Here is the example of use unixODBC access mysql, hope it will behelpful.

create wrapper odbcwrapper library 'libdb2rcodbc.a ' options (MODULE
'/home2/sunyunf/unixODBC/lib/libodbc.so', DB2_FENCED 'N')
DB20000I The SQL command completed successfully.

create server odbcserver type odbc version 3 wrapper odbcwrapper options
(node 'mysql', dbname 'test', PUSHDOWN 'Y', DB2_MAXIMAL_PUS HDOWN 'Y',
DB2_IUD_ENABLE 'Y',DB2_ONE_REQ UEST_PER_CONNEC TION 'N' )
DB20000I The SQL command completed successfully.

create user mapping for user server odbcserver options ( REMOTE_AUTHID
'Login', REMOTE_PASSWORD 'Passw0rd' )
DB20000I The SQL command completed successfully.

Hello.

Serge, thanks for your help.
I reinstal my environment on RHEL5 and is not succesfull :((

I install and configure ODBC-access to my PostgreSQL test database, is
work fine
Next i'm try to configure federated db :

db2 =create wrapper odbc options (module '/usr/lib64/
libodbc.so',DB2 _FENCED 'N')
DB20000I The SQL command completed successfully.

db2 =create server db2pgserv type odbc version 3.0 wrapper odbc
options (node 'pgtest', dbname 'testpgdb')
DB20000I The SQL command completed successfully.

db2 =create user mapping for db2inst1 server db2pgserv options
(remote_authid 'postgres', remote_password 'db2wfs913rhel5 ')
DB20000I The SQL command completed successfully.

db2 =set passthru db2pgserv
DB20000I The SQL command completed successfully.

db2 =select * from testtab
SQL1822N Unexpected error code "I" received from data source
"DB2PGSERV" .
Associated text and tokens are "[". SQLSTATE=560BD

In db2diag.log for this error logged this message :

2007-09-17-09.27.31.897164-420 E57705E753 LEVEL: Error
(Received)
PID : 3139 TID : 46912633403232P ROC : db2agent
(DB2PGDB) 0
INSTANCE: db2inst1 NODE : 000 DB : DB2PGDB
APPHDL : 0-7 APPID: *LOCAL.db2inst1 .070917160821
AUTHID : DB2INST1
FUNCTION: DB2 UDB, ODBC wrapper, report_error_me ssage, probe:1072
DATA #1 : String, 16 bytes
Server name:
DATA #2 : String, 9 bytes
DB2PGSERV
DATA #3 : String, 16 bytes
Function name:
DATA #4 : String, 7 bytes
connect
DATA #5 : String, 16 bytes
ODBC native err:
DATA #6 : signed integer, 4 bytes
0
DATA #7 : String, 16 bytes
ODBC sqlstate:
DATA #8 : String, 1 bytes
I
DATA #9 : String, 16 bytes
ODBC error text:
DATA #10: String, 1 bytes
[

[Serge Rielau]

Some more comments from the experts:
-----
Since the customer can access PostgreSQL via ODBC externally, I think
his/her odbc.ini is set correctly.

I suggest the customer use unixODBC-install-dir/bin/dltest to verify
whether the PostgreSQL odbc driver could be loaded by unixODBC driver
manager. We recommend DataDirect as the default Driver manager.

A full record of db2diag.log will be more helpful, seems the fraction
from the customer is too short.
-----------

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[mitrofun63]

Serge Rielau:

Some more comments from the experts:
-----
Since the customer can access PostgreSQL via ODBC externally, I think
his/her odbc.ini is set correctly.

I suggest the customer use unixODBC-install-dir/bin/dltest to verify
whether the PostgreSQL odbc driver could be loaded by unixODBC driver
manager. We recommend DataDirect as the default Driver manager.

A full record of db2diag.log will be more helpful, seems the fraction
from the customer is too short.
-----------

Hi, Serge.

Big thank's for your responses.

In my environvent i use the libodbcpsql.so library shipped with
unixODBC package (the psqlodbc.so shipped with postgres-odbc package
not worked becuase undestandable reasons :( )

I test's this library, what say's your expert :

[root@db2 bin]# dltest /usr/lib64/libodbcpsql.so. 2.0.0
SUCCESS: Loaded /usr/lib64/libodbcpsql.so. 2.0.0

And for my test SELECT in set passthu mode no other messages are
logged on db2diag.log (i restart select statement and recheck
db2diag.log after this)

If possible, are your experts check the same solution on the same
environment ?

Thank you.