DB2 9 Fundamentals: Certification Study Guide

Along with the DB2 editions that make up the core of the DB2 Family, several other products designed to expand and enhance the functionality and capabilities of DB2 are available. These products, which make up the remainder of the DB2 Family, are:

• DB2 Clients

• DB2 Connect

• DB2 Extenders

The following sections provide detailed descriptions of each of these products.

DB2 Clients

In order to create a DB2 client/server environment, you must have some type of client software installed on the workstation that will serve as the client before communications with a server can be established. Consequently, several of the DB2 editions available contain the software needed to set up and configure the following types of DB2 clients:

• DB2 Runtime Client

• DB2 Client

Each of these clients can be created (by installing the appropriate client software) on any number of workstations; however, the type of client you elect to create for a given workstation is often determined by the requirements of that workstation. For example, if a particular workstation will only be used to execute an application that interacts with a remote DB2 database stored on a server, it would need to be configured as a DB2 Runtime Client. This would be done by installing the DB2 Runtime Client software that is appropriate for the operating system being used on that workstation. Figure 2-2 shows how such a DB2 client/server environment might look.

Figure 2-2: Simple DB2 client/server environment.

DB2 Runtime Client

The DB2 Runtime Client provides workstations running a variety of operating systems with the ability to access remote DB2 databases. This client is a lightweight client (having a smaller deployment footprint compared to the full DB2 Client in terms of install image size and disk space required) that provides the following features/capabilities:

• Support to handle database connections and process SQL statements, XQuery expressions, and DB2 commands.

• Support for common database access interfaces, including JDBC, ADO.NET, OLE DB, ODBC, and the DB2 Call Level Interface (CLI). This includes drivers and the capability to define data sources. For example, installing a DB2 client on a Windows workstation may cause the DB2 ODBC driver to be installed and registered. Application developers and other users can then use the Windows ODBC Data Source Administrator tool to define DB2 data sources.

• Support for common network communication protocols, including TCP/IP and Named Pipe.

• Support for installing multiple copies of a client on the same computer. These copies can be the same or different versions of DB2.

• Support for Lightweight Directory Access Protocol (LDAP).

• Versions that run on both 32-bit and 64-bit operating systems.

• License terms that allow free redistribution of the DB2 Runtime Client with any applications you develop.

In addition, the DB2 Runtime Client provided for Windows can be packaged with an application to provide database connectivity for that application; the Windows DB2 Runtime Client is available in the form of Windows Installer merge modules that enable you to include the DB2 Runtime Client DLL files in any application install package you create.

DB2 Client

Like the DB2 Runtime Client, the DB2 Client provides workstations running a variety of operating systems with the ability to access remote DB2 databases. However, where the DB2 Runtime Client provides basic connectivity capabilities, the DB2 Client delivers the same functionality as the DB2 Runtime Client, plus the tools needed to perform client/server configuration, database administration, and application development. Features/capabilities provided by the DB2 Client include:

• The Configuration Assistant to assist with cataloging remote databases and configuring database servers.

• The Control Center and other graphical tools for database administration. It is important to note that these tools are only available for 32-bit and 64-bit versions of Linux and Windows.

• First Steps for new users.

• Application development tools.

The application development tools provided with the DB2 Client are the same as those provided with the various DB2 Developer's editions: a set of libraries and header files for each programming language supported, a set of sample programs and tutorials to assist with your development efforts, and an SQL precompiler/binder, which is used to process source code files containing embedded SQL so that they can be compiled and linked by a conventional high-level programming language compiler.

DB2 Connect

For information technology professionals who have made DB2 on iSeries and zSeries servers the cornerstone of their business, DB2 Connect provides a robust, highly scalable communications infrastructure for connecting Linux, UNIX, Windows, and mobile applications to data stored in z/OS (mainframe) and i5/OS (minicomputer) databases. DB2 Connect can be used seamlessly with some of the more common standard interfaces for database access, including ODBC, OLE DB, JDBC, SQLJ, ADO, ADO.NET, RDO, DB2 CLI, and Embedded SQL. DB2 Connect (and DB2) also delivers full support for the Microsoft .NET programming model, along with its respective IDE, Visual Studio.NET. DB2 Connect can also be used to access nondatabase resources such as CICS, WebSphere MQ, IMS, or VSAM.

DB2 Connect is an add-on product that must be purchased separately unless DB2 Enterprise Server Edition, which provides a limited-use version, is installed. DB2 Connect is available in several editions, and each is designed to address specific data access and usage needs. The DB2 Connect editions that are available are:

• DB2 Connect Personal Edition: DB2 Connect Personal Edition is intended to be used by a single user on a single workstation who needs connection to any number of mainframe and/or minicomputer database servers.

• DB2 Connect Enterprise Edition: DB2 Connect Enterprise Edition is intended to be used by multiple users on multiple workstations who need connection to any number of mainframe and/or minicomputer database servers. DB2 Connect Enterprise Edition is priced using the Standard server/user model that is used to price DB2 Enterprise Edition. Therefore, this edition is not suitable in environments where multi-tier client/server applications or Web-based applications are used, because determining the number of concurrent users in such environments is not practical and licensing every registered user may be cost-prohibitive.

