DB2 UDB for Windows, UNIX, and Linux

---

In this section we consider the particular characteristics of DB2 UDB on the various platforms.

Overview

DB2 Universal Database (UDB) is the IBM object-relational database solution for the UNIX, Linux, and the Windows operating environments. It is built on a solid foundation, bringing together a client and server database product with IBM's leadership in mission-critical relational database technology. The result is a highly scalable, highly extensible, very easy-to-use and manage database that can be trusted with your most critical database applications. DB2 UDB is available on the following platforms:

• Windows NT/2000/XP/Server 2003,

• Windows 95/98/ME

• Linux (on Intel)

• Linux (on 390)

• AIX

• HP-UX

• Sun Solaris

Key capabilities and benefits

We now consider the main capabilities and benefits of DB2 UDB.

Superior scalability

One of the major benefits of DB2 UDB is scalability. It can run on a laptop supporting a mobile worker, or it can run on a massively parallel machine supporting multiple terabytes of data and thousands of users, and of course, it can support various configurations of SMPs (symmetric multiprocessors) and clusters of SMPs in between.

It is important to note that this scalability includes the same object-relational function up and down the line. This makes DB2 UDB the perfect database platform for small to medium sized businesses that are growing; for large enterprises that need to deploy 2-tier or 3-tier applications from desktop to department to enterprise; and for ISVs and Business Partners who are servicing all of these customers.

You will probably not outgrow DB2 UDB as your applications and businesses grow. You will be able to add data and still maintain query performance; or add users and still maintain response time; or just add hardware to speed up your queries.

DB2 UDB's scalability features can be grouped into three major capabilities:

• Advanced parallel processing

• High-performance computing

• Efficient large database operations

Advanced parallel processing

DB2 Universal Database uses parallel processing to speed up both transaction processing (OLTP) and query processing (OLAP, data warehousing, data mining) or mixed workloads involving both. It supports both parallel transactions and parallel query on all major hardware architectures, including symmetric multiprocessors (SMP), clusters, and massively parallel processors (MPP):

• SMP parallel support

  UDB will automatically execute multiple transactions (SQL statements) in parallel by dispatching them to the multiple processors in an SMP machine. UDB can also automatically execute a single query (SQL statement) in parallel by breaking the query into subtasks and dispatching each subtask to a different processor. Furthermore, if the data for an SQL statement is spread across multiple disk locations, UDB can also use parallel I/O to execute the data retrieval in parallel.

• Cluster and MPP parallel support

  When the Database Partitioning feature is used, a UDB database can be spread across multiple servers in a cluster or multiple nodes on an MPP. UDB will automatically execute multiple transactions (SQL statements) in parallel by dispatching them to the multiple nodes. UDB can also automatically execute a single query (SQL statement) in parallel by breaking the query into subtasks and dispatching each subtask to a different node.

  The key to efficient parallel processing across nodes is intelligent partitioning and parallel optimization. UDB will automatically spread (partition) the data across the nodes such that the optimizer will "know" where each row is located, and will be able to dispatch the processing to the node where the data is located — minimizing the movement of data between nodes. This is known as a shared-nothing architecture and it is the best approach for ensuring scalability across multiple nodes, particularly when very large databases of 100s of gigabytes or multiple terabytes are involved.

High-performance computing

In addition to advanced parallel processing, UDB supports a number of important high-performance computing features that significantly enhance performance of both queries and transactions. The combination of these and other features make DB2 UDB particularly good at handling mixed workloads. These high-performance features include:

• 64-Bit memory support

  Support now exists for very large physical memory (64-bit memory support). DB2 exploits 64-bit systems and 32-bit systems capable of supporting greater than 4 GB of real memory. The buffer pool can be used to keep data available in this additional memory, thus reducing I/O, and significantly improving performance.

• Multiple buffer pools

  You can create multiple buffer pools of various sizes (number of pages) and assign table spaces to them using the CREATE BUFFERPOOL SQL statement. This provides the database administrator greater control of the availability of data in memory. For example, operational transaction tables can be given their own bufferpool so they would not have to compete with ad hoc queries for memory. This could help maintain OLTP performance while still providing good response to end-user queries.

• Asynchronous page cleaner

  The ability to offload buffer page writing operations from a task executing the SQL query to another task can significantly improve query response time. The asynchronous page cleaning tasks ensure that sufficient space exists in the database buffers for query data. This capability can eliminate the need for query to wait synchronously until modified pages are written from the buffer to disk in order to make room for query data.

• Sequential prefetch

  The performance of queries, which require a large amount of sequential disk I/O is significantly improved due to the overlapping of I/O and CPU processing. This is particularly useful for query processing.

• List prefetch

  The support for list prefetch improves the performance of queries that access data randomly or non-sequentially.

• Media-spanning through table spaces

  With UDB, database administrators can partition a database into parts called table spaces. When creating a table, the name of the base, index, and long table space can be specified. Using index and long table spaces to store indexes and LOB data respectively allows these structures to be kept separate from the rest of table data. This flexibility can be used to increase database performance and availability. A table space can be spread over one or more physical storage device, providing the media-spanning capability.

• Direct media access (raw device support)

  UDB has the ability to store data directly on a device, without incurring the overhead of using a file system, for improved performance and data integrity. The administrator can choose to define a table space that uses devices, or uses the native file system.

• Big block reads

  This capability enables the reading of several disk pages using a single I/O operation, reducing the CPU usage, and in the process, improving query response time.

• REORG utility

  UDB includes a REORG utility that will re-sequence rows in a clustered order and eliminate fragmenting due to updates and deletes. This can significantly enhance performance of tables with a high rate of change. A REORGCHK utility is also provided to determine if tables are in need of reorganization.

• Connection concentrator

  The connection concentrator allows a physical database agent to be used by multiple logical agents, each representing a client connection. The restriction on the number of client connections is now solely based on the transaction load and the machine's ability to handle the workload. The connection concentrator uses scheduler technology to run individual transactions for multiple clients over the same physical database connection.

