Nuggets from DB2 by the Bay, Part 2

More items of information from the 2010 International DB2 Users Group North American Conference, held earlier this month in Tampa (in fact by the bay -- the convention center is on the waterfront).

IBM's Roger Miller delivered a session on DB2 for z/OS Version 10 (in beta release for the past couple of months) with typical enthusiasm (opening line: "This is a BIG version"). Some of his points:

Much attention was paid to making life easier for DBAs. Among the labor-saving features of DB2 10:

• Automated collection of catalog stats, so you don't have to mess with the RUNSTATS utility if you don't want to.
• Worry-free scale-up, with DB2 thread-related virtual storage now above the 2 GB "bar" in the DB2 database services address space. The number of concurrently active threads can go WAY up in a DB2 10 environment.
• Access path stability, a favorite of many DB2 9 users, is enhanced. That makes for worry-free rebinding of packages (and you'll want to rebind to get the aforementioned below-the-bar virtual storage constraint relief, and to get the benefit of optimizer improvements).
• Reduced catalog contention will allow for more concurrency with regard to CREATE/ALTER/DROP activity.
• The ability to build a tablespace compression dictionary on the fly removes what had been a REORG utility execution requirement.

Resiliency, efficiency, and growth:

• DB2 9 for z/OS gave us some nice utility performance enhancements (referring particularly to reduced CPU consumption). DB2 10 delivers significant CPU efficiency gains for user application workloads (batch and OLTP).
• More-granular DB2 authorities enable people to do their jobs while improving the safeguarding of data assets. An example is SECADM, which does not include data access privileges.
• "Release 2" of DB2's pureXML support improves performance for applications that access XML data stored in a DB2 database.
• An "ALTER-then-REORG" path makes it easier to convert existing tablespaces to the universal tablespace type introduced with DB2 9.
  

Counting down. Roger's 10 favorite DB2 10 features:

• 10. (tie) Hash access to data records (do with one GETPAGE what formerly might have required five or so GETPAGEs), and index "include" columns (define a unique index, then include one or more additional columns to improve the performance of some queries while retaining the original uniqueness-enforcement characteristic of the index).
• 9. Improved XML data management performance and usability.
• 8. Improved SQL portability.
• 7. Support for temporal (i.e., "versioned") data (something that previously had to be implemented by way of application code).
• 6. The new, more-granular security roles.
• 5. More online schema changes.
• 4. Better catalog concurrency.
• 3. 5X-10X more concurrent users due to the removal of memory constraints.
• 2. CPU cost reductions for DB2-accessing application programs.
• 1. Productivity improvements.

You get a lot of benefits in DB2 10 conversion mode:

• More CPU-efficient SQL.
• 64-bit addressing support for more of the EDM pool and for DB2 runtime structures (thread-related virtual storage usage). You'll need to rebind to get this benefit.
• Improved efficiency of single-row retrieval operations outside of singleton SELECTs, thanks to OPEN/FETCH/CLOSE chaining.
• Distributed thread reuse for high-performance database access threads (aka DBATs).
• Improved elapsed times for insert operations, thanks to parallelized index updates (for tables on which multiple indexes have been defined).
• Support for 1 MB pages.
• Access path enhancements, including the ability to get index matching for multiple in-list predicates in a query.
• More query parallelism (good for zIIP engine utilization).
• More avoidance of view materialization (good for efficiency).
  

More stuff:

• Dynamic statement cache hits for statements that are identical except for the values of literals (this requires the use of a new attribute setting).
• CPU efficiency gains of up to 20% for native SQL procedures (you regenerate the runtime structure via drop and recreate).
• Hash access to data rows.
• Index include columns.
• In-line LOBs (storing of smaller LOB values in base table rows). Roger called these smaller LOBs "SLOBs." LOBs stored in-line in a compressed tablespace will be compressed. In-line storage of LOBs will require a universal tablespace that's in reordered row format (RRF). Said Roger: "RRF is the future."
• Universal tablespaces can be defined with the MEMBER CLUSTER attribute (good for certain high-intensity insert operations, especially in data sharing environments).
• "ALTER-then-REORG" to get to a universal tablespace, to change page size, to change DSSIZE (size of a tablespace partition), and to change SEGSIZE. With respect to "ALTER-then-REORG," you'll have the ability to reverse an "oops" ALTER (if you haven't effected the physical change via REORG) with an ALTER TABLESPACE DROP PENDING CHANGES.
• Online REORG for all catalog and directory tablespaces.

Scalability improvements:

• Reduced latch contention.
• A new option that lets data readers avoid having to wait on data inserters.
• Much more utility concurrency.
• 64-bit common storage to avoid ECSA constraints.
• Package binds, data definition language processes, and dynamic SQL can run concurrently.
• The skeleton package table in the directory will use LOBs. With CLOBs and BLOBs in the DB2 directory, the DSN1CHKR utility won't be needed because there won't be any more links to maintain.
• SMF records produced by DB2 traces can be compressed: major space savings (maybe 4:1) with a low cost in terms of overhead.
• "Monster" buffer pools can be used with less overhead.
• You'll be able to dynamically add active log data sets.
• You'll be able to grant DBADM authority to an ID for all databases, versus having to do this on a database-by-database basis.
  

More catalog and directory changes:

• The catalog and directory will utilize partition-by-growth universal tablespaces (64 GB DSSIZE).
• There will be more tablespaces (about 60 more).
• Row-level-locking will be used.
• The objects will be DB2-managed and SMS-controlled.
  

It really is a BIG version -- and there's still more to it (I've just provided what I captured in my note-taking during Roger's session). More nuggets to come in my part 3 post about DB2-by-the-bay. Stay tuned.