Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning

Norberto Gasparotto Filho
Norberto Gasparotto Filho is a database specialist with more than eight years of experience with database administration. He was the winner of the first edition of "DB2's Got Talent" contest in 2011. He also worked as programmer using a variety of technologies, and has certifications in both programming and database administration areas. In his blog ("Insights on DB2 LUW database admin, programming and more"), Norberto shares lessons learned in the day-to-day database administration work, tips and knowledge. During his spare time, Norberto likes to run, ride a bike and have fun with his kids and wife. Learn more in Norberto's profile in the developerWorks community.

Summary:  This tutorial introduces you to the basics of the IBM® DB2® 10.1 product editions, functionalities and tools, along with underlying concepts that describe different types of data applications such as OLTP, data warehousing / OLAP, non-relational concepts and more. It will briefly introduce you to many of the concepts you'll see in the other tutorials in this series, helping you to prepare for the DB2 10.1 Fundamentals certification test 610.

View more content in this series

Date:  18 Oct 2012
Level:  Introductory PDF:  A4 and Letter (2292 KB | 50 pages)Get Adobe® Reader®


DB2 products

Stating that DB2 10.1 delivers the right data management solutions for any business is not a marketing speech. No other database management system can match the advanced performance, availability, scalability, and manageability features that are found in DB2 10.1. However, there are different editions of DB2 available, each suited to a different part of the marketplace. On the Fundamentals exam you are expected to understand the different DB2 products and editions, covered in this section.

Within IBM Information Management software, there are essentially two flavors of DB2: DB2 for z/OS and DB2 for Linux, UNIX, and Windows (sometimes referred to as DB2 for LUW or DB2 for distributed platforms). All the distributed editions of DB2 that are currently available are shown in Figure 1. If you examine this figure closely, you will see a progression — each edition displayed includes all the functions, features, and benefits of the editions found below it (along with additional features and functionality) as you move up the stack.

Figure 1. The different editions of DB2 for distributed platforms that are available
DB2 Express-C, DB2 Express Edition,                         DB2 Workgroup Edition, DB2 Enterprise Server Edition, DB2 Advanced Enterprise                         Server Edition, and InfoSphere Warehouse

It’s important to note that if you decide to move from one product edition to another, there's no need to worry about incompatibilities due to product migration. For example, if you created a database using DB2 Express-C, and later decided to purchase DB2 Enterprise Server Edition, you can keep using the same server by simply upgrading DB2 using the DB2 Enterprise Server Edition (ESE) installation image. The end result will be a functioning database environment that now has many more features available. The only concern is whether or not features you desire are available in the target version. For instance, if you move from DB2 ESE to DB2 Express, you would no longer be able to use partitioned tables, MDC, storage optimization, multi-temperature storage and any other feature that is not present with DB2 Express.
Even if you want to move to another product such as InfoSphere® Warehouse (which you’ll see more about later), no changes to your existing databases are needed.


You saw the term LUW, and you know it stands for Linux, UNIX, and Windows. At one time, DB2 for Linux, UNIX, and Windows was called DB2 UDB (Universal Database). The term UDB is not in use anymore (since DB2 9), and you should avoid using it. If you find a paper referring to DB2 UDB, it's probably referring to version 8 or earlier.

Across the Linux, UNIX, and Windows platforms, the DB2 code is about 90% common, with 10% of the code on each operating system reserved for tight integration into the underlying operating system (such as using huge pages on AIX® or the NTFS file system on Windows).

Throughout this tutorial, you will see several references to DB2 for z/OS and DB2 for IBM i. These are part of the DB2 family of products as well, however, they run on higher platforms (the mainframe). While DB2 for z/OS and DB2 for IBM i databases run on specific hardware, operating system and platforms, their SQL is 95% portable to DB2 for Linux, UNIX, and Windows.

DB2 for Linux on System z® (also known as zLinux) should not be confused with DB2 for z/OS. In this case, the DB2 product that runs on zLinux is DB2 for Linux, UNIX, and Windows — and any DB2 Client or driver is able to connect to it, without need of DB2 Connect (which will be covered later).

