By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

DB2 and PostgreSQL federation

P: n/a
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.

Sep 11 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
mi********@gmail.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
Sep 12 '07 #2

P: n/a
Serge Rielau wrote:
mi********@gmail.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
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_PUSHDOWN 'Y',
DB2_IUD_ENABLE 'Y',DB2_ONE_REQUEST_PER_CONNECTION '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
Sep 13 '07 #3

P: n/a
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_PUSHDOWN 'Y',
DB2_IUD_ENABLE 'Y',DB2_ONE_REQUEST_PER_CONNECTION '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 : 46912633403232PROC : 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_message, 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
[



Sep 17 '07 #4

P: n/a
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
Sep 17 '07 #5

P: n/a

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.

Sep 18 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.