Programming and Development

Numerous SQL and programming features are being added to DB2 V8 that will make the job of programming both easier, but at the same time, more complex. This might sound like a paradox, but it is true. Great new features will make programming simpler once they are learned, but it will take time and effort to train the legions of DB2 developers on this new functionality, and when and how best to use it.

Common Table Expressions and Recursion

One big improvement in V8 is the ability to code common table expressions (CTEs) . A common table expression can be thought of as a named temporary table within a SQL statement that is retained for the duration of a SQL statement. There can be many CTEs in a single SQL statement but each must have a unique name and be defined only once. A CTE is defined at the beginning of a query using the WITH clause.

CTEs are an important new feature of DB2 for several reasons. First, in some situations they can be used to reduce the number of views that are needed. Instead of creating a view, you can code a CTE right into your query. But second, and more importantly, CTEs enable recursive SQL.

A recursive query is one that refers to itself. I think the best way to quickly grasp the concept of recursion is to think about a mirror that is reflected into another mirror and when you look into it you get never-ending reflections of yourself. This is recursion in action.

Recursive SQL can be very elegant and efficient. However, because of the difficulty developers can have understanding recursion, it is sometimes thought of as "too inefficient to use frequently." But, if you have a business need to walk or explode hierarchies in DB2, recursive SQL will likely be your most efficient option. Recursion is covered in more depth in Chapter 2 of this book.

Architecture Changes Impacting Application Programming

V8 offers significant changes to the SQL system limits. First, as we have already mentioned, DB2 will now offer long name support for database objects. But it does not stop there. DB2 V8 expands the maximum length of SQL statements to support up to 2 megabytes. This is a major change that permits much more complex SQL statements to be written, optimized, and run within DB2. Additionally, V8 increases the length of literals and predicates to 32K and will support joining up to 255 tables in a single SQL statement. This last one has been promised before, but is finally delivered properly in V8.

Also, as noted in the initial architecture section, 64-bit virtual addressing will greatly increase the amount of memory available to DB2. And IBM is making major enhancements to the internal SQL control block structures, so that DB2 will use memory more efficiently. So more memory, used more efficiently , should translate into the more efficient execution of DB2 SQL.

Java and XML Improvements

For Java programmers DB2 V8 offers expanded functionality in the form of support for both Type 2 and Type 4 Java drivers. Both will be updated to support the JDBC/SQLJ 3.0 standard which brings enhanced support for things such as SAVEPOINT s and WITH HOLD cursors , as well as improvements to connection pooling, and a long list of other expanded features.

DB2 V8 pushes more XML support into the DB2 engine. This includes support for some built-in XML publishing functions such as XMLELEMENT and XML2CLOB (among others).

Additional V8 Programming Improvements

But there are many more application- related improvements in DB2 V8 than just CTEs and recursion. For example, DB2 V8 removes one of the biggest SQL performance impediments of all-time by handling most unlike data types in Stage 1. Previously, if the data type and length of the columns and variables did not match exactly, the predicate was evaluated at Stage 2. DB2 V8 will compare unlike data types in Stage 1 as long as the data types are compatible (that is, number to number, or character to character, and so on).

And there are more application enhancements worth noting in DB2 V8. Consider all of these new features:

• A new statement, GET DIAGNOSTICS , is added that improves the ability to get diagnostic information.

• SEQUENCE objects and sequence expressions.

• New MQSeries functions to read and receive from queues.

• Dynamic scrollable cursors that no longer require temporary tables to implement.

• Scalar fullselect, which means that a SELECT statement that returns a single can be used wherever an expression is allowed.

• More than one DISTINCT clause can now be specified per SQL statement.

• The ability to mix EBCDIC, ASCII, and Unicode columns in a single SQL statement.

• Qualified column names on the SET clause of INSERT and UPDATE statements.

• Grouping expressions can be used in search conditions in HAVING clauses, in the SELECT clause, and in sort key expressions of an ORDER BY clause.

• The ability to SELECT from an INSERT statement.

• Multi-row FETCH and INSERT statements where more than one row can be fetched or inserted by a single statement.

And, as with every previous new DB2 version, IBM is making significant enhancements to improve application performance. DB2 V8 optimization enhancements are scheduled to include sophisticated query rewrite capabilities to support materialized query tables, sparse indexing to improve star join performance, support for parallel sort, and better support for queries with data type and length mismatches which would have caused less efficient access paths in previous releases.