What are the top new features of DB2 10?

(1)

What are the top new features of DB2 10?

As you’re probably aware, at the end of October 2010 IBM launched the latest version of its flagship database product – DB2 10 for z/OS. Having been involved in the early beta testing and whilst working with large DB2 for z/OS users, Triton have been able to really get to grips with some of the great new technical features.

From a technical point of view there are some really interesting new features and collectively, they deliver real and quantifiable business benefits as well. The business justification for moving to DB2 10 is really quite compelling.

However, in this article we’re keeping it techie and taking you through some of the features which we believe are going to be the most important for you to make use of after your upgrade.

Top new features:

• CPU/Performance Improvements

• Virtual Storage Enhancements • Security Extensions

• Improved Catalog Concurrency

• Temporal Data

• Access Path Management • pureXML enhancements • Currently Committed semantics • Automated statistics

• Dynamic schema change enhancements • In-memory object support

• Optimiser enhancements • MEMBER CLUSTER for UTS

• Backup and recovery enhancements • Enhanced audit

• Include additional index columns • Enhanced SQL OLAP functions • Skip Migration

• And many more…. !

Here are some of our personal favourites: CPU/Performance Improvements

Most releases of DB2 in the past have kept performance regression very low, with customers typically taking a CPU hit of less than 5% out of the box (and all or some of that could usually be clawed back later through implementing new function). Version 8 of DB2 for z/OS was somewhat more painful for most customers, with the inefficiencies associated with the move to 64-bit addressing typically increasing overall CPU usage by 5-10% and sometimes even more. That CPU increase made it even more difficult than usual for many infrastructure managers to put together a convincing business case for upgrading to V8, especially as many sites began to consider the upgrade just as the current financial crisis began and IT budgets were squeezed. Although Version 9 has since redressed the balance somewhat with modest CPU savings for many customers, it’s clear that IBM was determined to provide a compelling cost case for moving to Version 10 in addition to some intriguing new function. Because of this, CPU improvements were one of the major design goals for

(2)

DB2 10. Many savings are available “out of the box” with no application or database changes required. There are even more available with some DBA/developer effort!

What CPU reductions can you expect for CPU reductions of 5

Up to additional 10% CPU savings using new functions CPU reductions of up to 20% for new workloads For static SQL, REBIND typically required

Virtual Storage enhancements

In V8 IBM began a major project to transform DB2 into a 64 provide some scalability improve

DB2 10. Many savings are available “out of the box” with no application or database changes . There are even more available with some DBA/developer effort!

What CPU reductions can you expect for transactions, queries, and batch? CPU reductions of 5-10% for traditional workloads

Up to additional 10% CPU savings using new functions CPU reductions of up to 20% for new workloads For static SQL, REBIND typically required

a major project to transform DB2 into a 64-bit RDBMS. The groundwork was laid to some scalability improvements but a lot of DBM1 objects remained below the 2GB bar DB2 10. Many savings are available “out of the box” with no application or database changes

. The groundwork was laid to ments but a lot of DBM1 objects remained below the 2GB bar.

(3)

In the DB2 9 release things a little, but only by another 10 300-500 threads per DB2 subsystem

DB2 10 moves 80-90% of the remaining objects above the

threads per subsystem. This is great news because less DB2 subsystems mean lower data sharing overhead and less systems to manage and maintain. We also get m

objects such as dynamic statement cache

Security Extensions

In DB2 10 new authorities have been introduced to separate data administration and data access: • Security Administrator (SECADM)

• System DBA (SYSTEM DBADM) • Data Administrator (DATAACCESS) • Performance Specialist (SQLADM) New row and column data access policy controls and apply to SELECT, INSERT, UPDATE and DELETE.

a little, but only by another 10-15% for most customers 500 threads per DB2 subsystem.

90% of the remaining objects above the bar, resulting in 5-10x improvement in . This is great news because less DB2 subsystems mean lower data sharing overhead and less systems to manage and maintain. We also get more space for critical storage

atement cache.

In DB2 10 new authorities have been introduced to separate data administration and data access: Security Administrator (SECADM)

System DBA (SYSTEM DBADM) Data Administrator (DATAACCESS) Performance Specialist (SQLADM)

New row and column data access policy controls have been fully integrated into the database engine and apply to SELECT, INSERT, UPDATE and DELETE.

15% for most customers - practical limit of

10x improvement in . This is great news because less DB2 subsystems mean lower data sharing

ore space for critical storage

In DB2 10 new authorities have been introduced to separate data administration and data access:

(4)

(5)

At a recent IBM event (link to presentation) closer look:

Most IT systems need to keep historical as well as current information.

