DB2 9 Fundamentals: Certification Study Guide

DB2, formally called DATABASE 2, was born on MVS in 1983. In 1987, DB2 arrived on the Personal Computer as the Database Manager in OS/2 1.3 Extended Edition; a year later, it emerged as SQL/400 for IBM's new AS/400 server. By 1992, DB2 had become a stand-alone product on OS/2 (it now had the name DB2/2), and in 1993, DB2 appeared on AIX; this port prompted another name change, and DB2/2 became DB2 for Common Servers. New editions of DB2 were introduced on HP-UX and Solaris in 1994, on Windows in 1995, and on Linux in 1999. Along the way the name changed again, and DB2 for Common Servers became DB2 Universal Database.

DB2 9 is the latest release of IBM's popular data management software for distributed systems, and with this release comes yet another name change. Like previous versions, DB2 runs on a wide variety of platforms (AIX, HP-UX, Linux, Solaris, Windows, i5/OS, and z/OS), and several editions are available-each of which has been designed to meet a specific business need. These editions, along with an extensive suite of add-on products that provide additional storage capability and advanced connectivity, are collectively known as the DB2 Family. The editions that make up the heart of the DB2 Family are:

• DB2 Everyplace

• DB2 Express

• DB2 Express-C

• DB2 Personal Edition

• DB2 Workgroup Server Edition

• DB2 Enterprise Server Edition

• DB2 Data Warehouse Edition

• DB2 Personal Developer's Edition

• DB2 Universal Developer's Edition

• DB2 Enterprise Developer's Edition

• DB2 for i5/OS

• DB2 for z/OS

All of the DB2 Family editions available, along with the type of computing environment each edition is primarily designed for, can be seen in Figure 2-1.

Figure 2-1: DB2 Family Editions.

DB2 Everyplace-Database Edition and Enterprise Edition

DB2 Everyplace is a small-footprint (approximately 350 KB) relational database and a high-performance data synchronization solution that allows enterprise applications and data to be extended to mobile devices such as personal digital assistants (PDAs), handheld personal computers (HPCs), and smart phones. DB2 Everyplace can be used as a local, stand-alone database that resides on a mobile device or to access information stored on remote servers whenever a connection is available. DB2 Everyplace can also be embedded directly into mobile devices to increase their functionality.

DB2 Everyplace is available in two editions: DB2 Everyplace Database Edition and DB2 Everyplace Enterprise Edition. DB2 Everyplace Database Edition is designed to be used by Independent Software Vendors (ISVs) and application developers who wish to create powerful mobile and embedded applications that work with DB2 Everyplace database data stored directly on a mobile device. DB2 Everyplace Enterprise Edition is designed to be a complete datacentric mobile synchronization server. This secure server is responsible for managing the distribution and synchronization of data between mobile device users and back-end data sources such as DB2 9, Informix, Oracle, Sybase, and Microsoft SQL Server. (Synchronization is performed whenever a connection to the back-end data source is detected.)

DB2 Express Edition

DB2 Express Edition (or DB2 Express) is an entry-level data server that is designed to be used on microcomputers that have up to two CPUs (a dual-core processor is treated as a single CPU), have up to 4 GB of memory, and are running a supported version of Linux, Solaris, or Windows. DB2 Express contains a rich feature set that will meet the needs of most deployments; for workloads or environments that require additional functionality, add-on features are available for an additional licensing fee. Add-on features available for DB2 Express include:

• pureXML: The DB2 pureXML™ Feature provides simple, efficient access to XML data while providing the same levels of security, integrity, and resiliency that are available for relational data. DB2 9 stores XML data in a hierarchical format that naturally reflects the structure of XML. This storage approach, along with innovative XML indexing techniques, allows DB2 to manage XML data efficiently while eliminating the complex and time-consuming parsing that is typically required to store XML data in a relational database.

