Oracle database link syntax

When you set the DB2_COMPATIBILITY_VECTOR registry variable to enable the use of 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.

Examples

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