Returning Modified Rows—SELECT Statements with Side Effects

Abstract

This chapter proposes and implements a clean SQL extension to provide applications a way to return values that have been modified by INSERT, UPDATE, or DELETE operations. The SQL in the IBM® DB2® Universal Database™ 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 an additional processing on modified rows. Within a single unit of work, the 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. Additionally, 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 multi-table data-change statements typically found in ETL (extraction, transformation, and load) applications. The chapter demonstrates significant performance benefit with the experiences in the TPC-C benchmark. Experimental results emphasize that the new SQL is more efficient in query execution compared to the classic SQL.