• High Availability: The DB2 High Availability Feature provides 24 x 7 availability to a DB2 data server. Three packages that constitute this feature are High Availability Disaster Recovery (HADR), Online Reorganization, and IBM Tivoli System Automation for Multiplatforms (TSA MP). HADR allows failover to a standby system in the event that a software or hardware failure occurs on the primary system. Online Reorganization reconstructs the rows in a table to eliminate fragmentation and compacts information for better performance while permitting uninterrupted access to table data. TSA MP provides high availability by automating the control of IT resources such as processes, file systems, and IP addresses.

• Workload Management: The DB2 Workload Management Feature leverages the Connection Concentrator in conjunction with either Query Patroller (QP) or the DB2 Governor to provide a more proactive, fail-safe workload environment. Connection Concentrator enables fail-safe operation and load balancing of a workload. Query Patroller is a powerful query workload management tool that proactively and dynamically controls submission and execution of queries to manage DB2 data server workloads better. The DB2 Governor monitors and changes the behavior of applications that run against the DB2 data server, depending on rules that you specify in the governor configuration file. For example, if an application is using too much of a particular resource, a rule might specify that the priority of the application is to be changed or that the application is to be forced to disconnect from the database.

• Performance Optimization: DB2 Performance Optimization combines the functionality of three modules to provide performance optimizations for complex queries, data clustering for On-Line Analytical Processing (OLAP) applications, and high performance for machines with multiple processors. This feature consists of materialized query tables (MQTs), multidimensional clustering (MDC), and Query Parallelism. MQTs are tables whose definition is based on the result of a query; queries against an MQT can return results faster than would be possible if a normal table were used. MDC provides an elegant method for clustering data in tables along multiple dimensions. Query Parallelism allows for the simultaneous processing of parts of a single query by multiple processors, dramatically improving overall performance on multi-processor machines.

• DB2 Homogenous Federation Feature: The DB2 Homogeneous Federation Feature provides the ability to manage and access remote DB2 and Informix data servers as if they were local tables; it also allows applications to access diverse types of data (mainframe and distributed, public and private) as if it were in a base table, regardless of where it physically resides. Homogeneous federation meets the needs of customers who require unified access to data managed by multiple data servers.

DB2 Express-C

DB2 Express-C is a no-charge entry-level data server that is designed to be used on microcomputers that have up to two CPUs, have up to 4 GB of memory, and are running a supported version of Linux or Windows. DB2 Express-C is intended to be used for evaluation purposes and for the development/deployment of C, C++, Java, .NET, PHP, and XQuery applications. Essentially, DB2 Express-C is a subset of DB2 Express Edition with one exception: Where pureXML is available as an add-on feature for DB2 Express, it is included with DB2 Express-C. Features found in DB2 Express that are not available with DB2 Express-C include:

• Spatial Extender Client and samples

• Microsoft Cluster Server support

• Informix Data Source support

• Replication Data Capture (both SQL Replication and Replication with MQ Server)

• DB2 Web Tools

• Global Secure Toolkit

• APPC and NetBios support

Additionally, the add-on features that are available for DB2 Express cannot be added to DB2 Express-C. If you wish to use any of those features, you must first purchase a license for DB2 Express Edition and then purchase the desired add-on features. Fortunately, DB2 Express-C can be seamlessly upgraded to DB2 Express without requiring modifications to existing databases or database applications.

DB2 Personal Edition

DB2 Personal Edition (PE) is a single-user, full-function relational database management system that is ideal for desktop or laptop-based deployments. Databases under its control can be managed remotely, making it the perfect edition for occasionally connected or remote office implementations that do not require multi-user capability.

With DB2 Personal Edition a user can create, manipulate, and administer any number of local databases; however, each database created must reside on a storage medium that is managed by the PC on which the DB2 software has been installed. Remote clients cannot access databases that are under DB2 Personal Edition's control, but PCs running DB2 Personal Edition can act as remote clients and access data stored on other DB2 servers.

DB2 Personal Edition can be deployed on any Personal Computer (PC) that is running Linux or Windows; however, you must acquire a separate license for each user who will have access to a database under its control.

DB2 Workgroup Server Edition

