- Package hints that are globally available
- An easier method for getting plan hints for dynamic SQL
- A new CURRENT EXPLAIN MODE special register that allows EXPLAIN at PREPARE time
- A method to apply certain DSNZPARM optimization keywords to individual SQL statements
- Additional features.
DB2 10 will allow an upgrade from DB2 9 or V8. If you’re one of the few looking to jump directly to DB2 10 from V8, you can. However, if you envision migrating to DB2 9 this year or next, we strongly suggest doing so.
8. XML performance
DB2 10 offers insert and query performance improvements such as binary XML support and multi-versioning for XML data. However, there are also XML query language enhancements, including subdocument update, data and time support, and the use of XML in native SQL stored procedures and User-Defined Functions (UDF). DB2 will use the z/OS XML System Services for XML validation with 100 percent of that validation being redirected to specialty processors: the System z Application Assist Processor (zAAP), and System z Integrated Information Processor (zIIP). Among other XML data integrity improvements, DB2’s CHECK DATA utility will perform document structure and schema validation.
7. SQL enhancements and improved portability
DB2 10 adds further SQL improvements to those introduced in prior releases. One of the handier improvements is an increased length of timestamps taking the stored value out to the nanosecond. Another is a new special register SET CURRENT TIMESTAMP, which allows the precision of the timestamp to be set. Several enhancements allow the control of time zones, including a data type, two new special registers, a new ZPARM keyword, and a new operator.
An application program will be able to use indicator variables when determining whether a target column of a host variable should contain a default value or an unassigned value. If the value is unassigned, the column is treated as if it was never part of the statement. This capability will be enabled on an individual PREPARE statement for dynamic SQL or with a new bind option for static SQL.
DB2 10 extends DB2’s support for data warehouse and Business Intelligence (BI) applications via the moving sum and moving average functions, a subset of the Online Analytical Processing (OLAP) specification. Moving sum and moving average divide the final result of a query (after applying the WHERE, GROUP BY, and HAVING CLAUSES) into partitions. The rows in the new partitions are sorted and aggregates are applied to sets of rows.
DB2 10 also has more indexable/stage 1 predicates and SQL pagination (mapping of predicates to a single index).
The various SQL enhancements improve DB2 family consistency and simplify portability from other platforms, including other relational database managers.
6. Temporal (or versioned) data