Removes rows from a table.
delete [from] [[database.]owner.]{view_name|table_name}
[where search_conditions]
delete [[database.]owner.]{table_name | view_name}
[from [[database.]owner.]{view_name|table_name
[(index index_name [ prefetch size ][lru|mru])]}
[, [[database.]owner.]{view_name|table_name
(index index_name [ prefetch size ][lru|mru])]} ]...]
[where search_conditions]
delete [from] [[database.]owner.]{table_name|view_name}
where current of cursor_name
Component Integration Services processes delete when the table on which it operates has been created as a proxy table. Component Integration Services forwards the entire request (or part of it) to the server that owns the actual object.
Component Integration Services executes delete using one of two methods:
Method 1 – The entire command is forwarded to the remote server as a single statement in close to its original syntax. If the syntax and remote capabilities match, the entire statement is forwarded and processed remotely. This is referred to as quickpass mode.
Method 2 – If the entire command cannot be forwarded to a remote server, Component Integration Services declares and opens one or more cursors in update mode, and begins a scan on the remote table. Each cursor forwards as much of the original statement’s predicates to the remote server as possible. For each row fetched that meets the search criteria, a positioned delete is executed.
When Component Integration Services forwards delete to a remote server, the table name used is the remote table name, and the column names used are the remote column names. These names may not be the same as the local proxy table names.
Component Integration Services generally passes the original delete syntax to remote servers as a single statement, but the following conditions will likely cause the statement to be executed using method 2, above:
The statement contains multiple tables that are not located in the same remote server
The statement contains local tables (including temporary tables)
The statement contains case expressions
The statement contains text or image columns
The statement contains certain referential integrity checks
The statement contains system functions in the predicate list
The statement contains syntax that the remote server does not support
The format involving where current of is never forwarded to a remote server and causes the statement to be executed using method 2 above.
If Component Integration Services cannot pass the entire statement to a remote server, a unique index must exist on the table.
If Component Integration Services cannot forward the original query without alteration, it performs the delete using method 2.
If Component Integration Services cannot forward the original query without alteration, it performs the delete using method 2.
If Component Integration Services cannot forward the original query without alteration, it performs the delete using method 2.
The syntax forwarded to servers of class direct_connect is dependent on the capabilities negotiation, which occurs when Component Integration Services first connects to the remote DirectConnect. Examples of negotiable capabilities include: subquery support, group by support, and built-in support.
A DirectConnect can request that delete be generated in DB2 syntax.
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.
Servers of class db2 do not contain the capabilities negotiation features of server class direct_connect, so the syntax passed to the remote server is simpler than that allowed by Transact-SQL. The syntax does not contain the following:
Search conditions containing subqueries, group by, or order by clauses
Transact-SQL built-in functions
Transact-SQL operators (such as bitwise operators)
Syntax not allowed by DB2
Component Integration Services processes delete using method 2, described above, when the statement is complex.
If the server is a DB2 system, use trace flag 11215 to instruct Component Integration Services that the remote server can handle all DB2 syntax. This assumption is not made automatically because not all gateways using the db2 server class are actually connected to DB2 systems. When trace flag 11215 is turned on, quickpass mode is used unless the following conditions exist:
The statement cannot be expressed in DB2 syntax.
The statement contains outer joins.
The statement contains like clauses with Sybase extensions.
The statement contains built-in functions that are not supported by DB2.
delete in the Reference Manual