DB2 Workgroup Server Edition (WSE) is a multi-user, full-function client/server database management system designed to be used on microcomputers that have up to four CPUs, have up to 16 GB of memory, and are running any of the following operating systems:

• AIX 5.2 (64-bit)

• AIX 5.3 (64-bit)

• HP-UX 11iv2

• Solaris 10 (64-bit)

• Red Hat Enterprise Linux (RHEL) 4 (32-bit and 64-bit)

• SUSE Enterprise Linux Server (SLES) 9 (32-bit and 64-bit)

• SUSE Enterprise Linux Server (SLES) 10 (32-bit and 64-bit)

• Novell Enterprise Server 9 (32-bit)

• Windows 2000

• Windows NT

• Windows XP Professional Edition

• Windows XP Professional x64 Edition

• Windows 2003 Standard Edition (32-bit and 64-bit)

• Windows 2003 Enterprise Edition (32-bit and 64-bit)

• Windows 2003 Datacenter Edition (32-bit and 64-bit)

DB2 Workgroup Server Edition includes all of the features of DB2 Express, while providing scalability to larger servers. As with DB2 Express, the following add-on features are available for an additional licensing fee:

• pureXML

• High Availability

• Workload Management

• Performance Optimization

• DB2 Homogeneous Federation Feature

With or without these features, DB2 Workgroup Server Edition is the ideal data server for small- to medium-sized business environments and departments that comprise a small number of internal users.

DB2 Enterprise Server Edition

DB2 Enterprise Server Edition (ESE) is a multi-user, full-function, Web-enabled client/server database management system that easily scales to handle high-volume transaction processing, multi-terabyte data warehouses, and mission-critical applications from such vendors as SAP. It is designed to be used on any size of server (from one to hundreds of CPUs) that is running any of the following operating systems:

• AIX 5.2 (64-bit)

• AIX 5.3 (64-bit)

• HP-UX 11iv2

• Solaris 10 (64-bit)

• Red Hat Enterprise Linux (RHEL) 4 (32-bit and 64-bit)

• SUSE Enterprise Linux Server (SLES) 9 (32-bit and 64-bit)

• SUSE Enterprise Linux Server (SLES) 10 (32-bit and 64-bit)

• Novell Enterprise Server 9 (32-bit)

• Windows XP Professional Edition

• Windows XP Professional x64 Edition

• Windows 2003 Standard Edition (32-bit and 64-bit)

• Windows 2003 Enterprise Edition (32-bit and 64-bit)

• Windows 2003 Datacenter Edition (32-bit and 64-bit)

DB2 Enterprise Server Edition includes all of the functionality found in DB2 Workgroup Edition, plus features that are needed to handle high user loads and provide 24x7x365 availability, including:

• High Availability Disaster Recovery (HADR)

• Table (range) partitioning

• Online reorganization

• Materialized Query Tables

• Multi-dimensional data clustering

• Full intra-query parallelism

• Connection Concentrator

• The DB2 Governor

• Tivoli System Automation for Multiplatforms (TSA MP)

DB2 Enterprise Server Edition also comes packaged with a tightly integrated connectivity product (DB2 Connect) that allows it to participate in heterogeneous networks using the Distributed Relational Database Architecture (DRDA) protocol. This allows up to five users to interact with iSeries and zSeries-based DB2 databases, Informix Dynamic Server (IDS) databases, and non-database host resources such as CICS, VSAM, and IMS. (If more user connectivity is needed, you can purchase additional DB2 Connect user entitlements.)

In addition to many of the add-on features available for DB2 Express and DB2 Workgroup Edition, the following add-on features are available for DB2 Enterprise Server Edition (again, for an additional licensing fee):

• Storage Optimization: The DB2 Storage Optimization Feature gives you the ability to compress data on disk in order to decrease disk space and storage infrastructure requirements. Since disk storage systems can often be the most expensive components of a database solution, even a small reduction in the storage subsystem can result in substantial cost savings for the entire database solution.

