IBM's DB2 competes with Informix, Microsoft, Oracle, and Sybase

\[Editor's Note: In many organizations, the database engine is the workhorse for most onsite applications. Choosing a database is often as difficult as choosing a server-side operating system. The Windows NT Magazine Lab is launching a series of articles about major, enterprise-size database products for NT. The series starts with IBM's DB2 database. Look for coverage of Informix, Microsoft, Oracle, and Sybase database and online analytical processing (OLAP) products in future issues.\]

In December 1996, Windows NT Magazine examined IBM's DB2 Common Server (see Elizabeth Lindholm, "Is DB2 Right for You?"). At that time, DB2 was missing several components (e.g., integrated replication in the base product, enhanced GUI management tools, and a lack of support for symmetric multiprocessing­SMP) that limited IBM's share in the NT market against competitors such as Microsoft and Oracle. Since then, IBM has added several components to better position DB2 in the NT database market.

The History of DB2
The following DB2 products are currently available:

  • DB2 Universal Database (UDB) 5.0 (Personal, Workgroup, and Enterprise Editions)
  • DB2 Common Server 2.0 and DB2 Parallel Edition 1.0 (pre-UDB releases)
  • DB2 for OS/400
  • Mainframe DB2 (DB2 for VSE and VM; DB2 for OS/390)

The difference between DB2 Common Server and DB2 UDB 5.0 is that the latter merges the functions of DB2 Common Server (including DB2 Database Server 4.0) with DB2 Parallel Edition and can run on SMP, Massively Parallel Processor (MPP), and clustered systems.

You can purchase several versions of DB2 UDB. The Workgroup Edition includes the following components:

  • DB2 UDB 5.0
  • DB2 Client Pack with DB2 Client Application Enablers (CAEs)
  • DB2 Relational Extenders for text, image, audio, and video