• DB2 Connect Application Server Edition: DB2 Connect Application Server Edition provides the same functionality as DB2 Connect Enterprise Edition, however, license charges are based on the number of processors being used by Web or application servers that are connecting to mainframe and/or minicomputer database servers. This edition is designed to provide a cheaper alternative to DB2 Connect Unlimited Edition for environments in which one or more multi-tier applications will access enterprise data with regular frequency, and the growth of the number of users of these applications is expected to be slow or minimal over time.

• DB2 Connect Unlimited Edition: DB2 Connect Unlimited Edition provides the same functionality as DB2 Connect Enterprise Edition, however, license charges are based on Millions of Service Units (MSUs) per hour. This edition is designed to provide an attractive pricing alternative for environments where a large amount of access to enterprise data is needed today or will be needed sometime in the future.

DB2 Extenders

In most relational database systems, including DB2, data is stored according to its data type. Therefore, in order to store a wide variety of data, DB2 contains a rich set of built-in data types, along with a set of functions that are designed to work with each data type provided. DB2 also allows users to create their own data types (known as user-defined types) and supporting functions (known as user-defined functions) to handle data that does not map directly to one of the built-in data types available. Building on this capability, the developers of DB2 created several sets of user-defined data types and user-defined functions for the sole purpose of managing specific kinds of data. Collectively, these sets of user-defined data types and functions are referred to as extenders, because they extend the basic functionality and capabilities of a DB2 database. Currently, six different extender products are available. They are:

• DB2 Audio, Video, and Image (AVI) Extender

• DB2 Text Extender

• DB2 Net Search Extender

• DB2 XML Extender

• DB2 Spatial Extender

• DB2 Geodetic Extender

• DB2 Audio, Video, and Image Extender: As the name implies, the DB2 Audio, Video, and Image Extender contains a set of user-defined data types and functions that allow a DB2 database to store and manipulate nontraditional data such as audio clips, movies, and pictures. The data types and functions that are provided by the DB2 Audio, Video, and Image Extender can be used in SQL statements just like any of the built-in data types and functions available. And because SQL can be used to construct multi-data-type queries, this extender provides a lot of flexibility when searching for information. For example, a query could be written to locate a particular movie by searching for its description, the date it was recorded, or its total playing time. Additionally, the Query By Image Content (QBIC) capability provided with this extender can be used to locate images that have a particular color combination or that have colors and/or textures that are similar to those of another image.

• DB2 Text Extender: The DB2 Text Extender contains a set of user-defined data types that can store complex text documents in a DB2 database and a set of user-defined functions that can extract key information from such documents, regardless of where they are stored (text documents can be stored directly in a DB2 database or in a file system that is accessible to the DB2 Database Manager). This extender's strength comes from IBM's powerful linguistic search and text-mining technology; this technology allows users to construct queries that will search through any kind of text document, including most word processing documents, for:

  • A specific word

  • A specific phrase

  • A particular word sequence

  • Word variations (such as plural forms of a word or the word in a different tense)

  • Synonyms of a particular word

  • Similar-sounding words

  • Words that have a similar spelling

  • Words that have a particular pattern (for example, all words that begin with the characters "data")

• DB2 Net Search Extender: The DB2 Net Search Extender provides application developers with a way to integrate the search functionality provided by the DB2 Text Extender into their applications. Unlike the DB2 Text Extender, the DB2 Net Search Extender does not provide linguistic processing support. However, it does provide better query performance and scalability by means of its caching and optimization techniques. For this reason, the DB2 Net Search Extender is well suited for high-end e-business applications where search performance on large indexes can be critical and the ability to scale the processing of concurrent queries is needed. Key features the DB2 Net Search Extender provides include:

  • The ability to create multiple indexes on a single column (indexing proceeds without acquiring row-level locks)

  • The ability to create indexes across multiple processors

  • The ability to search for a particular word or phrase

  • The ability to search for words that have a similar spelling

  • The ability to perform wildcard searches (for example, search for all words that begin with the characters "net")

  • The ability to control how search results are sorted

  • The ability to limit the number of search results returned

  • The ability to search for tags or sections (with or without using Boolean operations)

• DB2 XML Extender: The DB2 XML Extender contains a set of user-defined data types and functions that can be used to store XML documents in a DB2 database (as character data or in external files) and to manipulate such documents, regardless of where they are stored. The DB2 XML Extender can be used to decompose ("shred") XML elements from a document and store them in columns and tables; it can also compose (create) new XML documents from existing character and numerical data or previously shredded XML documents. And because the DB2 XML Extender is compatible with the DB2 Net Search Extender, the powerful search capabilities provided by the DB2 Net Search Extender can be used to quickly locate information stored in one or more sections within a set of XML documents. (Retrieval functions let you retrieve complete documents or individual elements within a document.)

