The DB2 Universal Database family

The DB2 Universal Database family is very large. This book introduces you primarily to the DB2 UDB for z/OS product. You will probably also want to know a little about some of the other products with which DB2 UDB for z/OS works. Your company probably has some of these other products installed, so read this section for an overview of the entire DB2 UDB family.

The immediate family includes:

• DB2 Universal Database for z/OS

• DB2 Universal Database for iSeries

• DB2 Universal Database for Linux, UNIX^®, and Windows (see “More servers” on page 18 for a list of supported operating systems)

Recommendation: You can download free or trial demonstration versions of many products and tools in the DB2 UDB family. By using demonstration code, you can increase your understanding of the various products you will read about in this chapter and elsewhere in this book. To download demonstration copies, visit the IBM Web site at www.ibm.com/software/db2. Then select a specific DB2 product and choose the download option on that product's home page.

If you think about the preceding list of DBMS products as the immediate family, you can extend the family metaphor a bit further. Think of other products that this section describes as the many extended family members that contribute to the strength and success of the overall family.

You may wonder what the term “universal” means in the name of some of the DB2 database management systems. It does not mean that the underlying code of the various DB2 UDB DBMSs is identical. On the contrary, IBM developed the underlying code specifically so that each DBMS could exploit the individual capabilities of the various operating systems.

The term “universal” encompasses the following characteristics:

• Data types among the DB2 UDB products are compatible.

• Many different types of clients can access data in the DB2 UDB products.

• You can develop applications with SQL that are common across the DB2 UDB family and port them from one DB2 UDB product to another with minimal modification. (Porting means moving an application from one operating system to another.)

• The DB2 UDB family of products can support scalable applications. For example, imagine that your application starts with a small number of users and small volumes of data and transactions but then grows significantly. Because of scalability, your application can continue to work efficiently as you distribute some of the data or transactions to another DBMS in the family.

• Reliability (for nonstop 24x7 availability) is possible with the entire DB2 UDB product family.

• Similar function is typically incorporated into each DB2 UDB product over time.

• Tools are available to help you manage all the DB2 Universal Database products in a similar way.

This section introduces various parts of the DB2 UDB family one at a time.

Tip: Identify someone who is familiar with your company's I/S environment. Ask that person to provide a list of the products with which you are likely to work. Your company might have only a subset of the products that are mentioned in this section and in other sections of this book. Knowing a little bit about your company's environment will help you know which sections of this book are most important for you to read.

Enterprise servers

Enterprise servers are the machines that handle the largest applications. z/OS is the operating system for IBM's largest hardware platform, zSeries. (You can read more about this operating system in “z/OS overview” on page 70.) As a result, most people think of DB2 UDB for z/OS as the largest DB2 Universal Database product. As Figure 1.3 shows, other DB2 products can also act as enterprise servers:

• DB2 Universal Database for Linux, UNIX, and Windows (see “More servers” on page 18 for a list of other supported operating systems.)

• DB2 Universal Database for iSeries, which runs under the OS/400^® operating system, supports applications in the midrange iSeries environment.

• DB2 for VSE and VM (which does not carry the “Universal” label) supports large applications in the VSE and VM environments.

DB2 UDB editions

This section provides a brief overview of the various DB2 UDB database editions that run in the DB2 workstation environment.

• DB2 UDB Enterprise Server Edition runs on any size server in the Linux, UNIX, and Windows environments. This edition provides the foundation for the following capabilities:

  - Transaction processing

  - Building data warehouses and Web-based solutions

  - Connectivity and integration for other DB2 UDB enterprise data sources and for Informix data sources

  The DB2 Connect feature provides functionality for accessing data stored on midrange and enterprise server database systems, such as DB2 UDB for z/OS and DB2 UDB for iSeries. This edition supports both local and remote DB2 clients.

• DB2 UDB Workgroup Server Editions are suited for a small business environment with up to four CPUs. These editions support both local and remote DB2 clients.

• DB2 UDB Personal Edition provides a single-user database designed for occasionally connected or remote-office implementations. You can use this edition to create and manage local databases, or you can use it as a client to DB2 UDB Enterprise Server Edition or Workgroup Server Edition database servers. DB2 UDB Personal Edition does not accept requests from clients.

