create proxy_table

(Component Integration Services only) Creates a proxy table without specifying a column list. CIS derives the column list from the metadata it obtains from the remote table.

Syntax

create proxy_table table_name
	[external [table | directory | file]]
	at pathname
	[column delimiter “<string>”]

Parameters

Examples

Usage

  • 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. CIS 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.

  • CIS automatically converts the datatype of the column into an SAP ASE 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 SAP ASE 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 assumes 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 uppercase), is stored in SAP ASE as lowercase, if table_name is lowercase. If table_name is uppercase, then the column names is stored as uppercase values. If table_name is in mixed case, all column names are stored as received from the remote site.

  • create proxy_table is not supported with temporary tables.

  • You cannot combine create proxy_table statement with other statements in a single batch.

  • A proxy table stores only metadata. As such, the only space used is the result of making entries in system catalogs. It is estimated that a hundred proxy tables consume about 1MB of space, assuming an average of two indexes per table.

  • SQL user-defined functions are not currently supported with create proxy table, create table at remote server, or alter table.

    Note: The execution of SQL functions requires the syntax username.functionname().
  • If the remote SAP ASE table has one or more encrypted columns, CIS updates the proxy table’s metadata in syscolumns to reflect the column’s encryption properties and its key ID.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create proxy_table permission defaults to the table owner and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

11

Audit option

create

Command or access audited

create procedure

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
update statistics
create existing table
create table