• Multidimensional clustering (MDC)

  Multidimensional clustering (MDC) provides an elegant method for flexible, continuous, and automatic clustering of data along multiple dimensions. This results in significant improvement in the performance of queries, as well as significant reduction in the overhead of data maintenance operations, such as reorganization, and index maintenance operations during insert, update, and delete operations. Multidimensional clustering is primarily intended for data warehousing and large database environments, and it can also be used in online transaction processing (OLTP) environments.

  MDC enables a table to be physically clustered on more than one key (or dimension) simultaneously. Prior to the introduction of MDC, DB2 only supported single-dimensional clustering of data, through clustering indexes. Using a clustering index, DB2 maintains the physical order of data on pages in the key order of the index, as records are inserted and updated in the table. Clustering indexes greatly improve the performance of range queries that have predicates containing one or more keys of the clustering index. With good clustering, only a portion of the table needs to be accessed, and when the pages are sequential, more efficient prefetching can be performed.

  With MDC, these benefits are extended to more than one dimension, or clustering key. In terms of query performance, range queries involving any combination of specified dimensions of the table will benefit from clustering. Not only will these queries access only those pages having records with the correct dimension values, these qualifying pages will be grouped by extents. Furthermore, although a table with a clustering index can become unclustered over time as space fills up in the table, an MDC table is able to maintain its clustering over all dimensions automatically and continuously, thus eliminating the need to reorganize the table to restore the physical order of the data.

Efficient large database operations

Scalability requires much more than performing transactions and queries faster using parallel processing or other high-performance features. It also requires that maintenance operations like data loading, backup and recovery, and indexing are scalable such that large data volumes can be efficiently managed. DB2 UDB provides the following in support of large database operations:

• High speed LOAD utility: DB2 UDB includes a high-speed LOAD utility that significantly increases the speed of doing data loads while ensuring recoverability of the data being loaded. It can take input from a number of file formats or an SQL SELECT statement, and directly builds table space pages without the overhead of logging or SQL INSERT processing. It can also build indexes and collect statistics during the load. The LOAD utility can significantly reduce the amount of time it may take refresh or add data to a data warehouse.

• SMP support in utilities: DB2 UDB not only exploits SMP architectures in transaction and query processing, but in its utility operations as well:

  • Parallel data loading: Loading data is a heavily CPU-intensive task. The LOAD utility takes advantage of multiple processors for tasks such as parsing and formatting data. Also, the LOAD utility can use parallel I/O servers to write the data to the containers in parallel. The LOAD utility has been shown to scale linearly as the number of processors is increased.

  • Parallel index creation: DB2 exploits I/O parallelism and CPU parallelism when creating an index. This helps to speed up index creation when a CREATE INDEX command is issued during restart (if an index is marked invalid) and during REORG processing.

  • Parallel backup and restore: Backing up and restoring data are heavy I/O-bound tasks. DB2 exploits I/O parallelism and CPU parallelism when performing backups and restores. DB2 takes advantage of multiple CPUs by assigning buffer manipulators among the CPUs. You can also backup to or restore multiple devices (such as tapes) in parallel.

• Cluster/MPP support in utilities: DB2 Universal Database with the Database Partitioning Feature can also exploit cluster and MPP architectures in its utility operations:

  • Parallel split: As mentioned above, the key to MPP parallelism is intelligent partitioning. The db2split utility is provided to split data into partitions prior to loading. It can run in parallel on multiple nodes thus significantly speeding up the overall load process.

  • Parallel data loading: Once the data is split, the LOAD utility can run in parallel on each node. If each node is an SMP, it takes advantage of multiple processors for tasks such as parsing and formatting data, and can use parallel I/O servers to write the data to the containers in parallel.

  • Autoloader: This simplifies the process of splitting and loading multiple partitions of a table.

  • Parallel index creation: The CREATE INDEX command runs in parallel on each node.

  • Parallel backup and restore: Backup and restore operations can be run in parallel across nodes. DB2 exploits I/O parallelism and CPU parallelism when performing backups and restores. DB2 takes advantage of multiple CPUs by assigning buffer manipulators among the CPUs.

These advanced parallel processing, high-performance computing, and efficient large database operation features make DB2 Universal Database the most scalable, object-relational, and Web-enabled database in the industry. If you start out on a small NT server, it is easy to upgrade your hardware or move your data to a larger UNIX server if you need more capacity. If you are building a large data warehouse, you can start out on a single node and easily add additional nodes to support more data, more users, or improved response times. Whether your needs today are large or small, you can rest assured that you will not get boxed in if you grow.

Multimedia extensibility

Another major benefit of DB2 UDB is extensibility. By extensibility, we mean the ability to store and manage not only traditional relational tables with characters and numbers, but also multimedia, complex objects such as documents, images, audio, video, spatial information, time-series data, etc. This may also include industry-specific objects such as X-rays, fingerprints, engineering drawings, insurance claim forms, etc.

The key to this capability is what is called object-relational technology. DB2 UDB's object-relational capabilities allow you to add your own data types and business functions to the database — effectively tailoring the database to fit your specific business or application requirements. IBM was first to deliver this capability in DB2 Common Server Version 2 in 1995. DB2 UDB's implementation is more advanced and more robust than any other "Universal Server" on the market, and it has been thoroughly field tested making it very reliable.

DB2 UDB's extensibility features can be grouped into the following categories:

• Universal Data

• Business rules

• Advanced SQL

• DB2 Extenders™

These extended object-relational capabilities open up a whole new world of potential applications for you, and a very productive and efficient way to deliver them.

Universal Database (object-relational)

DB2 Universal Database supports the following key object-relational features. These are implemented according to SQL3 standards so they represent an open, non-proprietary approach.

• User-Defined Types (UDTs): UDTs allow users to define new data types, which are represented in the database using built-in types. For example, a user can define two currency types CDOLLAR for Canadian Dollars and USDOLLAR for U.S. Dollars. These types are distinct in the sense that they should not be directly compared to one another or to the decimal type, although the decimal type might be chosen for the internal representation of both UDTs in the database. UDTs, like built-in types, can be used for columns of tables as well as parameters of functions, including User-Defined Functions (UDFs). For example, a user can define a data type such as ANGLE (which varies between 1 and 360) and a set of UDFs to act on it, such as SINE, COSINE, and TANGENT.