• DB2 UDB Developer Editions let you develop and test applications that run on one operating system and access databases on the same or on a different operating system.

• DB2 UDB Express Edition is designed for small and medium-size businesses.

Figure 1.4 shows the DB2 UDB editions.

Clusters

As you learned earlier in this chapter, a cluster is a complex of machines that work together to handle multiple transactions and applications. To optimize performance, throughput, and response time, organizations can distribute their application transactions and data, and can run database queries in parallel.

The following DB2 Universal Database products use cluster technology:

• DB2 Universal Database for z/OS

• DB2 Universal Database for iSeries, which runs in the parallel iSeries environment

• DB2 Universal Database for Linux, UNIX, and Windows

Figure 1.5 identifies the environments in which the DB2 Universal Database products can operate in clusters.

More servers

In addition to the enterprise servers, most companies also support smaller-scale servers on local area networks (LANs). These servers handle important applications that don't demand the resources that are available on the larger enterprise servers.

The DB2 Universal Database for Linux, UNIX, and Windows product works on a variety of servers, as Figure 1.6 shows.

You can also use a special implementation of DB2 called DB2 Information Integrator. You can read more about this product in “Federated database support through DB2 Information Integrator” on page 28.

Figure 1.6 shows that DB2 Universal Database runs on the Linux operating system. Linux also runs on the zSeries platform. This means that the zSeries platform offers two operating systems (z/OS and Linux) on which you can run DB2 Universal Database products.

The networks: WANs and LANs

The DB2 Universal Database family products can communicate by using both wide area networks (WANs) and local area networks (LANs).

WAN

A wide area network generally supports the enterprise servers such as DB2 UDB for z/OS; they require either Transmission Control Protocol/Internet Protocol (TCP/IP) or Systems Network Architecture (SNA).

LAN

A local area network generally supports smaller servers, which require TCP/IP, IPX/SPX, or NetBios.

Figure 1.7 illustrates network communication using a WAN and a LAN.

Personal, mobile, and pervasive environments

DB2 is available on even very small devices designed for individual use. You can write programs that access DB2 data on your own desktop, laptop, or hand-held computer while you are traveling or working at home. Then, later, you can synchronize these databases with corporate databases in the enterprise.

In the desktop and laptop workstation environments, DB2 Universal Database Personal Edition provides a database engine for a single user. DB2 Personal Edition serves your needs if you are working independently and occasionally connected or mobile.

For hand-held computers, DB2 Everyplace enables lightweight database applications on all the Palm Operating System, Windows CE, Embedded Linux, QNX Neutrino, Linux, and Symbian EPOC operating systems. DB2 Everyplace is available in two editions: Enterprise Edition and Database Edition.

Figure 1.8 illustrates the personal, mobile, and pervasive environments.

Clients

One reason the DB2 family carries the “universal” name is that the DB2 Universal Database family supports a wide variety of clients. Figure 1.9 illustrates the various clients that can run DB2 applications.

Sources of data

DB2 Universal Database for Linux, UNIX, and Windows supports access to many different data sources with a single SQL statement. This support is called federated database support. For example, with federated database support, you can join data from a wide variety of data sources, including the following relational and nonrelational objects:

• All DB2 Universal Database products

• DB2 for VSE & VM

• Oracle

• Microsoft^® SQL Server, Microsoft Excel

• Sybase

• Informix

• ODBC

• OLE DB

• Teradata

• Documentum

• XML

If you also use DB2 Information Integrator, your applications that access the DB2 Universal Database DBMSs can have read-write access to additional data sources, Web Services, and WebSphere Business Integration. Access to heterogeneous, or dissimilar, data means that applications can accomplish more, with less code. The alternative would be that programmers would write multiple programs, each of which accesses data in one of the sources. Then the programmers would write another program that would merge the results. Clearly, access to heterogeneous data is a powerful asset for any organization that has data in a variety of sources. You can read more about DB2 Information Integrator later in this chapter.

Figure 1.10 identifies the sources of data in the DB2 Universal Database environment.

Management tools

Many different products and tools are available in the marketplace to help you manage the DB2 environment, regardless of which platform you use. Several products are particularly helpful to people who are managing a DB2 Universal Database environment. Figure 1.11 identifies these products, and the sections that follow the figure introduce them.

