Temporal, or versioned data, involves the ability to give your data two-dimensional history. Consider an example. Let’s say you have a table with customer policy information. This table has a row for the customer describing his coverage. This row has a special kind of column identifying when the policy was opened (its effective time) and when it expires (its end time). However, now the customer changes his coverage.
Rather than simply updating the existing row and effectively destroying any information originally known about the policy, the organization can make a change, causing a new row to be created with a new effective time but with the same end time. Every policy change would be reflected with a new row with a new effective time. If they close the policy, a final row is added that updates the end time. You may say, “We already do this.” Well, what if you could do this all through the database engine with no need for complex application coding? In addition, the database engine could retrieve any piece of data between any set of times. It’s available in DB2 10. DB2 will also give you system date capability for start time and end time representing when a row was initially inserted and modified or deleted and giving you a history table. Abstracts such as AS OF, FROM and TO, and BETWEEN times are translated into business time and system time paired columns managed by DB2, enforcing business rule integrity.
5. Improved security
DB2 10 provides some of the most significant security and compliance enhancements ever made to DB2:
- A complete and total separation between system and database administration (SYSADM and DBADM) from any kind of data access of the objects they manage
- A DBADM type privilege set that’s granted across all databases, not just one database
- Specific administrative-type privilege sets designed for security administrators and performance and monitoring management
- An EXPLAIN privilege that will allow the holder to issue an EXPLAIN ALL without having any kind of execute privileges on the SQL statement
- SQL PREPARE and DESCRIBE TABLE statements without privileges on the objects
- Explain dynamic SQL statements using the new special register CURRENT EXPLAIN MODE = EXPLAIN
- Column and row access controls that allow masking of values and restriction of user access to certain cells. (This feature is almost like using a view definition to enforce the results of a grant.)
- Extended and redefined audit privileges.
Security in DB2 will be more flexible, duties to be performed will be better separated, administrators will be able to perform their jobs without fear of accessing data, and it will all be simpler to control. There’s even a new clause that will prevent cascading.
4. More online changes
Unlike previous versions of DB2, this time it’s not just about Data Definition Language (DDL); it’s about subsystem changes, utility changes, and data definition changes. In today’s fast-paced world, who has time to recycle DB2 just because something changed?
Online changes to table spaces are significant. Remember how you couldn’t specify MEMBER CLUSTER with Universal Table Spaces (UTS) in DB2 9? In DB2 10, you can specify MEMBER CLUSTER for a UTS and use ALTER TABLESPACE to turn it on.
With DB2 10, you can change DSSIZE and SEGSIZE much more easily with the ALTER command. You can also alter from simple table space to a partition by growth UTS, a simple segmented table space to a partition by growth UTS, and partitioning to partition by range UTS. REORG is going to play a huge role in all this conversion from one table space type to another. There are several other UTS enhancements planned that are too numerous to detail here.
The article “The Magic of Keeping DB2 in Order” (z/Journal February/March 2010) mentioned recommendations that were being validated because of some APAR restrictions. However, in DB2 10, the news is even better. Sort work files will be limited to DB2 managed page sets with zero secondary specified, and Declared Global Temporary Tables (DGTTs) are limited to DB2 managed page sets with a secondary (non-zero) specification.