• User-Defined Functions (UDFs): UDFs allow queries to contain powerful computation and search predicates to filter irrelevant data close to the source of the data. UDFs enable users to provide a set of functions that interpret a UDT, thereby defining the semantics of the UDT. Support for UDFs enable the creation of function libraries, which can be developed by IBM, third party vendors, or customers and then integrated directly in the database. The SQL optimizer takes into account the semantics and execution cost of UDFs, thus treating User Defined Functions exactly as the built-in functions such as SUBSTR and LENGTH. Applications may be developed using different application language environments, such as C, C++, COBOL, and FORTRAN, while sharing a set of UDTs and UDFs through SQL.

• Large Objects (LOBs): LOBs allow users to store very large (multi-gigabyte) binary or text objects in a database. Binary LOBs can be used to store multimedia objects such as documents, video, images, and voice. LOBs can also be used to store small structures whose semantics are defined by UDTs and UDFs. A powerful set of built-in functions, such as search, substring, and concatenation are supported for LOBs. Additional functions can be defined at any time by means of UDFs. More than one LOB column can be defined on a table.

• User-Defined Table Functions (Table UDFs): SQL users can now access data not stored in the relational format, and makes full use of the query capabilities of the relational database. It is often difficult, if not impossible to subject data from non-relational data stores to relational operations. User-defined table functions are an extension to SQL which solves this problem. A table function is an external user-defined function that constructs a derived table. The program for the function can access data from the various sources and format it into a tabular form returned from the table function. Once the table function is written, it can be used in the FROM clause of queries. Table functions can be used not only to subject this external data to the power of SQL, but also to capture external data permanently into relational tables.

• OLE User-Defined Functions (UDFs): Object Linking and Embedding (OLE) automation is part of Microsoft's architecture on Windows platforms. It enables applications (OLE automation servers) to expose objects and methods that can then be accessed by other applications (OLE automation controllers) including: Lotus SmartSuite® (R), Lotus Approach, MS Office/Backoffice, SAP R/3, QMF (TM) HPO/Shuttle, many Web tools, many Visual Basic applications, and others. DB2 provides OLE automation controller support for accessing OLE automation server data through UDFs. These external UDFs allow data from OLE automation servers to be returned to SQL queries through DB2.

Business rules

Business rules enable the definition of complex integrity rules, which can be used to enforce the correctness of a database. They enhance the power of the other object-oriented features. They augment object-code-only libraries (whose methods cannot be modified) to support additional specific object attributes and constraint checking. They also help to enforce inter-object data integrity rules. The key features supporting business rules include:

• Defaults: Defaults allow you to set default values for columns that are not assigned values explicitly in INSERT statements.

• Generated Columns: Generated columns let you specify an SQL function that will be used to calculate a value for a column that is not assigned a value in an INSERT statement. This might be used to generate a unique number for each new row, or to assign a tax code based on other columns such as salary or marital status.

• Check Constraints: Check constraints are generally used to enforce business rules not covered by key uniqueness or referential integrity constraints. For example, a user may define constraints on the EMPLOYEE table, which specify that the job of an employee can only be one of Sales, Mgr, or Clerk, and that every employee that has been with the company for more than 8 years must have a salary of more than $40,000.

• Referential Integrity: Referential integrity (RI) lets you define required relationships between and within tables. Referential constraints are declared when a table is defined, and ensure the consistency of data values between related columns in different tables. DB2 maintains these relationships, so you do not need to program this function into applications.

• Triggers: Triggers may be used to maintain complex cross-table business rules, automatically generate a value for a newly inserted row, read from other tables for cross-referencing purposes, write to other tables for audit-trail purposes and provide an alert capability, by adding a trigger, which invokes a User-Defined Function (for example, to send an electronic mail message). A user might define a trigger that increments the number of employees each time a new row is inserted into the EMPLOYEE table.

• Stored procedures: DB2 clients can call stored procedures that execute on the DB2 server. Stored procedures push processing back from the client to the server, and thus provide support for thinner clients and for execution of the logic at the server-side. They also cut back significantly on network traffic, resulting in better response time. DB2 UDB stored procedures are somewhat unique in that they are not written in some proprietary language, but can be written in Java, C, C++, etc. They can also be written in the stored procedure language defined by the database standards committee. Stored procedures can return multiple rows and multiple result sets for greater efficiency.

Advanced SQL

Advanced SQL queries allow you to encapsulate a great deal of processing logic into a single, non-procedural statement. Examples of this include:

• Recursive SQL queries: Changes to the SQL optimizer will enable DB2 server databases to support not only the bills-of-material queries, but also the more powerful form of recursive queries such as path expression queries. Examples of queries that become possible with support for recursion are:

  • Bills-of-material queries where a user wants to return subparts of parts, and subparts of the subparts, and so on.

  • Path expression queries, where a user wants to calculate the lowest cost plane fares on multi-hop routes. For example, the following query can be formulated using recursive SQL: Return all possible flights from Toronto to Perth without making a stopover in London or Chicago, and with no more than a three-plane changes.

  • The optimizer is capable of making sophisticated transformations and optimizations for recursive queries and non-recursive queries, resulting in selection of better access plans for improved performance.

• Outer join support: Left, right, and full outer join operations are supported using standard SQL92 syntax; that is, a join operation whose result includes unmatched rows in addition to matching rows.

DB2 Extenders

The DB2 Extenders build on the object-relational infrastructure of DB2. Each extender is a package of predefined UDTs, UDFs, triggers, constraints, and stored procedures that satisfies a specific application domain. With the Extenders, the user can store text documents, images, videos, and audio clips in DB2 tables by adding columns of the new data types provided by the extenders. The actual data can be stored inside the table or outside in external files.

These new data types also have attributes that describe aspects of their internal structures, such as "language" and "format" for text data. Each extender provides the appropriate functions for creating, updating, deleting, and searching through data stored in its data types. The user can now include these new data types and functions in SQL statements for integrated content searching across all types of data.

The following four DB2 Extenders are provided as part of the DB2 Developer's Editions.

DB2 Text Extender

Text Extender adds the power of full-text retrieval to SQL queries by making use of features available in DB2 that let you store unstructured text documents of up to 2 GB in databases. Text Extender offers DB2 users and application programmers a fast, versatile, and intelligent method of searching through such text documents. Text Extender's strength lies in its ability to search through many thousands of large text documents at high speed, finding not only what you directly ask for, but also word variations and synonyms. You are not restricted to searching only in text documents stored in DB2 databases, you can also search in text documents stored in files.