The DB2 UDB Enterprise Edition supports host connectivity via DB2 Connect 5.0 (formerly Distributed Data Connection Services­DDCS) for connecting to other DB2 platforms (e.g., OS/390, OS/400, VSE and VM) and Distributed Relational Database Architecture (DRDA, which is IBM's version of Open Database Connectivity­ODBC) servers.

A single-user DB2 UDB Personal Edition is available for NT, Windows 95, and OS/2. The product isn't a server version, so it doesn't include the CAEs, but it can function as a client to any DB2 server. Developers can buy the DB2 Personal Developer's Edition or the DB2 Universal Developer's Edition. Both editions are similar to the basic bundles described above, except they include additional software development kits (SDKs) that let developers access DB2's Call Level Interface (CLI) and write applications in programming languages such as C, C++, REXX, or Visual Basic (VB), using embedded SQL.

IBM offers the support that Microsoft developers expect. In addition to DB2 and Data Warehouse Technical Conferences, IBM offers annual Technical Interchanges similar to Microsoft's TechEd. (The independent International DB2 Users Group­IDUG­hosts an annual event, held this year from May 10 to 14 in San Francisco, California.) IBM has a developer connection at http://www.developer.ibm.com and a technical library at http://www.software.ibm .com/data/db2/library that function similarly to the Microsoft Developer Network (MSDN). You can also find free DB2 manuals online at http://www.software.ibm.com/cgi-bin/db2www/library/pubs.d2w/report?uid=UNKNOWN&pwd=&search_type=SIMPLE&r_host=1cust64.max.

In February, IBM launched DB2 OLAP Server 1.0, which combines Arbor's Essbase 5.0 OLAP server with IBM's DB2. As with UDB, personal, workgroup, enterprise, and developer versions are available (for more information, go to http://www.software.ibm.com/data/db2/db2olap). IBM's Digital Library and Visual Warehouse products are based on DB2, and VisualAge for Java e-business ships with a copy of DB2.

New in UDB
IBM released the first version of DB2 in 1983, in pre-GUI mainframe days. Starting with DB2 Common Server, IBM tried to make DB2 as easy to install and administer as SQL Server; with DB2 UDB, IBM has perhaps outdone Microsoft. IBM has also added support for SMP to UDB. With DB2 Common Server, you had to use a separate Parallel Edition to exploit parallelism in SMP and MPP systems. With UDB, you can perform the following parallel operations: Load, Insert, Update, Delete, Reorg, Join, index operations, sorts, backup and restore operations, and table scans. DB2 UDB also offers built-in replication, with DataPropagator-Relational (which was formerly an extra-cost option). DB2 DataJoiner is required for heterogeneous joins and is sold separately.

DB2 UDB includes extensions to SQL and supports online analytical processing (OLAP) via enhanced star-join support (data warehouses often use star joins). UDB also comes with CUBE and ROLLUP commands, which let users perform drill downs, rollups, and cross tabulations. Like Common Server, UDB supports recursive SQL, which can handle difficult structures such as those required for bills of materials. UDB adds support for outer joins and schemas, and IBM is developing support for many SQL3 features. Microsoft and Oracle SQL Server users want a DB2 equivalent to Transact-SQL (T-SQL) or Programming Language SQL (PL/SQL). IBM never developed a proprietary extended SQL (such as T-SQL or PL/SQL) with support for programming constructs such as loops and IF statements; thus, developers must write code, including stored procedures, in DB2's ANSI-compliant SQL or embed SQL in other programming languages.

On the performance front, DB2 UDB adds global caching for SQL statements and supports 64-bit addressability, which accommodates very large memory (VLM). Database administrators (DBAs) can define multiple, various-size buffer pools to better control the data in memory (e.g., binding a frequently accessed table to a particular buffer pool so that the table remains in memory). You can achieve VLM support through an Extended Store (ESTORE), which lets you define a separate address space to use as a buffer pool extension. Multiple tablespaces can also share buffer pools. In addition, you can harness tablespaces to support tables with large objects, storing the objects in separate tablespaces.

Additional DB2 UDB improvements are more efficient client/server communication, faster crash and rollforward recovery, and an improved LOAD utility. UDB also adds support for Distributed Relational Database Architecture (DRDA) over TCP/IP (DRDA previously required SNA) and the Open Software Foundation's Distributed Computing Environment (DCE), a popular infrastructure for managing mainframe-shop enterprise security. DB2 UDB ships with Java Database Connectivity (JDBC) and ODBC 3.0 drivers and supports client-side applets (which let you run applications against DB2 without needing CAEs, for example).

Another new feature is the DB2 Governor, which controls functions such as application behavior. The Governor is a combination front-end utility and daemon. You create rules for application behavior and use the daemon to collect statistics. The Governor then checks the statistics against your rules and can take various actions.

DB2
Contact: IBM * 800-426-4968

Web: http://www.software.ibm.com/data/db2

Price: DB2 UDB Workgroup Edition, $999, DB2 Universal Developer's Edition, $995

System Requirements: Windows NT 4.0
133MHz Pentium processor or better, 48MB of RAM (25 users), 70MB of hard disk space

Hands On
Installing DB2 UDB on NT 3.51 or NT 4.0 is easy. The installation program detects network protocols and can create a new NT user with DB2 DBA privileges. I installed the Workgroup Edition on an NT 4.0 laptop as a local server and the Universal Developer's Edition on an NT 3.51 server, so I didn't need to consider the connectivity issues associated with a normal enterprise installation.

All versions of DB2 UDB ship with a complete documentation set (14 manuals totaling more than 30MB, which you can selectively install via the custom installation). I particularly recommend that you read the 239-page Quick Beginnings for Windows NT before installation. Another great resource for developers or DBAs who are already familiar with another relational database management system (RDBMS) environment is Appendix C in the 133-page manual Administration: Getting Started. IBM calls Appendix C a terminology map, but the appendix also maps common utilities.

After you install DB2 UDB and reboot the server, the first screen lets you install the sample database. I always install the sample database on a development server. Many third-party products have demonstration or quick-start modules that use RDBMSs' default sample databases, and technical sessions and reviews often refer to those databases. Many SDK sample programs also use the default sample database. Moreover, installing a sample database on your production system gives you a test database for setting up DB2 clients.

The default DB2 UDB for NT Workgroup Edition program group includes several options. Screen 1 shows the available options.

The next step in a typical WAN or LAN installation is to install the DB2 clients (CAEs). You need to set up DB2 usernames on the DB2 server before you install the client software; Appendix C in Quick Beginnings for Windows NT provides recommended naming conventions for user IDs, group names, instance names, and object names. (An instance is a copy of the database manager code in a specific directory; unlike Oracle, DB2 can have multiple instances on one system.) During installation, DB2 creates a default instance called DB2 and sets up required Registry values and environmental variables. You use a valid username and password to attach to an instance, and then you connect to a database within the context of the instance. To connect to a local sample database, type

connect to sample

To connect to a remote database, type the following in the Command Center or Command-Line Processor (CLP):

connect to sample user using

After installing the CAEs you need, use Client Configuration Assistant (CCA) to configure NT, Win95, or OS/2 clients. (For Mac, UNIX, and Windows 3.1 clients, follow the instructions in the readme files in the platform-specific subdirectories.)

CLP and the Control Center
Like its predecessors and other RDBMSs, DB2 UDB retains a simple and fast CLP interface that you can use instead of the GUI Command Center. If you migrated SQL Server 4.21 databases to 6.x, you might remember the Chkugr utility that warned you about migration problems. DB2 UDB ships with a similar utility, Db2ckmig, that you will want to run before trying to upgrade an earlier DB2 database to UDB. DB2's LOAD utility loads raw data into database tables, but for help migrating Sybase, Oracle, or other databases to DB2, IBM recommends third-party products such as ManTech International's SQL Conversion Workbench.

Like SQL Server's bcp utility, LOAD is the preferred tool for fast loading large data sets (delimited or positional ASCII or PC/IXF). You can use LOAD to replace (LOAD Replace) or append (LOAD Insert) existing table data. You can also use DB2's Import utility to access the table while you add data or to check referential integrity constraints during the load process.

The easiest way to create a new database is to use the UDB DB2 Control Center's Create Database SmartGuide. Note the distinction between the Control Center and Command Center--Control Center is the main administrative module, whereas Command Center is for issuing SQL commands. The SmartGuide is a wizard that prompts you for the required details and manages the database creation, but you can also create a database using the standard create database command from the Command Center. This command provides several options for aliases, code sets, collation sequences, extents, segment directories, and tablespaces.

DB2 UDB ships with several other new SmartGuides (wizards). For example, CCA uses a SmartGuide to add databases to clients, and the Control Center has SmartGuides to configure performance, back up databases, restore databases, and create tables and tablespaces.

Extenders
The combination of support for large binary data fields and DB2 extenders makes DB2 UDB universal or object relational (see the sample database in Screen 2, on page 75, which includes employee photo and resume data). The sidebar "ORDBMSs: Universal Databases," on page 74, provides an overview of universal databases. Binary large object (BLOB) and character large object (CLOB) data types are just regular SQL data types.

To compare UDB's extended data types (text, audio, video, and image) with plain BLOB data, you need one of the DB2 developer editions. The SDK's programming guides and sample programs are self-explanatory. The extenders are literal extensions of the DB2 engine (see Figure 1), and DBAs control whether a given extender runs inside or outside the database address space. UDB ships with four extenders:

  • Text Extender lets you perform full-text queries against unstructured text documents as large as 2GB, regardless of whether the documents reside in DB2 databases.
  • Image Extender lets you import and export images and their attributes; control access to images with the same protection level as traditional business data; select and update images based on their attributes, such as format, width, and height; and display both miniature and full-size images.
  • Video Extender lets you import and export video clips and their attributes; play video clips; select and update clips based on video attributes, such as compression method, length, frame rate, and number of frames; and use shot detection to retrieve specific video-clip shots.
  • Audio Extender lets you import and export audio clips and their attributes; select and update audio clips based on audio attributes, such as number of channels, length, and sampling rate; and play audio clips.

Extenders define new data types and functions using DB2 UDB's built-in support for user-defined data types (UDDTs) and user-defined functions (UDFs). For example, you can use Text Extender to issue the following SQL Select statement:

SELECT * FROM Emp_Resume
WHERE DB2TX.CONTAINS (
 RESUMEDOC,
   '"DBA"
   IN SAME PARAGRAPH AS "DB2"')

RESUMEDOC is the column that contains the text documents you want to search, and DB2TX.CONTAINS is one of several search functions that Text Extender includes.

Try It
IBM doesn't have Microsoft's or Oracle's presence in the NT database market. Despite its recent NT marketing campaigns, IBM hasn't been as successful as Oracle in reaching the traditional Microsoft developer community. However, IBM's DB2 might surprise you with the technology and resources that it provides. I've been using DB2 since it first appeared in the OS/2 Extended Edition in 1988, and I've found UDB to be both DBA friendly and programmer powerful. I have highlighted only a few of its features in this article, and I urge you to try it yourself. For a list of DB2-related resources, go to the Windows NT Magazine Web site at http://www.winntmag.com.