Jun 21 ’10
Top-10 Features of DB2 10 for z/OS: A Preview
On March 12, 2010, IBM announced a closed beta for DB2 10 for z/OS and a few select customers are now putting it through its paces. We don’t yet know when DB2 10 will be Generally Available (GA), nor has IBM announced when DB2 Version 8 (in service for six years) will go out of service.
This article offers a DB2 10 for z/OS “Top-10 list,” previewing new functions of a system that offers improved performance with fewer CPUs.
10. Productivity improvements
DB2 10 reduces the need for REORGs by:
- Building compression dictionaries on the fly
- Using index list prefetch, a single SDSNEXIT data set you can share across multiple DB2 subsystems
- Providing the ability to update all statistics stored in the DB2 catalog.
DB2 10 uses a new set of stored procedures and catalog tables to determine what statistics need to be collected and when. It also records what activities are being performed and does it all via an automatic scheduling process. That’s a significant improvement over RUNSTATS, which is challenging to use and can result in collecting unnecessary statistics or not enough statistics, a waste of resources that may not be available.
9. Better access to data
DB2 10 will take advantage of hash access, which should be faster and provide a nice CPU savings. Hashing is a technique that turns a key into a physical location of a row, resulting in one-page access for a matching row on an equal predicate. If you’ve worked with IMS/VS, you may already have some exposure to hash access. If not, no problem. You’ll quickly learn to like it.
There’s also a nice index improvement called index includes. A unique index exists to enforce a unique constraint on a table. Sometimes, a unique index doesn’t contain enough columns in the key to give index-only access. One possible solution is to create two indexes. One index enforces uniqueness and a second index, with additional columns in the key, gives index-only access. DB2 10 will let you "include" additional columns in the index that are not included in the key to enforce uniqueness. One index gives you uniqueness and index-only access.
Both hash access and index include won’t be available until the release of DB2 10 New Function Mode (NFM).
Then there’s access path stability. This is a bit more than what was delivered via an APAR to DB2 9 and V8. Although you still get all the package versioning in DB2 10, you also get:
- Package hints that are globally available
- An easier method for getting plan hints for dynamic SQL
- A new CURRENT EXPLAIN MODE special register that allows EXPLAIN at PREPARE time
- A method to apply certain DSNZPARM optimization keywords to individual SQL statements
- Additional features.
DB2 10 will allow an upgrade from DB2 9 or V8. If you’re one of the few looking to jump directly to DB2 10 from V8, you can. However, if you envision migrating to DB2 9 this year or next, we strongly suggest doing so.
8. XML performance
DB2 10 offers insert and query performance improvements such as binary XML support and multi-versioning for XML data. However, there are also XML query language enhancements, including subdocument update, data and time support, and the use of XML in native SQL stored procedures and User-Defined Functions (UDF). DB2 will use the z/OS XML System Services for XML validation with 100 percent of that validation being redirected to specialty processors: the System z Application Assist Processor (zAAP), and System z Integrated Information Processor (zIIP). Among other XML data integrity improvements, DB2’s CHECK DATA utility will perform document structure and schema validation.
7. SQL enhancements and improved portability
DB2 10 adds further SQL improvements to those introduced in prior releases. One of the handier improvements is an increased length of timestamps taking the stored value out to the nanosecond. Another is a new special register SET CURRENT TIMESTAMP, which allows the precision of the timestamp to be set. Several enhancements allow the control of time zones, including a data type, two new special registers, a new ZPARM keyword, and a new operator.
An application program will be able to use indicator variables when determining whether a target column of a host variable should contain a default value or an unassigned value. If the value is unassigned, the column is treated as if it was never part of the statement. This capability will be enabled on an individual PREPARE statement for dynamic SQL or with a new bind option for static SQL.
DB2 10 extends DB2’s support for data warehouse and Business Intelligence (BI) applications via the moving sum and moving average functions, a subset of the Online Analytical Processing (OLAP) specification. Moving sum and moving average divide the final result of a query (after applying the WHERE, GROUP BY, and HAVING CLAUSES) into partitions. The rows in the new partitions are sorted and aggregates are applied to sets of rows.
DB2 10 also has more indexable/stage 1 predicates and SQL pagination (mapping of predicates to a single index).
The various SQL enhancements improve DB2 family consistency and simplify portability from other platforms, including other relational database managers.
6. Temporal (or versioned) data
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.
The maximum sort key length will also be increased from 16,000 to 32,704 with a maximum record length limit increased to 65,529, along with record lengths for sorts being restricted to 32k. DB2 10 will deliver partition by growth support for work files. And there are other improvements for work files; those will be a topic for a future article.
Note: Classic table space partitioning, a concept used since DB2 V1.1, will be deprecated in DB2 10.
There have been numerous warnings in previous versions of DB2 about the removal of private protocol. Well, it has finally happened. Private protocol will not be in DB2 10. Review APARs PK92339 and PK64045 to prepare for the removal of private protocol.
If you’re hoping you can avoid index REORGs, or perform them less frequently, DB2 10 addresses your wish at least partially with:
- List prefetch for index leaf pages
- Improved caching for non-leaf pages
- Sequential detection and index look-aside for parent key look-up on Referential Integrity (RI) inserts
- A new IFCID to track leaf page splits
- Bunches of REORG improvements and enhancements for everything else a REORG does.
All these enhancements are available in DB2 10 Conversion Mode (CM)—and there are other items on the list of utility improvements. Data set-level FlashCopy for COPY, REORG inline copy, and LOAD inline copy FLASH COPY backups can be used as input to RECOVER, COPYTOCOPY, DSN1COPY, and DSN1PRNT. A few new ZPARMs will help out, too.
A set of new stored procedures will use the new DB2 Admin Scheduler and facilitate automatic collection of statistics.
On the system side, checkpoint processing can now occur by both time and number of logs. Again, a few new ZPARMs will be useful. DISPLAY LOG will tell you what’s happening. You’ll also benefit from a command to dynamically add a new ACTIVE LOG to DB2. You won’t be able to dynamically delete a log, but adding one could be significant.
3. Concurrency improvements
The DB2 catalog function will be enhanced with support for changing catalog tables, not just adding more of them. Also, DB2 catalog capacity will be enhanced with more tables. Future catalog posters won’t be posters at all; they’ll be wall hangings for only large walls.
You’ll experience fewer performance bottlenecks with heavy workloads in DB2 10 due to reduced latching and system serialization contention and the use of 64-bit common storage to avoid Extended Common Service Area (ECSA) constraints. Parts of the DB2 catalog have been restructured so that DDL, BINDs, and PREPAREs can process without contending with each other. The restrictive size of the SPT01 has been addressed by using Large Objects (LOBs) for certain package parts. All this will be accomplished by:
- Using more table spaces (60 new catalog table spaces)
- Using partition by growth table spaces
- Implementing row-level locking for some catalog tables
- Allowing real online REORG (SHRLEVEL CHANGE) and CHECK against the catalog.
You can avoid some REORG issues with the catalog if you get rid of the links in the catalog tables that used to use them. DB2 is also going to use more Character LOBs (CLOBs) and Binary LOBs (BLOBs) for catalog columns with long strings as well as manage the catalog tables using more DB2-managed System Managed Storage (SMS)-controlled DB2 table spaces. Catalog conversion will occur during Enabling New Function Mode (ENFM).
2. Enable 10 times the users by avoiding memory constraints
Up to 90 percent of storage for thread processing is now above the 2GB bar. This should give most customers the ability to run five to 10 times the number of threads in a single subsystem.
This change impacts many things, one of which is Logical Partition (LPAR) consolidation. Although data sharing’s major benefit is availability, many customers have moved to multiple data sharing members to spread their users across multiple LPARs. Even in DB2 9, you were pressed to run more than 500 to 700 users in a single DB2 address space. If you needed more users, you had to move to data sharing; the more users, the more data sharing members were required. Of course, reducing LPARs reduces complexity and cost and things can be easier to monitor and manage, Nevertheless, data sharing is still a great availability feature and useful if you have high-availability requirements.
1. CPU reductions for transactions, queries, and batch
DB2 lab staff members have said DB2 10 provides the best reduction in CPU for transactions and batch in more than 20 years. The lab expects most customers to see about a 5 to 10 percent reduction in CPU times when they install DB2 10 CM. Once DB2 10 is installed and customers start to take advantage of the new functionality and enhancements in NFM, they should see additional memory and CPU usage reductions.
What Isn’t Included
It’s only fitting to also mention what’s being removed with DB2 10. This list, which may grow or shrink, assumes an upgrade from DB2 9 to DB2 10. If upgrading from V8 to DB2 10, there are additional items that will be removed that are listed in the DB2 9 Installation Guide (GC18-9846):
- Private protocol won’t be supported, so it’s time to move to Distributed Relational Database Architecture (DRDA). The REXX tool in DB2 9 can help you identify and convert all objects still using private protocol.
- If you have any old packages that were bound at DB2 V5 or before, they won’t work in DB2 10 and, if detected, will be automatically rebound.
- EXPLAIN tables created before V8 are no longer supported; all EXPLAIN tables will also use UNICODE.
- XML Extender is no longer supported; you can use pureXML.
- DB2 10 will no longer support plans having directly bound Database Request Modules (DBRMs). Planning should start as soon as possible to move off the old style plans that use DBRMs. There should be maintenance in DB2 9 to help with migration from DBRMs bound directly into plans.
- ACQUIRE(ALLOCATE) packages won’t be supported; use ACQUIRE(USE) instead.
- DSNCHKR is no longer supported for managing DB2 catalogs.
- Compression on table space SPT01 isn’t supported.
DB2 10 offers many valuable enhancements, but there’s still much more to talk about that’s not mentioned or fully detailed here. Clearly, few data servers can top DB2’s efficiency, resilience, and growth potential.
To learn more about DB2 10 and monitor its evolution, visit www.ibm.com/software/data/db2/zos/db2-10/. This will be an interesting year for DB2. Once GA is announced, the excitement will continue. Stay tuned!