Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

DB2 9 for Developers

DB2 9 for Developers

5.0 1
by Philip K. Gunning

See All Formats & Editions

DB2 9 is a major release including totally new features, most notably the tight integration of the new DB2 9 XML native data type into the DB2 9 data server. If you're a DB2 developer or database administrator facing the task of implementing DB2 9, this book needs to be on your desk. In it, recognized DB2 guru and top consultant Philip K. Gunning shows how IBM has


DB2 9 is a major release including totally new features, most notably the tight integration of the new DB2 9 XML native data type into the DB2 9 data server. If you're a DB2 developer or database administrator facing the task of implementing DB2 9, this book needs to be on your desk. In it, recognized DB2 guru and top consultant Philip K. Gunning shows how IBM has integrated native XML into the DB2 engine and gives developers and DBAs a detailed roadmap for implementing and exploiting the new XML capabilities.

First, Gunning recaps the basics of XML and explains why XML is so important in today's business environment. Support for standard industry XML schemas is covered. Next, Gunning shows developers how to develop DB2 XML applications, describes the best way to design an XML database, and reveals the optimum way to tune the DB2 XML hybrid database. Because the book is written by an "in-the-trenches" consultant, readers will find it full of practical advice.

Also covered are practical details on the enhancements and features of the new release and the process for implementing them, along with in-depth examples and useful scripts. The new DB2 9 table partitioning and data compression are highlighted, and DB2 optimization of SQL and XML is discussed in detail. Gunning covers XML enhancements to DB2 utilities, such as LOAD and EXPORT, and provides recommendations. He's even included performance considerations and tuning suggestions to help application developers and DBAs develop and maintain high-performance databases with DB2 9.

DB2 9 for Developers goes beyond the manuals and certification guides and teaches not only the "why" but the "how" of an expertly designed, smoothlyimplemented DB2 database. This is a book for DB2 practitioners by the DB2 master practitioner himself.

With DB2 9 for Developers, you will: Discover all the new DB2 9 features, including the DB2 XML hybrid engine, XQuery, XPath, the XDM data model, and more, Learn how to develop top-performing DB2 XML applications using XQuery and XPath, Understand when to use an XML database design versus a relational database design, Receive insight, examples, and recommendations from one of the top DB2 consultants in the industry.

About the Author:
Phil Gunning is the principal of Gunning Technology Solutions

Product Details

Mc Press
Publication date:
Sold by:
Barnes & Noble
File size:
8 MB

Related Subjects

Read an Excerpt

DB2 9 for Developers

By Philip K. Gunning

MC Press

Copyright © 2008 Philip K. Gunning
All rights reserved.
ISBN: 978-1-58347-684-0


DB2 9 Product Overview

DB2 9 for Linux, UNIX, and Windows (LUW) comes in several editions, or product offerings. IBM developed these different editions to meet the needs of small, medium, and large businesses. There are offerings for developers, workgroups/departments, and enterprises, as well as for embedded applications and very large databases.

Foremost among the enhancements in DB2 9 is the introduction of native support for XML data across the entire DB2 product line. Performance and scalability enhancements are also integrated into all editions, giving developers, database administrators (DBAs), and companies added flexibility in choosing the right DB2 solution.

In this chapter, we review the key features and benefits of each edition. A familiarity with the different offerings will help you choose or recommend the DB2 product that best meets your business objectives. In Chapter 2, we'll look more closely at the new features and enhancements available in DB2 9.

DB2 Express-C

DB2 Express-C is a no-charge, entry-level, hybrid data server available via Web download or on the DB2 Discovery Kit CD. This DB2 product is especially suited for application developers and can be used with the Starter Toolkit for DB2 Ruby on Rails and the enhanced DB2 PHP Data Object (PDO) for PHP5. DB2 Express-C is available for the following platforms.

You can download DB2 Express-C at http://www-306.ibm.com/software/data/db2/express/download.html. The download page also provides a link to the DB2 Express-C online community.

DB2 Express

IBM designed the DB2 Express Edition to be embedded in applications that require a relational database. By packaging the database with the application, independent software vendors (ISVs) can make it easier for their customers to install vendor software packages that contain an integrated DB2 database. DB2 Express 9 contains all core DB2 capabilities and is easy to install and use. It provides all the data integrity, backup, and recovery capabilities usually found in an industrial-strength database.