Text Extender can access various kinds of text documents, including word-processing documents in their original native form, and offers a rich set of retrieval capabilities including word, phrase, wild card, and proximity searching using Boolean logic ("and/or" logic).

At the heart of Text Extender is IBM's high-performance, advanced search engine. It allows your applications to access and retrieve text documents in a variety of ways. Your applications can:

• Search for documents that contain specific text, synonyms of a word or phrase, or sought-for words in proximity, such as in the same sentence or paragraph

• Do wild card searches, using front, middle, and end masking for word and character masking

• Search for documents of various languages in various document formats

• Make a fuzzy search for words having a similar spelling as the search term. This is useful for finding words even when they are misspelled

• Make a free-text search in which the search argument is expressed in natural language

• Search for the names of people, places, or organizations

• Search for words that sound like the search term

You can integrate your text search with business data queries. For example, you can code an SQL query in an application to search for text documents that are created by a specific author, within a range of dates, and that contain a particular word or phrase. Using the Text Extender programming interface, you can also allow your application users to browse the documents. By integrating full-text search into DB2's SELECT queries, you have a powerful retrieval function that combines attribute and full-text search.

DB2 Image Extender

The DB2 Image Extender defines a data type and functions for images using DB2 UDB's built-in support for user-defined types and user-defined functions. It also exploits DB2 UDB's support for large objects of up to 2 GB and uses DB2 UDB triggers to automatically store and maintain attribute information for images. With the DB2 Image Extender, you can:

• Import and export images: You can import and export images and image attributes into and out of a database. When an image is imported, the DB2 Image Extender stores and maintains image attributes such as size in bytes, format, height, width, and number of colors.

• Control access to images: This can be done with the same level of protection as traditional business data.

• Convert the format of images: You have the flexibility of importing or exporting an image in its source format or converting the format of the image when importing or exporting. You can also convert an image in other ways, such as rotating it or changing its scale.

• Secure and recover images: Images and their attributes stored in a DB2 database are afforded the same security and recovery protection as traditional business data.

• Query images: This can be done based on related business data or by image attributes. Images can be searched based on data that the user maintains, such as a name, number, or description; or by data that the DB2 Image Extender maintains, such as the format of the image or its distribution of colors.

• Generate and display image thumbnails and full images: A thumbnail is a miniature version of an image. When an image is imported into a database, the DB2 Image Extender creates and stores a thumbnail of the image. You can use the DB2 Image Extender to retrieve a thumbnail or a full-size image, and then use the DB2 Image Extender to invoke a favorite browser to display the thumbnail or full-size image.

The DB2 Image Extender supports a wide variety of image formats, such as GIF (including animated GIFs), JPEG, BMP, and TIFF.

DB2 Audio Extender

The DB2 Audio Extender defines a new data type and functions for audio using DB2 UDB's built-in support for user-defined types and user-defined functions. It also exploits DB2 UDB's support for large objects of up to 2 GB and uses DB2 UDB triggers to automatically store and maintain attribute information for audio objects.

With the DB2 Audio Extender, you can:

• Import and export audio clips: You can import and export audio clips and audio attributes into and out of a database. When an audio clip is imported, the DB2 Audio Extender stores and maintains audio attributes such as number of audio channels, transfer time, and sampling rate.

• Secure and recover audio data: Audio clips and their attributes stored in a DB2 database are afforded the same security and recovery protection as traditional business data.

• Query audio clips: This can be done based on related business data or by audio attributes. You can search for audio clips based on data that you maintain, such as a name, number, or description; or by data that the DB2 Audio Extender maintains, such as the format of the audio or the date and time that it was last updated.

• Play audio clips: You can use the DB2 Audio Extender to retrieve an audio clip, and then use the DB2 Audio Extender to invoke a favorite audio browser to play the audio clip.

The DB2 Audio Extender supports a variety of audio file formats, such as WAVE, MIDI, MPEG1, and AU, and can work with different file-based audio servers.

DB2 Video Extender

The DB2 Video Extender defines a new data type and functions for video using DB2 UDB's built-in support for user-defined types and user-defined functions. It also exploits DB2 UDB's support for large objects of up to 2 GB and uses DB2 UDB triggers to automatically store and maintain attribute information for video objects.

With the DB2 Video Extender, users can:

• Import and export video clips: You can import and export video clips and their attributes into and out of a database. When a video clip is imported, the DB2 Video Extender stores and maintains video attributes such as the frame rate, compression format, and number of video tracks.

• Secure and recover video data: Video clips and their attributes stored in a DB2 database are afforded the same security and recovery protection as traditional business data.

• Query video clips: This can be done based on related business data or by audio attributes. You can search for video clips based on data that you maintain, such as a name, number, or description; or by data that the DB2 Video Extender maintains, such as the format of the video or the date and time that it was last updated.

• Build storyboards: You can use the DB2 Video Extender to automatically segment a video clip into shots based on scene changes. A scene change is a point in a video clip where there is a significant difference between two successive frames. When the DB2 Video Extender detects a scene change, it records data for the associated shot, including representative frames. This makes it easy to build summaries of a video, called storyboards.

• Play video clips: You can use the DB2 Video Extender to retrieve a video clip, and then use the DB2 Video Extender to invoke a favorite video browser to play the video clip.

The DB2 Video Extender supports a variety of video file formats, such as MPEG1, MPEG2, AVI, and QuickTime, and can work with different file-based video servers.

DB2 XML Extender

The IBM DB2 XML Extender implements the IBM overall XML technical strategy. That is, to be the world leader in e-business. The XML Extender as a part of the IBM business to business(B2B) server offering, makes the DB2 server to be XML enabled. It provides new technology for emerging market of early XML adoption and evaluation. The Extender offers the capability of XML storage and data interchange. By storage, the XML Extender provides mechanism of storing and retrieving XML documents in DB2, and searching the content of XML with high performance. By data interchange, the XML Extender provides mapping between new and existing DB2 relational tables and XML formatted documents. Together, the XML Extender allows DB2 customers to do e-business anywhere, enabling XML with their B2B and business to consumer(B2C) applications.

With the content of your structured XML documents in the DB2 database, you can combine structured XML information with your traditional relational data. Based on the application, you can choose whether to store entire XML documents in DB2 as a nontraditional distinct data type, or you want to map the XML content as traditional data in relational tables. You can decide how structured XML documents be stored or created through the Document Access Definition (DAD).