• Advanced Access Control: The Advanced Access Control Feature lets you decide exactly who has write access and who has read access to individual rows and/or columns in one or more tables. Label-Based Access Control (LBAC), the package that provides this feature, controls access to table objects by attaching security labels to them. Users attempting to access an object must have been granted an appropriate security label. When an object protected by a security policy is accessed, DB2 applies the appropriate access rules to determine whether access should be granted or not. When there's a match, access is permitted; without a match, access is denied.

• Database Partitioning: The DB2 Database Partitioning Feature (DPF) provides the ability to manage very large databases better by dividing them into multiple partitions and storing those partitions across a cluster of multiple inexpensive servers. DPF processes complex queries more efficiently, giving better performance with smaller systems, while providing an option to easily expand to a cluster of servers as the organization's data needs grow. DPF can also improve data availability by reducing the time required for, as well as the impact of, standard maintenance activities.

• Geodetic Data Management: The Geodetic Data Management feature provides the ability to store, access, manage, and analyze geographic location-based, round earth information for weather, defense, intelligence, or natural resource applications. By treating the Earth as a continuous spherical coordinate system rather than a flat map, the Geodetic Data Management feature enables you to manage and analyze spatial information with accuracies in distance and area as well as to develop applications that require geographical location analysis.

• Real-Time Insight: The DB2 Real-Time Insight feature is used to manage incoming data with message rates of tens to hundreds of thousands of messages per second. Such messages can come from multiple data streams and may be aggregated, filtered, and enriched in real time before being stored or forwarded to other servers. The DB2 Real-Time Insight feature is powered by the DB2 Data Stream Engine, which can be used to load large volumes of data with high throughput and low latency, store and publish data from multiple feeds (up to 100), and make data (both real-time and historical) available to queries through standard SQL, C-API, and Java API interfaces.

Designed for mid-size to large businesses, DB2 Enterprise Server Edition is the ideal foundation for building multi-terabyte data warehouses, high-availability, high-volume OLTP systems, or Web-based Business Intelligence (BI) solutions.

DB2 Data Warehouse Server Edition

DB2 Data Warehouse Edition (DWE) is the top-of-the-line DB2 Edition for dynamic data warehousing. It is designed for today's data center environments, where OLTP and decision support are merged into integrated information management systems. This integrated platform for developing warehouse-based analytics includes core components for warehouse construction and administration as well as Web-based applications with embedded data mining and multi-dimensional Online Analytical Processing (OLAP).

The core engine for DB2 Data Warehouse Edition is DB2 Enterprise Server Edition and the DB2 Data Partitioning Feature. (DB2 Enterprise Server Edition includes data warehouse enhancing features such as materialized query tables, the starburst optimizer, and multi-dimensional clusters; the DB2 Data Partitioning Feature provides increased parallelism to aid in performing administration tasks, as well as scalability to support very large databases and complex workloads.) Along with the core engine, DB2 Data Warehouse Edition is comprised of the following components:

• DB2 DWE Design Studio: DB2 DWE Design Studio is a unified graphical development environment that can be used to build BI solutions. (DB2 DWE Design Studio is essentially an extension of Eclipse-based Rational Data Architect (RDA) modeling functions.) With DB2 DWE Design Studio, designers can connect to source and target databases, reverse-engineer physical data models, build DB2 SQL-based data flows and mining flows, set up OLAP cubes, and prepare applications for deployment to runtime systems.

• DB2 DWE SQL Warehousing Tool: The DB2 DWE SQL Warehousing Tool provides a way to solve integration problems in a DB2 data warehouse environment. The tool provides a metadata system and an integrated development environment (IDE) to create, edit, and manage logical flows of higher-level operations, and a code generation system that understands the flows and translates them into optimized SQL code for execution. Once the development of the flows is complete, the second part of the SQL Warehousing Tool comes into play: the packaging of the code generated and any associated artifacts into a data warehouse application that can be deployed into various target runtime systems.

