Component Integration Services only Creates a proxy table without specifying a column list. Component Integration Services derives the column list from the metadata it obtains from the remote table.
create proxy_table table_name [on segment_name] [ external [ table | directory | file ] ] at pathname [column delimiter “<string>”]
specifies the local proxy table name to be used by subsequent statements. table_name takes the form dbname.owner.object, where dbname and owner are optional and represent the local database and owner name. If dbname is not specified, the table is created in the current database; if owner is not specified, the table is owned by the current user. If either dbname or owner is specified, the entire table_name must be enclosed in quotes. If only dbname is present, a placeholder is required for owner.
specifies the segment that contains the remote table.
specifies that the object is a remote table or view. external table is the default, so this clause is optional.
specifies that the object is a directory with a path in the following format: "/tmp/directory_name [;R]". “R” indicates “recursive.”
specifies that the object is a file with a path in the following format: "/tmp/filename".
specifies the location of the remote object. pathname takes the form server_name.dbname.owner.object, where:
server_name (required) – is the name of the server that contains the remote object.
dbname (optional) – is the name of the database managed by the remote server that contains this object.
owner (optional) – is the name of the remote server user that owns the remote object.
object (required) – is the name of the remote table or view.
used to separate fields within each record when accesssing flat files, column delimiters The column delimiter can be up to 16 bytes long.
The column delimiter string can be any character sequencer, but if the string is longer than 16 bytes, only the first 16 bytes are used. The use of column delimiter for proxy tables mapped to anything but files will result in a syntax error.
This example creates a proxy table named t1 that is mapped to the remote table t1. Component Integration Services derives the column list from the remote table:
create proxy_table t1 at "SERVER_A.db1.joe.t1"
create proxy_table is a variant of the create existing table command. You use create proxy_table to create a proxy table, but (unlike create existing table) you do not specify a column list. Component Integration Services derives the column list from the metadata it obtains from the remote table.
The location information provided by the at keyword is the same information that is provided by sp_addobjectdef. The information is stored in the sysattributes table.
If the remote server object does not exist, the command is rejected with an error message.
If the object exists, the local system tables are updated. Every column is used. Columns and their attributes are obtained for the table or view.
Component Integration Services automatically converts the datatype of the column into an Adaptive Server datatype. If the conversion cannot be made, the create proxy_table command does not allow the table to be defined.
Index information from the remote server table is extracted and used to create rows for the system table sysindexes. This defines indexes and keys in Adaptive Server terms and enables the query optimizer to consider any indexes that may exist on the table.
After defining the proxy table, issue an update statistics command for the table. This allows the query optimizer to make intelligent choices regarding join order.
When executing create proxy_table table_name at pathname, the table and column names will assume the same case as table_name, if the server identified by pathname is case insensitive (such as DB2 and Oracle).
The columns returned by a case insensitive server (typically in upper case), will be stored in Adaptive Server as lower case, if table_name is lower case. If table_name is uppercase, then the column names will be stored as uppercase values. If table_name is in mixed case, then all column names will be stored as received from the remote site.
Proxy tables in earlier versions of Adaptive Server occupy one extent (8 pages), as well as one extent for each index on a proxy table. In a pre-12.5.0.1 server with 16K logical page size, each proxy table uses 128K worth of space.
In Adaptive Server 12.5.0.1, proxy tables and indexes do not use extents; they use space only in the system catalogs, which Sybase estimates to be 1MB per 100 proxy tables (assuming an average of two indexes per table). The normal upgrade does not reclaim this unused space. To reclaim this space, first drop, then re-create the proxy table.
ANSI SQL – Compliance level: Transact-SQL extension.
create proxy_table permission defaults to the table owner and is not transferable.
Commands create existing table, create table