IBM DB2 and IMS tools

The IBM Data Management collection of tools offers DB2 tools for z/OS, iSeries, Linux, UNIX, and Windows, as well as tools for IMS, which is the hierarchic DBMS that runs in the z/OS environment.

These tools are organized into six different categories with the following capabilities:

Database administration

Navigate through database objects and perform database administration tasks on one or many objects at a time. This category also includes tools that are used to alter, migrate, and compare objects in the same or in different DB2 systems.

Utility management

Manage DB2 systems with high-performance utilities and automation.

Performance management

Monitor and tune DB2 systems and applications to obtain optimal performance and lowest cost.

Recovery management

Examine recovery assets and recover DB2 objects to a point in time in the event of system outage or application failure. This category also includes tools to help you manage recovery assets.

Replication management

Propagate data changes by capturing and applying changes to remote systems across the DB2 UDB family.

Application management

Manage DB2 application changes with minimal effort, and build and deploy applications across the enterprise.

Most of the database tools that support DB2 UDB for z/OS contain ISPF (Interactive System Productivity Facility) interfaces that allow you to perform most DB2 tasks interactively. With the ISPF interfaces integrated together, you can move seamlessly from one tool to another. Many of these tools provide a GUI (graphical user interface) as well.

DB2 Utilities Suite In this version of DB2 UDB for z/OS, the DB2 Utilities Suite is available as an optional product. You must separately order and purchase a license to such utilities, and discussion of those utility functions in this publication is not intended to otherwise imply that you have a license to use them.

With the DB2 and IMS collection of tools, you can anticipate:

• Immediate support of new versions of DB2

• Cross-platform delivery

• Consistent interfaces

• Thorough testing that is performed on the same workloads as the database products

You can read more about specific Data Management tools throughout this book.

DB2 Control Center

DB2 Control Center is a database administration tool that you can use to administer your DB2 Universal Database environment, which includes DB2 UDB for z/OS. DB2 Control Center is an integrated part of DB2 Universal Database for Linux, UNIX, and Windows. DB2 UDB for z/OS also includes DB2 Control Center.

The DB2 Control Center displays database objects (such as tables) and their relationships to each other. Using the DB2 Control Center interface, you can manage local and remote servers from a single workstation. From the Control Center, you can perform operations on database objects throughout the family of DB2 Universal Database DBMS products. You can also use the DB2 Control Center to start other tools, such as Data Warehouse Center, DB2 Replication Center, and DB2 Visual Explain. You can read more about these tools in this chapter and later in this book.

DB2 Warehouse Manager

DB2 Warehouse Manager extends the scalability, manageability, and accessibility of your DB2 warehouse. You can move data from the source to the target and control the server on which transformations take place. With DB2 Warehouse Manager, you can:

• Increase the scalability of data warehouses

• Manage a data warehouse that is on DB2 UDB for z/OS

• Quickly deploy data marts, which are small data warehouses that apply to a single department or team

• Provide end users easy access to data

DB2 Warehouse Manager runs on AIX, Windows, z/OS, Solaris, OS/390, iSeries, and Linux.

Application development tools

DB2 UDB provides a strong set of tools for application development. This section introduces the key tools that developers use to create DB2 UDB applications, stored procedures, and applications that support business intelligence and e-business.

WebSphere Studio Application Developer

WebSphere Studio Application Developer is a fully integrated Java development environment. Using WebSphere Studio Application Developer, you can build, compile, and test J2EE (Java 2 Enterprise Edition) applications for enterprise e-business applications with:

• JSP (JavaServer Pages) files

• EJB (Enterprise JavaBeans^™) components

• 100% Pure Java applets and servlets

You can read more about WebSphere Studio Application Developer in “Web-based applications and WebSphere Studio Application Developer” on page 367.

DB2 Development Center

The DB2 Development Center is a tool that helps you define and implement stored procedures and user-defined functions. Using this tool, you can build Java and SQL stored procedures for the DB2 UDB for z/OS environment or for other DB2 Universal Database DBMSs. You can launch the DB2 Development Center from the DB2 Control Center. You can read more about DB2 Development Center in “Using the DB2 Development Center” on page 207.