• DB2 DWE Administration Console: The DB2 DWE Administration Console is a Web-based application that can be used to manage and monitor BI applications. Built upon the WebSphere Application Server, the DB2 DWE Administration Console uses Web clients to access and deploy data warehouse applications modeled and designed for DWE.

• DB2 DWE OLAP Acceleration: DB2 DWE OLAP Acceleration provides a simple, efficient method for creating specialized relational structures that add OLAP functionality to a DB2 data warehouse. (Administrators simply drag objects onto predefined layouts to add OLAP functionality; no extensive knowledge of OLAP is needed.) DB2 DWE OLAP Acceleration also accelerates OLAP queries by analyzing dimensional models and recommending aggregates that will improve OLAP performance. Administrators can use the DB2 DWE OLAP Acceleration feature to show summarized, graphical views of business activity. End users, in turn, can experience faster cube loads and receive the ability to drill down into the data to get more detail than that provided in a summary.

• DB2 DWE Data Mining and Visualization Features: DB2 DWE Data Mining and Visualization Features help you discover hidden relationships in your data, without requiring you to export the data to a special data mining computer or work with small samples of the data. The data mining process starts with historical data being gathered and put through a series of mathematical functions to derive business rules, which are then collected together to form a Model. Next, the Visualization feature provides data mining model analysis via a Java-based results browser. This is done to verify that the business rules derived are accurate. Finally, the verified business rules are applied to new data to determine the appropriate predicted outcome. This process of applying the business rules is called Scoring; Scoring in real time allows businesses to catch fraudulent records and defects faster.

• DB2 DWE Alphablox Analytics: DB2 Alphablox provides the ability to rapidly create custom Web-based applications that fit into the corporate infrastructure and reach a wide range of users, inside and outside the corporate firewall. Applications built with the Alphablox platform run in standard Web browsers, allowing real-time, highly customizable multi-dimensional analysis from a client computer. DB2 Alphablox is tightly integrated with DB2 DWE OLAP Acceleration, which provides common metadata and database optimization for Alphablox multi-dimensional analysis.

• DB2 Query Patroller: DB2 Query Patroller is a powerful query management system that can be used to control the flow of queries against a DB2 database dynamically. With the DB2 Query Patroller, an administrator can define separate query classes for queries of different sizes to share system resources better among queries; give queries submitted by certain users high priority so that they will run sooner, automatically put large queries on hold so that they can be canceled or scheduled to run during off-peak hours; and collect information about completed queries to determine trends and identify frequently used tables and indexes.

DB2 Personal Developer's Edition

DB2 Personal Developer's Edition contains both Linux and Windows versions of DB2 Personal Edition as well as the DB2 Extenders, DB2 Connect Personal Edition, and a software development toolkit (SDK) that can be used to develop applications that interact with databases that fall under DB2 Personal Edition's control. Using the tools provided with DB2 Personal Developer's Edition, an individual application developer can design, build, or prototype single-user desktop/laptop applications that interact with DB2 9 databases, using a wide variety of methods, including:

• Embedded Structured Query Language (SQL)

• IBM's Call Level Interface (CLI), which is comparable to Microsoft's Open Database Connectivity (ODBC) interface

• DB2 9's rich set of application programming interfaces (APIs)

• Java Database Connectivity (JDBC)

• SQLJ

• .NET

• PHP

• PYTHON

The toolkit provided with DB2 Personal Developer's Edition contains a set of libraries and header files for each programming language supported, a set of sample programs to help with your development efforts, and an SQL precompiler/binder, which is used to pre-process source code files containing embedded SQL so that they can be compiled and linked by a conventional compiler.

It is important to note that applications developed with the toolkit provided with DB2 Personal Developer's Edition can be run on any PC on which DB2 Personal Developer's Edition or DB2 Personal Edition has been installed. However, the DB2 software provided with this edition cannot be used for production systems.

DB2 Universal Developer's Edition