requirements would have involved the DBA and application developers spending valuable time creating and testing the code and

while minimizing any performance impact.

DB2 10 provides this functionality as part of the database engine, making DBAs and developers more productive. All the DBA needs to do is ind

when they are created, and DB2 will automatically maintain the history whenever an update is made to the data. Elegant SQL support allows the developer to query the database with an "as of" date, which will return the information that was current at the specified time.

Preparing a table for temporal support is relatively simple. Start and end timestamp columns are used by DB2 to determine when a given version of a row was valid, so these need to be added to the table to be temporally enabled (this can be done via

columns can be specified as GENERATED ALWAYS

(link to presentation), Temporal Data proved to be a hot topic so

Most IT systems need to keep historical as well as current information. Previously, these kinds of requirements would have involved the DBA and application developers spending valuable time creating and testing the code and associated database design to support the historical perspective, while minimizing any performance impact.

DB2 10 provides this functionality as part of the database engine, making DBAs and developers more productive. All the DBA needs to do is indicate which tables/columns require temporal

when they are created, and DB2 will automatically maintain the history whenever an update is made to the data. Elegant SQL support allows the developer to query the database with an "as of" date,

urn the information that was current at the specified time.

Preparing a table for temporal support is relatively simple. Start and end timestamp columns are used by DB2 to determine when a given version of a row was valid, so these need to be added to the table to be temporally enabled (this can be done via ALTER TABLE...ADD COLUMN

GENERATED ALWAYS (so that the relevant timestamps are

, Temporal Data proved to be a hot topic so let’s take a

Previously, these kinds of requirements would have involved the DBA and application developers spending valuable time

associated database design to support the historical perspective,

DB2 10 provides this functionality as part of the database engine, making DBAs and developers more temporal support when they are created, and DB2 will automatically maintain the history whenever an update is made to the data. Elegant SQL support allows the developer to query the database with an "as of" date,

Preparing a table for temporal support is relatively simple. Start and end timestamp columns are used by DB2 to determine when a given version of a row was valid, so these need to be added to the

ALTER TABLE...ADD COLUMN). If required, the (so that the relevant timestamps are

(6)

automatically populated by DB2) and SELECT * statements submitted by

Once the columns have been added to the base table, an additional "history table" is created. This has to have an identical structure to the base table (which can be easily accomplished v

TABLE….LIKE). Finally, an ALTER TABLE….ADD VERSIONING

versioning on the base table and identify the associated history table to DB2. As shown the diagram below, DB2 then automatically maintains the history tab

in the temporal table. This is completely transparent to the developer, who codes INSERT/UPDATE/ DELETE SQL against the base table as usual.

When a row is updated (as shown at time T3 in the diagram), DB2 will store a version of the old in the history table before updating the current row in the main table. Similarly, when a row is deleted it is first copied to the history table before being removed from the main table. DB2 maintains system timestamps (the SYS_START and SYS_END colum

during which a given version of the row was current.

automatically populated by DB2) and IMPLICITLY HIDDEN (so that they won't show up on any SELECT * statements submitted by application programs).

Once the columns have been added to the base table, an additional "history table" is created. This has to have an identical structure to the base table (which can be easily accomplished v

ALTER TABLE….ADD VERSIONING statement is used to enable temporal versioning on the base table and identify the associated history table to DB2.

As shown the diagram below, DB2 then automatically maintains the history table for updated rows in the temporal table. This is completely transparent to the developer, who codes INSERT/UPDATE/ DELETE SQL against the base table as usual.

When a row is updated (as shown at time T3 in the diagram), DB2 will store a version of the old in the history table before updating the current row in the main table. Similarly, when a row is deleted it is first copied to the history table before being removed from the main table. DB2 maintains system timestamps (the SYS_START and SYS_END columns shown) to record the period during which a given version of the row was current.

ow up on any

Once the columns have been added to the base table, an additional "history table" is created. This has to have an identical structure to the base table (which can be easily accomplished via CREATE

statement is used to enable temporal

le for updated rows in the temporal table. This is completely transparent to the developer, who codes INSERT/UPDATE/

When a row is updated (as shown at time T3 in the diagram), DB2 will store a version of the old row in the history table before updating the current row in the main table. Similarly, when a row is deleted it is first copied to the history table before being removed from the main table. DB2

(7)

The new "AS OF" clause in SQL SELECT statements allow the developer to see the data as it was at a given point of time. In the example, the policy information at time T2 is required, which will return the original address (A3) instead of the current address (A4). Note that the developer will be completely unaware that the history table has been accessed in this case, as DB2 automatically determines which version of the temporal table to get the data from.