Part 1  |  Part 2

Business Intelligence

Now, DB2 V8.1 brings business intelligence capabilities into the database engine.

Multidimensional clustering (MDC)

MDC is new clustering technology that provides a method for automatic, continuous clustering of data along multiple dimensions. And MDC tables don’t require database maintenance operations, such as reorganization. MDC primarily benefits data warehousing type queries and large database environments; however, it can also be useful for transaction processing.

MDC lets tables be physically clustered on more than one dimension (key) simultaneously (refer to figure 3).

Figure 3: An example of an MDC with two dimensions, Merchandise and YearAndQtr.

To understand how MDC works, it’s helpful to know the following definitions:

• Cells are made up of unique combinations of dimension values composed of blocks of pages, where a block is a set of consecutive pages on disk.
• Slices are sets of blocks containing pages having certain key values of one of the dimension block indexes.
• Dimension block indexes are indexes that are automatically created for each dimension specified.
• Composite block indexes are automatically created indexes that contain all dimension key columns. They’re used to maintain the clustering of data over insert and update activity. These indexes are only created if a single dimension doesn’t already contain all the dimension key columns. The optimizer can use both dimension block indexes and composite block indexes to efficiently retrieve data.

Every page of an MDC table is part of exactly one block, and all blocks of an MDC table consist of the same number of pages. This characteristic is known as the Blocking Factor. The Blocking Factor is equal to the extent size, so that block boundaries line up with extent boundaries.

Dimension block indexes are structured like traditional indexes except at the leaf level. Instead of using record identifiers (RIDs) as traditional indexes do, dimension block indexes at the index-leaf level point to block identifiers (BIDs). Because they point to blocks that can contain many pages, BID entries are much smaller than RID indexes.

Tables with MDC can also have traditional indexes, triggers, and referential integrity. Design Advisor doesn’t consider or recommend MDCs at this time. For more information, refer to the DB2 UDB Administration Guide: Planning.

Materialized Query Tables (MQT)

In V8.0, MQTs use summarized data to provide high-performance joins. Automatic summary tables (ASTs) are a subset of MQTs in which the AST has a FULLSELECT that contains a GROUP BY clause summarizing data from the tables referenced in the FULLSELECT.

Queries can be routed to MQTs whose definitions contain a join that is not aggregated. The optimizer will recognize that the MQT contains the requested information and will use the MQT instead of the base tables. MQTs can be incrementally maintained resulting in improved performance.

Nickname support

MQTs can be defined on nicknames, resulting in the remote data being cached on the local DB2 instance. This capability can result in huge performance gains for federated queries, because the remote data is accessed locally. And, if the remote table is not available for some reason, DB2 can use the MQT defined on it if it meets the routing criteria, resulting in improved availability and performance.

User-maintained MQTs

IBM recognized the need to provide a mechanism for users to maintain and load tables that contain precomputed data. The user-maintained MQT is the solution. These MQTs are managed by the user (not the system) and are distinguished by the MAINTAINED BY USER option of the CREATE SUMMARY TABLE statement. This enhancement makes it easier for existing Oracle users to migrate to DB2.

Prefetching enhancements

You can use block-based buffer pools to improve prefetch performance. The BLOCKSIZE parameter of the CREATE or ALTER BUFFERPOOL statement defines the size of the blocks and number of pages to be read from disk in a single I/O. When a block based buffer pool is defined, DB2 will use block I/Os to read multiple pages into the buffer pool in a single I/O. This enhancement will benefit data warehouses and large database environments that use significant prefetching.

Compression of nulls and defaults

You can enable this feature, which benefits companies with data warehouses and large databases, by using the VALUE COMPRESSI/ON and COMPRESS SYSTEM DEFAULT clause of the CREATE TABLE statement. This enhancement reduces disk storage requirements and can increase the performance of large table scans. Once enabled, a new data row format will provide efficient storage of null and 0-length values. Note that only system default values are compressed; userdefined values aren’t.

Data warehouse enhancements

The DB2 Warehouse Manager, once limited to Windows platforms, supports AIX and Linux. The warehouse server, logger, initialization, external trigger, and mail notification program are all available on AIX, and Warehouse Manager capabilities have been extended to Linux (32-bit Intel) with Linux Kernel level 2.4.7 and glibc 2.2.4.

Performance

Many of the changes I’ve mentioned benefit performance. And there are many more, which I’ll cover here.

Catalog and authorization caching

For databases with multiple partitions, an extension of catalog cache will be provided at each partition. Cached information will include SYSTABLE and authorization information. For applications with multiple coordinator partitions, this feature will improve performance significantly, as the local cache will eliminate trips to the catalog partition.

Asynchronous I/O enhancements

V8.1 exploits AIX asynchronous page cleaning performance. Because asynchronous I/O isn’t always enabled on AIX, it must be enabled before V8.1 installation. Two AIX asynchronous I/O parameters that you can tune are MINSERVERS and MAXSERVERS. Configure these parameters using the System Management Interface Tool. Refer to the AIX Performance Management Guide for additional information.

Java stored procedure performance enhancements

Java routines are implemented using a thread-based model, which results in significant performance improvement for multiple routines executing simultaneously. Thread-based models, or lightweight threads, share process memory and control blocks and can block without causing a context switch. Additionally, routines share the Java Virtual Machine (JVM); previously, a JVM was created for each executing routine.

Connection concentrator

