Another useful administration feature of DB2 V8 is schema evolution. Today, there are many types of DB2 changes that require the DBA to DROP and then re-CREATE the object to implement the change. Schema evolution lets the DBA make more types of changes to database objects using native DB2 features. For example, DBAs can:
- Add and rotate partitions of partitioned table spaces
- Expand the length of numeric and character columns using the ALTER statement.
Basically, schema evolution provides more support for various changes to occur directly, using ALTER statements.
Other administration highlights include:
- Support for up to 4,096 partitions per partitioned table space
- Row-level security
- Session variables (for global security)
- Data Partitioned Secondary Indexes (DPSIs).
DPSIs are significant because they’re geared to resolve one of the biggest management headaches DB2 DBAs encounter — dealing with Non-Partitioned Indexes (NPIs) on tables in a partitioned table space. A DPSI is basically a partitioned NPI.
The new security features are interesting, too! With row-level security, DB2 can support applications that need a more granular security scheme. For example, you might want to set up an authorization scenario so employees can see their own data, but no one else’s. To complicate matters, you might also want each employee’s immediate manager to be able to see his payroll information and all of his employee’s data — and so on up through the organization chart. Setting up such a security scheme is next to impossible with current DB2 versions, but it’s straightforward using row-level security in DB2 V8.
Finally, it looks like we’ll be able to have partitioning and clustering independent of one another. In other words, the clustering index key can be different from the partitioning index key.
Programming and Development
Numerous SQL and programming features are being added to DB2 V8 that will make the job of programming both easier and simultaneously more complex. This may sound like a paradox, but it’s true. Great new features will make programming simpler once they’re learned. However, it will take time and effort to train the legions of DB2 developers on this new functionality, including when and how best to use it.
Some V8 SQL improvements include:
- The ability to get diagnostic information
- True SEQUENCEs
- Dynamic scrollable cursors
- Scalar fullselect
- Multiple DISTINCT clauses
- Qualified column names on the SET clause of INSERT and UPDATE statements
- The ability to mix EBCDIC, ASCII, and Unicode columns in a single SQL statement
- The ability to SELECT from an INSERT statement.
V8 will also significantly change SQL system limits. Besides long name support for database objects, V8 expands the maximum length of SQL statements up to 2 megabytes. This major change permits 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 should be delivered in V8.