You can run a whole business on DB2/2, Oracle, or SQL Server. But there are important differences in scalability, support, and reliability.
Barry Nance
If you work with important business information such as customer names, orders, billings, and payments, a relational database management system (RDBMS) is essential. Running an RDBMS on a networked desktop PC puts the data close to the people who use it.
For this review, we looked at the latest versions of three PC-based RDBMSs that have the power and the capabilities needed to run an enterprise: IBM's DB2/2 2.1, Oracle's Oracle 7.1, and Microsoft's SQL Server 6.0.
All three systems offer stored procedures, triggers, and constraints. A stored procedure is a set of processing steps that executes on the database server PC rather tha
n on the client. A trigger is a stored procedure that fires when a specified event (row insertion, deletion, or updating) occurs. A constraint is a business rule in the database that specifies acceptable values or relationships among data fields. Constraints let you enforce referential integrity relationships among your database tables; you can ensure, for example, that no one can delete a customer's account in one table if billing records for that customer still exist in another table.
These are big-league features that, along with higher capacity and performance, help distinguish
these database managers
from such products as Microsoft Access, Lotus Approach, and Borland Paradox.
Stress Test
Overall, we found Oracle to be the best of the three. It scales the most consistently across PCs, minicomputers, and mainframes (see the chart
"Comparing Relational Database Features"
), and it offers more platform choices than DB2 or SQL Serv
er. We recommend Oracle for enterprises with many remote sites of diverse sizes. It is also mature, a market leader, and rich in features.
DB2/2, though it
scales almost as well
as Oracle, doesn't offer you as big a range of choices; for example, the next higher gradation of DB2, which runs on IBM's RS/6000 RISC workstation, has relatively little third-party support. Still, for companies downsizing to client/server and that already have DB2 on a mainframe, DB2/2 is an excellent desktop solution. It's also a good choice for organizations that need the utmost in reliability.
SQL Server runs only on Microsoft Windows NT, which limits its scalability to 486-based PCs through DEC Alpha-based systems and, eventually, PowerPCs. But it offers excellent integration with Visual Basic and with Microsoft and third-party applications.
To gauge reliability, we wrote an "RDBMS killer" program to find out how each product behaves at its saturation point. The multithreaded program, emitt
ing SQL operations to each RDBMS at an ever-increasing rate, ran directly on the database server rather than across the LAN. All three products died during the stress test, as we expected. We wanted to study the failure itself, not measure how long each RDBMS lasted (long before your own database software died, you would have gotten more hardware and scaled your systems upward).
DB2/2 failed gracefully, shutting down and leaving the database undamaged. But when SQL Server and Oracle died, we had to run repair utilities before we could resume using the database.
DB2/2 2.1
IBM continues to make DB2 more consistent across PCs (running OS/2), RS/6000s (AIX), AS/400s (OS/400), and mainframes. IBM has targeted other platforms for DB2 as well, including HP-UX, Solaris, Sinix (Siemens Nixdorf Unix), and Windows NT. But DB2 is not yet one database product with a single code base. Slight differences exist among versions, most notably in the Data Definition Language (DDL) statements
you use to allocate and initialize a new database. The bottom line: You sacrifice some portability with DB2.
We tested DB2/2, the OS/2 version that supports symmetric multiprocessing (SMP). DB2/2 supplies programming interfaces for a wide variety of computer languages, including C, C++, COBOL, and FORTRAN. IBM's own VisualAge and many third-party products (such as Watcom's VX-REXX) are excellent add-on tools you can use with DB2/2.
You can configure DB2/2 to replicate data among a set of database servers. DB2/2 can also work through Distributed Database Connection Services (DDCS) or other middleware products to interface with mainframe DB2 databases. Administration is easy, either locally or remotely, through GUI administration software. For remote sites that don't have their own database administrator, DB2/2 offers remote administration tools. A separate product, Visualizer, lets you build, update, or query your database.
DB2/2 2.1 subjects SQL statements to one of nine levels of optimizati
on just prior to processing those statements. The nine levels, which can be configured by a database administrator or set by application software, allow precise tuning of database response times. You'd use level 0 or 1 for SQL that's already optimized by the programmer. Higher levels let DB2/2 examine and reformat SQL submitted by, for instance, a front-end query tool such as Microsoft Access.
Programs can emit either "dynamic" or "static" SQL to DB2. Dynamic SQL is compiled at run time; it can be a string of text, for instance, that someone types into a program at the command line. In contrast, static SQL consists of statements, embedded directly in the program, that are fully known at compile time. Precompile and postcompile steps, part of a process IBM calls "binding," store the static SQL statements in files with a .BND extension. In general, static SQL executes much faster than dynamic SQL.
Oracle 7.1
Oracle runs on more than 90 platforms (about 60 of which are Unix e
nvironments). Because the database software is essentially the same code for each platform, Oracle is amazingly consistent on different platforms. Moving an Oracle database from one platform to another is a simple matter of using the export and import utilities supplied with the package. Database administration and design are also consistent among platforms. The same DDL for creating an OS/2 Oracle database can also create a NetWare NLM, NT, or Unix one.
Oracle takes advantage of SMP in environments that offer multiple CPUs (OS/2, NT, AIX, SCO). The previous version of Oracle provided only "strict" data replication through two-phase commit or unsynchronized table snapshots. Version 7.1 adds loose, time-delayed replication from a primary database site and optimistic replication that allows any one database site to update without waiting for other sites to catch up. You can also configure Oracle to replicate table updates onto another (remote) Oracle database or a DB2 mainframe database.
Administeri
ng Oracle is easy, since the OS/2 and NT versions come with native GUI tools, but you administer the NetWare NLM version through an over-the-wire Windows interface. Oracle, through a separate product, offers remote administration tools for distant sites that don't have their own database administrators.
Oracle supports the use of triggers, stored procedures, and database constraints through its PL/SQL database server programming environment. It also supports dynamic SQL and, for embedded SQL, a form of static SQL. However, unlike in DB2/2, there is no separate "bind" step the programmer performs during development. And, to improve processing of SQL statements, Oracle uses an optimizer to predigest SQL. (See the sidebar "SQL Compilers: The Performance Nexus" for an explanation of SQL processing in all three products.)
Oracle isn't quite as easy to program as DB2/2, and it doesn't have tight, thread-oriented integration with the host operating system. On the other hand, Oracle enjoys a great deal of
support from third-party software vendors, and SQL*NET is an excellent, multiplatform-oriented SQL delivery mechanism.
SQL Server 6.0
Microsoft originally licensed an OS/2 version of SQL Server from Sybase. But when Microsoft ditched OS/2 for Windows NT, it terminated that business relationship. The latest version of SQL Server continues to look much like the Sybase database management products (which run on Unix platforms), but Microsoft says that it has changed about 60 percent of SQL Server. We found that the underlying architecture has changed considerably from prior versions. The programming interface and configuration process, on the other hand, are similar to earlier versions of both Microsoft SQL Server and the Sybase product.
SQL Server, which now runs only under NT, has a new GUI administration tool called SQL Enterprise Manager (SEM). While previous versions of the RDBMS used a text-mode Interactive SQL (ISQL) interface, this one has GUI database-administration
tools. New features include scrollable cursors, distributed management objects, and extended stored procedures. Before, you coded your SQL Server stored procedures in Microsoft's (Sybase's) Transact-SQL language, and you were limited both in performance and in function to the design of Transact-SQL. Extended stored procedures allow SQL Server to use external programs to handle database events. And, like Oracle, the program uses a cost-based optimizer to examine and reformat SQL statements.
Microsoft has tightly integrated SQL Server into the NT environment. It is consistently the same database manager, from both architectural and user interface viewpoints, on Intel, Mips, or DEC Alpha systems. NT schedules individual SQL Server threads on different CPUs if your database server is an SMP machine.
SQL Server, like DB2/2 and Oracle, can automatically replicate changes onto remote databases, but its replication uses a publish-and-subscribe metaphor. A distribution server hosts a distribution database
, which holds rows from published tables until SQL Server can copy the rows to the databases that subscribe to the published tables. A publication database can define publication tables, and a subscription database can subscribe to those published items. Through SQL Server's SEM interface, replication is particularly easy to set up. However, changes made on a remote SQL Server by means of a remote procedure cannot be rolled back (undone).
SQL Server's tight integration with NT helps make it a secure database, and SEM gives it point-and-click ease of use. But SQL Server isn't yet ready for the enterprise. If you use OS/2 or Macs, you'll have to buy an ODBC connectivity solution from a company other than Microsoft. And because of the rift between Microsoft and Sybase, SQL Server customers who want to mix the Sybase and Microsoft versions of the program will have trouble.
PRODUCT INFORMATION
DB2/2 Client/Server 2.1........
..........$1495
(includes five client licenses;
$85 per additional client)
IBM
Armonk, NY
(800) 342-6672
(914) 765-1900
fax: (313) 225-4020
Oracle 7.1...............................$199 per server
($99 per client)
Oracle
Redwood Shores, CA
(415) 506-7000
fax: (415) 506-7200
SQL Server 6.0...........................$999
(plus $119 for 20 client licenses)
Microsoft
Redmond, WA
(800) 426-9400
(206) 882-8080
fax: (206) 936-7329
Top Three Reasons To Buy...
DB2/2
------------
Supports
faster-executing static SQL
Works
on SNA networks
Is
easy to program
Oracle
------------
Supports
row locking
Scales
in fine gradations from PCs to mainframes
Is
consistent across platforms
SQL Server
------------
Works
well with Vi
sual Basic and shrink-wrapped Windows programs
Has
good Windows NT-based security features
Replication
is easy to set up and administer
DB2/2 Oracle SQL Server
===============================================================================
Price per user $123 $101 $25
(50 users) (1)
Server platforms PC (OS/2), PC (OS/2, NT, NetWare), NT
(with OS) RS/6000 (AIX), assorted Unix, mainframe
AS/400 (OS/400), (MVS, VM)
mainframe
(MVS, VM)
Client DOS, Windows, DOS, Windows, OS/2, DOS, Windows (2)
platforms OS/2, Macintosh Macintosh
RAM (replica- 16 MB 16 MB 32 MB
tion enabled)
Symmetric multi- Y
Y Y
processing
support
Maximum columns 256 256 250
per table
Row locking -- (3) Y -- (3)
Replication Y Y Y
Centralized Y -- (4) Y
management
Referential Y Y Y
integrity
Stored procedures, Y Y Y
triggers, and
constraints
Programming C, C++,COBOL, C, C++, COBOL, C, C++, COBOL,
languages REXX, FORTRAN FORTRAN Visual Basic
Transport IPX, NetBIOS, SNA IPX, NetBIOS, TCP/IP IPX, NetBIOS,
protocols (APPC), TCP/IP TCP/IP
Static SQL Y N N
SNMP alerts Y N N
KEY
=====
Y = yes; N = no
(1) Includes base product plus 49 client licenses
(2) Microsoft does not support OS/2 and Mac clients, but
third-party support is available
(3) Page-level only
(4) Oracle offers separate remote-monitoring utilities
illustration_link (6 Kbytes)
While SQL Server runs only on Windows NT, DB2 scales from PCs up through mainframes. Oracle, which runs on more than 90 platforms, scales especially well.
screen_link (47 Kbytes)
DB2/2 2.1
(top screen)
helps new database administrators by automatically running a user-setup program and sample database-creation software after installation.
Oracle 7.1's Object Manager
(middle screen)
lets you assign specific database privileges to each user.
SQL Server 6.0
(bottom screen)
works closely with NT's built-in security mechanisms, especially its User Manager, which lets you define security levels by group.
Contributing editor Barry Nance is the author of several books, including Using OS/2 Warp and Client/Server LAN Programming (Que, 1994). You can reach him via the Internet at
barryn@bix.com
.
Enterprise Database Managers