DB2 Express makes maximum use of DB2 autonomics (self-management) and workload management. It provides tight integration with application development tools and enables developers to construct enterprise applications quickly. All these capabilities make DB2 Express ideal for ISVs and for small and medium businesses (SMBs). DB2 Express Edition does not include some advanced features of DB2 9:

• DB2 High Availability Feature

>> High Availability Disaster Recovery (HADR)
>> Online Reorganization
>> IBM Tivoli System Automation for Multiplatforms (SAMP)

• DB2 Performance Optimization Feature

>> Multidimensional Clustering (MDC)
>> Query Parallelism

• DB2 pureXML Feature

• DB2 Storage Optimization Feature

>> Backup Compression
>> Row Compression

• DB2 Workload Management Feature

>> Connection Concentrator
>> Query Patroller (QP)
>> DB2 Governor

• IBM Homogeneous Federation Feature

Other than the Storage Optimization Feature, all these features are available as additional priced options for DB2 Express.

DB2 Workgroup Server Edition

DB2 Workgroup Server Edition (WSE) is designed for small- to medium -volume online transaction processing (OLTP) and Web-based databases. This widely used edition helps companies minimize software license costs, keeping them in line with business and data requirements. IBM offers DB2 WSE on a per-processor or a per-authorized-user model. You can use this edition on servers with up to four processors and 16 GB of memory.

Many of my clients have started out using WSE for medium-volume OLTP and Web databases. This edition is an excellent entry point for companies that are getting started with DB2 or are porting to DB2 from other databases. Some of my clients have stayed on WSE for several years; others have moved to DB2 Enterprise Server Edition (ESE, described later) as they required additional capability.

When the time comes, moving from WSE to ESE is easy. Best of all, you can purchase many enterprise features for WSE as your processing requirements evolve. The following features are available as additional priced options for DB2 WSE (as well as for DB2 Express):

• DB2 High Availability Feature
• DB2 Performance Optimization Feature
• DB2 pureXML Feature
• DB2 Workload Management Feature
• IBM Homogeneous Federation Feature

DB2 Personal Edition

DB2 Personal Edition (PE) is available for single-user environments. This fully functional DB2 product features built-in replication and is ideal for desktop deployments and occasionally connected laptop users. DB2 PE can be managed remotely, providing an advantage for companies that need to support a database at a remote location. A typical deployment of DB2 PE might be as part of a sales force automation application or a customized insurance agent solution.

The following features and capabilities are not included in DB2 PE:

• Online Reorganization
• DB2 Performance Optimization Feature
• DB2 pureXML Feature
• DB2 Workload Management Feature
• Row Compression
• IBM Homogeneous Federation Feature

DB2 Enterprise Server Edition

DB2 Enterprise Server Edition (ESE) is a high-performance, scalable, complete database solution for medium to large businesses. In use today by Fortune 500 companies worldwide, DB2 ESE can be deployed on servers containing from one to hundreds of processors. It is ideal for data warehouses, OLTP systems, and Web applications and for enterprise-wide database solutions such as customer relationship management (CRM), enterprise resource planning (ERP), business intelligence (BI), and supply chain management (SCM). If you know that your business requirements are going to demand more than four CPUs or will need access to more that 16 GB of memory, ESE is the entry point for you.

In a typical deployment, DB2 ESE might function as the back-end database to an e-commerce site, as an OLTP database in support of teller or branch operations, as a mixed hybrid database in an OLTP or complex query environment, or as a large data warehouse using the Database Partitioning Feature (DPF) option. DB2 ESE serves business-critical applications that require top performance and high availability in continuous-operation environments.

DB2 ESE is available on a per-user or a per-processor pricing model and includes the following capabilities:

• Connection Concentrator
• DB2 Governor
• IBM Tivoli SAMP
• Materialized Query Tables
• Multidimensional Clustering
• Online Reorganization
• Query Parallelism
• Table Partitioning (new in DB2 9)
• DB2 Advanced Access Control Feature
• DB2 Database Partitioning Feature
• DB2 Geodetic Management Feature
• DB2 pureXML Feature
• DB2 Storage Optimization Feature
• Row Compression
• Real-Time Insight

Database Enterprise Developer Edition

Database Enterprise Developer Edition is a low-cost DB2 solution that developers can use to prototype and construct applications for deployment on a wide variety of IBM Information Management platforms. This edition includes all the DB2 9 features, enabling developers to develop applications using the full breadth of new DB2 capabilities, and comes with the following software:

