The Top Ten Features of DB2 for
Every new release of DB2 is
burgeoning with new features and functionality that will be of use to someone.
And everyone has their own opinion as to which features are the most important.
I’m using this installment of my column to comment on the top ten new features
of the latest version of DB2 that will be most useful to the most shops. So, as
is standard with such lists, let’s start with number 10 and work our way up to
10. Online Schema Change:
It has been a long-standing desire of DBAs to be able to change DB2 database
schemas using simple ALTERs instead of having to drop and re-create DDL. Well,
V8’s online schema change capability is IBM’s first step in making that dream a
reality. But it ranks so low on this Top 10 list because the job is not complete
and there are underlying issues with what had been delivered. The net result is
that some types of changes are now easier, but application performance can
suffer after a change when DB2 converts between the old format and the new
9. Recursive SQL:
Recursion is implemented in DB2 using common table expressions (CTEs), which
also are new to DB2 V8. A CTE can be thought of as a named temporary table
within a SQL statement that is retained for the duration of that statement. A
CTE is defined at the beginning of a query using the WITH clause. After it is
defined, the CTE can be referenced by name within the rest of the SQL
statement. Although it may be a bit complicated to learn, recursive SQL adds
great power to our DB2 querying capabilities. If you have a business need to
walk or explode hierarchies in DB2, recursive SQL helps immensely.
8. Dynamic Scrollable
Cursors: Although scrollable cursors were introduced in V7, V8 boosts their
value by making them more usable with better performance. Dynamic scrollable
cursors enable direct access to the data in the base table instead of having to
use a declared temporary table – and they do not materialize. Each FETCH returns
the most current data and is sensitive to all INSERTs, UPDATEs, and DELETEs –
and that means no more delete holes and missing updates.
7. Multi-row FETCH and
INSERT: With this great new feature you can use arrays as a target for your
FETCH or to bulk INSERT data. By accessing data in sets, instead of row by row,
you can greatly speed up application performance when large numbers of rows need
to be processed.
6. Materialized Query Tables:
A materialized query table, or MQT, is basically a view whose data is
materialized and saved. So, you can turn a view into a table using MQTs,
providing potential benefit for your complex business intelligence queries
performing summaries and analytics. Of course, there are management issues
involved, but you can use MQTs to create “denormalized” structures to improve
query performance – and leave those base tables fully normalized.
5. Sequence Objects:
Sequence objects improve upon DB2’s ability to automatically assign sequential
values to a column. Like identity columns, sequence objects control the starting
point for generating values and how to increment; but sequence objects are more
flexible and generally useful than identity columns. Sequence objects are not
forced onto a single table, can be used by multiple data types, and are easier
to control during program testing.
Data-partitioned secondary indexes, or DPSIs, help to alleviate the management
problems of NPIs. Because the index partitioning scheme aligns with the data
partitioning scheme, even when the key is not the same, index partitions can be
managed independently by utilities. Of course, there are performance issues that
need to be addressed in your applications if you switch from NPIs to DPSIs.
3. Stage 1 for Unlike Data
Types: In past versions of DB2, whenever the data type and length of the two
arguments of a predicate did not match, DB2 would degrade the predicate to Stage
2. Now, for like data types (numeric to numeric, or character to character, for
example), DB2 will not degrade predicates to Stage 2 due to non-matching data
type and length. All you need to do is rebind!
2. Partitioning changes:
V8 introduced many useful changed to partitioning. Collectively, I group these
together as #2. Changes include the ability to define up to 4,096 partitions to
a single table space, to define the partitioning limit keys in the table instead
of in an index, and the ability to separate clustering from partitioning. These
modifications greatly improve our ability to define, use, and manage partitioned
table spaces in our applications.
1. 2M SQL Limit: And
finally, at #1, we have the ability to write very long and complex SQL
statements, without getting the nebulous –101 return code, which provides the
very useful explanation “SQL statement too complex.” By changing the size of
the largest SQL statement from 32,765 bytes to 2,097,152 bytes, V8 makes it
possible to write much more complex – and therefore much more useful – DB2 SQL
statements. And that is definitely a #1 priority!
Feel free to disagree with me,
but I think these are the ten features of V8 that will have the biggest,
immediate impact on DB2 systems and applications.