DB2 Universal Developer's Edition is designed to be used by application developers who wish to design, build, and prototype applications for deployment on any of the DB2 client or server platforms available. This comprehensive package contains versions of DB2 Everyplace, DB2 Express, DB2 Personal Edition, DB2 Workgroup Server Edition, and DB2 Enterprise Server Edition (along with all of the add-on features available) for each operating system supported. It also contains DB2 Extenders, DB2 Connect Personal Edition, DB2 Connect Enterprise Edition, and a software development toolkit (the same software development toolkit that comes with DB2 Personal Developer's Edition). This toolkit can be used to develop applications that utilize the latest DB2 9 technologies available.

As with DB2 Personal Developer's Edition, the DB2 software provided with DB2 Universal Developer's Edition cannot be used to create a production database system.

DB2 Enterprise Developer's Edition

DB2 Enterprise Developer's Edition is designed to be used by application developers who wish to design, build, and prototype applications for deployment on any of the IBM Information Management client or server platforms available. This comprehensive package contains everything found in the DB2 Universal Developer's Edition, along with versions of Informix Dynamic Server (IDS) Enterprise Edition, Cloudscape, and DB2 Connect Unlimited Edition for zSeries for each operating system supported.

As with DB2 Universal Developer's Edition, the DB2 software provided with DB2 Enterprise Developer's Edition cannot be used to create a production database system.

DB2 for i5/OS

DB2 for i5/OS is an advanced, 64-bit relational database management system that leverages the On-Demand capabilities of System i, such as Dynamic Logical Partitioning to respond quickly to changing workloads in order to ensure business continuity in a dynamic environment. Unlike other DB2 editions, DB2 for i5/OS is built directly into the operating system. As a result, Version/Release naming will differ because DB2 for i5/OS follows the i5/OS version/release numbering scheme, and not the DB2 for Linux, UNIX, and Windows version/release scheme. The current level of DB2 for i5/OS is Version 5 Release 4 (V5R4).

Because of the unique architecture of IBM's System i5 and the way the DB2 database engine has been tightly integrated with the operating system, many of the traditional database-specific administration requirements associated with other database management systems either aren't necessary with DB2 for i5/OS or are provided through various operating system facilities. For example, graphical tools used to administer a DB2 for i5/OS database are provided as part of the iSeries Navigator component of the OS, whereas DB2's Control Center is used to administer DB2 editions that run on Linux, UNIX, and Windows. With iSeries Navigator, you can perform the following tasks:

• Create and/or work with DB2 database objects (tables, views, indexes, user-defined functions, etc.)

• Define referential integrity constraints

• Reverse-engineer a database to produce SQL DDL from existing data objects

• Create and debug SQL stored procedures

• Manage database logging (Journaling)

• Analyze SQL/Query performance using Visual Explain

• Use the Database Navigator to view and work with database objects and any related objects

DB2 for i5/OS's cost-based query optimizer, unique single-level-store architecture, and database parallelism feature allow it to scale nearly linearly within an iSeries SMP configuration. And if additional functionality is needed, there are several utilities available (including utilities for data replication, parallel processing, and query management) that either can be added to the core database functionality or included in the System i Enterprise Edition bundle.

DB2 for z/OS

DB2 for z/OS is a multi-user, full-function database management system that has been designed specifically for z/OS, IBM's flagship mainframe operating system. For over four decades the IBM mainframe has been a leader in data and transaction serving; DB2 9 for z/OS builds on the value delivered by the IBM mainframe and provides features that include:

• Rich hybrid data server support for both relational and XML data, along with the necessary services to support both (pureXML)

• New data types (DECFLOAT, BIGINT, and VARBINARY)

• Native SQL procedural language

• Improved security with roles, trusted context, and new encryption functions

• Extensions of DB2 for z/OS V8 capabilities

• Enhancements to large-object support and performance

• Volume-based copy and recover

• Refinements to optimization

• QMF interface design changes that provide on demand access to data, reports, and interactive visual solutions via a Web browser

• Enablement for IBM System z Integrated Information Processors (zIIP)

DB2 9 for z/OS is designed to cut IT infrastructure costs significantly, streamline efforts to meet compliance obligations, and simplify data serving on the System z9 operating system.