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

PK--> FK relationship between tables of different databases

P: n/a
Hi there,

Is it possible to have a primary key - foreign key relationship between
tables in different databases in DB2 ? (databases are installed in
different physical servers)

Thanks a lot for your answers

Dov MORYUSEF
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This can be done trough federated server. However created constraint
cannot be ENFORCED. To use this feature, you have to enable Federated
Database System Support on both servers by issuing statement in Command
Window:
db2 update dbm cfg using FEDERATED YES
Next steps: create wrapper, server, nickname and at last create foreign
key which references created nickname.

--First server
CONNECT TO db_one user akalicki using ****;
CREATE TABLE akalicki.Test1 (col1 INT, col2 INT);
DISCONNECT db_one;
--Second server
CONNECT TO db_two user db_user using ****;

CREATE WRAPPER DRDA LIBRARY 'db2drda.dll' OPTIONS( ADD DB2_FENCED 'N');

CREATE SERVER DB_ONE TYPE DB2/UDB VERSION '8.2' WRAPPER DRDA AUTHID
"akalicki" PASSWORD "*****" OPTIONS( ADD DBNAME 'DB_ONE', PASSWORD
'Y');

CREATE USER MAPPING FOR "DB_USER" SERVER "DB_ONE" OPTIONS ( ADD
REMOTE_AUTHID 'akalicki', ADD REMOTE_PASSWORD '*****') ;

CREATE NICKNAME db_user.Test_nickname FOR DB_ONE.akalicki.Test1;

CREATE TABLE db_user.Test2 (col1 INT, col2 INT);

ALTER TABLE db_user.Test2
ADD CONSTRAINT CONST_FK FOREIGN KEY (col2)
REFERENCES db_user.Test_nickname (col1)
ON DELETE NO ACTION ON UPDATE NO ACTION
NOT ENFORCED ENABLE QUERY OPTIMIZATION ;

CONNECT RESSET;

Nov 12 '05 #2

P: n/a
Thanks for your answer.

In fact, my first server is DB2 UDB for iSeries V5R3 and second a DB2
UDB V8 for Linux. I have to adapt your commands to these platforms.

I will try it.

Many thanks.

Dov
ad****@wp.pl a écrit :
This can be done trough federated server. However created constraint
cannot be ENFORCED. To use this feature, you have to enable Federated
Database System Support on both servers by issuing statement in Command
Window:
db2 update dbm cfg using FEDERATED YES
Next steps: create wrapper, server, nickname and at last create foreign
key which references created nickname.

--First server
CONNECT TO db_one user akalicki using ****;
CREATE TABLE akalicki.Test1 (col1 INT, col2 INT);
DISCONNECT db_one;
--Second server
CONNECT TO db_two user db_user using ****;

CREATE WRAPPER DRDA LIBRARY 'db2drda.dll' OPTIONS( ADD DB2_FENCED 'N');

CREATE SERVER DB_ONE TYPE DB2/UDB VERSION '8.2' WRAPPER DRDA AUTHID
"akalicki" PASSWORD "*****" OPTIONS( ADD DBNAME 'DB_ONE', PASSWORD
'Y');

CREATE USER MAPPING FOR "DB_USER" SERVER "DB_ONE" OPTIONS ( ADD
REMOTE_AUTHID 'akalicki', ADD REMOTE_PASSWORD '*****') ;

CREATE NICKNAME db_user.Test_nickname FOR DB_ONE.akalicki.Test1;

CREATE TABLE db_user.Test2 (col1 INT, col2 INT);

ALTER TABLE db_user.Test2
ADD CONSTRAINT CONST_FK FOREIGN KEY (col2)
REFERENCES db_user.Test_nickname (col1)
ON DELETE NO ACTION ON UPDATE NO ACTION
NOT ENFORCED ENABLE QUERY OPTIMIZATION ;

CONNECT RESSET;

Nov 12 '05 #3

P: n/a
If you'd like it to be enforced, perhaps you could CREATE the TABLE
locally and keep it in sync with the other database via a job (such as
every hour, day, or week).

B.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.