The ability to SELECT from an INSERT statement is an intriguing feature. In some cases, it’s possible today to perform actions on an inserted row before it gets saved to disk. For example, a BEFORE TRIGGER might change data before it’s even recorded to disk. But the application program won’t have any knowledge of this change that occurs in the trigger.
Identity columns and user-defined defaults have similar effects. What if the program needs to know the final column values? Today, this is difficult and inefficient to implement. The SELECT FROM INSERT syntax in DB2 V8 solves this problem. It lets you insert the row and retrieve the values of the columns with a single SQL statement. It performs well because it performs both the INSERT and SELECT as one operation.
Consider Figure 1. The data is inserted as specified in the VALUES clause, and retrieved as specified in the SELECT. Without the ability to select COL5, the program would have no knowledge of the value supplied to COL5 because it was assigned using CURRENT DATE. With this new syntax, the program can retrieve the CURRENT DATE value that was just inserted into COL5 without adding overhead.
Also, 64-bit virtual addressing will greatly increase the amount of memory available to DB2. IBM is making major enhancements to the internal SQL control-block structures, so DB2 will use memory more efficiently. So more memory, used more efficiently, should translate into more efficient execution. As with every previous version, IBM seeks to improve application performance. DB2 V8 optimization enhancements are expected to include:
- Sophisticated query rewrite capabilities to support materialized query tables
- Sparse indexing to improve star join performance
- Support for parallel sort
- Better support for queries with data type and length mismatches (which would have caused less efficient access paths in previous releases).
For Java programmers, DB2 V8 offers expanded functionality in support for both Type 2 and Type 4 Java drivers. Both will be updated to support the Java Database Connectivity (JDBC)/SQLJ 3.0 standard. The standard enhances support for SAVEPOINTs and WITHHOLD cursors and provides improvements to connection pooling, among a long list of other expanded features.
Finally, DB2 V8 provides more XML support that is being pushed into the DB2 engine. This should include support for some built-in XML publishing functions such as XMLELEMENT and XML2CLOB, among others.
DB2 V8 is the most significant new version of DB2 ever, in terms of new functionality and the amount of new engine code IBM is writing. This should result in a bevy of improvements that will make our databases and applications more reliable, available, and efficient. As users of DB2, that should excite us all! Z