If Component Integration Services does not pass all of a command’s syntax to a remote server (such as all clauses of a select statement), the syntax that is passed along is described for each server class.
Each command has several sections that describe it:
• Description – contains a brief description of the command.
• Syntax – contains a description of the full Transact-SQL syntax of the command. • Usage – contains a general, server class-independent description of handling by
Component Integration Services.
• Server class ASEnterprise – contains a description of handling specific to server class ASEnterprise. This includes syntax that is forwarded to a remote server of class ASEnterprise.
• Server class ASAnywhere – contains a description of handling specific to server class ASAnywhere. This includes syntax that is forwarded to a remote server of class ASAnywhere.
• Server class ASIQ – contains a description of handling specific to server class ASIQ. This includes syntax that is forwarded to a remote server of class ASIQ.
• Server class direct_connect – contains a description of handling specific to server class direct_connect. This includes syntax that is forwarded to a remote server of class direct_connect. In this release, all comments that apply to server class
direct_connect, also apply to server class sds.
alter table
Component Integration Services includes changes to the alter table command.
When the server receives the alter table command, it passes the command to an appropriate access method if:
• The object on which the command is to operate has been associated with a remote or external storage location.
• The command consists of an add column request. Requests to add or drop constraints are not passed to the access methods; instead, they are handled locally.
alter table is passed to remote servers as a language request. See alter table in the Reference Manual: Procedures
These sections describe the alter table command in different server classes.
Server Class ASEnterprise
Component Integration Services forwards the following syntax to a server configured as class ASEnterprise:
alter table [database.[owner].]table_name
{add column_name datatype [{identity | null}] {[, next_column]}...}
| [drop column_name [, column_name]} | modify column_name [data_type] [NULL] | [not null]] [, column_name]}
• When a user adds a column with the alter table command, Component Integration Services passes the datatype of each column to the remote server without type name conversions.
• For ASEnterprise class servers only, the lock clause is also forwarded, if contained in the original query, if the version of SAP ASE is 11.9.2 or later.
Server Class ASAnywhere
Server Class ASIQ
• Handling of alter table by servers in this class is the same as for ASEnterprise servers. • text and image datatypes are fully supported by server class ASIQ.
Server Class direct_connect
• Component Integration Services forwards the following syntax to a remote server configured as class direct_connect:
alter table [database.[owner].]table_name add column_name datatype [{identity | null}] {[, next_column]}...
• Although Component Integration Services requests a capabilities response from a server with class direct_connect, support for alter table is not optional. Component Integration Services forwards alter table to the remote server regardless of the capabilities response.
• The behavior of the server with class direct_connect is database dependent. The Transact-SQL syntax is forwarded, and errors may or may not be raised, depending on the ability of the remote database to handle this syntax.
• Server class direct_connect does not support bigint, unsigned tinyint, unsigned smallint, unsigned int, unsigned bigint.
• If the syntax capability of the remote server indicates Transact-SQL, SAP ASE datatypes are sent to the remote server. If the syntax capability indicates DB2 SQL, DB2 datatypes are sent.
Direct Connect does not support bigint, unsigned tinyint, unsigned smallint, unsigned int, unsigned bigint.
Table 14. DirectConnect datatype conversions for alter table
SAP ASE Datatype DirectConnect Default Datatype
binary(n) binary(n) bit bit char char date date datetime datetime decimal(p, s) decimal(p, s) float float image image
SAP ASE Datatype DirectConnect Default Datatype int int money money numeric(p, s) numeric(p, s) nchar(n) nchar(n) nvarchar(n) nvarchar(n) real real smalldatetime smalldatetime smallint smallint smallmoney smallmoney time time timestamp timestamp tinyint tinyint text text unichar unichar unitext unitext varbinary(n) varbinary(n) varchar(n) varchar(n)
case
Component Integration Services includes changes to the case command. See case in the Reference Manual: Procedures
Server Clas ASEnterprise
The presence of a case expression in the original query syntax will not cause the query optimizer to reject quickpass mode.
Server Class ASAnywhere
The presence of a case expression in the original query syntax will not cause the query optimizer to reject quickpass mode.
Server Class ASIQ
The ability to handle case expressions is not set for servers in this class. When a SQL statement containing a case expression is optimized, the presence of the case expression causes the Component Integration Services quickpass optimization to reject the statement. When this happens, the case expression must be evaluated by the local SAP ASE after retrieving data from the remote server.
Server Class direct_connect
The ability to handle case expressions is determined by the result set from the RPC sp_capabilities. If direct_connect indicates that it can handle case expressions, then Component Integration Services forwards them to the direct_connect when quickpass mode is used to handle the query.