IBM DB2 is a relational DBMS with integrated support for a number of NoSQL capabilities, including XML, graph store and Java Script Object Notation (JSON). Used by organizations of all sizes, DB2 provides a data platform for both transactional and analytical operations as well as continuous availability of data to keep transactional workflows and analytics operating efficiently.
OSes DB2 supports
DB2 is available for three primary platforms:
- Workstation, with DB2 for Linux, Unix and Windows (LUW)
- Midrange, with DB2 for iSeries
- Mainframe, with DB2 for z/OS, the predominant mainframe relational DBMS
DB2 supports these Unix variants: AIX, Solaris and HP-UX; Linux variants include Red Hat, SUSE, Ubuntu and Linux on pSeries and System z.
DB2 for i comes pre-installed on the iSeries server and, as such, won't be addressed here.
BLU Acceleration, added to DB2 in Version 10.5, provides a column store capability, as well as technological performance boosts for actionable compression, exploitation of the single instruction multiple data (SIMD) capabilities of modern CPUs, and data skipping technology.
For DB2 11, BLU Acceleration can be deployed across a massively parallel processing (MPP) network cluster architecture. BLU Acceleration can be used in database partitioning facility environments for MPP scale out, which can improve query performance for large partitioned applications. Additionally, this version improves in-memory capabilities for column-organized tables and integration with IBM Data Server Manager to analyze workloads and determine whether they can benefit from column-oriented tables.
DB2 for LUW 11 also adds support for High Availability Disaster Recovery, or HADR, in pureScale clustered DB2 databases. Data can be mirrored from a primary pureScale database cluster to a second local or remote standby cluster, thereby improving recoverability. An additional improvement for pureScale is the delivery of online fix pack update support, allowing users to apply DB2 11.1 fix packs to individual members running in a pureScale cluster without having to take the entire cluster offline.
On the security front, DB2 11 enables the use of enterprise key management systems for storing native encryption master keys. The DB2 Advanced Recovery Feature is a bundle of database backup, recovery and data extraction tools that can help users improve availability, mitigate risk and optimize administrative tasks.
The current version of DB2 for z/OS is 11, which was released in October 2013. Highlights of DB2 11 for z/OS include performance and availability enhancements, improved analytics capabilities, application compatibility features, improved access path stability and archive transparency. IBM also offers the IBM DB2 Analytics Accelerator, which is an add-on to DB2 for z/OS for speeding up analytical queries.
DB2 for LUW requires two configuration files: the Database Manager configuration file containing configuration parameters for an entire instance, and Database configuration files for each database.
DB2 for LUW is also embedded into IBM's database PureData appliance, enabling turnkey implementation of DB2 without the time-consuming installation and configuration required of a normal DBMS installation.
DB2 for z/OS is installed as a subsystem with components for database services, lock management, distributed request processing and connecting to other mainframe agents. The subsystem is configured with a set of parameters called the DSNZPARMs.
As of Version 11, IBM offers eight editions of DB2 for LUW:
- DB2 Advanced Enterprise Server Edition offers the highest level of functionality available for DB2 and is suitable for transactional, warehouse and mixed workloads. This edition has no processor, memory or database size limits and comes with a full complement of warehouse tools, InfoSphere Optim tools and IBM Data Studio.
- DB2 Advanced Workgroup Server Edition is similar to the DB2 Advanced Enterprise Server Edition, except it places limits on processor, memory and database size. This edition is best-suited for deployment in a departmental, workgroup or midsize business environment.
- DB2 Enterprise Server Edition is suitable for transactional and mixed workloads, and like the advanced version, it has no processor, memory or database size limits. However, unlike the advanced version, it lacks column-organized tables, in-memory database, data compression, workload management, replication and distributed partitioning capabilities.
- DB2 Workgroup Server Edition is primarily for transactional workloads. This edition places limits on processor, memory and database size, which makes it ideal for medium-size workloads.
- DB2 Direct Advanced Edition is a version of DB2 Advanced Workgroup Server Edition for digital delivery.
- DB2 Direct Standard Edition is a digital version of DB2 Workgroup Server Edition for digital delivery.
- DB2 for Big Data combines the relational engine of DB2 with IBM's BigInsights platform for big data and analytics. This edition is designed to enhance portability of data across relational systems and Hadoop.
- DB2 Developer Edition is designed for a single application developer to design, build and prototype applications for deployment on any of the IBM Information Management client or server platforms.
Additionally, DB2 Express-C edition, an entry-level edition of the DB2 data server for the developer and partner community, is available for free. It can be installed on physical or virtual systems with any amount of CPU and RAM, and is optimized to use up to a maximum of two cores and 16 GB of memory.
DB2 data types
DB2 supports the standard numeric, character, and date/time data types well as LOBs, XML and user-defined types. JSON is supported via DB2 JSON, a driver-based solution that delivers JSON data representation within the context of an RDBMS.
IBM has published numerous TPC benchmarks for DB2 on all platforms supported. IBM sells DB2 for LUW direct, through business partners, online and through application specific licensing, which allows vendors such as SAP to license and sell DB2 relational DBMS as part of their applications.
Pricing is based on processor value unit (PVU), which is a unit of measure that IBM uses to license its software. IBM applies a PVU count to each core of a processor. The pricing is then based on the number of PVUs for the processor and by the number of processors made available to DB2.
The developer edition is licensed by authorized user, not PVU. There are additional nuances to pricing and licensing based on DB2 editions that aren't covered here.
The license cost for DB2 includes first-year support and subscription, entitling the owner to contact IBM for support as well as to download fixes and upgrades to future versions/releases of the product.
DB2 for z/OS relational DBMS is sold directly by IBM and is licensed as a monthly license charge (MLC) product. There are numerous metrics available for pricing MLC products. A 90-day free trial download is available from the IBM website.
About the author
Craig S. Mullins is a data management strategist, researcher, consultant and author with more than 30 years of experience in all facets of database systems development. He is president and principal consultant of Mullins Consulting Inc. and publisher/editor of TheDatabaseSite.com. Email him at firstname.lastname@example.org.
This article was updated in September 2016.