PK--> FK relationship between tables of different databases

[Dov MORYUSEF]

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

[adik_q]

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_ni ckname 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_ni ckname (col1)
ON DELETE NO ACTION ON UPDATE NO ACTION
NOT ENFORCED ENABLE QUERY OPTIMIZATION ;

CONNECT RESSET;

[Dov MORYUSEF]

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_ni ckname 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_ni ckname (col1)
ON DELETE NO ACTION ON UPDATE NO ACTION
NOT ENFORCED ENABLE QUERY OPTIMIZATION ;

CONNECT RESSET;

[Brian Tkatch]

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.