Returning modified rows - select statements with side effects

Authors:
Andreas Behm;Serge Rielau;Richard Swagerman
Affiliations:
IBM Toronto Lab, Markham, ON, Canada;IBM Toronto Lab, Markham, ON, Canada;IBM Toronto Lab, Markham, ON, Canada
Venue:
VLDB '04 Proceedings of the Thirtieth international conference on Very large data bases - Volume 30
Year:
2004

Citing 3
Cited 3

A modeling study of the TPC-C benchmark

SIGMOD '93 Proceedings of the 1993 ACM SIGMOD international conference on Management of data
Principles of transaction processing: for the systems professional

Principles of transaction processing: for the systems professional
Integrating Triggers and Declarative Constraints in SQL Database Sytems

VLDB '96 Proceedings of the 22th International Conference on Very Large Data Bases

A distance-based algorithm for clustering database user sessions

ISMIS'05 Proceedings of the 15th international conference on Foundations of Intelligent Systems
Finding and analyzing database user sessions

DASFAA'05 Proceedings of the 10th international conference on Database Systems for Advanced Applications
Reweaving the tapestry: integrating database and messaging systems in the wake of new middleware technologies

Data Management in a Connected World

Quantified Score

Hi-index	0.00

Visualization

Abstract

SQL in the IBM® DB2® Universal DatabaseTM for Linux®, UNIX®, and Windows® (DB2 UDB) database management product has been extended to support nested INSERT, UPDATE, and DELETE operations in SELECT statements. This allows database applications additional processing on modified rows. Within a single unit of work, applications can retrieve a result set containing the modified rows from a table or view modified by an SQL data-change operation. This eliminates the need to select the row after an INSERT or UPDATE, or before a DELETE statement. As a result, fewer network round trips, less server CPU time, fewer cursors, and less server memory are required. In addition, deadlocks can be avoided. The proposed approach is integrated with the set semantics of SQL, and does not require any procedural logic or modifications on the underlying relational data model. Pipelining multiple update, insert and delete operations using the same source data provides a very efficient way for multitable data-change statements typically found in ETL (extraction, transformation, load) applications. We demonstrate significant performance benefit with our experiences in the TPC-C benchmark. Experimental results show that the new SQL is more efficient in query execution compared to classic SQL.