For non-traditional XML data types, the XML Extender adds the power to search rich data types of XML element or attribute values, in addition to the structural-text search provided by the Text Extender. For traditional SQL data which are either decomposed from incoming XML documents, or in existing relational tables to be used to create outgoing XML documents, the XML extender provides a custom mapping mechanism to allow the transformation between XML documents and relational data.

Net.Data®, which connects Web applications to DB2, now has built in XML exploitation. This allows you to generate XML tags as output from your Net.Data macro, instead of manually entering the tags. You can also specify an XML style sheet (XSL) to be used to format and display the generated Output.

DB2 Spatial Extender

DB2 supports the notion of Spatially Enabled SQL Queries. You can now integrate spatial data (locations through coordinates) with normal SQL data. The combination of these two technologies gives users access to new types of queries that they could not previous run. The DB2 Spatial Extender is used to achieve this functionality. The Spatial extender will store and index the spatial data (coordinate information) and allow specific spatial queries against this data.

The following features are available with the DB2 UDB Spatial Extender:

• Infrastructure to model and manipulate spatial data: spatial types, catalog views, spatial functions, support for spatial reference systems and spatial "layers"

• Object types and operations that conform with OpenGIS Consortium (OGC) and ISO SQLMM standards

• A multi-level grid index implementation modelled after the ESRI proprietary grid index technology

• Stored procedure API to create and manage spatial resources

• Geocoding is performed either incrementally or in batch mode. The Spatial Extender supports not only a default geocoder (shipped with the product), but also geocoders supplied by users and vendors.

• Spatial Extender supports DB2 UDB spatial data. But you can use DB2 UDB to create joins between this data and data outside of DB2 UDB. You can do this if you install Spatial Extender on the server that includes a federated database, and if you then enable this database for spatial operations. Once this database is so enabled, you can create joins between its spatial data and data in federated data sources. Currently, federated data sources are relational only (Oracle and DB2 family)

• Superior ease of use and ease of administration capabilities relative to other relational implementations

• Import, Export, and Geocode operations

• A sample application to help customers to set up and use Spatial Extender

DB2 Net.Search

The DB2 Net Search Extender contains a DB2 stored procedure that adds the power of fast full-text retrieval to DB2 applications. It offers application programmers a variety of search functions, such as fuzzy search, stemming, Boolean operators, and section search. Searching using DB2 Net.Search Extender can be particularly advantageous in the Internet, when search performance on large indexes and scalability according to concurrent queries are important factors.

The Net.Search extender in DB2 UDB allows very fast text search with:

• Word or phrase searches

• Stemmed searches

• Boolean operations

• Fuzzy searches

• Wildcard searches

• Fielded searches

• Presorted searches

• Cursor capability

• High speed indexing

• Creation of multiple indexes at the same time

• Only read access to the user data is required

Net.Search uses main memory database technology to reach high performance levels. Load tests resulted in 90 million Web site hits per day without degradation in the search performance.

DB2 Universal Database's support for universal data, business rules, advanced SQL, and multimedia data objects and methods makes it among the most extensible object-relational database in the industry. Among the key differentiators is that this is built according to SQL standards, and that it is built into the optimizer. This makes the implementation more open and allows for better performance than other implementations.

Web enablement

Customers need to be able to make the data stored in their DB2 database systems accessible to employees of the company, and selectively to their suppliers and customers through private network (intranet) and public network (Internet) applications. DB2 Universal Database is fully integrated with Web technology so that data can be easily accessed from the Internet or from your intranet with complete security. The following facilities included with UDB allow you to Web-enable your database applications right out of the box:

• DB2 Java support

• Net.Data: A Webserver Database Gateway

DB2 Java support

Java applications are very attractive to customers who would like to develop a single application running on any operating system, and who would like to reduce the cost of application distribution and maintenance.

Java Database Connectivity (JDBC) is a database access interface for Java applications that are being delivered with DB2. The DB2 JDBC database interface supports this API.

DB2 provides native support for Java at client workstations and DB2 servers. Java is supported on the client workstations in three ways:

• A Java application uses the DB2 Client Application Enabler, which must be installed on the client workstation to communicate with the DB2 server. Customers with existing DB2 client/server configurations can now use Java as a database application development tool.

• Java applets allow applications to be developed that access DB2 servers without requiring DB2 Client Application Enabler code to be installed on client workstations. Java applets can be automatically downloaded to the client workstation at application invocation time.

• The new type 4 JDBC driver is a two-tier pure Java JDBC driver, which allows a Java client to communicate directly with DB2 servers through the DRDA® protocol. This driver is designed to replace the type 3 driver. You should migrate applets that use the type 3 JDBC driver to the type 4 driver, in preparation for the end of type 3 driver support.

Java support in DB2 servers consists of the ability to create native Java-based, user-defined functions and stored procedures.

Net.Data

Included in the server product boxes is a copy of Net.Data, a Web-enabling tool for interactive database-to-Web applications. Net.Data is IBM's strategic product for enabling Internet/intranet access to relational data on a variety of platforms. It provides open data access to DB2 and other data sources including Oracle, Sybase, and any ODBC data server. With Net.Data, your application can use DB2 to build dynamic Web pages to support electronic commerce applications.

Net.Data provides for high-performance, robust, application development function and exploitation of existing business logic through open programming interfaces. Net.Data tightly integrates with Web-server APIs such as those from IBM, Lotus, Netscape, and Microsoft, providing higher performance than common gateway interface (CGI) applications.

Net.Data provides connection management to your key relational databases for optimum performance. Net.Data can establish a continuous connection to specified databases. Net.Data maintains the connection throughout the Web application and across invocations of Web applications. Since the database connection is continuous, the application does not experience the overhead of repeated connects to the database. The result is peak performance of your interactive Web application.

The Net.Data application is a macro with a rich macro language, variable substitution, conditional logic, and optional function calls. Net.Data supports client-side processing with Java applets and Java Script. Server-side processing includes Java applications and REXX, Perl, and C/C++ applications.

Net.Data and Java support make DB2 Universal Database Internet ready right out of the box. For the most complete Web-enabled database solution on the market, DB2 UDB teams with WebSphere for a complete e-business environment.

Partner solutions