Now let’s find out more about the editions of DB2 for Linux, UNIX, and Windows that are available.

DB2 for Linux, UNIX, and Windows 10.1 Editions

Different product editions of DB2 for Linux, UNIX, and Windows enable users to choose the specific flavor of DB2 that best fits their needs. The remainder of this section describes each product edition available, and provides a brief overview of the features and functionality that is offered with each product edition.

DB2 Express-C

As the entry-level edition, DB2 Express-C (also known as “DB2 Express-Community edition") gives you all the core DB2 10.1 capabilities at no charge. Designed to be up and running in minutes, it includes self-management features as well as some of the functionality that was only found in paid editions in the past. Yes, it's FREE to develop, use and distribute!

Features included:

  • Self Tuning Memory Manager (STMM)
    • STMM auto-configures several memory configuration parameters, simplifying the memory-management task.
  • pureXML
    • Through pureXML, DB2 stores XML documents in their native format and enables the creation of indexes on XML columns (resulting in faster search and retrieval of data), query through XQuery and SQL over XML data, and much more.
  • Backup and archived logs compression
    • Backups as well as archived logs are known as huge disk space consumers. Backups can be compressed by just using the COMPRESS clause with the DB2 backup utility, and log archive compression can be enabled through a database configuration (set LOGARCHCOMPR1 to ON). Archived log compression was introduced in DB2 10.1.
  • Oracle compatibility
    • Since version 9.7, one of the great new features added to DB2 is its compatibility with Oracle databases (also called Oracle enablement). With this feature, you can use Oracle’s data types, PL/SQL, functions and so forth, with a DB2 database. DB2 for Linux, UNIX, and Windows is 98% compatible with Oracle, which means that you would have to concern yourself with just 2% of your application’s code if you wanted to move from Oracle to DB2. That also means that Oracle professionals can now say they can work with DB2!
  • Time Travel Query
    • Another new feature of DB2 10.1 makes it possible to issue queries that will find out what your data looked like at a specific date and time (using what are known as temporal tables, which are tables that have been prepared to hold temporal data). As temporal tables enable the use of system and application date/time values, it is possible to issue changes that will happen in the future! Confused? Curious? You can find out more in the third tutorial in this series: "Working with Databases and Database Objects".
  • Federation with DB2 for Linux, UNIX, and Windows and Informix®
    • This feature allows you to access objects in other DB2 for Linux, UNIX, and Windows databases (Homogeneous Federation) as well as in Informix databases.
  • DB2 Text Search Extender
    • With this feature, you can use the CONTAINS clause in your queries for full-text search in tables that have text search-compatible indexes.
  • Spatial Extender
    • This feature makes it possible for you to store, retrieve, search, and manage spatial data that is represented by “geographic features" like a river, a forest and so on.
  • Resource Description Framework (RDF)
    • Also known as NoSQL graphs storage, this new feature introduced in DB2 10.1 enables developers to work with information triples or quads, in huge volumes, and at a high velocity, using the SPARQL query language.

DB2 Express-C 10.1 is available for the following operating systems:

  • Windows (32/64bit)
  • Linux (on x86 32/64bit and POWER)
  • Solaris (on x86-64)

DB2 Express-C is available in more than 16 languages and is much less restrictive than other free entry-level database products. As a free edition, DB2 Express-C limits are only applied to CPU (DB2 Express-C will use up to 2 cores) and memory (it can use up to 4 GB). That means, if your server has 16 cores and 20 GB of RAM, DB2 Express-C 10.1 will work, but it will only use 2 cores of your server's CPU and 4 GB of your RAM. If you were expecting more restrictions for a free/community edition database management product, that's not what happens with DB2 Express-C! Your databases will be able to grow a lot in volume, and you'll be able to have as many connected users as you need.

New releases for DB2 Express-C are made available as major updates are released for other editions. However, upgrades are only possible through the installation of new releases over previous ones, and once a new release is published, links for older releases are removed from the download website.

Should your database need more processing power or memory, or should you need more formal support, access to fix packs (product updates), or additional features like SQL Replication and High Availability Disaster Recovery (HADR), you can migrate Express-C to any of the other DB2 editions available.

DB2 Express Edition

Ideal for small and medium businesses (SMB), DB2 Express edition is a fully-functional edition of DB2 at an attractive entry-level price. DB2 Express Edition includes all the Express-C features, plus the following:

  • DB2 Advanced Copy Services (ACS)
    • This feature enables you to use the fast copying technology available with some storage devices to perform backup and restore operations, which can dramatically speed-up backup and restore operations.
  • Online reorganization
    • This feature allows you to issue REORGs (a command that reorganizes/rebuilds tables and indexes) online – that is, while the database is in use, and its objects are being accessed.
  • Label Based Access Control (LBAC)
    • This makes it possible to protect data using labels and security policies.
  • Row and Column Access Control (RCAC)
    • Introduced in DB2 10, RCAC complements the existing table privileges model by protecting access to a table at the row level, column level, or both.
  • Web services federation
    • DB2 can have objects federated with web services, through web services wrappers, using Web Services Description Language (WSDL).
  • Homogeneous SQL replication
    • DB2 Express can replicate data with other DB2 for Linux, UNIX, and Windows databases through capture and apply agents.
  • High Availability Disaster Recovery (HADR)*
    • This feature allows you to have a cluster of servers consisting of a primary database server and multiple standby database servers (multiple standby is a new feature in DB2 10.1). Standby database servers can take over to continue working and minimize impact for applications when problems occur with the primary database. Another offering with this feature is Read on Standby (ROS), which makes it is possible to issue queries (SELECT statements) against a standby database.
  • Tivoli Service Automation for Multiplatforms (SA MP) support
    • This support is used in conjunction with HADR to trigger automatic failover in a two-node HADR cluster when a failure occurs.

*To use the HADR feature, you must license the DB2 Express Edition product on both servers in the cluster. You can host several standby databases on the same server, in which case only one license is needed.

DB2 Express Edition 10.1 licenses are allowed to use up to 8 GB of memory (total), and can use up to 4 cores of a server's CPU. This is the only edition that lets you can benefit from Fixed Term Licensing (FTL), a yearly subscription option offered as a low-cost alternative to permanent licensing. Being the entry-level charged edition (with a license that must be renewed annually), DB2 Express FTL is very well suited for users coming from Express-C. Other licensing methods are also available.

DB2 Workgroup Server Edition (WSE)

DB2 WSE is the perfect database solution for departmental, workgroup, or medium-sized business environments. It delivers all features present in DB2 Express Edition, and is the entry-level edition for DB2 pureScale functionality. DB2 WSE can be used on the following platforms:

  • Linux (except Linux on System z)
  • Windows
  • AIX
  • Solaris (SPARC and x64)
  • HP-UX and Itanium

The main advantage of DB2 WSE over Express edition is that it allows you to use much more CPU and RAM.
DB2 Workgroup Server Edition 10.1 use is restricted to 16 cores per server and 64 GB of RAM. If the pureScale feature is in use, these limits will apply to the entire cluster. But the really good news is that pureScale is provided at no additional charge with DB2 WSE. (We’ll look at the pureScale feature in more detail a little later.) It is important to note that installation requirements and platforms for DB2 pureScale are significantly different from those needed for regular DB2.

DB2 Enterprise Server Edition (ESE)

Ideal for high-performing, robust, on-demand enterprise solutions, DB2 ESE is designed to meet the data server needs of mid- to large-size businesses. It can be deployed on Linux, UNIX, and Windows servers of any size, from one to hundreds of processors, and on both physical and virtual servers.

DB2 ESE comes with all functionality of WSE, plus the following:

  • Connection Concentrator
    • Allows DB2 to handle workloads for tens of thousands of users without dedicating database server resources to each one. This feature is part of Workload Manager, which is available with DB2 Advanced Enterprise Server Edition (AESE).
  • Query Tuner
    • A utility that provides recommendations and analysis for tuning a single query.
  • Materialized Query Tables (MQTs)
    • Structures that enable complex query results to be stored in regular tables. You can refresh those tables periodically, so access to the results is greatly improved. MQTs are key to solving complex query performance problems.
  • Multidimensional Clustering (MDC) Tables
    • MDC provides an elegant method for clustering data in tables along multiple dimensions in a flexible, continuous, and automatic way. It can significantly improve query performance, and can significantly reduce the overhead of data maintenance, such as reorganization and index maintenance operations during insert, update, and delete operations. MDC is primarily intended for data warehousing and large database environments, but it can also be used in online transaction processing (OLTP) environments.
  • Multi-temperature data management
    • This is a new feature of DB2 10.1 where, based on storage groups you define, DB2 distributes data among different device types, thereby placing data that is accessed frequently or constantly on faster storage, and data that is accessed infrequently or almost not at all on slower (and cheaper) storage devices.
  • Query parallelism
    • This feature provides the ability to break a query in multiple parts and process them in parallel using intra-partition parallelism, thereby improving performance.
  • Table partitioning
    • A data organization scheme in which table data is divided across multiple storage objects called data partitions or ranges according to values in one or more table columns. Each data partition is stored separately and can reside in different table spaces, in the same table space, or a combination of the two.

You can extend some of DB2 ESE’s functionality with the purchase of any of the following additional extension packages:

  • DB2 Storage Optimization feature: Enables use of Adaptive Compression and classic row compression in DB2 ESE (offered free of charge in DB2 AESE).
  • DB2 pureScale functionality: Described later in the tutorial

The pricing model for DB2 ESE is available as Processor Value Unit (PVU) or per authorized user. Thus, as long as the license is dimensioned accordingly, a DB2 ESE database will be entitled to use all the resources available in a server.

DB2 Advanced Enterprise Server Edition (AESE)

The most complete DB2 edition available, DB2 AESE is a powerful database management solution that offers all of the functionality present with DB2 ESE, adding (at no additional cost) the following features and benefits:

  • Adaptive Compression and classic row (static) compression
    • Allows compression of data in tables using classic row compression (where data is compressed using table-level dictionaries) and extends it by compressing data dynamically using page-level dictionaries. Temporary tables are compressed when DB2 deems it necessary, and indexes used in compressed tables are compressed by default. (DB2 ESE users must purchase the DB2 Storage Optimization feature to obtain this functionality.)
  • Workload Manager
    • A utility that monitors the behavior of applications that run against a database, and changes the behavior depending on the rules that you specify in a configuration file. (For example, you can control system resources so that no one department or service class overwhelms a database server with requests.)
  • Continuous data ingest
    • A high-speed client-side DB2 utility that streams data from files or named pipes into DB2 target tables, usually to populate data warehouse databases.
  • Federation with DB2 for Linux, UNIX, and Windows and Oracle data sources
    • A feature that allows you to query tables that reside in an Oracle database, as if they were local tables in your DB2 database.
  • IBM InfoSphere Data Architect
    • A complete solution for designing, modeling, discovering, relating, and standardizing data assets. You can use it for data modeling, transformation, and DDL generation, and to build, debug, and manage database objects such as SQL stored procedures and functions.
  • IBM InfoSphere Optim Configuration Manager
    • Provides advice on how to change database configurations, and stores states and changes in a repository. IBM InfoSphere Optim Configuration Manager makes it possible to compare current and historical data, helping to understand and resolve problems related to configuration changes.
  • IBM InfoSphere Optim Performance Manager Extended Edition
    • Allows you to identify, diagnose, solve, and prevent performance problems in DB2 products and in associated applications including Java and DB2 Call Level Interface (CLI) applications.
  • IBM InfoSphere Optim pureQuery Runtime
    • Lets you deploy advanced pureQuery applications that use static SQL for a wide range of benefits. It bridges the gap between data and Java technology by harnessing the power of SQL within an easy-to-use Java data access platform. It also increases security of Java applications helping to prevent threats like SQL injection.
  • IBM InfoSphere Optim Query Workload Tuner
    • Enables all tuning features in both the IBM Data Studio full client and the IBM Data Studio administration client.

As with DB2 ESE, pureScale functionality is priced separately. Otherwise, the pricing model for DB2 AESE is the same as that used for DB2 ESE.

DB2 Database Enterprise Developer Edition (DEDE)

DB2 DEDE is a special offering tailored to provide developers almost all of the features that are present in other DB2 editions. With DB2 DEDE, a single application developer is able to design, build, and prototype applications, using advanced DB2 features, without having to spend unnecessary money for DB2 licenses. As the name implies, this edition is only meant to be used for development purposes and cannot be used in production.

DB2 DEDE further extends DB2 AESE by adding the following:

  • Database Partitioning Feature (DPF)
  • DB2 Connect functionality
  • DB2 pureScale functionality

You must acquire a separate user license for each Authorized User of this product; PVU licensing is not available.

DB2 clients

No matter what edition you have running on your database server, any applications you use will have to connect to it. Such connection is done through DB2 clients and drivers — and there's always a right client or driver suited for every application type. By knowing what each client product offers, you'll be able to choose the one that best matches your application needs.

The IBM Data Server client and driver types available are as follows:

  • IBM Data Server Driver Package
  • IBM Data Server Driver for JDBC and SQLJ
  • IBM Data Server Driver for ODBC and CLI
  • IBM Data Server Runtime Client
  • IBM Data Server Client

Figure 2 shows all features and capabilities that are offered with each DB2 client and driver available.

Figure 2. What’s inside each DB2 client and driver
DB2 clients and drivers and what's inside them

For example, if you need to connect a Windows application to a DB2 database using ODBC, you will need the IBM Data Server Client Driver for ODBC/CLI. Or, if you want to administer DB2 using the Command Line Processor (CLP), the IBM Data Server Runtime Client should be enough.

Every DB2 Database Server edition includes the DB2 Data Server Client — which means that when working on the server where your database resides, you have all the connectivity to your databases that is available; this connectivity can also be used to establish a connection to databases residing on remote servers. (The only exemption is DB2 Express-C, which does not include Replication Center.)
It's also possible to connect to DB2 for System i or DB2 for System z databases by registering a DB2 Connect Personal Edition license to any DB2 client or driver. In fact, this is the simplest way of connecting an application to a DB2 database that resides on a mainframe.

DB2 data server drivers and clients are available for free — no additional licensing is required to use them, and the drivers can be embedded within applications. Thus, it is possible to redistribute DB2 drivers. For more details, access the IBM DB2 10.1 Information Center from the link in the Resources section.

DB2 Connect

As mentioned earlier, DB2 is supported on both distributed systems and on platforms such as z/OS and IBM i — in fact, DB2 for Linux, UNIX, and Windows originally came from the mainframe.

But to be able to connect to such databases, just downloading and installing a DB2 client is not enough. Use of mainframe databases from DB2 clients requires another product known as DB2 Connect. If you want to connect directly to a database running on DB2 for z/OS or DB2 for IBM i, you must register a DB2 Connect license key within your DB2 client. As mentioned earlier, DB2 Connect licenses are applicable to both clients and drivers.

It is also possible to have a DB2 Connect Server, which acts as a gateway between DB2 on a mainframe and DB2 for Linux, UNIX, and Windows so that regular DB2 for Linux, UNIX, and Windows clients are able to connect to mainframe databases through a gateway. Figure 3 illustrates how DB2 Connect works in a client and server environment.

Figure 3. How DB2 Connect works
How clients and drivers can connect to mainframe databases through DB2 Connect

It is important to understand that DB2 Connect and DB2 clients are different products that are used for different purposes. (The word "Connect" sometimes leads to confusion). Every time you see a reference to DB2 Connect, you must remember that, although it can be used to connect to databases on DB2 for LUW, this product is mainly used to connect regular DB2 clients and drivers (or even database servers) to mainframe databases (that is, to DB2 for z/OS or DB2 for i databases). I've seen people download DB2 Connect and use it as regular client — which now you know is not what it is intended for.

2 of 10 | Previous | Next


Zone=Information Management
TutorialTitle=DB2 10.1 fundamentals certification exam 610 prep, Part 1: Planning