• DB2 Spatial Extender: Traditionally, geospatial data has been managed by specialized Geographic Information Systems (GISs) that, because of their design, have been unable to integrate their spatial data with business data stored in other relational database management systems or data sources. Shortly after DB2 Universal Database, Version 5.0 was released, IBM, together with Environmental Systems Research Institute (ESRI), a leading manufacturer of spatial database systems, created a set of user-defined data types for describing spatial data (for example points, lines, and polygons) and a set of user-defined functions to query spatial objects (for example to find area, endpoints, and intersects). This set of user-defined data types and functions make up the DB2 Spatial Extender.

  At the basic level, the DB2 Spatial Extender allows you to store spatial data in a DB2 database. With this capability, you can generate, analyze, and exploit spatial information about geographic features, such as the locations of office buildings or the size of a flood zone, and present it in a three-dimensional format. The DB2 Spatial Extender also enables you to add another element of intelligence to your database by integrating spatial information with business data (text and numbers).

• DB2 Geodetic Extender: Although many organizations rely on traditional two-dimensional map-based technology (which the DB2 Spatial Extender is designed to support), some need to treat the world as round rather than flat; a round Earth is paramount for calculations and visualizations in such disciplines as military command/control and asset management, meteorology and oceanography (scientific, government, or commercial), and satellite imagery. The DB2 Geodetic Extender contains a set of user-defined data types and functions that treat the Earth like a globe rather than a flat map (it can construct a virtual globe at any scale), thus making it easier to develop applications for business intelligence and e-government that require geographical location analysis.

  The DB2 Geodetic Extender is best used for global data sets and applications. It has the capability to manage geospatial information referenced by latitude and longitude coordinates and support global spatial queries without the limitations inherent in two-dimensional map projections. To handle objects defined on the Earth's surface with precision, the DB2 Geodetic Extender uses a latitude/longitude coordinate system on an ellipsoidal earth model-or geodetic datum-rather than a planar, x- and y-coordinate system. This avoids the distortions, inaccuracies, and imprecision inevitably introduced by planar projections.

  The Geodetic Extender provides a cost-effective solution for global, spherical coordinate (latitude/longitude)-based problems, because it models a spherical problem with a spherical solution.

A Word About XML Data

XML is a simple, very flexible text format derived from Standard Generalized Markup Language (SGML, ISO 8879). Originally designed to meet the challenges of large-scale electronic publishing, XML is playing an increasingly important role in the exchange of a wide variety of data on the Web and elsewhere. Why? Because XML provides a neutral, flexible way to exchange data between different devices, systems, and applications; data is maintained in a self-describing format that accommodates a wide variety of ever-evolving business needs.

One approach to storing XML data in a relational database has been to "shred" or decompose XML documents and store their contents across multiple columns in one or more tables. (This is the functionality provided by the DB2 XML Extender.) The shredding of XML documents enables users to leverage their existing SQL programming skills, as well as popular query and reporting tools, to work directly with selected portions of the "converted" XML data. This approach is ideal if the XML data being stored is tabular in nature. However, the cost associated with decomposing XML data often depends on the structure of the underlying XML document. XML documents are composed of a hierarchical set of entities, and many XML documents contain heavily nested parent/child relationships and/or irregular structures. Shredding such documents may require a large number of tables, some of which may need to have values generated for foreign keys in order to capture the relationships and ordering that is inherent in the original documents. Moreover, querying a "shredded" document can require complex SQL statements that contain many joins.

Another popular method is to use character and binary large object (CLOB and BLOB) data types to store the entire contents of an XML file in a single column of a row within a table. With this approach, the overhead required to break a document into pieces and store the pieces in various columns of one or more tables is eliminated. Additionally, complex joins aren't needed to reconstruct the original XML document, because it was never decomposed for storage. However, the use of large objects-character or binary-to store XML documents has its drawbacks as well. Searching and retrieving a subset of an XML document can be resource intensive. And when performing updates, the entire document must be replaced, even if only a small portion of the original document has been changed. This can result in unacceptably high processing costs, particularly if the XML document is large.

DB2 9 is the first IBM implementation of a "hybrid" or multi-structured database management system. In addition to supporting the traditional tabular, relational data model, DB2 9 supports the native hierarchical data model found in XML documents and messages. With DB2 9, XML documents are stored in tables that contain one or more columns that are based on a new XML data type. (Tables created with XML data types may also contain columns with traditional SQL data types, including numeric data, character strings, and date/time/timestamp data.) This "pure" support for XML includes new storage techniques for efficient management of hierarchical structures inherent in XML documents, new indexing technology to speed up retrieval of subsets of XML documents, new capabilities for validating XML data and managing changing XML schemas, new query language support (including native support for XQuery as well as new SQL/XML enhancements), new query optimization techniques, integration with popular application programming interfaces (APIs), and extensions to popular database utilities. The result is a single DBMS platform that offers the benefits of a relational database environment and a pure XML data store.