When you set the DB2_COMPATIBILITY_VECTOR registry variable
to support Oracle database link syntax, you can connect with a remote
database, table, or view.
Enablement
You enable Oracle database link
syntax support by setting the DB2_COMPATIBILITY_VECTOR registry variable
to hexadecimal value 0x20000 (bit position 18), and then stop and
restart the instance to have the new setting take effect.
db2set DB2_COMPATIBILITY_VECTOR=20000
db2stop
db2start
To take full advantage of the DB2 compatibility
features for Oracle applications, the recommended setting for the
DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility
bits.
The database link syntax uses the @ (at sign) to indicate
an in or membership condition. For example, to access a remote object
pencils under
schema
user using a database link to
stock,
you can use:
SELECT * FROM user.pencils@stock;
Note: The
DB2 system supports the use of the @ character as a valid character
in an ordinary identifier. For example, you can create a table with
pencils@stock as its name. When database link support is enabled,
the @ character is treated as a special delimiter in table, view,
and column references. If you want to use @ in database object names
when link support is enabled, you must enclose the name with double
quotes.
Example
Remote
object references are formatted as:
<schema_name>,<object_name>@<server_name>
Column
references can also be included:
<schema_name>,<object_name>,<column_name>@<server_name>
The
following SELECT statements query a remote table named EMPLOYEE:
SELECT birthdate FROM rschema.employee@sudb WHERE firstname='SAM'
SELECT rschema.employee.birthdate@sudb FROM rschema.employee@sudb
WHERE rschema.employee.firstname@sudb ='SAM'
You
can also issue UPDATE, INSERT, and DELETE statements against a remote
table:
UPDATE rschema.employee@sudb SET firstname='MARY'
INSERT INTO rschema.employee@sudb VALUES ('Bob')
DELETE FROM rschema.employee@sudb