Retrieves rows from database objects.
select [all | distinct] select_list
[into [[database.]owner.]table_name]
[from [[database.]owner.]{view_name|table_name
[(index index_name [ prefetch size ][lru|mru])]}
[holdlock | noholdlock] [shared]
[,[[database.]owner.]{view_name|table_name
[(index index_name [ prefetch size ][lru|mru])]}
[holdlock | noholdlock] [shared]]... ]
[where search_conditions]
[group by [all] aggregate_free_expression
[, aggregate_free_expression]... ]
[having search_conditions]
[order by
{[[[database.]owner.]{table_name.|view_name.}]
column_name | select_list_number | expression}
[asc | desc]
[,{[[[database.]owner.]{table_name|view_name.}]
column_name | select_list_number | expression}
[asc | desc]]...]
[compute row_aggregate(column_name)
[, row_aggregate(column_name)]...
[by column_name [, column_name]...]]
[for {read only | update [of column_name_list]}]
[at isolation {read uncommitted | read committed |
serializable}]
[for browse]
[plan "abstract plan"]
Usage
Component Integration Services processes the select command when any table on which it operates has been created as a proxy table. When possible, Component Integration Services forwards the entire syntax of a select command to a single remote server. This is referred to as quickpass mode.
When Component Integration Services forwards the select command to a remote server, the table name used is the remote table name, and the column names used are the remote column names.
The following keywords are ignored for all servers except Sybase System 10 and later versions of Adaptive Server Enterprise, but they do not prevent Component Integration Services from using quickpass mode:
lock
index
parallel
prefetch size
holdlock
noholdlock
readpast
shared
at isolation
The following keywords are never forwarded to a remote server and they do prevent Component Integration Services from using quickpass mode:
compute by
for browse
into
plan “abstract plan”
Quickpass mode is not used if any of the following conditions exist:
All tables referenced in the from clause do not reside on the same remote server
Any tables are local (including temporary tables)
The query contains syntax that the remote server does not support
select commands in a union operation can all be forwarded to a remote server, including the union operator, if all tables in the select commands reside on the same remote server.
If the select command returns a sorted result set involving a character column from a remote server (for example, in a union operation, a group by clause, or an order by clause), the rows may be returned in an unexpected sort order if the remote server is configured with a different sort order than Adaptive Server. You can rerun the query with traceflag 11216 turned on to receive the expected sort order. This traceflag is global and should be turned off as soon as the query is executed.
All syntax is supported. Since the remote server is assumed to have all capabilities necessary to process Transact-SQL syntax, all elements of a select command, except those mentioned above, are forwarded to a remote server, using quickpass mode.
A bulk copy transfer is used to copy data into the new table when a select...into command is issued and the into table resides on a remote Adaptive Server. Both the local and remote databases must be configured with dboption set to select into / bulkcopy.
All syntax is supported. Since the remote server is assumed to have all capabilities necessary to process Transact-SQL syntax, all elements of a select command, except those mentioned above, are forwarded to a remote server, using quickpass mode.
If the select...into format is used and the into table is accessed through the ASAnywhere interface, bulk inserts are not used. Instead, Component Integration Services uses Client-Library to prepare a parameterized dynamic insert command, and executes it for each row returned by the select portion of the command.
All syntax is supported. Since the remote server is assumed to have all capabilities necessary to process Transact-SQL syntax, all elements of a select command, except those mentioned above, are forwarded to a remote server, using quickpass mode.
If the select...into format is used and the into table is accessed through the db2 interface, bulk inserts are not used. Instead, a separate connection is used to handle the text of a CIS-generated insert command.
All syntax is supported. Since the remote server is assumed to have all capabilities necessary to process Transact-SQL syntax, all elements of a select command, except those mentioned above, are forwarded to a remote server, using quickpass mode.
A bulk copy transfer is used to copy data into the new table when a select...into command is issued and the into table resides on a remote Adaptive Server. Both the local and remote databases must be configured with dboption set to select into / bulkcopy.
The first time Component Integration Services requires a connection to a server in class direct_connect, a request for capabilities is made of the DirectConnect. Based on the response, Component Integration Services determines the parts of a select command to forward to the DirectConnect. In most cases, this is determined by the capabilities of the DBMS with which the DirectConnect is interfacing.
If the entire statement cannot be forwarded to the DirectConnect using quickpass mode, Component Integration Services compensates for the functionality that cannot be forwarded. For example, if the remote server cannot handle the order by clause, quickpass is not used and Component Integration Services performs a sort on the result set.
Component Integration Services passes data values as parameters to either a cursor or a dynamic SQL statement. Language statements can also be used if the DirectConnect supports it. The parameters are in the datatype native to Adaptive Server and must be converted by the DirectConnect into formats appropriate for the target DBMS.
The select...into command is supported, but the table must have a unique index if the table has text or image columns.
If the select...into format is used and the into table is accessed through a DirectConnect, bulk inserts are not used. Instead, Component Integration Services uses Client-Library to prepare a parameterized dynamic insert command, and executes it for each row returned by the select portion of the command.
By default, Component Integration Services does not forward syntax involving order by, group by, union, distinct, all, and expressions that involve more than column names.
When you turn traceflag 11215 on, the full capabilities of a DB2 database are assumed, and Component Integration Services forwards as much syntax to the remote server (gateway) as DB2 can process, including order by, group by, union, and so forth.
See Also
select in the Adaptive Server Reference Manual.