Connection concentration enables many transient connections, such as Internet connections, to share the same logical coordinating agent. As a result of the reduced memory requirements, DB2 can accommodate more concurrent users. Previously, DB2 would create a coordinating agent for each connection. Sharing logical coordinating agents greatly reduces memory requirements for each connection and improves performance by reducing the number of context switches. (Context switches require that the previous execution state and memory structures be saved and restoredlater, meaning lots of overhead.) You can enable this feature by setting MAX_CONNECTI/ONS greater than the value of MAX_COORDAGENTS.

Full 64-bit support

Version 8.1 is 64-bit capable, which lets DB2 provide the super-scalability businesses need today. Full 64-bit capability enables DB2 to make full use of large real memories, which can be exploited for larger buffer pools, sort memory, and other DB2 memory areas. This capability is urgently needed by some of the larger DB2 installations and will provide immediate relief to memoryconstrained systems in use today. The common client plays a key role in this regard, as it provides 64-bit capability for client applications as well as connectivity to 32-bit versions of DB2.

Declared Global Temporary Table (DGTT) enhancements

V8.1 adds rollback support of data changes to DGTTs. The NOT LOGGED clause, which was mandatory in V7.0, is optional. You can create an index on a temporary table and run RUNSTATS.

Type-2 indexes

Type-2 indexes improve performance by eliminating most next-key-share locks, as entries are marked deleted instead of physically deleted from the page. Type-2 indexes are required for online load, online reorganization, and MDC. A table can’t have a mix of type-1 and type-2 indexes. Tables can be migrated to type-2 indexes via index reorganization. Type-2 indexes let you create an index on a column that’s longer than 255 bytes.

Availability

Customers demand access to their accounts anytime, anywhere, from anyplace. Businesses must provide this pervasive capability or lose customers. Several enhancements in DB2 V8.1 help meet those demands.

Online Table Reorganization

Online table reorganization offers a significant opportunity for DBAs to improve the availability and performance of supported databases. No longer will reorganizations have to be scheduled for offhours or during maintenance windows. Online reorganization works by reorganizing a table in place. There is no requirement for temporary space, and specific commands give you the capability to monitor the status of the reorganization.

Online table reorganization allows applications to access the table during the reorganization. Reorganization can also be paused and resumed later, if needed. (Note that online table reorganization is only allowed on tables with type-2 indexes.)

Online Index Reorganization

Online index reorganization allows a table to be read or updated during an index reorganization. During the reorganization, all indexes on the table are rebuilt using a shadow copy. During the time that the shadow copy is made available, no access is allowed to the table. A REORG INDEXES command has been provided with which to run online index reorganization. Unlike online table reorganization, online index reorganization is not done in place and requires temporary space. For more information, refer to the DB2 UDB V8.1 Command Reference.

Dynamic online configuration parameters. You can set more than 50 configuration parameters online without having to stop and restart the instance or database. Parameters that control key memory areas can be changed dynamically and monitored using the Memory Visualizer. Changes can be deferred so that they take effect at the next instance or database start. The GET DB CFG and GET DBM CFG commands feature a SHOW DETAIL option that shows the current and deferred parameter values. A few of these parameters can be set to automatic, and DB2 will adjust the value automatically as the workload changes.

Online Buffer Pool Enhancements

You can create, drop, or alter buffer pools online and defer changes. And if a buffer pool is dropped, DB2 will immediately make the memory available to database shared memory so that it can be reused.

With these capabilities, a DBA could create a script that would run after the business day and alter parameters for nightly batch work or DB2 loads, and then run another script after nightly processing has completed to alter the parameters back to the day settings. This scenario should sound familiar to experienced DB2 for OS/390 DBAs as it is quite common on that platform.

DMS Container Enhancements

You can drop an existing container, reduce the size, and add containers to avoid a rebalance. To add containers to a DMS tablespace to avoid rebalance, use the BEGIN NEW STRIPE SET option of the ALTER TABLESPACE command. This approach forces new containers to be added above the high-water mark. These operations can be performed online, and the new container is immediately available for use.

Serviceability

An INSPECT command lets you check the architectural integrity of tablespaces and tables online. INSPECT can also be used to identify the types of indexes on a table. Additionally, the DB2 trace facility has been redesigned to significantly reduce overhead so that it can be used to capture important diagnostic information for DB2 Support. A command in v8.1, DB2SUPPORT, provides DB2 Support with a “support bundle” of diagnostic and problem determination information.

The Database Analysis and Reporting Tool (db2dart) is officially supported in V8.1. Use db2dart to diagnose structural problems with underlying database objects and repair damaged objects.

Onward and Upward

Through autonomic computing and the SMART initiative, IBM has made DB2 V8.1 easier to operate and manage. Automatically configured parameters and wizards and tools will make DBAs more productive. Application development has been tightly integrated into DB2 with support for a myriad of development tools. The availability enhancements position DB2 to handle tough business demands and to grow with them into the future. And V8.1’s full 64-bit capability will enable DB2 to scale all the way to the moon.

--

Philip Gunning started Gunning Technology Solutions, LLC in 2003. He is a Principal Consultant specializing in DB2 with Gunning Technology Solutions. You might even call him a DB2 Evangelist. Phil has over 17 years experience in the Information Technology industry. Prior to starting his own company, Phil held senior positions with DGI and others,and has consulted with Fortune 500 companies and with government. Phil has presented at IDUG North America, IDUG Europe, and DB2 User Groups all over North America. You can reach him at pgunning@breakthroughdb2.com