DB2 OLAP Server

DB2 OLAP Server helps you deliver analytical applications that give your organization a competitive edge. You and other users can ask questions intuitively because the OLAP Server presents the data in business dimensions. (A business dimension is a category of data, such as products or time periods that an organization might want to analyze.) DB2 OLAP Server enables you to do fast, intuitive, multidimensional analysis.

DB2 OLAP Server runs on z/OS, OS/390, Linux, AIX, HP-UX, Solaris, iSeries, and Windows. The marketplace offers many applications and tools that support DB2 OLAP Server. You can create your own applications or implement a turnkey (easy-to-implement) solution that you can acquire from solutions providers.

DB2 UDB Extenders

DB2 UDB provides extender applications that add the power of full-text retrieval to SQL queries that can be easily integrated with other types of searches. Other DB2 UDB Extenders can search for images, video, and voice data, and they can also handle XML documents and spatial data.

You can use the following DB2 UDB Extenders to add new types of data to your DB2 UDB databases:

• DB2 UDB XML Extender provides an end-to-end solution for storing and retrieving XML documents.

• DB2 UDB Spatial Extender lets you add spatial data, such as geographical information, to your database, and lets you query and analyze spatial data and standard business data.

• DB2 UDB Net Search Extender offers scalable, full-text retrieval for large numbers of concurrent users.

• DB2 UDB Text Extender adds the power of full-text retrieval to SQL queries, using features available in DB2 UDB for z/OS that let you store unstructured text documents.

• DB2 UDB Image, Audio, and Video Extenders let you access and store image, audio, and video data. The DB2 UDB extenders for z/OS provide user-defined functions and APIs that you can use to access these objects.

Middleware and client APIs

The final parts of the family are middleware and client application programming interfaces (APIs). Return for a moment to the family metaphor. You can think of the middleware as that one person in your extended family that keeps communication open among the entire family. The middleware part of the DB2 UDB family helps the rest of the family work together more effectively. Another means of communicating with family members is with client APIs.

Figure 1.12 shows the middleware and client API components that work in the DB2 Universal Database environment.

This section briefly describes how these middleware components help other DB2 UDB family members work well together and how you can use client APIs to access those family members.

WebSphere family of products

WebSphere is actually a broad portfolio of products that help you achieve the promise of e-business. The nine product families that comprise the WebSphere portfolio provide all the infrastructure software that you need to build, deploy, and integrate your e-business. The WebSphere products fall into the following categories:

• Foundation & Tools for developing and deploying high-performance business applications

• Business Portals for developing scalable enterprise portals and enabling a single point of personalized interaction with diverse business resources

• Business Integration for end-to-end application integration

Key members of the WebSphere family that this book focuses on are part of the Foundation & Tools portion of the WebSphere portfolio:

WebSphere Application Server

A Java 2 Enterprise Edition (J2EE) and Web services technology-based application platform. WebSphere Application Server enables an organization to move quickly from simple Web publishing to secure e-business. With WebSphere Application Server, you can take advantage of these services:

• Web services for faster application development. You can read more about Web services in “Web services and DB2” on page 373.

• Dynamic application services for managing your e-business environment with Web services and J2EE 1.3 support that uses standard, modular components to simplify enterprise applications.

• Integrated tools support with WebSphere Studio Application Developer.

WebSphere Studio

A suite of tools that span development for the Web, the enterprise, and wireless devices.

• For application development: WebSphere Studio Application Developer works with Java and J2EE applications and other tools that include WebSphere Studio Enterprise Developer for developing advanced J2EE and Web applications. You can read more about WebSphere Studio Application Developer in “Web-based applications and WebSphere Studio Application Developer” on page 367.

• For application connectivity: WebSphere MQ is a message handling system that enables applications to communicate in a distributed environment across different operating systems and networks.

• For Web development: WebSphere Studio Homepage Builder is an authoring tool for new Web developers, and WebSphere Studio Site Developer is for experienced Web developers.

WebSphere Host Integration

A portfolio of products for accessing, integrating, and publishing host information to Web-based clients and applications.

WebSphere products run on the most popular operating systems, including z/OS, AIX, Linux, OS/390, OS/400, Windows 2000, Windows NT, and Solaris.

