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
CHAPTER 1
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:
HADR
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
HADR
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.
Summary
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.
CHAPTER 2
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:
(Continues...)
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.