• DB2 WSE • DB2 ESE • Informix Dynamic Server (IDS) Enterprise Edition • Cloudscape • DB2 Connect Unlimited Edition for zSeries

Note: All DB2 9 products include the DB2 Net Search Extender and the DB2 Spatial Extender.

DB2 Developer Workbench

A key component of every edition of DB2 for LUW is DB2 Developer Workbench, a feature-rich application development tool that replaces the former DB2 Development Center. Based on the Eclipse framework, Developer Workbench is shipped in the same media pack as DB2 9 but requires a separate install. As with most IBM development software, you can also download Developer Workbench from the Web.

The workbench is the primary interface for developing applications for DB2 9, and it serves as a common interface for working with DB2 on System i and z/OS systems. You can also use the workbench with Apache Derby and IBM Cloudscape.

Developer Workbench contains many features that increase developer productivity. Using the workbench, you can

• browse, create, and edit database objects
• edit tables and columns
• migrate previous Development Center projects
• collaborate with other developers
• copy, move, and extract data
• build SQL and XQuery statements
• develop SQL, SQLJ, and XML applications
• develop and deploy stored procedures and user-defined functions

IBM provides extensive information and tutorials to help developers learn and use Developer Workbench so they can easily build and deploy DB2 applications.

Information Management Products

Although not part of DB2, three WebSphere products provide information integration, replication, and event publishing support for DB2 9 databases: WebSphere Federation Server, WebSphere Replication Server, and WebSphere Data Event Publisher.

WebSphere Federation Server

WebSphere Federation Server V9.1 provides seamless access to enterprise information, making it appear as though the data originates from a single source. This offering provides the following capabilities:

• Federated two-phase commit (which enables the updating of multiple distributed data sources simultaneously)

• A common view of data

• The ability to use federated stored procedures in a federated query

WebSphere Replication Server

WebSphere Replication Server V9.1 provides high-volume DB2 SQL and queue-based replication (Q-Replication) for the DB2 9 WSE and ESE offerings. Version 9.1 offers increased scalability and performance over previous releases. Administration has been improved, and the replication server features additional auditing and monitoring capability. Supported replication sources and targets include DB2, Informix Dynamic Server, Microsoft SQL Server, Oracle, Sybase Adaptive Server Enterprises, and Sybase SQL Server. Informix Extended Parallel Server and Teradata are supported as replication targets.

WebSphere Data Event Publisher

WebSphere Data Event Publisher V9.1 enables business integration by publishing changed data events that can feed updated data into critical applications such as master data management and data warehousing.


DB2 9's unparalleled support for XML data, and its integration of this support into the entire DB2 product line, enables companies to reach new levels of information integration. This chapter highlighted the benefits and features of each DB2 9 edition, giving you the background you need to choose the right solution for your business.


DB2 9 Enhancements

DB2 9 is a hybrid relational and XML data server that provides the ability to store both relational and XML data. DB2 is the first database to provide integrated support for a native XML data type, and it supports both SQL and XQuery using the same optimizer and database engine. Many in the database industry are comparing the significance of this fundamental change with the invention of the relational database.

XML has become the interchange and document format of choice across a broad range of industries. Until now, however, XML hasn't been tightly integrated into relational databases. DB2 9 introduces a new native XML data store that is fully integrated in the DB2 database engine, letting you access and manage XML data by leveraging DB2 functionality. DB2 9 lets you use SQL and XQuery in the same query, and it supports the indexing of XML data to enable rapid retrieval of XML documents.

Note: Starting with DB2 9, IBM uses the term "data server" to describe its database product. A data server provides software services for the secure and efficient management of structured information. DB2 9 is a hybrid relational and XML data server.

This chapter highlights the new features in DB2 9. Subsequent chapters provide additional implementation details and examples. Our discussion here breaks down the enhancements into the following categories:

• Native XML data store support

• Application development enhancements

>> Enhanced .NET integration
>> Developer Workbench
>> Java Database Connectivity (JDBC)

• Performance and scalability enhancements

>> Row compression
>> Table partitioning
>> Materialized query tables (MQTs)
>> Large record identifiers
>> Statistical views
>> Faster data loading capabilities

• Manageability enhancements

>> ALTER TABLE statement
>> Improved maintenance policies
>> Database administration

• Installation and fix pack enhancements

• Backup and recovery enhancements

• Security enhancements

• Problem determination and troubleshooting enhancements

Native XML Data Store Support