You can read more about WebSphere in “Chapter 10. DB2 and the Web.”

DB2 Connect

DB2 Connect leverages your enterprise information regardless of where that information is. DB2 Connect gives applications fast and easy access to existing databases on IBM enterprise servers. The applications can be e-business applications or other applications that run on UNIX or Windows operating systems.

DB2 Connect offers several editions that provide connectivity to host and iSeries database servers. DB2 Connect Personal Edition provides direct connectivity, whereas DB2 Connect Enterprise Edition provides indirect connectivity through the DB2 Connect server.

With DB2 Connect, you can do the following tasks:

• Extend the reach of enterprise data by providing users with fast and secure access to data through intranets or through the public Internet

• Integrate your existing core business applications with new, Web-based applications that you develop

• Create e-business solutions by using the extensive application programming tools that come with DB2 Connect

• Build distributed transaction applications

• Develop applications by using popular application programming tools such as Visual Studio .NET, ActiveX Data Objects (ADO), OLE DB, and popular languages such as Java

• Manage and protect your data

• Preserve your current investment in skills

Users of mobile PCs and pervasive computing devices can use DB2 Connect to access reliable, up-to-date data from z/OS and iSeries database servers.

DB2 Connect provides the required performance, scalability, reliability, and availability for the most demanding applications your business uses. DB2 Connect runs on AIX, HP-UX, Linux, Solaris, Windows XP, Windows Me, Windows 2000, Windows 98, and Windows NT.

Federated database support through DB2 Information Integrator

Information integration technology provides access to diverse, distributed data. With this technology, you can integrate a wide range of data, including traditional application sources as well as XML, text documents, Web content, e-mail, and scanned images.

The DB2 Information family of products are a key part of the information integration framework. The product components include a federated data server and a replication server for integrating these diverse types of data.

The following key technologies provide information integration:

• XML facilities for working with XML data

• Web services support

• Federation technology

• Additional features such as advanced search and flexible data replication

The IBM federated database systems offer powerful facilities for combining information from multiple data sources. As “Sources of data” on page 20 explains, these facilities give you read and write access to diverse data from a wide variety of sources and operating systems as though the data is a single resource. With a federated system you can:

• Keep data where it resides rather than moving it into a single data store

• Use a single API to search, integrate, and transform data as though it is in a single virtual database

• Send distributed requests to multiple data sources within a single SQL statement

For example, in a single SQL statement you can join data that is located in a DB2 UDB table, an Oracle table, and an XML tagged file.

The DB2 UDB product that supports data federation is DB2 Information Integrator.

You should consider federation as an integration strategy when the technical requirements of your project involve search, insert, update, or delete operations across multiple heterogeneous, related sources or targets of different formats. During setup of the federated systems, information about the data sources (for example, the number and the data type of columns, the existence of an index, the number of rows) is analyzed by the query optimizer to formulate fast answers to queries. The query optimization capability of federated systems can automatically generate an optimal plan based on many complex factors that are in this environment. This automatically generated plan makes application development in a federated system much easier because developers no longer need to dictate the execution strategies in the program.

Data replication

Data replication is the process of maintaining a defined set of data in more than one location. Replication involves copying designated changes from one location (a source) to another location (a target) and synchronizing the data in both locations. The source and the target can be in servers that are on the same machine or on different machines in the same network.

For example, assume that you need a large number of your employees to have access to data but giving them access causes performance problems. You can replicate the data and give them access to the copy, rather than to the source of the data. IBM offers several tools to help you effectively replicate data in the DB2 Universal Database environment. These tools include DB2 DataPropagator^TM, DataRefresher^TM, and IMS DataPropagator.

DB2 DataPropagator is particularly useful to companies that use DB2 Universal Database products and that also need to synchronize their data in more than one location. DB2 DataPropagator runs in many operating systems, such as:

• AIX

• HP-UX

• Linux

• NUMA-Q

• OS/2^®

• OS/400

• Solaris

• UNIX

• VM/ESA^®

• VSE/ESA

• Windows XP, Windows 2000, Windows 98, Windows NT

• z/OS

