Thursday, September 5, 2013

Oracle DBLINK and DB2 Three-Part-Names for Federation

One of the new features that went mostly unnoticed for DB2 10.1 and DB2 10.5 is the support for so-called three-part-names (nice pun?) for Federation. It allows the access to remote tables without the need to create nicknames. Thus, applications and users can be more dynamic and agile in what they access (there are drawbacks to it, too). When the DB2_COMPATIBILITY_VECTOR is set accordingly, even the Oracle-like DBLINK syntax of "schema.table@database" is tolerated. So much for the theory, I tried to get it to work on my laptop...

I already had two different databases created, and the DB2 compatibility vector is set to "ORA". Thus, I could focus on Federation-related commands. The first thing to do is to enable my DB2 instance for Federation (FEDERATED is the configuration parameter).
db2 update dbm cfg using federated yes
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.


The next commands are directly executed in my first, "local" database, after connecting to it:
db2 => create wrapper drda
DB20000I  The SQL command completed successfully.



This creates the DRDA wrapper which is required to access DB2 sources. If you want to access Oracle databases from within DB2, you would need to create the NET8 or Oracle wrapper and some more steps which are described here. As I am going to access a local DB2 database, I don't need to catalog any nodes or map users. So what is left is to create a server, i.e., to make the database I want to access known to my database I am operating with.

db2 => create server myserver type db2/cs version 10.5 wrapper drda authorization hloeser password "secrets4you" options (dbname 'DBTWO')
DB20000I  The SQL command completed successfully.



Thereafter, I can already access the remote table using either a three-part-name or the Oracle dblink syntax.

db2 => select * from myserver.henrik.worldtest

ID          S             
----------- ---------------
          1 Hello World   

  1 record(s) selected.
 

db2 => select * from henrik.worldtest@myserver

ID          S             
----------- ---------------
          1 Hello World   

  1 record(s) selected.


Both statements do not require a nickname to be created, thus saving administration time and allowing to migrate Oracle applications more easily. One thing to note is that I need to connect to my local database with the user option in order for Federation to pass on my credentials. That is "connect to dbone user henrik" will work, just using "connect to dbone" will bring you authentication errors or SQL0204N "... is an undefined name".

Have you tried out dblink syntax with DB2? Comments?