In order to implement a data management solution that meets the changing needs of your business, you will need a robust relational database, and carefully chosen applications to run against it. DB2 Universal Database provides the rock-solid foundation required to successfully deploy enterprise solutions, while thousands of Independent Software Vendors (ISVs) offer a diverse range of sophisticated applications that support DB2.

In close partnership with IBM, leading software developers like Siebel, I2, SAP, Baan, Dassault, and PeopleSoft, develop enterprise resource planning (ERP) solutions and CRM solutions, as well as B2B solutions and other applications that deliver the functionality your user base needs. These premier ISVs recognize that DB2 Universal Database has the performance, reliability, and flexibility to meet the demands of today's operational and business intelligence applications.

So, if you are looking for a data management solution, IBM's many business partners offer an extraordinary range of applications designed to run on DB2. From the ERP solutions mentioned above, to the business intelligence tools offered by Andyne, Brio, Business Objects, and Cognos, to the DB2 Extenders created by Consistency Point Technologies, Environmental Systems Research Institute, Inc. (ESRI), The Fillmore Group, Formida Software, Innovus Corp., IsoQuest, Inc., OG Software (Oxford Group), and Prime Factors, Inc., you will find that our business partners offer a constantly expanding universe of solutions.

For the most up-to-date information on solutions, and to look through the on-line IBM Solutions Catalog, go to these two sites:

• http://www.software.ibm.com/data/partners/

• http://www.software.ibm.com/solutions/isv

Business Intelligence powerhouse

By Business Intelligence (BI) we mean applications like data warehousing, data mining, on-line analytical process (OLAP), and decision support. Many customers are looking for ways to mine and analyze their operational data for competitive advantage. These are among the most important uses of data management technology today, because they provide customers with excellent returns on their investment.

Several factors make DB2 UDB an outstanding data store for BI applications:

• These applications often involve large volumes of data. Typically, small to medium size warehouses and datamarts might contain 50 GB to 300 GB. Large installations can contain several terabytes. UDB addresses these requirements with its outstanding scalability, including advanced parallel query, and VLDB operations.

• Queries against the database tend to be very complex. UDB has the most advanced query optimizer in the industry, providing excellent query performance with a minimum of DBA time required for tuning.

• DB2 UDB has some features specifically designed to assist with on-line analytical processing (OLAP) support.

• DB2 also offers the Warehouse Manager feature, an integrated set of tools for building, managing, and maintaining data warehouses and datamarts. The Warehouse Manager tool works with the Warehouse Center, the integrated GUI tool, which co-ordinates and automates the activities needed to extract, clean, and populate the data into your informational data store.

Advanced optimizer

The DB2 SQL optimizer strengthens DB2's leadership position in query optimization for traditional applications, and also extends this robust support to new application domains enabled by the object-oriented extensions described earlier. This new optimization technology provides the function and performance needed by customers to analyze and exploit vast amounts of valuable information stored in their databases.

The increasing importance of decision support applications represents an important customer trend, which can result in very complex database queries. These queries may have been written by end-users, generated by automatic tools, or produced as a result of many point-and-click application interfaces popular in today's DOS, Windows, OS/2, and UNIX environments.

The optimizer incorporates a very sophisticated query rewrite phase that automatically transforms a complex query into a simpler query that is easy to optimize. As a result, the end user will realize the best possible performance regardless of the way a query is structured.

The optimizer also looks at a greater number of alternatives in its search for the best query execution plan, as well as employing more sophisticated techniques of modeling the cost of different ways of fetching the data from disk. All these techniques can result in orders of magnitude of performance improvement for complex queries as compared to existing SQL optimizer technology.

A related technology is DB2's support of Materialized Query Tables (MQT) or Automated Summary Tables (AST). Using AST queries that might take minutes or hours to complete can be shortened dramatically, often to seconds or even sub-second response times. This is done by precalculating summary information into a summary table, then using the power of the optimizer's query rewriting to change the submitted query so that it retrieves the information from the summary table, rather than recalculating it. Users do not have to change their queries to take advantage of this performance improvement, it is handled automatically by the DB2 optimizer once the administrator defines the AST.

OLAP support

Data warehouse and OLAP applications are characterized by the use of a special design technique, which is called star schema, to model relational data for multidimensional analysis (MDA). Under this schema it is common for a large "fact" table to be joined to multiple "dimension" tables in a very complex SQL join query. Any optimization techniques that can be used to improve the performance of these joins can significantly improve the performance of the overall application. DB2 Universal Database has special optimization techniques to do this:

• Index ANDing using dynamic bit maps: UDB uses dynamic bit-map technology to efficiently combine multiple indexes. Performance is improved for queries that use columns that are key columns of different indexes over the same table. This includes the use of indexes for multi-way joins.

• Star joins: DB2's star join algorithm exploits dynamic bit maps to join a large fact table with a series of relatively small dimension tables, thus minimizing data I/O.

OLAP and MDA applications are also characterized by queries involving complex grouping and aggregation of data. With DB2 UDB, the GROUP BY clause has been extended to support "super groups." One type of super group is a ROLLUP group, which is a result set that contains "sub-total" and "overall total" rows in addition to the regular grouped rows. Another type of super group is a CUBE group, which is a result set that contains "cross-tabulation" rows in addition to all the rows that would be in a ROLLUP group for the same columns.

The ROLLUP and CUBE aggregations are useful in OLAP (on-line analytical processing) decision support applications to aggregate base data in multiple dimensions and over roll-up/drill-down hierarchies. An example of a common usage of this is analyzing data in three dimensions, such as looking at sales by product, by store, and by month.

DB2 Universal Database's scalability and parallel query performance, advanced query optimization, and unique OLAP support make it the best database engine to power any Business Intelligence application.

DB2 UDB has particular strengths in supporting business intelligence applications such as data warehousing and on-line analytical processing (OLAP). DB2 leads the industry in parallel database technology and query optimization resulting in the proven ability to help customers find competitive advantage, better customer service, or reduce costs by mining their data for the knowledge required to make better decisions. Furthermore, this does not require the additional expense of a specialized database; DB2 UDB provides a single database that can be used across an enterprise for all data management requirements from OLAP to OLTP.

Ease of use and management

DB2 Universal Database is one of the easiest databases in the industry to use and manage. It includes a complete suite of graphical tools to satisfy the needs of:

• Database administrators (DBAs)

• Application programmers

It also includes tools to assist with client set-up and ad hoc query and reporting for end users.

Administering databases with the DB2 Administration Tools

You can administer local or remote servers using the DB2 Administration Tools. Use the Control Center to perform administration tasks such as configuring DB2 instances and databases, backing up and recovering data, scheduling jobs, and managing media, all from a graphical interface.

Managing instances and database objects using the Control Center

The Control Center displays instances and database objects (such as table spaces, tables, and packages) and their relationships to each other. Using the Control Center, you can manage local and remote servers from a single point of control.

From the Control Center, you can perform operations on database objects. You can:

• Create and drop a database

• Create, alter, and drop a table space or table

• Create, alter, and drop an index

• Back up and recover a database or table space

• Define replication sources and subscriptions to replicate data between systems

• Monitor resources and events on a server

You can also control DB2 instances by:

• Maintaining communication protocols

• Setting database manager and database configuration values that affect performance

SmartGuides or wizards are provided to help you perform complex tasks. For example, SmartGuides are available to tune the performance of your system or to recommend what indexes you need (and it will build them for you too).

The Control Center provides additional functionality to assist you in managing your servers:

• Control Center: Use the Control Center to start another session of the Control Center to administer a server.

• Satellite Center: Use the Satellite Center to manage the satellites that are served by a particular DB2 Control Server. It provides create, remove, modify, and manage functions for satellites and groups. You can also create and manage scripts to administer the satellites.

• Command Center: Use the Command Center to enter DB2 commands and SQL statements in an interactive window and see the execution result in a result window. You can scroll through the results and save the output to a file.

• Task Center: Use the Task Center to create scripts, which you can store and invoke at a later time. These scripts can contain DB2 commands, SQL statements, as well as operating system commands. Scripts can be scheduled to run unattended. These jobs can be run once or set up to run on a repeating schedule; a repeating schedule is particularly useful for tasks like backup.

• Health Center: Use the Health Center to monitor your system for early warnings of potential problems, or to automate actions to correct problems discovered.

• Journal: Use the Journal to view all available information about jobs that are pending execution, executing, or that have completed execution. You can also view the recovery history log, the alerts log, and the messages log; and review the results of jobs that are run unattended.

• Tools Setting: Use the Tools Setting to change the settings for the DB2 Administration Tools.

• License Center: Use the License Center to manage licenses and display license status and usage of any DB2 products installed on your system. You can also use the License Center to configure your system for proper license monitoring.

• Data Warehouse Center: Use the Warehouse Center to manage data warehouses and datamarts. You can define data sources, cleansing steps, and target systems. You can set up a schedule for when data extractions are run on the data sources, and when data will be propagated to the target warehouses.

• Information Center: The Information Center provides quick access to DB2 product information. This product information includes such items as: database tasks, reference material, DB2 documentation, troubleshooting aids, sample programs for application development, and DB2 Web-related URLs.

• Development Center: The Development Center provides an easy-to-use interface for developing routines such as stored procedures and user-defined functions (UDFs). A set of wizards makes it easy to perform your development tasks. The Development Center provides a single development environment that supports the entire DB2 family ranging from the workstation to z/OS. You can launch the Development Center as a stand-alone application from the IBM DB2 Universal Database program group or from a DB2 Universal Database center, such as the Control Center, the Command Center, or the Task Center.

  With the Development Center, you can:

  • Create, build, and deploy Java and SQL stored procedures

  • Create, build, and deploy user-defined functions:

    • SQL table and scalar UDFs

    • UDFs that read MQSeries messages

    • UDFs that access OLE DB data sources

    • UDFs that extract data from XML documents

  • Debug SQL stored procedures using the integrated debugger

  • See the contents of the server for each database connection that is in your project or that you have explicitly added to the Server view. You can also view and work with other database objects such as tables, triggers, and views.

  • Export and import routines and project information

  The Development Center also provides a DB2 development add-in for each of the following development environments:

  • Microsoft Visual C++

  • Microsoft Visual Basic

  • Microsoft Visual InterDev

  With the add-ins, you can easily access the features of the Development Center and other DB2 centers from your Microsoft development environment, making it easy for you to develop and incorporate stored procedures and UDFs into your DB2 application development.

DB2 autonomic technology

IBM has put a lot of research and development effort into providing advanced automation functionality within DB2. The results of this effort is known as Self-Managing And Resource Tuning (SMART) technology. The purpose of SMART is to reduce the complexity of managing DB2 for inexperienced users, and to reduce the total cost of ownership by enabling many administrative and tuning functions to be carried out automatically by the DBMS.

These are some examples of SMART in DB2 UDB:

• Configuration Advisor Wizard: This provides an easy-to-use GUI tool to tune the performance of a database with just minimal input about the anticipated workload.

• Design Advisor Wizard: This provides recommendations on adding new indexes or removing existing indexes based on your input of workload characteristics.

• DB2 Health Center: This constantly monitors the overall health of a system. It can automatically diagnose and fix many potential problems. In addition, it is able to contact an administrator by e-mail or pager, either to notify them that corrective action has been taken, or because a problem has been identified that is deemed by the administrator to be too risky to attempt an automated fix, or which cannot be fully automated because a hard limit has been reached or the task requires some form of manual intervention.

• Memory Tracker and Visualizer: This shows all memory in all pools at a particular instant. It provides a graphical display of memory usage and also supplies information to the DB2 monitoring facilities for ongoing analysis.

• Storage Management: This is a tool available through the Control Center. From this tool you can access the Storage Management view that displays a snapshot of the storage for a particular database, database partition group, or table space. Statistical information can be captured periodically and displayed depending on the object chosen:

  • For table spaces, information is displayed from the system catalogs and database monitor for tables, indexes, and containers defined under the scope of the given table space.

  • For databases or database partition groups, information is displayed for all the table spaces defined in the given database or database partition group.

  • For databases, information is also collected for all the database partition groups within the database.

  You can use the information displayed in this view to monitor various aspects of your storage, such as space usage for table spaces, data skew (database distribution) for database partition groups, and capture cluster ratio of indexes for database partition groups and table spaces. From the Storage Management view you can also set thresholds for data skew, space usage, and index cluster ratio. A warning or alarm flag will let you know if a target object exceeds a specified threshold.