You can use DB2 DataPropagator with DB2 Information Integrator to expand the scope of data replication to include heterogeneous data. When you use these two products together, you can replicate data between DB2 Universal Database DBMSs and non-IBM sources and targets that DB2 Information Integrator supports.

DB2 DataPropagator Version 8 offers an enhanced capture function and improvements to usability, flexibility, performance, and concurrency. For example, DB2 DataPropagator supports more frequent commits and fewer joins between replication tables.

A data replication solution supports business requirements such as the movement to client/server environments, data warehousing, and business reengineering. DB2 DataPropagator satisfies these replication requirements and more by delivering the data where you need it.

Client APIs

Application programming interfaces provide a variety of ways for clients to access a DB2 database server.

Java interfaces

DB2 provides two standards-based Java programming APIs for writing portable application programs that access DB2:

• JDBC is a generic interface for writing platform-independent applications that can access any SQL database.

• SQLJ is another SQL model that a consortium of major database vendors developed to complement JDBC. ISO (International Standards Organization) defines SQLJ. SQLJ is easier to code than JDBC and provides the superior performance, security, and maintainability of static SQL.

With DB2 UDB for z/OS support for JDBC, you can write dynamic SQL applications in Java, whereas with SQLJ support, you can write static SQL applications in Java. These Java applications can access local DB2 data or remote relational data on any server that supports DRDA. You can read more about JDBC and SQLJ support in “Using Java to execute static and dynamic SQL” on page 198. You can read more about DRDA in “Chapter 11. Distributed data access.”

With DB2 UDB for z/OS, you can use a stored procedure that is written in Java. (The DB2 Universal Database family supports stored procedures that are written in many additional languages.) A stored procedure is a user-written application program that the server stores and executes. A single SQL CALL statement invokes a stored procedure. The stored procedure contains SQL statements, which execute locally at the server. The result can be a significant decrease in network transmissions. You can read more about this topic in “Using an application program as a stored procedure” on page 202.

You can develop Java stored procedures that contain either static SQL (by using SQLJ) or dynamic SQL (by using JDBC). You can define the Java stored procedures yourself, or you can use the DB2 Development Center and WebSphere Studio Application Developer tools. You can read about static SQL and dynamic SQL in “Choosing programming languages and methods to use” on page 181.

ODBC

DB2 Open Database Connectivity (ODBC) is the IBM callable SQL interface for relational database access. Functions are provided to application programs to process dynamic SQL statements. DB2 ODBC allows users to access SQL functions directly through a call interface. Through the interface, applications use procedure calls at execution time to connect to databases, to issue SQL statements, and to get returned data and status information. You can read more about ODBC in “Using ODBC to execute dynamic SQL” on page 196.

Web services

Web services are self-contained, modular applications that provide an interface between the provider and consumer of e-business application resources over the Internet. Web services client applications can access a DB2 database. You can read more about DB2 as a Web services provider in “Web services and DB2” on page 373.

DB2 Development Add-Ins for Visual Studio .NET

The IBM DB2 Development Add-In for Microsoft Visual Studio .NET is a set of tightly integrated application development and administration tools designed for DB2 Universal Database. The Add-Ins integrate into the Visual Studio .NET development environment so that application programmers can easily work within their IDE to easily access DB2 data.

The following features offer key benefits:

• Support for client applications (both desktop and Web-based applications) to use .NET to access remote DB2 servers

• A tool for building stored procedures that makes it easy for any application programmer to develop and test stored procedures with DB2 UDB for z/OS without prior zSeries skills or knowledge

You can read more about DB2 support for .NET in “DB2 Development Add-In for Visual Studio .NET” on page 180.

The DB2 Universal Database family portrait

Now that you've read about the various parts of the family, see the family portrait, Figure 1.13, which puts all the parts together. (Remember that your company probably doesn't have all members of the family.)

Consider the entire DB2 family, plus the many other IBM and non-IBM products that work with DB2 and that are available in the marketplace. DB2 UDB for z/OS is a powerful base on which you can build and implement enterprise-scale solutions in these areas:

• e-business

• Transaction processing

• Business intelligence

• Content management

• Integrated application packages, such as the solutions from SAP R/3, PeopleSoft, Siebel, J.D. Edwards, and Lawson