IBM has created a "paradigm shift" with the invention of the native XML data store. While other relational databases require XML data to be shredded or parsed to enable storage in a single column or over multiple columns, DB2 9 is unique in that it stores XML data using a native XML data type. This native type lets you store well-formed XML documents in their hierarchical forms within columns of a table.

The native XML data type also enables DB2 to store and retrieve XML data without any of the overhead associated with shredding. DB2 9 stores XML data in a hierarchical format using the XPath Data Model (XDM), and new DB2 9 XML index structures enable rapid retrieval of XML data. The native XML data type also preserves digital signatures.

IBM has integrated support for the XQuery language tightly into the hybrid engine. In DB2 9, you can invoke XQuery directly by calling XML functions or invoke it indirectly from inside an SQL query.

New tools can help you develop XML/SQL queries without having to be an expert on XQuery syntax. DB2 9 Developer Workbench (which I introduced in the previous chapter) is a new development environment based on the Eclipse framework. It contains support for building XML functions using the XML data type and for registering XML schema. XQuery Builder, part of Developer Workbench, is a graphical interface that enables you to create and test XML queries without the need to understand the XQuery language.

IBM has enhanced the DB2 Control Center in DB2 9 to support the new DB2 9 XML data types, letting you administer both relational and XML data from the Control Center. Enhanced DB2 Explain and Visual Explain facilities support SQL/XML functions and XQuery statements and enable rapid development and tuning of SQL/XML queries.

Application Development Enhancements

DB2 9 adds support for BINARY, VARBINARY, and DECFLOAT data types, providing increased application support and flexibility. The DB2 Call Level Interface (CLI) and DB2 .NET Data Provider now support these types as well.

In keeping with the tight integration within the DB2 family of products, the DB2 9 CLI, IBM DB2 Driver for JDBC, and SQLJ now support making trusted connections to DB2 V9.1 for z/OS. A trusted connection can acquire a special set of privileges that are unavailable to it outside the trusted context.

The DB2 Command Line Processor (CLP)'s 64 K limit for SQL statements has been removed. The new limit of 2 MB provides CLP compatibility with other DB2 tools.

Also new in DB2 9 is support for the TRIM scalar function, which lets you remove blanks or occurrences of a specified character from the end or beginning of a string expression.

Enhanced .NET Integration

The DB2 9 .NET Data Provider supports the Microsoft .NET Framework V2.0. Additional new features enable you to develop powerful .NET applications. With support for the System.Data.Common base classes, you can develop a generic .NET database application without referencing any data-provider-specific classes. This support enables use of the generic DB2Connection class from the DB2 .NET Data Provider. The provider supports use of the common base classes declared in the System.Data. Common namespace.

New DB2Types classes provide a way to represent DB2 database column values as individual nullable objects. You can also use these classes as parameters for Common Language Routine (CLR) stored procedures or user-defined functions. The new classes belong to the IBM.Data.DB2Types namespace.

The new DB2 9 .NET Data Provider contains a DB2ResultSet class that supports scrollable and updatable result sets. Also included is a new data paging capability that provides a method to fetch a specific set of rows and a bulk data copy facility that uses the DB2BulkCopy, DB2CopyColumnMapping, and DB2CopyColumnMappingCollection classes.

Last, a new DB2DataAdapter property, UpdateBatchSize, enables applications to determine the number of SQL statements to collect before issuing the statements as a batch to the DB2 9 database.

DB2 Developer Workbench

In DB2 9, the new DB2 Developer Workbench replaces the former DB2 Development Center. An integrated development environment (IDE) based on the Eclipse framework, Developer Workbench enables you to rapidly develop, test, and deploy SQL and XQuery applications, user -defined functions, and stored procedures. Developer Workbench provides the following new abilities:


Excerpted from DB2 9 for Developers by Philip K. Gunning. Copyright © 2008 Philip K. Gunning. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Meet the Author

Philip K. Gunning is the principal of Gunning Technology Solutions, which provides DB2 consulting to firms throughout North America and Europe, and is active in the International DB2 User Group where he serves on the Solutions Journal editorial board. He is the author of DB2 Universal Database Handbook for Windows, UNIX, and Linux. He lives in Sinking Springs, Pennsylvania.

Customer Reviews

Average Review:

Post to your social network


Most Helpful Customer Reviews

See all customer reviews

DB2 9 for Developers 5 out of 5 based on 0 ratings. 1 reviews.
Guest More than 1 year ago
I picked up this book on a trip to get caught up on the latest with DB2 9 and the chapters on XML and new features was just what I needed.