Data access and replication

DB2 Universal Database is a distributed, fully networked RDBMS. It provides you with the flexibility of placing data anywhere in your network required for optimum service and productivity, and provides efficient means for clients to access that data over the network. Furthermore, DB2 provides the most efficient and seamless integration of data on mainframe and midrange data servers in the industry allowing you to reduce costs and improve cycle-times by leveraging your current investments in data, hardware, software, and skills.

DB2 UDB's support for distributed data can be grouped into three categories:

• Client and server data access

• Data replication

• Host data integration through DRDA

Client/Server data access

DB2 UDB supports a number of features that provide for efficient access to UDB database servers from client workstations over a network:

• Stored procedures: DB2 clients can call stored procedures that execute on the DB2 server. Stored procedures push processing back from the client to the server, and thus provide support for thinner clients. They also cut back significantly on network traffic resulting in better response time. UDB stored procedures are somewhat unique in that they are not written in some proprietary language, but can be written in Basic, Java, C, C++, etc. They can also be written in the standards based stored procedure language defined by the database standards committee. Stored procedures can return multiple rows and multiple result sets for greater efficiency.

• Compound SQL: DB2 client applications can batch multiple SQL statements into a single compound SQL statement that is transmitted over the network and executed as one at the server. This can cut down on network traffic and is particularly good for mass inserts, updates, or deletes over the network.

• Row blocking: DB2 UDB can reduce the number of network transmissions by buffering rows at both the client and server ends. The buffer sizes are tunable parameters.

• Two-phase commit: DB2 clients can access and update multiple UDB servers in a single transaction (unit-of-work). UDB servers have built-in transaction monitors that log the transaction phases and handle rollback in case of failure. The two-phase commit process is coordinated from the client.

Data replication

Data Propagator Relational is built into DB2 Universal Database (and remains available as separate products or features for DB2 products on MVS™, OS/390, OS/400®, and VSE and VM). This feature is based on an advanced change-capture technology. It provides a highly efficient way for automatically maintaining consistent copies of relational data in the DB2 family of databases without requiring any batch processing windows and explicit knowledge of, or changes to, business applications. Data Propagator includes:

• Easier and intuitive administration: The Control Center includes replication administration, provides many ease-of-use features, and acts as a single-point-of-control for the entire DB2 replication network.

• Subscription sets for transaction consistency: With subscription sets, updates to all related target tables are committed in a single unit of work, supporting referential integrity requirements.

• Update-anywhere replication: Robust, update-anywhere replication is enabled with rigorous conflict detection, and automatic compensation for a new breed of distributed applications.

• On-demand replication for occasionally connected and mobile systems: With support for on-demand replication, auto connect and disconnect, and minimization of connection time, mobile or occasionally disconnected systems running DB2 Universal Database Personal Edition on Windows NT/2000/XP, or Windows 98/ME are enabled to participate fully and efficiently in read-only and update-anywhere replication scenarios.

• DB2 source view replication: With support for view-based replication, you can subset and distribute data efficiently from normalized databases.

• Event-driven propagation: This is an easy mechanism for you to control the timing of propagation, for example, at the end of day or after the completion of a particular batch run; or the content of propagation only on updates performed before 6:00 p.m.

Distributed database access through DRDA

Many customers have a need to distribute data across relational databases from different vendors or on different hardware platforms. IBM has developed the Distributed Relational Database Architecture™ (DRDA) to support this. It has become the standard for integrating client and server databases and client-based tools with mainframe and midrange databases on OS/390, MVS, VSE, VM, and OS/400. DB2 UDB uses DRDA to provide the most complete and efficient data integration with these platforms in the industry.

There are two major subsets of DRDA function: the client or requester side and the server-side:

• DRDA Application Requester (DRDA-AR): This allows DB2 client workstations to transparently access data in DB2 for OS/390, DB2 for MVS, DB2 for OS/400 and/or DB2 for VSE & VM. The DRDA-AR is provided with DB2 Connect or is built into DB2 UDB Enterprise Server Edition.

• DRDA Application Server (DRDA-AS): This allows other DRDA-ARs to access data stored in DB2 Universal Database. In particular, this capability allows DB2 for MVS, DB2 for VSE & VM (SQL/DS™), and DB2/400 applications (or any other application that implements the DRDA Application Requester functionality) to access data located in the UDB server databases. With this capability, thousands of existing database applications running on the MVS, VM, and OS/400 platforms are able to access data stored in DB2 UDB databases. The DRDA-AS is included in DB2 UDB Workgroup Server Edition and Enterprise Server Edition.

Federated systems

A DB2 federated system is a special type of distributed database management system (DBMS). A federated system consists of a DB2 instance that operates as a server, and a database that serves as the federated database, one or more data sources, and clients (users and applications) who access the database and data sources. With a federated system you can send distributed requests to multiple data sources within a single SQL statement. The power of a DB2 federated system is in its ability to:

• Join data from local tables and remote data sources, as if all the data are local

• Take advantage of the data source processing strengths, by sending distributed requests to the data sources for processing

• Compensate for SQL limitations at the data source by processing parts of a distributed request at the federated server

• Write capability to perform INSERT, UPDATE, and DELETE actions on the data sources

• Ability to create remote tables on relational data sources

Federated database systems provide the middleware functionality for outstanding information integration. Built into DB2 Enterprise Server Edition is the ability to federate relational data across IBM's family of databases, including the DB2 family and Informix IDS.

Multi-platform support

DB2 Universal Database is one of the most open database platforms available. It runs on the most popular UNIX and Intel server platforms including AIX, HP-UX, Solaris, Linux, and Windows NT/2000/Server 2003. It supports all major industry standards relevant to distributed data so that it can be accessed using thousands of existing tools and applications, and can be easily managed within an open, network computing environment. These capabilities allow you to reduce costs and improve cycle-times by leveraging your current investments in data, hardware, software, and skills.

Bullet-proof reliability

DB2 Universal Database is setting the standard for quality and reliability in the client and server database industry. As more mission-critical applications are implemented on UNIX and Intel platforms, IBM's ability to bring mainframe-level reliability to this environment has become a major factor in choosing DB2. Better reliability and availability can reduce your costs, while scalability both within and across platforms can reduce the risk of dead-end projects.

---