Hi,
In your reply you have talked about DDF but its can only be run on OS/390. I'm running 2 DB2 databases (ver 8.1.4) on an AIX (ver 5.2) box. I want to run a query where I can join tables from both of the databases.
You have mentioned about putting some infomation in catalogue tables. Can you tell me how and what. Also do I have to use Federated systems option.
Hi,
I have worked with both Oracle and DB2 (OS/390).
I am assuming that you mean DB2 for OS/390 (mainframe).
If not, I will cover that below also.
Let me qualify my answer. This discussion assumes that you are not talking about a DB2 Data Sharing environment.
In DB2, you can have multiple databases in a DB2 subsystem.
Each DB2 subsystem has a DB2 Catalog (equivalent to an Oracle Data Dictionary).
So, if you are within one DB2 subsystem, and therefore, using the same DB2 catalog, you only need to access an object by owner.objectname (this assumes you have proper authorization).
This means that no matter what database the object is in within a given DB2 subsystem, you can access it without the equivalent of a database link in Oracle because all objects in the same DB2 subsystem are defined in one DB2 catalog. This also means that owner.objectname must be unique within a DB2 subsystem since there is one DB2 catalog.
As you know, for Oracle, each database has one Data Dictionary so to access an object in another database, and therefore another Data Dictionary, requires a database link.
Now, if you want to access another DB2 object that resides in a different DB2 subsystem, then you would need to use DDF (Distributed Data Facility) to access this object.
There are some catalog tables that are used by DDF to store info regarding the other DB2 subsystems that can be accessed.
Assuming that all required information is defined to DDF and the DB2 subsystems are connected by either SNA or TCP/IP, then you need to access your table by location.owner.objectame.
(location name is a value defined to DDF to uniquely define that DB2 subsystem).
Since most people find this cumbersome, you can make this easier for the user by defining a DB2 ALIAS. This is somewhat like a synonym. So, instead of a three-part name, you can
shortened this down to a 2-part name.
Here is an example:
For a table named SCOTT.TABLEA that resides in another DB2 subsystem whose location name is LOCATIONX you can define
an ALIAS as follows:
GRANT CREATEALIAS TO USERA;
SET CURRENT SQLID = 'USERA'; (COMMENT: this switches the current user to USERA)
CREATE ALIAS USERA.TABLEA FOR LOCATIONX.SCOTT.TABLEA;
Note that USERA can be anything. It does NOT have to be a user as you have in Oracle. You can use something descriptive such as
PRODSYSTEM, so you can have the alias be PRODSYSTEM.TABLEA.
Once the alias is created, the user can say
SELECT * FROM USERA.TABLEA;
Now, if you are referring to DB2 on the Windows, Unix, Linux, OS/2 platforms, then the method is very similar.
You can define what is known as a nickname in the DB2 catalog.
This nickname is equivalent to the alias we just mentioned.
The nickname is an alias for a 3-part name in UDB.
The 3-part name is server.owner.objectname.
Again, there are some catalog tables that need to have information, but once defined, you can access your data using a nickname.
So, for DB2 for OS/390 the equivalent is an alias.
For DB2 UDB for Windows, Unix, Linux, and OS/2 it is the nickname.
If you have any further questions please send a reply.
Hope this helps.