Blog do projektu Open Source JavaHotel

wtorek, 30 grudnia 2014

DB2, federation and three-part names

Federation
Federation (comprehensive redbook on that topic can be downloaded here) allows correlating data from different data sources. Although InfoSphere Federation Server requires additional licensing, so called "homogenous" federation (comprising only DB2  databases, local and remote)  is available also in free DB2 Express Edition.
DB2 supports also three-part name referring to remote object. The dbname.schema.tablename notation allows migration from other databases (like Oracle or MS SQL). Although it works nicely unfortunately I found a problem which made me several nervous evenings.
Recreate the problem
Firstly enable DB2 instance for federation
db2 update dbm cfg using FEDERATED yes

db2stop
db2start
Create two databases
db2 create database test
db2 create database feder
Database 'test' will contain our tables and database 'feder' will act as a federated database.
Create two simple tables in 'test'
db2 "CREATE TABLE X1 (A1 INT, A2 INT)"
db2 "CREATE TABLE X2 (B INT)"
Then connect to 'feder' and create federation server
db2 "create wrapper drda"
db2 "create server testserver type db2/udb  version '10.5'  wrapper drda  authid user_name  password \"user_password\"  options(  add dbname 'TEST')"
db2 "CREATE USER MAPPING FOR PUBLIC SERVER testserver  OPTIONS (REMOTE_AUTHID 'user_name', REMOTE_PASSWORD 'user_password')"
Important: pay attention to lack of ' or " in user_name in CREATE SERVER \" in user_password and ' in CREATE USER MAPPING. You can spend several hours trying to discover it on your own !
Create a view (inner join)
db2 "create or replace view XXX as select * from testserver.db2inst1.x1,testserver.db2inst1.x2"
So far so good. But while testing the view there is a surprise.
db2 "select * from xxx"
SQL0158N  Liczba kolumn podana dla tabeli "DB2INST1.XXX" różni się od liczby
kolumn w tabeli wynikowej.  SQLSTATE=42811
There is no way to overcome it. Running this join directly (without passing through view) is successful. It seems that : view on join between remote tables accessed through three-part name is not working.
Solution 
Just come back to old DB2 school and use nicknames.
db2 create nickname testserver_db2inst1_x1 for testserver.db2inst1.x1
create nickname testserver_db2inst1_x2 for testserver.db2inst1.x2
db2 "create or replace view XXX as select * from testserver_db2inst1_x1,testserver_db2inst1_x2"

db2 "select * from xxx"

A1          A2          B          
----------- ----------- -----------

  Wybrano rekordów: 0.

Conclusion 
Hoping to be fixed in next release. Besides, three-part naming convention is very useful.

Brak komentarzy:

Prześlij komentarz