Introduction

In late April 2005, IBM released the latest version of the IBM DB2 Universal Database for Linux, UNIX®, and Windows® (DB2) product, DB2 V8.2.2 (for those of you that follow the Fix Pack naming convention, you'll know this as Fix Pack 9).

This latest version builds on the the momentum of the DB2 V8.2 release (formerly known as "Stinger") with a good number of new features inspired by a key relationship with SAP and their customers. In fact, DB2 V8.2.2 marks the beginning of an even closer relationship (which has been around for over ten years) with SAP that makes a broad commitment between DB2 and SAP, expanding every aspect of the business, including sales, marketing, and development.

Not an SAP customer and wondering how DB2 V8.2.2 can help? Aside from being a leader in the $47 billion market for corporate-applications software, SAP has over 26,000 customers running over 88,700 installations of their software. From distinct solutions addressing the needs of small and medium-sized businesses to enterprise-scale solutions -- even if you're not an SAP customer, you likely share the same wish-list. I mean, who doesn't want a product that is easier to use, has less locking contention, minimizes storage administration, makes access to administrative data easier, and so on? Quite frankly, I've never been asked if IBM could make DB2 slower, or more difficult to use, or less available - well, you get the point.

Now, more than ever, the tag V8 has come to represent a platform whereby costs can be driven down through leading developer productivity for whatever mainstream language you choose to program in, a flattening of the time-to-value curves through a self-installing, self-tuning, self-healing, and self-configuring database, all built on a highly available and scalable database engine with exceptional performance on a multitude of platforms.

This is the first part of a three part series that takes an in-depth look at the new function, features, and benefits (and a couple of packaging changes) that accompany the DB2 V8.2.2 release. There is no way that I could cover ever detail and function in DB2 V8.2.2 - even if I wrote a 5 part series - so I've decided to pick what I think are the most important features to know about in V8.2.2.

In Part 1 I'll give you the 10,000 foot view on the release in general, discuss some packaging and pricing changes, and go into detail about a number of new features that are bound to make database administrators (DBAs) smile.

In Part 2 of this series, I'll dive into some of the enhancements in DB2 V8.2.2 that benefit performance and show how if the proof of the pudding is in the eating, then DB2 V8.2.2 is definitely for the eating. Aside from just being boisterous about our performance results achieved with this release, I'll take you through some specific enhancements we've implemented for locking and multi-dimensional clustering (MDC) tables, among other things.

This series ends with Part 3, where I'll talk about specific enhancements for the Windows platform and throw in a miscellaneous grab bag section for anything that's left over that I want to talk about.

Back to top

The view from 10,000 feet

So you want the Reader's Digest version of the DB2 V8.2.2 release? The goal of the DB2 V8.2.2 release was to provide a database that integrates seamlessly with SAP applications and requires minimal resources to maintain. We found that focusing on SAP requirements helped us reach thousands of existing and potential customers interested in lowering their total costs of ownership -- and we responded!

From a high level, DB2 V8.2.2 is an integrated SAP database solution that provides organizations features to simplify:

• Installation by "silently" making DB2 a part of the SAP installation process -- you can hide all the details of a DB2 installation in V8.2.2 and just present a status bar for end users if you want.
• Configuration by using the new SAP DB2 configuration "knob" -- a single configuration parameter that optimizes DB2 for SAP environments.
• Storage management with a single point for administration automation and a uniform page size for the database.
• Troubleshooting with a service tool that automatically gathers all required problem determination information to enable faster problem resolution.
• Concurrency with an optimizer enhanced for specific SAP concurrency optimizations that help reduce contention or reduced performance due to locking.
• Efficiency of SAP BW with key BI-related objects like MDC tables for even faster performance.
• And more!

Back to top

Packaging and pricing

Although some of the information in this section isn't part of the V8.2.2 release, I thought it would be helpful to keep you abreast of some major changes that have been announced around -- and just before -- the time of this release.

For starters, as of January 28th, 2005, all DB2 server editions (Express, Workgroup Server, Workgroup Server Unlimited, and Enterprise Server) come with the ability to leverage a 64-bit memory model for your database applications. When you think about the favorable licensing considerations around DB2 Workgroup Server Unlimited Edition running on a 4-way SMP box with 64-bit support, that's quite a database at a price that's quite attractive when compared to the competition.

In May 2005, IBM also announced a new pricing paradigm for dual core processors that are based on x86 (Intel's EM64T and AMD's Opteron chipset) and the OpenPower 710/720 server technologies. Essentially, for these architectures, customers pay per physical processor versus the cores on the processor. In addition to this, IBM also announced official support for static and dynamic sub-capacity licensing (licensing partitions on a server) in response to the strong trend of server consolidation on to large "carvable" SMP servers. I've written a separate article on these topics that includes all the details of these licensing changes, as well an overview of the technologies themselves.

As of DB2 V8.2.2, a copy of DB2 is also available with a 64-bit memory model on an AMD-powered Windows server. This support comes after Microsoft officially made available a copy of 64-bit Windows that supports this processor architecture. In addition to this, DB2 V8.2.2 also includes support for EM64T -- Intel's answer to AMD's 32-bit/64-bit interoperability of their chipset.

Sun Microsystems recently released Version 10 of their Solaris operating environment. DB2 V8.2.2 has been tested to work with Solaris 10 and includes support for key features in this new version like zones. While installing DB2 on the global zone and then inheriting that installation to other zones is not supported, there are no restrictions on installing DB2 on one or more local zones directly.

You may or may not be aware of this, but every DB2 Connect (as of V8.1.4) or DB2 server (as of V8.1.2) comes with a free copy of Mobility on Demand (DB2 Everyplace). The V8.2.2 release adds support for the latest mobile Windows platforms, including versions for the tablet PC.

Finally, the future releases of SAP are to be built on SLES 9, which uses the 2.6 kernel. Before DB2 V8.2.2, DB2 was shipped as two different installation images for IA32 and x86-64 hardware. To create a better test environment for DB2 and SAP, DB2 V8.2.2 will ship specific images for 2.6 kernel-based distributions (namely SLES9 and RHEL4), and other images for 2.4 kernel-based distributions.

As of DB2 V8.2.2, when you install DB2 for Linux on x86-64 or IA64 hardware, you must ensure that you select the right image based on the kernel level of your distribution. In fact, the installation program will stop you from installing the wrong installation image on your distribution. Figure 1 shows the installation program blocking an installation of a 2.6-based installation image on a workstation using the 2.4 kernel:

Providing this separate image helps us to ensure a better operating environment for SAP, leverage updated compilers for the processors, and ensure there are no incompatibility issues between kernel levels.

Back to top

Administrative enhancments

A number of administrative enhancements were driven by the focus on SAP in this release. From easier access to administrative data, to "lights-out" storage management for your DB2 data, there's a lot for database administrators (DBAs) to get excited about in DB2 V8.2.2. In fact, I'd say that aside from the ground-breaking performance achieved with DB2 V8.2.2, the features outlined in this section are by-and-large the "meat" of this release.

Single point of storage management

One of my favorite features for DBAs in the V8.2.2 release falls under the feature I'll refer to as the single point of storage management (SPSM). Ideally, applications such as SAP want to leverage a highly available and scalable data storage engine that is invisible to end users and administrative personnel and requires as little administrative intervention as possible.

In DB2 V8.2.2, storage paths can be pre-defined for a database as shown in Figure 2:

When you use this syntax to create a database in DB2 V8.2.2, the database is created as an automatic storage database by associating one or more storage paths with it (a storage path is essentially a file system or directory). Within such a database, you can then create what are known as automatic storage table spaces (they are available as DMS FILE table spaces only). For these table spaces, you do not specify a list of containers to use for the storage. Instead, DB2 automatically assigns containers based on the storage paths that are associated with the database while honoring an optional starting size. These automatic storage table spaces actually use the DMS FILE infrastructure and have auto-resize turned on by default. Basically, DB2 handles all of the storage management tasks associated with the table space - completely online and without the need for manual intervention. You can create default table spaces that are automatically managed by DB2 as well, but TEMPSPACE1 will still be created by default as an SMS table space. This new feature also allows a DBA to add new storage using the SQL API. To use this new feature, you have to be working with a new database at the V8.2.2+ level.

DBAs will especially love the ability to auto-extend a DMS FILE table space. It reduces the burden of watching monitoring activity and responded to potential outages based on a lack of allocated storage for DMS table spaces. They have the ability to send characteristics of the growth increment, maximum size, initial size, and so on. These automatic storage table spaces that can automatically extend existing containers (or creates news ones) as your table spaces fill up. Before DB2 V8.2.2, only SMS table spaces could automatically grow when space was needed. If you ran out of space on a DMS table space, you would receive an SQL0289N Unable to allocate new pages in table space error. In DB2 V8.2.2, any FILE-based DMS table spaces can be automatically extended (this feature is not supported for DMS RAW table spaces). Now, instead of getting an SQL0289N error, DB2 will reactively grow the last range of containers in the table space.

In addition to this, the auto-resize of the last range in the container will not cause a rebalance to avoid run-time performance issues (keep in mind that DB2 V8.1.2 introduced throttling to control the REBALANCER utility, so you have lots of options here). Of course, the auto-resize component will only kick in if there is space in the file system for the container to grow.

This feature is not enabled by default unless you are using the new automatic storage table space feature in V8.2.2 - then it's the default. The auto-resize feature is also supported in a partitioned database environment with values specified on a per partition basis.

For table spaces, the CREATE TABLESPACE command has been updated with the MANAGED BY AUTOMATIC STORAGE option to create these respective table spaces. This is actually the new default if you do not specify a management option.

Some examples of some of these new features and their options with respect to automated storage in DB2 V8.2.2 are shown in Figure 3 below:

This is another example of how the partnership with SAP helps contribute to a better version of DB2 for everyone. SAP uses DMS table spaces, but they wanted their users to have more ease-of-use characteristics with respect to the database storage model. This new approach marries all the characteristics you love about DMS table spaces (performance and flexibility) with the ease-of-use of SMS table spaces (storage allocaiton, and so on). Even if you don't use SAP, the ability to provide a single point of storage management for the database helps everyone.

SAP tuning knob

To flatten the configuration time involved in setting up DB2 for SAP environments, DB2 V8.2.2 introduces an SAP tuning 'knob'. There are a number of internal and external features and changes designed for SAP workloads in DB2 V8.2.2 (and some in previous releases as well that this knob will address). Many of these features are enabled via registry variables or configuration parameters. For example, the following registry variables all enable features that are suggested for use in SAP environments: db2_reduced_optimization, db2_interesting_keys, db2_correlated_predicates, db2_evaluncommitted, db2_skipdeleted, db2_object_table_entries, and so on.

To make the configuration and maintenance of SAP on DB2 more transparent, you can use the SAP tuning knob in DB2 V8.2.2 to set your DB2 environment to be optizmied for SAP. This "knob" is implemented as a single registry variable, DB2_WORKLOAD, that you set to SAP. To disable DB2 to run on the SAP setting, simply set the DB2_WORKLOAD registry variable to NULL and restart the database instance.

This feature alleviates the complexity of ensuring that all of the SAP features are enabled for your environment, as well as correctly setting the corresonding registry variables for that matter. Quite simply, just set DB2 on the SAP setting. In the future if we add more enhancements for SAP, you won't have to perform extra work when applying an upgrade because your database will be set to operate on SAP. Think about hitting the popcorn button on your microwave -- you're not sure how it happens, but when you put a bag of popcorn in the microwave and hit this button, it comes out perfect.

You can use the db2set -gd DB2_WORKLOAD=SAP command to list the default settings for this aggregate registry variable as shown in Figure 4 below:

Uniform database page size

Many customers like to take advantage of the fact that DB2 supports multiple page sizes: 4 KB, 8 KB, 16 KB, and 32 KB. Depending on the type of application, using a different page size can prove very beneficial. For example, OLTP-like applications would typically benefit from smaller pages sizes since operations tend to be quick updates a single row retrievals. At the same time, business intelligence-based workloads may benefit from larger pages sizes as more data can be stored on a page that is often scanned, aggregated, or sorted, and so on. Customers may choose to leverage a larger page size to get more data on the page -- for example, a 16 KB page size can support tables that exceed 64 GB in size.

When you create a database in DB2, 3 table spaces are created by default: SYSCATSPACE, TEMPSPACE1, USERSPACE1. Before DB2 V8.2.2, SYSCATSPACE was hard-coded internally such that it was created with a 4 KB page size. As well, DB2 always required a system temporary table space that had a 4 KB page size too. For these reasons, a default buffer pool is created that supports a 4 KB page when a new database is created in DB2.

This means that if a customer wanted to leverage a different page size for their database, they are forced to deal with a mixed-page environment (which can have performance benefits) and the administrative complexities that go along with it. If a customer wants to enforce a uniform page size (let's say they wanted to use an 8 KB page size), they can drop the USERSPACE1 table space and create a new table space with this page size -- but they still cannot change the SYSCATSPACE table space or the fact that at least one 4 KB temporary table space must exist. The end result -- customers have to manage at minimum 2 pages sizes if they want to use a uniform page size for their environment.

DB2 V8.2.2 adds the ability to create a database with an initial page size other than 4 KB (you can choose any page size that DB2 supports). This gives you the ability to choose any page size you want, and still maintain a single buffer pool and uniform page size across the database. When you create a database with a uniform page size, only one buffer pool to match this page size is required (even if it isn't a 4 KB). For example, if you create a new database with an 8 KB page size, the default buffer pool for all the table spaces will be 8 KB.

For informational purposes, a new database configuration parameter, called PAGESIZE, has been added in DB2 V8.2.2 as shown in Figure 5.

Not to complicate things, but for certain workloads, we've actually seen an increase in performance as well with a uniform page size since memory doesn't get fragmented. You'll have to experiment with your application, data, workload, and this new feature for yourself.

CREATE INDEX and COLLECT STATISTICS for declared global temporary toles in a single pass

DB2 introduced declared global temporary tables (DGTTs) in DB2 V7, and then extended their features in DB2 V8.1. The V8.1 implementation of DGTTs included the ability to create indexes on them and support the execution of the RUNSTATs utility against them to give the optimizer a better understanding of their distribution. Statistics and indexes are crucial for performance when large DGTTs are used.

In DB2 V8.2.2, DGTTs are enhanced so that you can create an index and collect statistics on a DGTT using the single command: CREATE INDEX WITH COLLECT STATISTICS. This new command allows you to collect statistics and build an index in a single pass of the base table, instead of two passes with the previous implementation of these features. This will provide significant time savings for operations, especially for large data warehousing environments that typically make extensive use of DGTTs. An example of this new command is shown in Figure 6.

Easier access to DB2 administrative infomation

Many commands in DB2 V8.2 have their own CLP syntax and corresponding APIs; however, there is not a standard way to call DB2 commands through the SQL API. Since many of the commands have C-based APIs, this makes it especially tough for Java-based programming to execute commands since they have to wrap these commands in JNI wrappers.

DB2 V8.2.2 now provides an SQL-like interface, in the form of a stored procedure, that can be used to pass some (and soon to be a growing list of) DB2 CLP commands to DB2 without the need for workarounds. Essentially, these stored procedures serve as a wrapper around the CLP C API and returns the results of the operation in a dynamic result set.

DB2 V8.2.2 is the first release where the invocation of a CLP command can be handled by a stored procedure. Essentially the new ADMIN_CMD stored procedure acts as a wrapper to the CLP (it even includes a parser).In DB2 V8.2.2, the following commands are supported with the ADMIN_CMD routine:

• DESCRIBE
• REORG INDEXES
• REORG TABLE
• UPDATE DATABASE CONFIGURATION
• RUNSTATS
• PRUNE DATABASE HISTORY
• EXPORT (Server-side only -- the path used in the command must be a valid fully-qualified path on the server and the path has to be accessible by the fenced user ID.)

The ADMIN_CMD routine resides in the SYSPROC schema and is automatically created in any new database at the DB2 V8.2.2 level. If you have an existing database and you want to use the function provided by this stored procedure, you need to update your databases after you install DB2 V8.2.2 using the db2updv8 utility.

The ADMIN_CMD stored procedure also takes advantage of the SQLCA support that was added for stored procedures in DB2 V8.2. This allows for SQLCA information to be returned from the administration API to the SQL interface that is calling the administration command.

This new infrastructure benefits clients in many ways. Aside from the basis of its implementation being able to fit cross-family, if new options are added to any commands in the future, clients simply have to update the string variable they pass to the stored procedure to take advantage of it. There is no need to migrate the catalogs, or learn a new signature, and the best part is, if any options are added, your existing scripts will always continue to work.

Figure 7 shows an example of entering the CALL ADMIN_CMD('DESCRIBE SELECT * FROM EMPLOYEE') command from the Command Editor:

DB2 V8.2.2 also comes with enhancements to the snapshot monitor UDFs. Before DB2 V8.2.2, some monitoring UDFs and stored procedures didn't include certain information that was returned by their respective application programming interfaces (APIs). To deliver a set of functions that have parity with other access methods in DB2 V8.2.2, a new set of routines have been added to ensure that all information returned is the same for any method of retrieval. It's planned that future versions of DB2 will utilize a standardized naming convention for administrative UDFs, so instead of changing the existing functions, we've decided to introduce a new standard in the DB2 V8.2.2 release.

The following list details the new function names and what these UDFs can be used for:

• SNAP_GET_DB to get database information
• SNAP_GET_TBSP to to get table space information
• SNAP_GET_TBSP_PART to get partition-specific table space information
• SNAP_GET_CONTAINER to get container information
• SNAP_GET_STO_PATHS to get container path information
• SNAP_GET_TAB to to get table information
• SNAP_GET_DYN_SQL to get dynamic SQL information
• SNAP_GET_DETAILLOG to get detailed log information

Figure 8 shows calling a UDF from within the Development Center that returns updated monitoring information:

In the event that any of these table functions change in the next version of DB2, customers need a way to maintain down-level compatibility with their purchased applications that leverage them (like SAP), or their own home-grown applications. To support down-level compatibility, snapshot monitor views on the table functions shown in the Figure 8 have been created. These views are guaranteed to exist in subsequent releases of DB2. Each of the views will be based on a SELECT * of the underlying monitor-based UDF and will be created under the SYSCATV82 schema.

In Figure 9 you can see that I'm calling the same kind of information by using a view instead of a table function in the previous figure:

DB2 V8.2.2 also enhances the output of the get_db_config stored procedure. Before DB2 V8.2.2, some of the information returned via the CLP method of retrieving database configuration information was not returned in this stored procedure. As of DB2 V8.2.2, all of the missing parameters have been added to the output of the get_db_config stored procedure. In addition to this, some touch-up work was done for other parameters that are returned by this routine for better readability and consistency with the CLP method of retrieval (for example, the COLLATE_INFO column behaves like the CLP version now; in DB2 V8.2, it displayed blank text). To use this routine in DB2 V8.2.2 requires an 8 KB page user temporary table space. If one does not exist, a SQL0286N error will be returned to the user or application.

DB2 V8.2.2 also gives you the ability to use SQL to get the details of your server systems. There's a new set of UDFs that allow remote clients to get details about a DB2 server, including machine, product, and instance information, and so on. These UDFs are created at database creation time. (Of course you need the correct authorizations to get to them).

This new feature is designed to help both DBAs in their environments, and ISVs that use DB2 to persist their application's data. For example, you may have a remote tool that needs to know if it is using a DB2 ESE or DB2 Express server -- for either capability enablement or licensing. Before DB2 V8.2.2, this information was only available through administrative APIs.

The new routines are defined in sqlescat.h and placed in the new db2envinfo library located in the sqllib/bin/routine directory. The new routines are:

• SYSPROC.ENV_GET_SYS_INFO which returns system information
• SYSPROC.ENV_GET_PROD_INFO which returns environmental information
• SYSPROC.ENV_GET_INST_INFO which returns instance information

Examples of each of these three new informational UDFs are shown in Figure 10:

You can even use structured query language (SQL) to see the setting of the DB2 registry. As you know (or have figured out by now), DB2 has an ongoing initiative to make administrative functionality accessible through SQL instead of other various methods (APIs, commands, and so on). SQL administrative routines and views make it easier for both DBAs and application developers to query and modify the state of their DB2 systems. And of course, stored procedures and functions can be easily exposed as Web services for further management extensibility (hint, hint, hint).

As I've previously mentioned, DB2 V8.2.2 adds the ability to query the current (effective) DB2 registry variable configuration through a call to a simple table function. This approach will come in especially handy when monitoring the registry configuration of remote DB2 servers without having to physically logon to each server to explicitly use the db2set command.

In DB2 V8.2.2, when using a partitioned database environment, this function will only return the registry setting for the partition that the user is connected to. If you are connected to one partition and wish to retrieve the effective settings for another partition, you must first connect to the new partition before invoking the table function. This limitation will be removed in a future FixPack, update, or release.

An example of looking at a system's DB2 registry setting is shown in Figure 11:

Finally, there's the new ADMIN_LIST_HIST() SQL-based UDF to retrieve the information stored in the DB2 History file. Before DB2 V8.2.2, you had to use the LIST HISTORY CLP command or the corresponding API to retrieve information. The ADMIN_LIST_HIST() UDF eliminates the need for DBAs to programmatically retrieve this information. This UDF is a read-only function however, deletes and updates to the DB2 History file can only be performed through the PRUNE HISTORY and UPDATE HISTORY commands.

An example of this command is shown in Figure 12 below:

As you can see, the bulk of the DB2 V8.2.2 release really puts a lot of productivity from an administrative perspective into your personnel -- but there's more than just administrative enhancements in this release.

Back to top

Wrap up

DB2 V8.2.2 has many enhancements, and we've just touched the surface. In part 2 of this series, we'll dive into those features that contribute to the peformance leadership that DB2 currently has in today's marketplace.

Back to top

Disclaimers...

The information in this article is submitted as a best effort basis as the author understands it and does not represent an official communication from IBM. Neither IBM nor the author is responsible for the information in this article.

Resources

• The article DB2 Universal Database and dual core and sub-capacity licensing (developerWorks, May 2005) gives you a greater level of detail on understanding licensing requirements when you have dual core processors or sub-capacity partitioning in your environment.
  
  
• The DB2 UDB V8.2.2 Release Notes provide detailed documentation for this release.
  
  
• The latest version of the DB2 product documentation is available in the DB2 Information Center.
  
  
• Visit the DB2 UDB V8 page on developerWorks for links to other articles about the latest version of DB2 for Linux, UNIX and Windows.
  
  

About the author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases Under Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloe, his new daughter. You can reach him at paulz_ibm@msn.com

Rate this article

Comments

Back to top

Table of contents

• Introduction
• The view from 10,000 feet
• Packaging and pricing
• Administrative enhancments
• Wrap up
• Disclaimers...
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers