The at pathname syntax used with create existing table command is the preferred method for defining the storage location of remote objects. The following method using sp_addobjectdef is also supported.
The stored procedure sp_addobjectdef defines the storage location of a remote object. This procedure allows the user to associate a remote object name with a local table name. The remote object may or may not exist before the storage location is defined. The syntax for sp_addobjectdef is:
sp_addobjectdef object_name, "object_loc" [,"object_type"]
object_name is the local proxy table name to be used by subsequent statements. object_name takes the form:
where dbname and owner are optional and represent the local database and owner name. If not present, the object is defined in the current database owned by the current owner. If either dbname or owner is specified, the entire object_name must be enclosed in quotes. If only dbname is present, a placeholder is required for owner.
object_loc is the storage location of the remote object. It takes the form:
server_name is the name of the server that contains this remote object (required.)
dbname is the name of the database managed by the remote server that contains this object (optional). If the server is class db2, this is the location_name portion of a DB2 table name.
owner is the name of the remote server user that owns the remote object (optional). If the server is class db2, this is the DB2 authorization ID.
object is the name of the remote table, view, or rpc.
aux1.aux2 is a string of characters that is passed to the remote server during a create table or create index command as the segment name; the meaning of this string is dependent upon the class of the server that receives it. If the server is class db2, aux1 is the DB2 database in which to place the table, and aux2 is the DB2 tablespace in which to place the table. aux1.aux2 is optional.
object_type is the type of remote object. It can be a table, view, file, or rpc. This parameter is optional; the default is table.When present, the object_type option must be enclosed in quotes.
To map the proxy table authors to the remote authors table, use the following syntax for the database shown in Figure A-1:
sp_addobjectdef authors, "ORACLEDC...authors", "table"
Figure A-1: Using sp_addobjectdef to map a remote table to a proxy table