- DB2 is DB2 is DB2
- What's new in DB2 10.5
- DB2 Express-C
- DB2 Express Server Edition
- DB2 Workgroup Server Edition
- DB2 Enterprise Server Edition
- DB2 Advanced Workgroup Server Edition
- DB2 Advanced Enterprise Server Edition
- DB2 Advanced Recovery Feature
- For developers only...
- Downloadable resources
- Related topics
Which distributed edition of DB2 10.5 is right for you?
In this age of information, data is everywhere, and the volumes of data that must be collected, processed, analyzed, and stored are growing at an exponential rate. Not surprisingly, companies around the world rely on the IBM Information Management portfolio of products to help them manage and gain competitive insight from their data. Whatever the need - from embedded and in-memory relational or NoSQL databases to massively scalable transactional databases or Big Data analytic data warehouses, to plug-and-play database appliances, to tools for managing and protecting data across its life cycle - IBM Information Management offers a comprehensive lineup of world class data management solutions. An overview of the entire Information Management portfolio is well beyond the scope of this article. Instead, this article focuses on a cornerstone of that portfolio, the DB2 for Linux, UNIX, and Windows database family.
As the name suggests, DB2 for Linux, UNIX, and Windows (DB2) is a database software designed to run on distributed operating systems; including Windows, AIX, HP/UX, Solaris, Linux on x64, Linux on Power, and Linux on System Z. Over 20 years, tens of thousands of customers in virtually every industry, including many of the largest corporations in the world, have come to rely on DB2 to power their businesses. Moreover, DB2 is at the core of hundreds of IBM solutions, in brands that include Cognos, WebSphere, Tivoli, and Lotus, as well as an impressive list of independent software vendor (ISV) products.
As of the April 23, 2013 announcement date, the DB2 10.5 product family consists of six priced editions, one separately priced feature, and one no-charge package. The goal of this article is to help you to decide which edition of DB2 10.5 will best meet your needs. For each DB2 edition, this article provides a brief overview of the included functionality, licensing options, restrictions or other considerations, key changes from DB2 10.1, and an interesting customer anecdote. After reading this article, be sure to check out the companion articles that are listed in the Related topics section. This article will be updated when new DB2 versions and releases come out, so don't forget to bookmark it.
DB2 is DB2 is DB2
The first thing to know about DB2 is that all editions share the same code base. In fact, the only technical differences among DB2 editions are resource limitations and advanced features or tool sets. This common core design is intentional, so that applications that are written for any DB2 edition can easily be moved to any other DB2 edition, on any operating system platform supported by DB2. This flexibility also applies to database administrator (DBA) skills, because a DBA with skills on one DB2 edition will immediately be productive on any other DB2 edition.
Figure 1 shows the DB2 editions that are available in Version 10.5. You can think of the DB2 family as a set of Russian dolls, with the no-charge DB2 Express-C Edition as the innermost doll and DB2 Advanced Enterprise Server Edition as the outermost doll.
Figure 1. DB2 editions that are available in DB2 10.5
The following capabilities are examples of identical functionality found in all editions of DB2 10.5, including DB2 Express-C:
- Oracle database compatibility
- DB2 makes it painless to move applications, not just between DB2 editions, but from Oracle databases as well. Ninety-eight percent PL/SQL compatibility levels give you the flexibility to move to DB2 quickly, without having to rewrite your applications.
- XML and NoSQL support
- DB2 is ideal for high performance storage and retrieval of traditional relational data as well as special purpose non-relational data types such as XML and RDF.
- Time-based queries
- DB2 temporal tables make it easy to find out what data looked like at any previous point in time, or what it will look like in the future. Audit questions that used to require days of restoring backup images to answer can now be addressed in moments with a simple query. "What will the future look like" queries can be handled with similar ease.
- Homogeneous federation
- This capability enables your applications to see all of your DB2 and Informix databases as a single DB2 database. Add a separately purchasable DB2 Connect edition, and you can also federate DB2 for z and DB2 for i databases.
- Backup compression
- Reduce storage costs by shrinking the size of your DB2 database backup images.
In addition, DB2 provides two Complete Enterprise Option (CEO) offerings that are designed with deployment flexibility in mind. Any of the components in the set can be deployed, as necessary, to match your application requirements. This enables application developers and installers to select the best architecture for the application, and requires an enterprise-wide commitment to IBM products.
- IBM DB2 CEO
- This offering provides the DB2 foundation for applications that give competitive advantage in Enterprise Resource Planning, Customer Relationship Management, Business to Business Commerce, and Sales Force Automation. The components are DB2 Workgroup Server Edition, DB2 Enterprise Server Edition, DB2 Developer Edition, and DB2 Connect Enterprise Edition.
- IBM Advanced DB2 CEO
- This offering addresses the needs of today's businesses by providing a secure and resilient information management system for your valuable information assets. It provides a broad range of DB2 data server capabilities by including DB2 Advanced Enterprise Server Edition, DB2 Enterprise Server Edition, DB2 Workgroup Server Edition, DB2 Developer Edition, and DB2 Connect Enterprise Edition.
What's new in DB2 10.5
Before jumping into the details of each DB2 10.5 edition, it is important to understand that DB2 10.5 introduces significant new functionality and packaging changes over the prior edition, DB2 10.1. These changes include the following items:
- BLU Acceleration
- BLU Acceleration is a collection of new DB2
technologies that work together to cut Big Data, data warehouse, and
operational analytic workloads down to size. These technologies
include columnar data store capabilities (also referred to as
column-organized tables) that eliminate the need for indices; a new
compression algorithm that uses actionable compression to enable the
DB2 engine to perform operations on compressed data; the ability to
quickly skip data that is not relevant to a query; and intelligent
Single Instruction Multiple Data (SIMD) chip-set utilization that
enables DB2 to do more work while using fewer CPU cycles. The result
is orders-of-magnitude performance improvements for many analytic
queries, and storage savings of up to 10x or more, on average. In
other words, BLU Acceleration lets you do more data analytics work
while lowering your administrative, hardware, storage, and software
Column organization works at the table level, which means that you can tailor your tables by usage to get the best overall performance in a mixed workload database. In most cases, tables that are used mainly with transactional SQL operations such as INSERT, DELETE, and single row SELECT should be left as traditional row-organized tables. Tables used mainly to service analytic workloads involving large amounts of data are good candidates for conversion to column organization. To help you identify which tables should be converted to improve the performance of your query workloads, InfoSphere® Optim™ Query Workload Tuner provides the Workload Table Organization Advisor. Simply run the advisor against your workload, convert the recommended tables, and start enjoying the benefits of BLU Acceleration in a way that is completely transparent to your applications. A virtual conversion for testing purposes only can help you to determine whether column-organized tables will improve the performance of your query workloads. For more information, see Virtually testing conversion of tables to column-organization.
- DB2 pureScale Feature and High Availability Disaster Recovery (HADR)
- The DB2 pureScale® Feature is a scalable cluster capability for OLTP applications introduced in DB2 9.8. With DB2 pureScale, you can transparently add or drop members from your cluster without experiencing an outage. As of DB2 10.5, you can avoid an outage even if your entire DB2 pureScale cluster goes offline. By taking advantage of DB2's built-in HADR capabilities to ship transaction logs to a standby database, you can fail over to a remote standby cluster or a single server at the other end of town, or the other side of the world, in as little as a few seconds from the time the primary site goes offline. By combining DB2 pureScale and HADR, DB2 10.5 lets you achieve the highest possible level of database availability and data recoverability. To maintain this high level of database availability, some administrative tasks can now be performed online, such as applying an online fix pack update to members and Cluster Caching Facility (CF) servers while the database remains available, or adding new members while the instance remains online. You can create backup images and restore images between instances with different numbers of members, or between DB2 pureScale and non-pureScale environments.
- Merger of DB2 editions and InfoSphere Warehouse editions
- Customers want DB2 editions that give them the freedom to use any type of workload. DB2 10.5 delivers on that request by merging capabilities from the InfoSphere Warehouse editions, the DB2 Storage Optimization Feature, and the DB2 pureScale Feature into the DB2 Advanced Enterprise Server Edition and the new DB2 Advanced Workgroup Server Edition. Customers with those editions have the flexibility to deploy a single server OLTP, clustered OLTP, single server warehouse, or clustered warehouse environment. They are also free to use BLU Acceleration, Storage Optimization, DB2 pureScale Feature, database partitioning, or any of the other rich tool sets and features that come bundled with the DB2 Advanced editions. You can use the DB2 Advanced editions with flexible deployment options to tackle almost any IT project with confidence.
- Easy upgrades
- Starting with DB2 10.5, moving from DB2 Workgroup Edition or DB2
Enterprise Edition to the DB2 Advanced editions can be accomplished
with a simple license key update. For example, if you are running DB2
Workgroup Edition 10.5 and have purchased an upgrade to DB2 Advanced
Workgroup Edition 10.5, you would simply apply the DB2 Advanced
Workgroup license key to your existing DB2 installation. There is no
need to reinstall DB2 code or to upgrade databases, which means that
you can update a license in minutes and with minimal impact to your
business operations. This new capability applies only to edition
updates between applicable DB2 10.5 editions, not to upgrades from an
earlier version of DB2.
That said, DB2 10.5 also makes it easy to upgrade to the DB2 Advanced editions, even if you are not yet ready to upgrade from your earlier version of DB2 or InfoSphere Warehouse. This is achieved by the inclusion of selected earlier versions of DB2 and InfoSphere Warehouse editions and features in the DB2 10.5 Advanced Edition packages, as described in the DB2 10.5 Announcement letter. Customers who buy or upgrade to a DB2 10.5 Advanced edition are free to use the earlier versions that are included with those packages, as long as they stay within the terms and limits outlined for the bundling of the DB2 10.5 Advanced edition package.
For example, suppose you are running DB2 Enterprise Server Edition 9.7 and that you want to upgrade your entitlements to DB2 Advanced Enterprise Server Edition so that you can take advantage of DB2 Storage Optimization and the latest version of InfoSphere Optim Performance Manager (OPM) Extended Edition. However, you are unable to upgrade your database from DB2 Enterprise Server Edition 9.7 at this time. No worries. DB2 10.5 Advanced Enterprise Server Edition includes DB2 Enterprise Server Edition 9.7 and DB2 Storage Optimization Feature 9.7. Because you already have DB2 Enterprise Server Edition 9.7 installed, you need to apply only the DB2 Storage Optimization Feature 9.7 license. DB2 10.5 Advanced Enterprise Server Edition also includes the latest version of OPM Extended Edition, so you would need to install that as well. At this point, you are able to start taking advantage of the extra value that comes in the DB2 10.5 Advanced Enterprise Server Edition, and it will have taken you only a few minutes to get up and running without having to upgrade your database.
- Terabyte (TB) pricing
- This is a new pricing metric for DB2 10.5 inherited from the
InfoSphere Warehouse family. With TB pricing, you license a DB2
Advanced edition by the terabytes of user data per database. A handy
reporting script is included with all DB2 editions to calculate the
terabytes used. For example, if you have 1.3 TB of raw, uncompressed
user data in your database, you would require a 2-TB entitlement for
that database. If you are able to compress that data down to 0.6 TB
(by using DB2's built-in compression capabilities or by some other
means), you would require only a 1-TB entitlement. As this example
shows, when licensing by the TB, you'll want to use compression if at
You should know that the TB pricing metric is not tied in any way to the number of processor cores that are executing your workloads. This makes a lot of sense for data warehouses, because it means that you can throw a lot more processing power at long-running analytic queries without having to worry about the licensing impact, because there is no such impact. That said, terabyte pricing does not make sense for OLTP workloads, so special limitations have been put in place to prevent DB2 terabyte entitlements from being used with OLTP workloads. These limitations are described in detail in the DB2 Advanced Enterprise Server Edition and DB2 Advanced Workgroup Server Edition sections below, so be sure to check those out before deciding on the TB pricing metric.
What's new in DB2 10.5 Fix Pack 5
DB2 10.5 Fix Pack 5 introduces significant new functionality, a product packaging enhancement, and a new licensing option to DB2 10.5.
- DB2 native encryption
- More businesses want or are mandated to encrypt sensitive data to meet organizational or regulatory requirements. DB2 native encryption, which provides encryption capabilities for database data, including backup images, within the DB2 database engine itself, ensures that sensitive data is secure at all times. It is easy to enable and transparent to applications that access the encrypted data. DB2 native encryption meets the requirements of NIST SP 800-131 compliant cryptographic algorithms and utilizes FIPS 140-2 certified cryptographic libraries. This new functionality is included in DB2 Express-C, DB2 Advanced Workgroup Server Edition, DB2 Advanced Enterprise Server Edition, and DB2 Developer Edition, and can be purchased for use with other editions, as described below, in the section entitled "DB2 Business Value Offerings".
- DB2 business value offerings
- Businesses are increasingly focused on extracting maximum value out
of their IT spending. Product packaging alone might not provide the
flexibility required to meet this objective, and many organizations
want to be able to choose the capabilities that they require from
among the features that are available. As a result, IBM introduced a
set of DB2 business value offerings to address this
need. These offerings enable organizations to gain access to DB2's
native encryption technology, DB2 pureScale technology, BLU
Acceleration technology (including shadow tables), and performance
management tooling. Customers can purchase one or more of the
following specific add-on solutions and capabilities that provide
targeted business value to their DB2 environments:
- IBM DB2 Encryption Offering provides data encryption at rest to assist businesses with security and regulatory requirements.
- The DB2 Encryption Offering enables DB2 Express Server Edition, DB2 Workgroup Server Edition, and DB2 Enterprise Server Edition customers to use DB2 native encryption technology to encrypt sensitive data.
- IBM DB2 Business Application Continuity Offering provides the continuous availability of DB2 pureScale technology at a lower cost for those customers who do not need the scaling capabilities, yet want to benefit from the reliability and availability provided by DB2 pureScale.
- The continuous availability of transactional database systems is critical to the ability of many businesses to meet client expectations or satisfy service level agreements. Many organizations deploy DB2 pureScale technology to provide continuous availability, but for some businesses, deploying a full DB2 pureScale cluster solution is impractical. With the DB2 Business Application Continuity Offering, organizations can deploy a two-member DB2 pureScale environment in which one member is available for active processing of application workloads, and the second member is both available for common database administration and ready to take over when the first member has a planned or unplanned outage. The DB2 Business Application Continuity Offering gives organizations that depend on their high-speed transactional processing environments a cost-effective continuous-availability capability delivered through DB2 pureScale. This offering is available on DB2 Workgroup Server Edition and DB2 Enterprise Server Edition.
- IBM DB2 BLU Acceleration In-memory Offering provides columnar and dynamic in-memory acceleration to speed up business intelligence workloads.
- The growing pervasiveness of, and demand for, analytics across organizations continue to put pressure on traditional database environments that are not designed to meet this demand or to accommodate new online analytical applications. IBM's BLU Acceleration technology delivers simple and scalable "speed of thought" analytics to help organizations meet this demand. Organizations that have hybrid environments with both transactional workloads and analytical workloads can use shadow tables to get the performance benefits of BLU Acceleration for analytic queries in an online transaction processing (OLTP) environment. A shadow table is a column-organized copy of a row-organized table that includes all columns or a subset of columns, and analytical queries against row-organized tables can be automatically routed to shadow tables. By combining the enhanced performance of complex analytical queries with the efficiency of row-organized tables for OLTP queries, you can use shadow tables to capitalize on the best of both worlds. This offering is available on DB2 Workgroup Server Edition and DB2 Enterprise Server Edition.
- IBM DB2 Performance Management Offering delivers enterprise class performance management capabilities that were previously available with DB2 Advanced editions.
- The DB2 Performance Management Offering helps businesses monitor, manage, and improve database and application performance. It includes IBM InfoSphere Optim Performance Manager, which provides database administrators and other IT staff with the information that they need to manage performance proactively, and helps to identify application, network, or database problems before they affect the business. InfoSphere Optim Performance Manager also helps to prevent performance problems by defining and using a more predictable database server execution environment. It helps solve performance problems by delivering workloads to InfoSphere Optim Query Workload Tuner (also included in the DB2 Performance Management Offering), which makes actionable tuning recommendations for the entire workload. This is available on DB2 Express Server Edition, DB2 Workgroup Server Edition, and DB2 Enterprise Server Edition.
The IBM DB2 Business Value Offerings must be licensed in a way that matches the type and number of entitlements for your installed DB2 edition. If DB2 was acquired as a supporting program under the License Information (LI) for another IBM Program, you must acquire sufficient entitlements for each offering.
The IBM DB2 Business Value Offerings must also be licensed on all idle or warm standby servers in a way that matches the types of entitlement for your installed DB2 edition on primary servers. In this case, the number of entitlements must be appropriate for the idle or warm standby servers in your environment, as described in the LI document for each offering.
- New licensing option
- DB2 10.5 Fix Pack 5 introduces a new licensing option for DB2 Advanced Workgroup Server Edition and DB2 Advanced Enterprise Server Edition in a DB2 pureScale environment. If your DB2 pureScale configuration contains a member used only for administrative or fail-over purposes, licensing requirements for this administrative member can be reduced.
For details about all of the DB2 10.5 fix pack enhancements, see the fix pack summary.
DB2 Express-C — A no-charge package to build, develop, and distribute
More than five years ago, IBM took the database market by storm and announced a special no-charge DB2 server package called DB2 Express-C. We refer to this as a package, because it is not an "official" DB2 edition. DB2 Express-C was designed for the partner and development communities, but as you get to know this package, you'll start to realize that it has applicability almost anywhere, and you'll be pleasantly surprised. DB2 Express-C is perfect for developers, small and medium deployments, academic communities, and more.
The major DB2 features that are not included in DB2 Express-C are outlined in the following list.
- You cannot purchase the DB2 Advanced Recovery Feature for use with DB2 Express-C.
- You cannot cluster two DB2 Express-C data servers together for high availability by using clustering software such as PowerHA SystemMirror, SteelEye, or the built-in clustering services that are part of the other DB2 editions. If you need to set up a database in a highly available environment, you must minimally license DB2 Express. Moreover, HADR, Advanced Copy Services (ACS), and online table reorganization are not included and cannot be purchased for DB2 Express-C.
- You cannot use replication services with DB2 Express-C.
- You cannot purchase the award-winning 24x7 IBM Passport Advantage support available with purchasable DB2 editions. This is one of the biggest restrictions with DB2 Express-C. The DB2 Express-C support model revolves around the strength of its community. This community is made up of some of the world's most experienced DBAs and a legion of DB2 developers and engineers who monitor a forum you can tap into for DB2 Express-C help, advice, and support. This approach resembles open-source community methodology. To help nurture the DB2 Express-C community, strong practitioner resources are available on web sites such as developerWorks.
- DB2 Express-C is only available at the current version. When a new version of DB2 Express-C is released, older versions are no longer available for download. This restriction requires special attention if you are using ISV software certified for a specific release level.
If you want to leverage any of these restricted capabilities or services, you need to purchase a for-fee edition of DB2. For example, the DB2 Express 12-month Fixed Term License (FTL) option gives you all of these features and more, at a very reasonable price.
DB2 Express-C 10.5 is available for servers that are running Linux or Windows operating systems. A complete list of supported platforms is available at the IBM DB2 Express-C download site.
Developers love DB2 Express-C because it enables them to develop and deploy applications free of charge. ISVs love DB2 Express-C because it enables them to freely develop, deploy, and distribute DB2 as part of their packaged application. And remember, because DB2 Express-C is DB2, you can deploy these applications on any DB2 edition and even move them to DB2 for z/OS without making any changes (assuming that you write your applications to the 95+ percent common SQL API set for the DB2 family).
Educators have an opportunity to extend the value of the IBM Academic Initiative program, which offers them complimentary access to DB2 technology when their students use DB2 Express-C without being tied to a specific course curriculum. These DB2 practitioners are then able to to build, deploy, and distribute database applications at no charge.
DB2 Express-C is so compelling that it received the prestigious Best of the Midmarket award from VARBusiness magazine. Want to test drive DB2 Express-C for yourself? Download a copy today.
- Licensing and pricing
- Use DB2 Express-C at no charge to build, deploy, and distribute
database applications. Consequently, there are no license fees
associated with this DB2 package. The code is, however, optimized in
such a way that it throttles the DB2 engine to a maximum of 2 cores of
processing power for the database on any-sized server or
You must also restrict DB2 10.5 to 16 GB of memory per server or virtualization session. For example, if you installed DB2 Express-C on a server that had 8 VMWare sessions, each configured with 4 cores and 16 GB of RAM, each copy of DB2 Express-C will schedule work on only 2 cores and the full 16 GB of RAM in each virtualization session. However, you could not install a copy of DB2 Express-C in a VMWare session with 20 GB of RAM, because that exceeds the 16-GB limit, unless you manually configured DB2 to use 16 GB of memory or less.
Finally, you cannot exceed 15 TB of user data in a DB2 Express-C database.
- Changes from DB2 Express-C 10.1 to 10.5
- Starting with DB2 10.5, you can use up to 16 GB of RAM per server or virtualization session, which is a substantial increase from DB2 10.1 (4 GB). Each database is limited to 15 TB of user data, as reported by the script provided with the software. In DB2 10.1, there was no restriction on the size of the user data.
- Consider it...
- Use DB2 Express-C to learn DB2, develop DB2 applications, and even to deploy production applications (depending on your requirements). DB2 Express-C is useful for a wide variety of applications and projects that don't require more than 2 cores of CPU and 16 GB of RAM.
- Tell me something cool about running on DB2 Express-C
- 1C is an icon of the booming Russian economy and is the second largest
provider of enterprise application software (EAS) by revenue in
Russia. Although they make a really cool flight simulator game, they
are mostly known for their accounting and financial management
software. 1C leverages all the power of DB2 Express-C, and there is
even a setting in DB2 (automatically set by the 1C installation
program) that runs the DB2 engine in "1C-mode".
Arktos is an HVAC equipment manufacturer. Arktos wanted to improve the overall responsiveness of their systems, automate database backups, and reduce recovery times. They hired a 1C business partner, BIT, and migrated to 1C:Enterprise running on DB2 Express-C. Arktos' Oleg Illyin notes that "The installation of the new system allowed us to accelerate most of our business processes, which led to significant reductions in the time required to complete most business transactions – 5 to 10 times on average. For example, the time required to process monthly closing transactions was reduced from six hours to 10-11 minutes. With the help of the database [DB2 Express-C], we now have an automated, fast, and reliable process for recovering data and restoring information systems in case of failure."
When the second largest EAS vendor in Russia trusts their critical applications to a free DB2 package, a 1C customer's application wait time is reduced by about 300 minutes to 11 minutes, and that same vendor offers you flight simulator software... now that's cool!
DB2 Express Server Edition... It's simple, secure, and inexpensive
DB2 Express Server Edition (DB2 Express) is the entry level, fully supported DB2 server that is a low-cost, full-featured, industrial-strength, and open industry standards-based relational database. The target users for this DB2 edition are small and medium businesses (SMBs) and ISVs that need a small but powerful transactional database. DB2 Express provides an attractive entry and competitive price point for businesses that choose to leverage the benefits of Linux (x64, IBM Power Systems), Solaris x64 (64-bit), and Windows x64 servers. (These links take you to the most up-to-date platform support information.)
Because DB2 Express is a full-fledged DB2 server at its core, DBAs can leverage its built-in autonomic manageability features, such as the self-tuning memory manager (STMM), IBM InfoSphere Optim management and development tooling, autonomic maintenance plans, automated backup tuning, backup compression, archive log compression, text searches, throttling, and more. Collectively, these services help to increase the performance and reliability of your DB2 solution, while at the same time minimizing administrative complexity, skills requirements, and overall total cost of ownership. Other popular SMB-targeted databases include only advanced availability, online operations, and management features in their Enterprise-only editions. With its autonomic capabilities, you might not even know that DB2 Express is there - and that includes your pocketbook!
This DB2 edition is fully compatible with the rest of the scalable DB2 family of relational databases for Linux, UNIX, and Windows platforms, and you can easily pre-configure DB2 Express to transparently install within your applications for easy deployment. Customers love DB2 Express, because all they see is a solution, and Business Partners love it because they can trust it to run virtually unattended.
DB2 Express comes with the right to use homogeneous SQL-based replication, row and column access control (RCAC), label-based access control (LBAC), and the high availability feature set.
- Homogeneous SQL replication
- Homogeneous SQL replication lets you integrate data across members of the IBM relational database server family, namely DB2 for Linux, UNIX, and Windows, as well as Informix, DB2 for z, and DB2 for i. (Note: Use of this capability with DB2 for z or DB2 for i might require the purchase of additional capabilities such as DB2 Connect.) Homogeneous SQL replication is used to replicate data between a central source data server and a set of target servers. If you also need to replicate to or from a non-IBM database, or would prefer to leverage queue-based replication instead of SQL replication, you need a product like IBM InfoSphere Data Replication instead, or one of the DB2 Advanced Editions that include Q-replication among three DB2 data servers.
- RCAC and LBAC
- RCAC and LBAC are security features that give data
stewards fine-grain control over what data a user can access. With
RCAC, it is easy to apply custom rules to control read and write
access for users and groups at the table row level, and read access at
the table column level. At run time, off-limits columns are masked and
off-limits rows are completely hidden from an unauthorized user. In
addition to providing a finely tunable and flexible security
mechanism, RCAC makes it much easier to develop multi-tenant
applications by providing automatic separation of duties and concerns
(data) residing in the same tables as a transparent and high
performing database service; these features add to the multi-tenant
services that are already provided by DB2.
LBAC is similar to RCAC except that it uses security labels attached to table objects to control who has access. Users attempting to access an object must have its security label granted to them. If there is a match, access is permitted. Without a match, access is denied and data is hidden. Unlike RCAC, LBAC is specifically targeted to applications where control must be structured around a rigid hierarchical representation of a business entity, such as government security clearance levels.
- High availability feature set
- The high availability feature set provides a number of services that
enhance the availability of applications running on DB2 Express
servers, including support for online table reorganization, a two-node
cluster license for the IBM Tivoli SA MP high availability service
that's built into DB2, DB2 Advanced Copy Services (ACS), and High
Availability Disaster Recovery (HADR).
HADR is a set of availability services that provide a turnkey database availability solution and protection plan. HADR not only provides database redundancy across the entire solution stack, but can meet even the most stringent availability service level agreements (SLAs) with mean time-to-repair (MTTR) values that are typically measured in the 30-60 second range. The best part about HADR is how easy it is to set up. What's more, read-on-standby technology enables you to open up a standby HADR database for read-only queries. (This type of access to the standby does have licensing considerations.)
With HADR, you can have up to three standby servers, which gives you the flexibility to have your data replicated at multiple sites for both high availability and disaster recovery, all with the same easy to use HADR technology included with DB2. You can also specify a time delay before a log is applied to any one of the standby servers, which you can use as a buffer to prevent potential human or application errors on the primary from being applied to the standbys.
- Online table reorganization (REORG)
- Online table reorganization, sometimes referred to as an in-place REORG, is unlike anything else you'll find in other vendors' offerings. It provides continuous table reorganization without the excess disk space requirement associated with the traditional "shadow" copy approach. Online table reorganization can be started, paused, and throttled. For example, you could run a reorganization operation throughout the evening and then throttle it back or even pause it during the day.
- DB2 Advanced Copy Services (ACS)
- DB2 ACS enables you to leverage the fast copy technology of supported storage devices to perform the data copying part of backup and restore operations. Using the storage device to perform the data copy phase makes backup and restore operations run much faster. A backup operation that uses DB2 ACS is often referred to as a snapshot backup.
- DB2 built-in clustering services
- DB2 includes a set of built-in clustering services that give
you the ability to cluster together two servers for high
availability and even to automate the failover of an HADR
solution. The DB2 installation program can automatically
configure this high availability cluster. To make your DB2
high availability solution even more bullet proof, there is a
high availability management framework, fronted by the DB2
high availability instance configuration utility (db2haicu),
which provides a text-based interface that you can use to
configure, administer, and synchronize the infrastructure
definition of your highly available database topologies in a
DB2 can collect information about your database instance, your cluster environment, and your cluster manager by querying your system, and can keep the whole cluster synchronized when changes occur. For example, consider the effects of adding a new storage container. What you do to one side of the cluster has to be done to the other side, and DB2 will automate that for you. This is a big deal, because the process is tedious and error prone, and human error is the number one reason for down time. DB2 helps you here tremendously.
- Licensing and pricing
- DB2 Express 10.5 has the following architectural limits with respect
to the server or virtualization session upon which it is installed:
- DB2 Express is limited to 64 GB of memory. Even if you have multiple instances of DB2 running concurrently, you cannot exceed the 64-GB limit, which applies cumulatively to all instances and databases in a server or virtualization session. If your virtualization technology does not let you cap the memory to 64 GB, you can do it manually by setting the DB2 instance_memory configuration parameter.
- DB2 Express is limited to 8 processor cores per server or virtualization session. You can install DB2 Express on any-sized physical server, but it will throttle itself to use only 8 cores. If you want DB2 Express to use more than 8 cores, simply divide up the physical server by using an IBM-recognized virtualization technology. Because the core limits are per virtualization session, two licensed copies of DB2 Express that are installed on separate virtual servers can each use up to 8 cores, or 16 cores in total. This is actually a great way to take full advantage of a large physical server to consolidate workloads from multiple small standalone servers.
- DB2 Express databases are restricted to 15 TB of user data per database under all charge metrics.
A DB2 Express server can be licensed in one of the following ways:
- Processor Value Unit License (PVU): You must purchase the total number of PVUs that are associated with the server or virtualization session where the DB2 Express software is installed. Because DB2 Express won't use more than 8 cores per virtualization session, be sure to limit the cores that are available to the virtualization session to a maximum of 8 to minimize your license cost under the PVU metric. This license allows an unlimited number of users to access the DB2 Express server by using any possible method. If you are licensing one or more warm DB2 Express standby servers with the PVU metric, you license each for 100 PVUs per physical standby server, regardless of the processor architecture. For more information about PVU licensing, check out the Virtualization Capacity License Counting Rules IBM website.
- Authorized User Single Install License (AUSI): You
must purchase the total number of authorized users (AUs) who will
access each copy of DB2 Express running on separate servers or
virtualization sessions. An AU is a single individual, or an
application or appliance that does not act on behalf of other users,
with a specific identity that resides inside or outside your company.
These licenses can be used over the internet, like an online banking
application, but only by well-known end users, because they must be
specifically identifiable for this license. Note the term specific
identity. If you are using multiplexing or connection
concentration software, these users need to be fully identified before
such technology is applied to a connection. An AUSI license is valid
only for a specific data server and is not transferable across work
shifts, although it can be transferred in the case of employee
You need an AUSI license for anyone accessing the database; however, no matter how many users are accessing your DB2 Express server, you need to buy a minimum of 5 AUSI licenses. For example, if you have 25 users who need to access two separate DB2 Express servers running in different virtualization sessions, you need to purchase a total of 50 AUSI licenses for these 25 users: 2 servers x 25 AUs per server. Even if a maximum of only 12 of these users were ever connected to the data server at one time, all 25 users have to be licensed for each server (so you still need 50 AUSI licenses). If you have a single DB2 Express server and only 3 users, you would still need to purchase 5 AUSI licenses because of the minimum number of users required for this edition. Finally, when licensing a warm DB2 Express standby server, you must license it for 5 AUSI licenses if the hot production server is also using the AU licensing model.
- 12-month Fixed Term License (FT): When you license DB2 Express with an FT license, you are essentially buying a yearly support and entitlement contract for your DB2 Express server. If your FT contract expires, you no longer have support or usage rights for this product. It is easy to calculate the cost of an FT license. You don't have to concern yourself with the number of users, PVU conversion of the server, cores, or anything else, you just buy an FT license for every server or virtualization session where DB2 Express is installed. If you have 5 servers, you need 5 licenses: it's that easy. The FT license allows an unlimited number of users to access the DB2 Express server. A warm standby server would require 1 FTL license per physical server if the primary server or servers were licensed under the FT metric. Under this licensing scheme, it does not matter if the standby server is warm or hot; you need the same number of licenses for the standby in either case.
- Limited Use Virtual Server License (LUVS): This licensing option allows an unlimited number of users to access the data server during a fixed term or perpetually, for a fixed price. The license is priced on a per server basis, and there is no limit on the size of the physical server. This licensing option is ideal for consolidating multiple workloads on a large physical server running DB2 Express in multiple virtual servers. Each physical or virtual server running DB2 Express is limited to 64 GB of memory and 8 processor cores. For example, if you have multiple copies of DB2 Express installed on the same physical server or virtualization session, all copies of DB2 Express use the same 8 processor cores, so no additional license is required. If you want to license a warm standby server under the LUVS metric, a single license entitlement is required per physical standby server. For hot or warm standby servers of a primary licensed under the LUVS metric, you must buy one LUVS license for each server. A cold standby server does not require a license.
When DB2 Express is used on a physical server with more than 8 cores, make sure that you leverage IBM's subcapacity licensing terms to save as much money as possible. With subcapacity licensing, you need to pay for only the cores that are used by DB2. However, different licensing metrics, platforms, and virtualization technologies have different prerequisites for using this edition in a subcapacity environment. For more information about subcapacity licensing, check out the Virtualization Capacity License Counting Rules IBM website for PVU licensing and the Subcapacity Licensing Guide for LUVS licensing.
Figure 2 shows the major differences between DB2 Express-C and DB2 Express.
Figure 2. Difference between DB2 Express-C and DB2 Express
- Changes from DB2 Express 10.1 to 10.5
- Starting with DB2 10.5, you can use up to 64 GB of RAM and 8 processor cores per server or virtualization session. Each database cannot contain more than 15 TB of user data, as reported by the script that is provided with the software. In DB2 10.1, you were restricted to 8 GB of RAM and 4 processor cores per server or virtualization session. There was no database size limit in DB2 10.1.
- Consider it...
- DB2 Express is an entry-level DB2 server that should be considered for workloads that will not greatly benefit from more than 64 GB of database memory or more than 8 cores of processing power. This edition is not well suited for applications that require high scalability and analytic features such as aggregates or multidimensional clustering (MDC) tables, because these features are not part of the base DB2 Express server.
- Tell me something cool about running on DB2 Express
- Sage, one of the world's best-known providers of end-to-end software
that runs SMBs, among many other things, ships their ACCPAC
application running on DB2 Express by default. Craig Downing (Vice
President of Product Management) notes that "DB2 clearly shows IBM's
commitment to the SMB market. In particular, the focus on automating
administrative tasks gives small and mid-sized business high data
reliability without high administrative attention."
When a company that services over 2.8 million users in North America alone (and over 4.5 million users worldwide) chooses DB2 Express to handle one of their most popular applications because of its high reliability and low administrative requirements... now that's cool!
DB2 Workgroup Server Edition... The best choice for departmental workload consolidation with high availability
DB2 Workgroup Server Edition (DB2 Workgroup) is a DB2 server that includes the same features as DB2 Express plus table partitioning. Table partitioning (sometimes referred to as range partitioning) enables you to divide table data among multiple storage containers, according to values in one or more table columns. For example, if you have a table containing sales data, you might allocate table partitions by the year of the sale. Whenever a new sales record is added to the table, DB2 automatically places that record in the appropriate data partition range. The benefits of table partitioning include improved manageability, because you can choose to back up and restore individual data partition ranges instead of entire tables; improved query performance, because the DB2 optimizer will scan only data in relevant data partitions; and improved "up" time, because you can roll data partitions in or out without having to take the entire table offline.
DB2 Workgroup can also handle more demanding workloads than DB2 Express, thanks to higher memory and processing core limits. For this reason, DB2 Workgroup is a great choice for department-sized transactional workloads that need more memory and processing power than is provided by DB2 Express.
Finally, DB2 Workgroup has a broader range of platform deployment options than its DB2 Express counterpart, including support on most of the distributed platforms where DB2 runs: Linux (x64, IBM Power Systems), AIX, Solaris (SPARC and x64), HP-UX Itanium, and Windows. (These links take you to the most up-to-date platform support information.)
Remember that DB2 Express features are part of DB2 Workgroup. For example, STMM, backup compression, archive log compression, pureXML, homogeneous federation, HADR with multiple standbys, row and column access control, time travel query, and more are all included in DB2 Workgroup, because these technologies are all part of DB2 Express.
- Licensing and pricing
- DB2 Workgroup is restricted to 16 cores per server or virtualization
session. DB2 Workgroup is also limited to 128 GB of RAM per server or
virtualization session. These limits must be observed regardless of
how many installations or instances of DB2 Express you have running on
a given server or virtualization session. Be sure to use eligible IBM virtualization technologies and the
appropriate DB2 memory limit parameter, if necessary, to ensure that you
stay within these limits.
For example, if your unpartitioned server has 32 cores, you cannot install DB2 Workgroup, because you have no way of ensuring that the DB2 server will use only 16 cores. However, by configuring a VMWare session to use no more than 16 cores, you could install DB2 Workgroup within that virtualization session and pay only for the cores that DB2 will actually use under IBM subcapacity licensing terms.
Of course, separate installations of DB2 Workgroup in different virtualization sessions can each use up to the limit of CPU/RAM. Consequently, DB2 Workgroup is an ideal choice for consolidation of departmental workloads onto a large physical server. By using IBM-recognized virtualization technologies to divide up the large physical server into several smaller virtual servers with no more than 16 cores each, you can fully utilize all of the capacity on the large physical server.
For example, if you configure two VMWare sessions and install DB2 Workgroup in each of them, and the physical server has 256 GB of RAM, each session's DB2 Workgroup server could address up to 128 GB of RAM. Naturally, you would have to license each of these DB2 Workgroup servers on this same physical server independently, because each installation resides in its own virtualization session. Licensing each session gives you two installations with access to 128 GB of server memory each.
DB2 Workgroup databases are restricted to 15 TB of user data per database under all charge metrics.
A DB2 Workgroup server can be licensed in one of the following ways.
- Processor Value Unit License (PVU): You must purchase the total number of PVUs that are associated with the server or virtualization session where the DB2 Workgroup software is installed. Because DB2 Workgroup won't use more than 16 cores per server or virtualization session, be sure to limit the cores that DB2 can access to a maximum of 16 so that you don't end up paying for cores that won't be used. This license allows an unlimited number of users to access the DB2 Workgroup server by using any possible method. If a warm DB2 Workgroup standby server is licensed under the PVU metric, it needs a license for 100 PVUs, no matter what processor architecture it has, and regardless of how many warm standby servers you intend to run on that physical server. This assumes that all the primary hot servers are also running DB2 Workgroup licensed under the PVU metric. If not, you would need to license 100 PVUs for any hot primary servers that are licensed under the PVU metric, plus you would need to acquire additional licenses for the remaining DB2 servers that are licensed under any other metric. For this reason, it makes sense to try to license all of your DB2 Workgroup primary servers under the same licensing metric to help minimize the licensing cost of your warm standby servers.
- Authorized User Single Install License (AUSI): The terms of this license metric are identical to what was described previously for DB2 Express when licensed by the AUSI metric, except that it now applies to authorized users (AUs) who will access each installed copy of DB2 Workgroup running on separate servers or virtualization sessions. As with DB2 Express, this license metric is best suited for environments where you can identify and purchase in advance sufficient AUSI entitlements for the individuals who will connect to each DB2 Workgroup server, and prevent unauthorized, unlicensed people from accessing those same servers. Also, like DB2 Express, you must acquire a minimum of 5 AUSI licenses per server or virtualization session in which a copy of DB2 Workgroup is installed, although multiple copies installed in the same virtualization session are treated as one installation from a licensing perspective. For example, if you have two copies of DB2 Workgroup installed and running in the same 16-core partition and want the same 7 people to be able to connect to either server, you would need to acquire only 7 AUSI licenses. However, if you were to put those 2 copies of DB2 Workgroup in separate virtualization sessions, you would need to purchase 7 x 2 = 14 AUSI licenses. Finally, when licensing a warm DB2 Workgroup standby server, you need to acquire only 5 AUSI licenses per physical server.
- Limited Use Socket License (LU Socket): This DB2
Workgroup licensing option is procured by paying a specified price for
each socket on the underlying server or virtualization session that
DB2 will use. In addition to the 16-core limit, you are also
restricted to no more than 4 processor sockets. For example, if you
have an unpartitioned 4-socket 4-core Xeon Nehalem EX E38899 server
rated at 1120 PVUs, you would need to buy only four LU Socket
licenses. However, if your physical server is greater than 16 cores,
you would first need to partition the server to ensure that each copy
of DB2 Workgroup is restricted to 16 cores. After you introduce
virtualization into the equation, things can get complicated when you
try to figure out how many LU Socket licenses you must purchase, based
on the number of virtual cores that will be used by DB2 Workgroup. Use
the following simple counting rule to help you calculate the required
LU Socket entitlements in almost any scenario:
- For each physical server (or, where partitioned, virtualization session) that will have a copy of DB2 Workgroup installed, sum the number of processor cores that are available to that server.
- Sum the results of step 1 for all virtual servers on a single physical server.
- Divide the result of step 2 by the number of processor cores per socket on the physical server.
- Round up the result of step 3 to the nearest whole number.
- The lesser of the result of step 4 and the number of active sockets on the physical server is the required LU Socket entitlement.
For example, suppose that you want to run DB2 Workgroup in two logical partitions (LPARs) on the same 48-core, hexacore physical server. Moreover, assume that one of the LPARs will have 5 cores and the other LPAR will have 7 cores. Applying the previous counting rules to this scenario yields 1 LU Socket entitlement for the 5-core LPAR plus 2 LU Socket entitlements for the 7-core LPAR. Therefore, you would need to buy 3 LU Socket licenses in total for this particular environment. For more DB2 Workgroup LU Socket licensing examples, see the Subcapacity Licensing Guide.
The great thing about LU Socket licensing is that you pay the same price per socket regardless of how many cores are on the socket. Keep in mind, however, that you cannot exceed the 16-core limit. LU Socket licensing delivers the best value when used with high-end processors that have many cores per socket. Like PVU, LU Socket licensing allows an unlimited number of users to connect to a DB2 Workgroup server, which makes it an ideal choice for customer-facing applications.
If primary servers are licensed under the LU Socket metric, one or more warm standby servers can be had for the price of a single LU Socket license per physical server.
- Changes from DB2 Workgroup 10.1 to 10.5
- Starting with DB2 10.5, you can use up to 128 GB per server or
virtualization session. Each database cannot contain more than 15 TB
of user data, as reported by the script provided with the software. In
DB2 10.1, you were restricted to 64 GB of RAM per server or
virtualization session. There was no database size limit in DB2 10.1.
Perhaps the biggest change from DB2 10.1 is the removal of the DB2 pureScale clustering capability from DB2 Workgroup Edition. That functionality is now only available in DB2 Advanced Workgroup Server Edition, DB2 Advanced Enterprise Server Edition, and DB2 Developer Edition, as described later in this article. Existing DB2 Workgroup customers who are using DB2 pureScale can request to be "grandfathered" to continue using the DB2 pureScale capability at no extra charge, as described in the DB2 10.5 Announcement letter.
- Consider it...
- DB2 Workgroup can play many roles in a business. It is very well suited for small- to medium-sized businesses that need a full-fledged scalable and available relational database store, but wouldn't benefit from more than 128 GB of memory or more than 16 cores of processing power. Perhaps more than DB2 Express, DB2 Workgroup is especially well suited for enterprise environments that need small servers for line of business applications, or for departments that need enterprise services for lower transactional throughput applications. However, like DB2 Express, this edition is not well suited for applications that require analytic features such as aggregates or MDC tables, because these features are not part of the base DB2 Workgroup server.
- Tell me something cool about running on DB2 Workgroup
- Montefarmaco OTC is a key player in the Italian market for
over-the-counter (OTC) pharmaceuticals and healthcare products. The
company delivers its products nationwide to around 12,000 local
pharmacies, generating up to 40,000 sales invoices every year. Each of
those documents needs to be processed, filed, and stored for a number
of years to comply with a variety of Italian pharmaceutical
To help manage their invoice repository, Montefarmaco OTC implemented a document repository solution that is based on DB2 Workgroup Edition. The extreme scalability and exceptional performance of DB2 Workgroup enables them to support increasing data volumes as their business grows. At the same time, they expect to reduce their overall storage and administration costs by around 50 percent. Did you get that? DB2 Workgroup Edition does more work and saves money at the same time. Now even the procurement folks think that DB2 is cool!
DB2 Enterprise Server Edition... Unmatched scalability, resiliency, and flexibility
DB2 Enterprise Server Edition (DB2 Enterprise) is a full-function, premier web-enabled client/server database server available on all distributed platforms where DB2 runs: Linux (x86, IBM Power Systems, System z), AIX, Solaris (SPARC and x64), HP-UX (Itanium), and Windows. (These links take you to the most up-to-date platform support information.)
DB2 Enterprise is meant for large and mid-sized departmental servers and is rich in base features and services. For example, query performance-boosting services such as intraquery parallelism (technology that breaks a single query into parts that are then executed in parallel), MDC tables (technology that enables data to be clustered according to multiple column attributes for faster retrieval and easier maintenance), materialized query tables (MQTs, which are like views whose results are ready for retrieval or for use in other queries) are all provided at no additional charge in this DB2 edition and are not available in DB2 Express or DB2 Workgroup.
DB2 Enterprise also includes multi-temperature storage management, which enables you to dedicate your most expensive storage devices to your most commonly accessed data, and your least expensive storage devices to your least commonly accessed data to help you maximize I/O throughput and performance where it matters the most. For example, suppose that you have a large table containing retail sales data. Data for the current quarter is inserted and updated frequently, and those transactions need to happen quickly, whereas older data is not accessed very often, and when it is acceptable for response time to be longer, processing can be delayed. With DB2's multi-temperature storage management, you can tell DB2 to put the data from the current quarter on fast SSD storage and everything else on slower disk storage. DB2 will do this in a way that is completely automatic and transparent to your applications.
There are no limits with respect to the amount of RAM that you can leverage with this DB2 edition; in fact, one benchmark result from our labs was obtained by using almost 4 TB of memory for the buffer pools; that's more RAM than the total data volumes that are owned by many companies. There is also no maximum PVU rating for the underlying server or virtualization session where the DB2 Enterprise software is running. The combination of no resource limits with high performance for both transactional and analytic workloads makes DB2 Enterprise a great choice for even the most demanding single server OLTP and mixed workloads.
- Licensing and pricing
You can license DB2 Enterprise in one of the following two ways.
- Processor Value Unit License (PVU): By purchasing the total number of PVUs that are associated with the server or the virtualization session where you plan to run the software, an unlimited number of users and devices can access the DB2 Enterprise server. Different platforms and virtualization technologies have different prerequisites that enable you to use this DB2 edition in a subcapacity environment. If you are licensing DB2 Enterprise as a warm standby (for example, in an HADR configuration), you need a license of only100 PVUs per physical standby server.
- Authorized User Single Install License (AUSI): The
terms of this license metric are identical to what we described
previously for DB2 Express when licensed by an AUSI license, except
that it now applies to authorized users (AUs) who will access each
installed copy of DB2 Enterprise running on separate servers or
virtualization sessions. As with DB2 Express, this license metric is
best suited for environments where you can identify and purchase
sufficient AUSI entitlements in advance for the individuals that will
connect to each DB2 Enterprise server. DB2 Enterprise has a minimum
set of AU users that must be licensed, just like DB2 Express and DB2
Workgroup. However, the difference is that you need to minimally
license DB2 Enterprise with 25 AUSIs for every 100 PVUs for which your
server or virtualization session is rated. For every installation,
there is a break-even point at which it makes more sense to license
the server through the PVU metric. For example, if you are licensing
DB2 Enterprise as a warm standby in an HADR configuration, you need to
license it for only 25 AUs (the required minimum for 100 PVUs).
Let's assume that you are running DB2 Enterprise on a 4-core LPAR on an IBM Power7 750 server rated at 100 PVUs per core. In this case, you would have to purchase at least 100 AUSI licenses, because the total PVU rating for this partition is 400 PVUs (400 PVUs / 100 PVUs = 4 x 25 AUs). If, instead, you are running DB2 Enterprise on a Power7 770 server that has four quad processors rated at 120 PVUs per core, you would have to purchase at least 500 AUSI licenses, because the PVU rating for this server is 1920 PVUs, and when you cross a 100-PVU threshold, you must round up to the next tier to establish the minimum number of users.
Consider another example. If you have 75 users that need to access two separate DB2 Enterprise servers, you would need to purchase a total of 150 AU licenses for these 75 users: 2 servers x 75 AUs per server = 150 (75 for each server). However, if both of these servers have 2 quad-core Intel Xeon E52600-based processors rated at 70 PVU per core, you would need a minimum of 300 AUSI licenses (150 for each server), because of the minimum number of AUSIs that accompany DB2 Enterprise (25 users for every 100 PVUs on the server): ((( 2 sockets x 4 cores = 8 cores) x 70 PVUs per core = 560 PVUs) / 100 PVUs rounded up = 6) x 25 AUs = 150 x 2 servers = 300 AUs.
- Changes from DB2 Enterprise 10.1 to 10.5
- Starting with DB2 10.5, the DB2 pureScale Feature and the DB2 Storage
Optimization Feature are bundled with the DB2 Advanced editions that
are described later in this article. These features are no longer
available as separately purchasable add-ons to DB2 Enterprise.
The Warehouse Design Studio and the SQL Warehousing (SQW) tools, formerly part of the InfoSphere Warehouse Editions, are now included with DB2 Enterprise 10.5. Warehouse Design Studio provides a common design environment for creating physical data models, OLAP cubes, data mining models, SQL data flows, and control flows. SQW is a graphical tool that replaces hand coded SQL by generating SQL for warehouse maintenance and administration, and data extraction, transformation, and load (ETL). Both tools make it easier to take advantage of DB2 Enterprise's built-in analytic capabilities (described earlier).
- Consider it...
- DB2 Enterprise should be strongly considered for any applications that require limitless flexibility (for example, the use of table partitioning) and scalability (there are no PVU or RAM limits). Because there are no limits, there really are no capacity planning considerations with DB2 Enterprise. That said, if you want to run a cluster of servers for extreme scalability, or if you have warehouse workloads that execute against massive amounts of data, or if you wish to leverage advanced features and tools like data compression, workload management, the groundbreaking BLU Acceleration innovations, IBM InfoSphere Optim productivity tools, and so on, then you should consider the DB2 Advanced Editions that are described later in this article.
- Tell me something cool about running on DB2 Enterprise
- Located in Beijing, China, The Palace Museum, also known as The
Forbidden City, is one of China's most important sights. The museum is
a symbol of traditional China, as well as the largest and
best-preserved masterpiece of classical Chinese architecture.
The Palace Museum recently implemented a first-of-a-kind, fully immersive, three-dimensional (3D) virtual world that recreates a visceral sense of space and time in The Forbidden City. This internet-based experience, which represents the city as it was centuries ago during the height of the Ming and Qing dynasties, supports thousands of concurrent users with scalability comparable to that of massive multi-player games. The solution is based on an all-IBM software stack underpinned by none other than DB2 Enterprise Edition... now that's cool!
DB2 Advanced Workgroup Server Edition... Best in class for all of your departmental workload needs
DB2 Advanced Workgroup Server Edition (DB2 Advanced Workgroup) is the newest member of the DB2 product family, having made its debut with DB2 10.5. This edition is available on all distributed platforms where DB2 runs: Linux (x64, IBM Power Systems, SYSTEM Z), AIX, Solaris (x64 and SPARC), HP-UX (Itanium), and Windows. (These links take you to the most up-to-date platform support information.)
DB2 Advanced Workgroup is meant for mid-size departmental deployments that require the most sophisticated features available in DB2; that is, any OLTP, warehouse, or mixed workloads that don't require more than 16 processor cores, 128 GB of memory (or, alternatively, 4 processor sockets; more on that below), and 15 TB of user data per database.
However, don't be misled by the "departmental" designation. DB2 Advanced Workgroup is fully stacked with all the high value data management capabilities you could ever possibly want, including everything that comes with DB2 Enterprise, plus BLU Acceleration column-organized tables, database partitioning, DB2 pureScale clustering, compression, customizable workload management, queue-based replication, continuous data ingest, as well as rich administration and development tools.
- Database partitioning
- Database partitioning, a capability inherited from InfoSphere Warehouse, enables table data to be spread out across multiple servers according to a defined distribution key. Complex analytical queries are transparently broken down by DB2 into subqueries that can be processed in parallel. For very large data stores, database partitioning provides a way to get great query performance by scaling out across multiple logical partitions or physical partitions.
- DB2 pureScale Feature
- DB2 pureScale Feature is another DB2 scale out
capability. However, unlike database partitioning, DB2 pureScale is
designed to provide availability and scalability for OLTP workloads,
and to meet the most demanding transaction processing needs, both now
and in the future. Breakthrough levels of availability are ensured,
thanks to uninterrupted processing during node failures, redundant
architecture, and integration with DB2's HADR capability, starting
with DB2 10.5. Adding capacity is painless because DB2 pureScale does
not require application changes.
If there is one thing to know about DB2 pureScale, it is that it provides transparent application scaling. Although this term is a competitor's favorite, it is just marketing. DB2 pureScale has direct lineage to the DB2 for z SYSPLEX coupling facility and inherits many fundamental characteristics, such as a global shared buffer pool, page registration, and locking services. This means that you don't have to build locality of data information into your applications. In addition, there are all sorts of amazing engineering techniques that provide the fastest failure detection and recovery times that we have ever seen in distributed computing environments, as well as near-linear scalability. It is outside the scope of this article to delve into what DB2 pureScale looks like and how it works. Figure 3 shows an architectural overview of a DB2 pureScale environment. As you can see, a DB2 pureScale environment is made up of multiple data servers. A cluster caching facility (CF) provides central control services for a global buffer pool, lock management, and interested page lists. A DB2 pureScale environment can have multiple CF servers. Clients connect to members in a DB2 pureScale cluster, and these members interact with the CF to help process client applications.
Figure 3. Architecture of a DB2 pureScale environment
- DB2 compression services
- DB2 compression services optimize the
performance of your database while minimizing the footprint of your
data. By using intelligent compression algorithms and techniques to
shrink the size of data located in tables (including temporary
tables), indices, and more, DB2 is able to maximize compression rates
and maintain those rates across time while also reducing memory usage.
As a result, DB2 compression yields impressive potential for memory
and disk savings (up to 80 percent) and performance speed-up for
I/O-bound systems (what data warehouse systems are not I/O bound?).
But there is more.
Consider for a moment the other implicit benefits that arise from compression. Trust us, it extends beyond disk savings. Think about all those backup images that you are mandated to keep. Not only will they be smaller, the backup operations themselves will run faster because you are backing up fewer data pages. Think about your Q/A and test environments. Consider for a moment that if data is compressed on disk and in the memory buffers, you will be able to get more data into those heaps. Not only will performance likely improve, but page-based maintenance operations such as RUNSTATS and REORG could run faster too. Take all of this into account, and consider the fact that DB2 can support over 2,300 rows on a single data page, and you've got a pretty compelling I/O bottleneck remover on your hands. Finally, consider the environment charge back for your storage; not only will it save you money, it'll reduce the ecological footprint of your IT solution. The point here is that compressing your data is more than just saving disk space; it is environmentally friendly too!
- DB2 workload management
- DB2 workload management is a DB2 capability that enables you to prioritize allocation of CPU, memory, and I/O to competing workloads according to business priorities. For example, let's assume that you have a mixed workload database used mainly for transactional purposes but will occasionally need to process long-running warehouse-type queries. Suppose, moreover, that you want to ensure that those long-running queries don't ever slow down the overall database system. With workload management, you can establish policies that will prevent the long-running queries from impacting transactional processing. When combined with workload management, multi-temperature storage management becomes even more powerful, because it enables you to also prioritize workloads according to the temperature of the data. As a result, even if you don't have different storage types, workload management will still give priority to the execution of workloads that impact hot data.
- Replication technologies
- Homogeneous Q replication and change data capture (CDC) are both replication technologies that provide greater throughput and can handle larger transaction volumes than homogeneous SQL replication. Homogeneous Q replication, in particular, can achieve throughputs in excess of 50,000 transactions per second with subsecond latencies, even with 1000 miles between source and target. With DB2 Advanced Workgroup Server Edition, you can use either of these capabilities to replicate from a single DB2 source data server to up to two DB2 target data servers.
To summarize, DB2 Advanced Workgroup comes with loads of capabilities and tools that are not even found in DB2 Enterprise, not to mention DB2 Workgroup. These capabilities and tools are included in the following list:
- BLU Acceleration column-organized tables
- DB2 pureScale
- Database partitioning
- solidDB and solidDB Universal Cache
- Homogeneous Q Replication
- Change Data Capture (CDC)
- Continuous Data Ingest (CDI)
- InfoSphere Optim Performance Manager (OPM) Extended Edition
- InfoSphere Optim Query Workload Tuner
- InfoSphere Optim Configuration Manager
- InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows
- InfoSphere Data Architect (10 Authorized User licenses)
- Warehouse Model Packs
- Warehouse Mining and Text Analytics
- Warehouse Cubing Services
- Cognos (5 Authorized User licenses)
DB2 Advanced Workgroup provides incredible value over DB2 Workgroup and even DB2 Enterprise by including multiple capabilities and tools that cannot be acquired with either of those DB2 editions. Moreover, you can use DB2 Advanced Workgroup for strictly transactional workloads, strictly warehouse workloads, or anything in between. Don't forget that DB2 Advanced Workgroup includes select DB2 and InfoSphere Warehouse 9.7 and 10.1 editions, as we described earlier, which makes it easy to start taking advantage of advanced DB2 capabilities and tools, even if you need to remain on the version 9.7 or 10.1 base code for the time being. If you want the ultimate in deployment flexibility and can live with the resource restrictions, DB2 Advanced Workgroup is a superb choice.
- Licensing and pricing
- If licensed under the PVU or AUSI options that are described below, DB2
Advanced Workgroup is restricted to 16 cores and 128 GB of RAM per
server or virtualization session. If licensed under the TB option
described below, DB2 Advanced Workgroup is restricted to 4 processor
sockets but has no memory restrictions. These limits must be observed
regardless of how many installations or DB2 Advanced Workgroup
instances (created by using the db2icrt command) you have running on a
particular server or virtualization session. Be sure to use eligible
IBM virtualization technologies and the appropriate DB2 memory limit
parameter, if necessary, to ensure that you stay within these limits.
For example, if your unpartitioned server has 32 cores on 4 processor sockets, you cannot install DB2 Advanced Workgroup with a PVU or AUSI license, because you have no way of ensuring that the DB2 server will use only 16 cores. However, by configuring a VMWare session to use no more than 16 cores, you could install DB2 Advanced Workgroup and apply a PVU or AUSI license within that virtualization session, and pay for only the cores that DB2 will actually use under IBM's subcapacity licensing terms. Alternatively, if you are eligible to license DB2 Advanced Workgroup by TB (as explained below), you could install the software on this unpartitioned server because it meets the 4-processor socket criterion.
Regardless of how you license DB2 Advanced Workgroup, you are limited to 15 TB of user data per database, as reported by the script provided with the software.
You can license DB2 Advanced Workgroup in one of the following three ways.
- Processor Value Unit License (PVU): This option allows an unlimited number of users and devices to access the DB2 Advanced Workgroup server. You must purchase the total number of PVUs that are associated with the server or virtualization session where the DB2 Advanced Workgroup software is installed. When licensed by PVU, DB2 Advanced Workgroup is not allowed to use more than 16 cores per server or virtualization session, so be sure to limit the number of cores that DB2 can access to no more than 16 so that you don't end up paying for cores that won't be used. Keep in mind that different platforms and virtualization technologies have different prerequisites that allow you to use this DB2 edition in a subcapacity environment. In addition, if you are licensing DB2 Advanced Workgroup as a warm standby (for example, in an HADR configuration), you need to license it for only 100 PVUs per physical standby server, or in the case of a DB2 pureScale or database partitioning warm standby cluster, 100 PVUs across all machines in that cluster.
- Authorized User Single Install License (AUSI): The terms of this license metric are identical to what was described previously for DB2 Enterprise when licensed by the AUSI metric, except that it now applies to authorized users (AUs) who will access each installed copy of DB2 Advanced Workgroup running on separate servers or virtualization sessions. As with DB2 Enterprise, this license metric is best suited for environments where you can identify and purchase sufficient AUSI entitlements for the individuals who will connect to each DB2 Advanced Workgroup server. In addition, as with DB2 Enterprise, you need to minimally license DB2 Advanced Workgroup with 25 AUs for every 100 PVUs for which your server or virtualization session is rated. If you are licensing DB2 Advanced Workgroup as a warm standby (for example, in an HADR configuration), you need to license it for only 25 AUs (the required minimum for 100 PVUs).
- Terabyte (TB) License: Under this licensing option,
you must license by the terabyte of user data (rounded up to the
nearest terabyte), as reported by the script provided with DB2
Advanced Workgroup. You must also calculate the required TB licenses
on a per database basis. Unlike the PVU or AUSI option, there are no
memory or core limits associated with the TB license metric, although
you are restricted to a maximum of 4 processor sockets. This license
metric is intended for use with predominantly warehouse workloads. For
that reason, you are required to either use database partitioning with
at least two active database partitions or maintain at least 75
percent of your user data in BLU Acceleration column-organized tables.
In the latter case, the majority of your workloads must access those
tables when a DB2 Advanced Workgroup server is licensed by the TB
metric. For column-organized tables, OPM has additional table space
and workload management metrics in its reporting capabilities to help
you monitor their use. For more details, see Custom reports in the OPM Information Center.
DB2 pureScale is not available with the TB license, but HADR capabilities are available with the TB license as of DB2 10.5 Fix Pack 4. Even without HADR, it is possible to set up a standby environment by using alternate technologies such as the homogeneous Q replication capability included with the DB2 Advanced editions. In that case, you would be required to license 1 TB per database on a warm standby.
When licensing a DB2 pureScale environment, you have to license DB2 Advanced Workgroup Edition for each member in the cluster, excluding the CF servers that don't need to be licensed. The aforementioned core and memory limits also apply to the entire cluster.
For example, let's assume that you have a DB2 pureScale environment composed of five Power7 virtual servers that are rated at 400 PVUs each (100 PVUs x 4 cores). Two of these servers are configured to perform the role of a CF, leaving three other servers to act as data members. You would be allowed to use DB2 Advanced Workgroup in this environment, because it falls within the total of 16 cores per cluster requirement for a PVU license. For this environment, you would have to purchase 1200 PVUs of DB2 Advanced Workgroup (400 PVUs x 3 servers). You do not have to include the two CFs with any DB2 Advanced Workgroup license. The CF servers don't have to sit on a separate server; they can reside within a virtualization session co-located on the same server as a DB2 member running in a separate virtualization session.
- Consider it...
- DB2 Advanced Workgroup is ideal if you want to have all the very best
DB2 capabilities and tools at your disposal. This edition comes
equipped with powerful capabilities such as BLU Acceleration, DB2
pureScale clustering, database partitioning, storage optimization
services (like compression), performance optimization (OPM), and other
features (homogeneous Q replication, federation, and DB2 workload
management), not to mention a full complement of high value tools
(like InfoSphere Optim Query Workload Tuner and InfoSphere Optim
pureQuery Runtime for Linux, UNIX, and Windows). DB2 Advanced
Workgroup is suitable for any type of workload that does not require
more than 16 cores and 128 GB of RAM (or 4 processor sockets if
licensed by TB) and won't use more than 15 TB of data. If you need
unlimited scalability or have massive databases exceeding the 15-TB
limit, we strongly recommend that you look at DB2 Advanced Enterprise
Edition (described below) instead.
If you are currently running DB2 Workgroup 9.7 or 10.1, or any InfoSphere Warehouse Departmental 9.7 or 10.1 edition, and are looking for additional capabilities and tools, consider DB2 Advanced Workgroup. Inclusion of the 9.7 and 10.1 versions of DB2 Workgroup and the InfoSphere Warehouse Departmental editions in DB2 Advanced Workgroup means that you can start to take advantage of any additional functionality supported at the version 9.7 and 10.1 code levels while migrating to the version 10.5 code base at your own pace.
DB2 Advanced Enterprise Server Edition... The real deal!
DB2 Advanced Enterprise Server Edition (DB2 Advanced Enterprise) is the ultimate IBM database server edition. This edition is available on all distributed platforms where DB2 runs: Linux (x86, IBM Power Systems, System z), AIX, Solaris (SPARC and x64), HP-UX (Itanium), and Windows. (These links take you to the most up-to-date platform support information.)
DB2 Advanced Enterprise is designed for mid-sized to very large enterprise servers and is packed with all of the same great features and tools that come with DB2 Advanced Workgroup Edition, but without any resource limitations whatsoever. If you need unrestricted ability to scale in, scale out, add memory, or store unlimited amounts of data, this is the recommended DB2 edition for you. Whatever the workload, whatever the need - from 100-plus-node DB2 pureScale clusters to petabyte data warehouses - DB2 Advanced Enterprise is up to the task.
One notable difference between DB2 Advanced Workgroup and DB2 Advanced Enterprise is that DB2 Advanced Enterprise includes the ability to federate to additional data sources, including Oracle and SQL Server databases. This capability enables you to access and integrate diverse data and content sources across your entire enterprise into a single, virtual, consolidated view to support your key business decisions and processes.
- Licensing and pricing
You can license DB2 Advanced Enterprise in one of the following three ways.
- Processor Value Unit License (PVU): This option allows an unlimited number of users and devices to access the DB2 Advanced Enterprise server. You must purchase the total number of PVUs that are associated with the server or the virtualization session where you plan to run the software. Different platforms and virtualization technologies have different prerequisites that allow you to use this DB2 edition in a subcapacity environment. In addition, if you are licensing DB2 Advanced Enterprise as a warm standby (for example, in an HADR configuration), you need to license it for only 100 PVUs per physical standby server.
- Authorized User Single Install License (AUSI): The terms of this license metric are identical to what was described previously for DB2 Advanced Workgroup when licensed by the AUSI metric, except that it now applies to authorized users (AUs) who will access each installed copy of DB2 Advanced Enterprise running on separate servers or virtualization sessions. As with DB2 Advanced Workgroup, this license metric is best suited for environments where you can identify and purchase sufficient AUSI entitlements for the individuals who will connect to each DB2 Advanced Enterprise server. And as with DB2 Advanced Workgroup, you need to minimally license DB2 Advanced Enterprise with 25 AUs for every 100 PVUs for which your server or virtualization session is rated. If you are licensing DB2 Advanced Enterprise as a warm standby (for example, in an HADR configuration), you need to license it for only 25 AUs (the required minimum for 100 PVUs).
- Terabyte (TB) License: Under this licensing option,
you must license by the terabyte of user data (rounded up to the
nearest terabyte), as reported by the script provided with DB2
Advanced Enterprise. You must also calculate the required TB licenses
on a per database basis. This license metric is intended for use with
predominantly warehouse workloads. For that reason, you are required
to use either database partitioning with a minimum of two active
database partitions, or maintain at least 75 percent of your user data
in BLU Acceleration column-organized tables. In the latter case, the
majority of your workloads must access those tables when a DB2
Advanced Enterprise server is licensed by the TB metric. For
column-organized tables, OPM has additional table space and workload
management metrics in its reporting capabilities to help you monitor
their use. For more details, see Custom reports in the OPM Information Center.
DB2 pureScale is not available with the TB license, but HADR capabilities are available with the TB license as of DB2 10.5 Fix Pack 4. Even without HADR, it is possible to set up a standby environment by using alternate technologies such as the homogeneous Q replication capability included with the DB2 Advanced editions. In that case, you would be required to license 1 TB per database on a warm standby.
When licensing a DB2 pureScale environment, you must license DB2 Advanced Enterprise Edition for each member in the cluster, excluding the CF servers, which do not need to be licensed.
- Changes from DB2 Advanced Enterprise 10.1 to 10.5
- DB2 Advanced Enterprise 10.1 was targeted at large OLTP or mixed
workloads that could run on a single large server. However, if you
wanted a clustered OLTP environment, you needed the add-on DB2
pureScale Feature. Alternatively, if you wanted database software that
was optimized for warehouse workloads, one of the InfoSphere Warehouse
editions would have been appropriate instead. All of that changes with
DB2 Advanced Enterprise 10.5. The addition of DB2 pureScale, BLU
Acceleration, and most of the capabilities that were previously found
in the InfoSphere Warehouse Enterprise editions, including database
partitioning, makes DB2 Advanced Enterprise 10.5 capable of handling
The TB pricing metric, which might be appropriate for your warehouse and analytic workloads, is also new for version 10.5. Choosing this metric enables you to add more CPU power to your long-running queries without having to incur additional costs, which would be the case under the PVU metric.
Finally, DB2 Advanced Enterprise 10.5 includes selected 9.7 and 10.1 versions of DB2 Enterprise Edition, InfoSphere Warehouse Enterprise Edition, and more, as described in the DB2 10.5 Announcement letter. These are unrestricted supporting programs, which means that they can be deployed instead of or in conjunction with DB2 Advanced Enterprise 10.5, thereby making it easy for you to start taking advantage of the capabilities that come with DB2 Advanced Enterprise 10.5 if you are running version 9.7 or 10.1 DB2 or InfoSphere Warehouse code.
- Consider it...
- DB2 Advanced Enterprise should be strongly considered for any applications that require limitless flexibility, scalability, compression, security, and advanced database management tools. DB2 Advanced Enterprise comes with everything that you need for your enterprise solution.
- Tell me something cool about running on DB2 Advanced Enterprise
- Domino's Pizza is one of the largest pizza delivery services in the world today. Having been in business for over 50 years and having grown into a global enterprise with franchises worldwide, Domino's Pizza understands and appreciates the enterprise-level solution that DB2 Advanced Enterprise provides. They collect data from their many locations to provide better products and services to their clients, and guess which database they use? You guessed it...DB2 Advanced Enterprise! Domino's DBAs note: "With DB2 Advanced Enterprise, we don't need to worry about which feature is included or not included, because everything we need is there. This allows us to concentrate on our application and solution. It's great!" Our kids' favorite pizza company in the world is using DB2, and we know that we don't have to worry about getting our pizza delivered to the right address, on time, and with the widest selection of fresh toppings... now that's cool!
DB2 Advanced Recovery Feature... Database backups and recovery made easy
DB2 Advanced Recovery Feature is a new separately-priced DB2 10.5 feature. It also happens to be the only add-on feature for DB2 10.5. This feature is actually a bundle of advanced database backup, recovery, and data extraction tools that can help your enterprise improve data availability, mitigate risk, and accelerate crucial administrative tasks when time is of the essence.
As of DB2 10.5, this feature bundle consists of the following three tools:
- IBM DB2 Merge Backup
- IBM DB2 Recovery Expert
- IBM InfoSphere Optim High Performance Unload for DB2
- Licensing and pricing
- DB2 Advanced Recovery Feature can be purchased for use with any DB2
edition except DB2 Express-C. You must acquire the same pricing metric
and number of entitlements as you acquired for the DB2 edition with
which this feature will be installed.
If DB2 Advanced Recovery Feature is to be installed on a warm standby server, no additional entitlements are required under any charge metric.
For developers only...
A special offering called DB2 Developer Edition (known as Database Enterprise Developer's Edition or DEDE prior to DB2 10.5) is available for application developers and testers. This is a reduced price offering that gives you access to most of the DB2 capabilities and editions, as well as DB2 Connect, for the purpose of developing, evaluating, demonstrating, and testing application programs. It is licensed on a per user basis. Depending on the number of users that you have, it might or might not be a more cost-effective solution for the processes that make up the application development life cycle.
You can use DB2 Developer Edition for any non-production environment. What's more, a developer or tester with a DB2 Developer Edition license can connect to any non-production DB2 server, which makes it an incredibly effective money saver for your business.
DB2 not only runs on many platforms, but it also has a flexible edition structure that enables you to find the right price point and services for the right solution. In DB2 10.5, you'll find a more comprehensive set of edition offerings and notice that all of the editions offer a lot more. Considering the state of the economy, you can never go wrong with more value.
Scaling DB2 is seamless from edition to edition, and this gives you the ability to leverage your DB2 investment as your business grows. Whether you are an SMB tracking sales over the internet, a mobile solutions provider, or a Fortune 500 company trying to analyze sales data to offer clients the right products at the right time, there is a DB2 edition that's right for you!
- Take a look at the DB2 with BLU Acceleration site.
- Read the "Licensing distributed DB2 10.5 data servers in a high availability (HA) environment" article to ensure that you are correctly licensing your DB2 for Linux, UNIX, and Windows data servers in a high availability environment.
- Read the "Compare the distributed DB2 10.5 data servers" article to compare the DB2 for Linux, UNIX, and Windows editions in a side-by-side comparison table.
- Learn about DB2 Express-C, the no-charge DB2 offering for the community.
- Download a free trial version of DB2 for Linux, UNIX, and Windows.
- Use DB2 for free. Download DB2 Express-C, a no-charge DB2 offering that provides the same core data features as DB2 Express Edition and a solid base from which to build and deploy applications.