DBMS - November 1996 - Comparison Summary

DBMS

Comparison Summary

By Martin Rennhackkamp
DBMS Server Comparison Supplement, November 1996

An Analysis Of The Strengths And Weaknesses Of The Big Six Database Servers.

In 1991 I performed a thorough evaluation and comparison of the four major DBMSs at the time: Informix, Ingres, Oracle, and Sybase. This comparison was done for a client building a huge distributed database application, currently in its second phase of d evelopment, with the first phase running successfully country-wide. At that stage, the distinguishing criteria were query optimizers, triggers, views, and support for distributed databases. Some products had these features, but some others' marketing per sonnel were just talking about them. For example, declarative integrity was a "future" that was at that stage only being phased into most of the DBMS products. It was relatively straightforward to draw up a checklist and fill it in with "yes" and "no" in the various columns.

Now, a mere five years later, these aspects - as well as many new features such as Internet support, remote procedure calls, and support for multiprocessor platforms - are standard items on each product's fact sheet and marketing material. On a very high level, DBMS products are becoming such commodity items in IT shops that it is almost possible to take a one-size-fits-all approach. On a detailed level, however, you must do a much more thorough evaluation. The products are evolving at such a fast pace, and their research and development teams are adding new features at such a rate, that simple yes/no entries in the comparison columns are just not enough. All of the comparisons would be similar, with mostly yes entries in each column and the odd no mar ked with an asterisk referring to a footnote saying, "Scheduled for the next release, currently in beta testing." If you want to do a thorough comparison today, you have to study each feature and check the extent to which it is implemented and the qualit y of the implementation. To explain further, I review a few of the features in this comparison supplement.

Relational Data Model

Although the DBMS products reviewed here can rightfully claim to be relational, their support of the relational data model must be scrutinized closely. Obviously they all support the basic relational concepts, such as data stored in tables and accessed b y high-level set operations, mostly through SQL. However, not one of the Big Six supports domains. It is crucial that these products address this fundamental concept of the relational data model. You must be able to define your domains and then specify y our table columns, and preferably also your stored procedure variables and parameters, in terms of the domains. This process is necessary to ensure tight type-checking, as you can do in some programming languages. If a "small" (in terms of market share) product such as Interbase could implement domains five years ago, surely the Big Six should be able to "get it right" today.

All of the Big Six can rightfully claim that they support declarative integrity constraints. Except for domain constraints, they all support declarative key, column, and referential-integrity constraints. But you must investigate these claims closely as well. Only Informix and Oracle support cascading delete as a referential-integrity constraint violation option, and only DB2 supports the full compliment of set null, cascade, and no action as prescribed by the ANSI SQL-92 standard. You should also check how these constraints are implemented. Most of these products use quite crude mechanisms to implement the constraints. For example, most of them create "hidden" indexes to implement primary-key and unique constraints. In addition, DB2, Informix, and Ora cle restrict you from creating an additional unique index on a column that has already been indexed "behind the scenes" for a unique constraint.

Database Objects

All of the products in this comparison supplement support binary large objects (BLOBs), which you can use to store images, text, documents, voice and sound recordings, and any other unstructured data. However, you must consider carefully how the BLOBs ar e processed by the front-end tools and languages you use to develop applications. Not all front ends and languages can easily handle these tricky data types. With some languages, such as C and C++, using a SQL preprocessor, you must process a text BLOB a s a series of text segments. Some fourth-generation development environments may not be able to process the BLOBs returned from your database server at all.

You should also check carefully how triggers are implemented, which is an important factor. Some products use their trigger mechanisms behind the scenes to implement declarative integrity constraints. It is even more important if you want to implement yo ur business rules using triggers. For example, CA-OpenIngres only has row-based triggers (called rules) that fire after the triggering operations, but you can have any number of triggers per table, each with its own name. Therefore, you can implement you r business rules in a very modular fashion. DB2, Informix, and Oracle, on the other hand, have set-based and row-based triggers, which can fire before and after the triggering operation, but you can only have one trigger per firing condition, which means that you must sometimes integrate unrelated business logic into single triggers. However, you can improve the situation by calling separate stored procedures for each business processing function.

Queries

The level of locking provided by the various DBMS products has long been a contentious issue, quite heavily exploited in some products' marketing material to downplay their opponents in the license-hunting game. There is no clear guideline as to whether row-level locking is generally better than page-level locking; the more suitable type of locking depends on the application and its throughput and isolation requirements. It is, however, extremely important to determine the concurrency control requiremen ts of your applications (preferably not in the presence of a particular product's salesperson) and to check whether the proposed product can satisfy your needs.

All of the DBMS products evaluated in this supplement claim to support the Entry Level ANSI SQL-92 standard. It is important to remember that Entry Level really covers only the most basic database-definition and database-manipulation operations. The more important and distinguishing aspects are covered in the Intermediate and Full levels of the standard. In addition, the standard was published four years ago, and some of its contents were already known long before that. It is high time that we, the cons umers, put pressure on the suppliers to provide more of the standard's additional levels. Fortunately, the rivalry among the various DBMS products will soon help us in this process.

An outer join operation is used more generally than most people think. It is therefore extremely important to check that the DBMS product supports the necessary types of outer joins in which you may be interested, such as full, left, and right outer join . It is also important to check the syntax used for the outer join operation. Although all of the products have some variation of the outer join operation, only CA-OpenIngres supports the correct ANSI SQL-92 standard syntax (the outer join is specified e xplicitly in the FROM clause of the SELECT statement). [1] All of the other products use some variation of the well-known Oracle syntax (the outer join is specified using some additional symbols such as an aster isk or plus sign in the join conditions in the WHERE clause).

Connectivity and Distribution

The distributed database support offered by the Big Six also varies considerably. Some products, such as CA-OpenIngres, DB2, and Sybase, offer a multidatabase shell through which you access remote tables. When you access data stored in multiple databases in the same transaction, these three products automatically apply the two-phase commit (2PC) protocol for you. Other products let you access remote data in a transaction and give you the facilities to implement the 2PC protocol programmatically. However , you still must code the whole protocol in your application.

Some products offer remote procedure calls (RPCs) in addition to their other distributed database products. The RPCs let you access the data in remote databases as if it were local. For example, a stored procedure called by an application can do an integ rity check on a remote database, without the application being aware of it. Sybase, in particular, has a very powerful RPC implementation.

Replication

Replication is such an involved topic that it warrants an investigation of its own. There are many issues to consider when evaluating replication products, such as the topologies supported and the replication product's architecture, functionality, manage ment and monitoring tools, and, most important, its throughput. If a replicator cannot give you the required transaction rate for the target topology, it simply will not work. It is also important to check whether it can replicate transactions in a full bidirectional, peer-to-peer configuration. This is important for fully replicated databases and even for hot standby configurations. For example, of the Big Six, only DB2, CA-OpenIngres, Oracle, and Sybase can be configured to replicate transactions in a full peer-to-peer configuration. Another issue is whether the DBMS can replicate to and from other DBMSs in a heterogeneous DBMS configuration. Most products can replicate to different databases through their gateway products, but only a select few, suc h as Sybase's Replication Server (through its Enterprise Connect middleware), can replicate to and from diverse sources.

Internet

Although all six of the DBMS products reviewed support the Internet and the World Wide Web in some way or another, they can basically be partitioned into two broad classes: script-based and server-based. Products such as Informix, CA-OpenIngres, and Syba se fall into the script-based class, in which you can include SQL operations or Perl scripts in your HTML Web pages or CGI scripts in order to access a specified database when the Web page is activated. The scripts must ensure that the data is returned i n an HTML-readable form. Products such as DB2, Oracle, and Microsoft SQL Server fall into the server-based class, in which a dedicated server process acts as a gateway to the designated databases to retrieve the required data and return it in an HTML-rea dable form. In addition, some of these products include tools to help you develop Web-oriented applications, such as the SQL Server Web Assistant that ships with Microsoft SQL Server.

Horses for Courses

Each of the DBMSs reviewed in this supplement has excellent features in some areas while lacking them in other areas. As the products keep leapfrogging over one another in the features race, there is never a clear, long-term winner or loser. The importan t thing to do when evaluating DBMSs is to determine your own set of requirements clearly, assign weights of importance to them, and compare the different products with respect to those requirements. It is important to have a clear and precise definition of your own requirements when comparing DBMSs. Read analysts' reports and comparison supplements such as this one, because they give you good pointers to the various products' strong and weak areas. Only you alone, however, can ensure that the product wi ll satisfy your own needs. Although each analyst or reviewer has his or her own interests and points of reference, your point of reference is what makes up your requirements.

You should bring many additional factors into the equation, such as cost, licensing, support costs, support availability, level of the support skills, and the availability of development skills, third-party tools, packaged solutions, and user groups wher e you can exchange ideas. It is also a good idea to discuss the products on your short list with a few of the active users and developers. If at all possible, get your references from the user community or a local user group. As you might expect, the ven dor of the DBMS product is bound to give you only the best success stories as references. It's useful to hear these, but for a realistic overall picture you must communicate with people who continue to use the product productively even though they have e xperienced a few problems.

The last important point to consider when shopping for a DBMS is that you are buying into a group of people. The group includes the sales manager of your local supplier assigned to your account, the technical support manager, the technical support staff assigned to your account, and the local branch manager, as well as the available training skills and the communication channel to the company's head office, top support and development staff, and directors. The more remote you are, the more important the communication channels are. I've seen some 16th-century turnaround times -I even encountered them when making requests for information and products for this comparison (names and addresses withheld to protect the guilty). You must feel comfortable with the way your DBMS supplier conducts business and supports your company's requirements, and the supplier must be able to deliver the service you require. If your IT director and the local branch manager can solve problems on the 19th hole, or if your "tec hies" can discuss optimization problems with their support guys on the squash court, be my guest! But the real question is how your DBMS supplier will help get the database server back online at 3:47 on a Saturday afternoon when the big game is on or, ev en more important, when it crashes at 3 p.m. on Friday the 25th while you do the payroll run.

Martin Rennhackkamp is the owner and principal consultant of The Data Base Approach, a corporation specializing in relational and distributed databases, based in Cape Town, South Africa. You can email Martin at mr@dba.co.za.

COMPARISON
CRITERIA ORACLE7
VERSION 7.3 SYBASE SQL
SERVER 11 INFORMIX-ONLINE 7.2 MICROSOFT SQL
SERVER 6.5 IBM
DB2 2.1.1 CA-OPENINGRES 1.2
Relational Data Model
Domains No No No No No No
Referential-integrity violation options Restrict, except cascading delete Restrict only Restrict, except cascading delete Restrict Restrict, cascade, set null Restrict only
Tailor referential messages No No No No No No
Referential WHERE clauses No No No No No No
Updateable views (with check option) Yes Yes Yes Yes Yes, including union views Yes
Database Objects
User-defined data types Yes Yes No Yes Yes Yes
BLOBs Yes Yes Yes Yes Yes Yes
Additional data types Image, video, text, messaging, spatial data types Binary, image, money, bit, text, varbinary Byte, text up to 2GB Large object Byte, byte varying, long byte, long varchar, money, spat ial data types
Table structures Heap, clustered Heap, clustered No choice No choice No choice B-tree, hash, heap, ISAM
Index structures B-tree, bitmap, hash B-tree B+ tree, clustered Clustered Clustered B-tree, hash, ISAM
Tuning facilities Table and index allocation Index pre-fetch, I/O buffer cache, block size, table partitioning Extents, table fragmentation by expression or round robin Fill factors, allocation Table and index allocation, cluster ratio, cluster factor Fill factors, pre-allocation, table and index allocation
Triggers
Level Row-based, set-based Set-based Row-based, set-based Set-based Row-based, set-based Row-based
Timing Before, after After Before, for each, after After Before, after After
Nesting Yes Yes Yes Yes Yes Yes
Stored procedures
Language PL/SQL Transact-SQL SPL Transact-SQL SQL & 3GL SQL-like
Nesting Yes Yes Yes Yes Yes Yes
Cursors Yes Yes Yes Yes Yes No
External calls RPC RPC System calls System calls Yes No (database events)
Events Yes Time-based events No No User-defined functions Database event alerters
Queries
Locking level Table, row Table, page Database, table, page, row Database, table, page, row Database, table, page, row Database, table, page
ANSI SQL compliance Entry-Level SQL-92 Entry-Level SQL-92 Entry-Level SQL-92 Entry-Level SQL-92 Entry-Level SQL-92 Entry-Level SQL-92
Cursors Forward Forward Forward, backward Forward, backward, relative, absolute Forward Forward
Outer joins Yes Yes Yes Yes Yes Yes
ANSI syntax No No No Yes[2] No Yes
APIs ODBC DBLIB, DTLIB, ODBC ESQL, TP/XA, CLI, ODBC ESQL, DBLIB, Distributed Management Objects, ODBC ESQL, ODBC ESQL, TP/XA, ODBC
Database Administration
Tools Oracle Enterprise Manager, Performance Pack Sybase SQL Manager, SQL Monitor SMI, DB/Cockpit, OnPerf Enterprise Manager, Performance Monitor Database Director, Visual Explain, Performance Monitor IPM, VisualDBA, IMA
SNMP support Yes Yes No Yes Yes Yes
Security C2(Trusted Oracle C2 C2, B1 (OnLine/Secure) NT integrated Three levels C2
Partial backup and recovery Configurable Configurable No Per-table Yes Per-table
Internet
Internet support Oracle WebServer web.sql ESQL or 4GL CGI Interface Kit Internet Information Server in Windows NT DB2 WWW Connection CA-OpenIngres/ICE
Connectivity, Middleware, and Distribution
Gateways to other DBMSs Any MVVS data source through EDA/SQL (Adabas, IDMS, IMS, SQL/DS, VSAM), any APPC data source, AS/400, DRDA, DB2, TurboImage, Sybase, Rdb, RMS, Informix, CA-Ingres, SQL Server, Teradata Adabas, A S/400, DB2, IDMS, IMS, Informix, Ingres, ISAM, Microsoft SQL Server, Oracle, Rdb, RMS, sequential files, SQL/DS, Sybase SQL Server, Teradata, VSAM Oracle, Sybase, IMS, DB2 No Oracle, Sybase, Informix, MS SQL Server DB2, Datacom, IMS, IDMS, VS AM, Oracle, RDB, Allbase, Informix, Oracle, Sybase
Distributed databases Part of base product OmniConnect Online server No DataJoiner CA-OpenIngres/Star
2PC protocol Yes Yes Yes, presumed abort N/A Yes Yes, automatic
Hetergeneous Through gateways Through DirectConnect No No Through DataJoiner Through gateways
Optimization Yes Yes Yes No Yes Yes
RPC Yes Yes No Yes No No
Replication
Recording Replication log/triggers Log buffer Log Log Log Rules (triggers)
Hot standby Yes Yes Yes Yes Yes Yes
Peer-to-peer Yes Yes No No Yes Yes
To other DBMSs Through gateways Through DirectConnect No Through ODBC Through DataJoiner Through gateways
Cascading Yes Yes No No No Yes
Additional
Restrictions
Name lengths 30 30 18 30 18 32
Columns 254 250 2767 250 255 300
Column size 2GB 1962 32,767 255 4005, except LOB 2008, except BLOB 2GB
Tables N/A 2 billion 477 million 2 billion Storage dependent N/A
Table size N/A storage dependent 64 terabytes 2 terabytes 64GB N/A
Table width By column storage dependent 32,767 2048 Storage dependent 2008, except BLOB
Platforms (operating system) Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh Most Unix systems, Windows NT, Windows 95 Windo ws NT Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh Most Unix systems, Windows NT, VAX VMS, Windows 95 (CA-OpenIngres/Desktop)

COMPARISON CRITERIA	ORACLE7 VERSION 7.3	SYBASE SQL SERVER 11	INFORMIX-ONLINE 7.2	MICROSOFT SQL SERVER 6.5	IBM DB2 2.1.1	CA-OPENINGRES 1.2
Relational Data Model
Domains	No	No	No	No	No	No
Referential-integrity violation options	Restrict, except cascading delete	Restrict only	Restrict, except cascading delete	Restrict	Restrict, cascade, set null	Restrict only
Tailor referential messages	No	No	No	No	No	No
Referential WHERE clauses	No	No	No	No	No	No
Updateable views (with check option)	Yes	Yes	Yes	Yes	Yes, including union views	Yes
Database Objects
User-defined data types	Yes	Yes	No	Yes	Yes	Yes
BLOBs	Yes	Yes	Yes	Yes	Yes	Yes
Additional data types	Image, video, text, messaging, spatial data types	Binary, image, money, bit, text, varbinary	Byte, text up to 2GB		Large object	Byte, byte varying, long byte, long varchar, money, spat ial data types
Table structures	Heap, clustered	Heap, clustered	No choice	No choice	No choice	B-tree, hash, heap, ISAM
Index structures	B-tree, bitmap, hash	B-tree	B+ tree, clustered	Clustered	Clustered	B-tree, hash, ISAM
Tuning facilities	Table and index allocation	Index pre-fetch, I/O buffer cache, block size, table partitioning	Extents, table fragmentation by expression or round robin	Fill factors, allocation	Table and index allocation, cluster ratio, cluster factor	Fill factors, pre-allocation, table and index allocation
Triggers
Level	Row-based, set-based	Set-based	Row-based, set-based	Set-based	Row-based, set-based	Row-based
Timing	Before, after	After	Before, for each, after	After	Before, after	After
Nesting	Yes	Yes	Yes	Yes	Yes	Yes
Stored procedures
Language	PL/SQL	Transact-SQL	SPL	Transact-SQL	SQL & 3GL	SQL-like
Nesting	Yes	Yes	Yes	Yes	Yes	Yes
Cursors	Yes	Yes	Yes	Yes	Yes	No
External calls	RPC	RPC	System calls	System calls	Yes	No (database events)
Events	Yes	Time-based events	No	No	User-defined functions	Database event alerters
Queries
Locking level	Table, row	Table, page	Database, table, page, row	Database, table, page, row	Database, table, page, row	Database, table, page
ANSI SQL compliance	Entry-Level SQL-92	Entry-Level SQL-92	Entry-Level SQL-92	Entry-Level SQL-92	Entry-Level SQL-92	Entry-Level SQL-92
Cursors	Forward	Forward	Forward, backward	Forward, backward, relative, absolute	Forward	Forward
Outer joins	Yes	Yes	Yes	Yes	Yes	Yes
ANSI syntax	No	No	No	Yes[2]	No	Yes
APIs	ODBC	DBLIB, DTLIB, ODBC	ESQL, TP/XA, CLI, ODBC	ESQL, DBLIB, Distributed Management Objects, ODBC	ESQL, ODBC	ESQL, TP/XA, ODBC
Database Administration
Tools	Oracle Enterprise Manager, Performance Pack	Sybase SQL Manager, SQL Monitor	SMI, DB/Cockpit, OnPerf	Enterprise Manager, Performance Monitor	Database Director, Visual Explain, Performance Monitor	IPM, VisualDBA, IMA
SNMP support	Yes	Yes	No	Yes	Yes	Yes
Security	C2(Trusted Oracle	C2	C2, B1 (OnLine/Secure)	NT integrated	Three levels	C2
Partial backup and recovery	Configurable	Configurable	No	Per-table	Yes	Per-table
Internet
Internet support	Oracle WebServer	web.sql	ESQL or 4GL CGI Interface Kit	Internet Information Server in Windows NT	DB2 WWW Connection	CA-OpenIngres/ICE
Connectivity, Middleware, and Distribution
Gateways to other DBMSs	Any MVVS data source through EDA/SQL (Adabas, IDMS, IMS, SQL/DS, VSAM), any APPC data source, AS/400, DRDA, DB2, TurboImage, Sybase, Rdb, RMS, Informix, CA-Ingres, SQL Server, Teradata	Adabas, A S/400, DB2, IDMS, IMS, Informix, Ingres, ISAM, Microsoft SQL Server, Oracle, Rdb, RMS, sequential files, SQL/DS, Sybase SQL Server, Teradata, VSAM	Oracle, Sybase, IMS, DB2	No	Oracle, Sybase, Informix, MS SQL Server	DB2, Datacom, IMS, IDMS, VS AM, Oracle, RDB, Allbase, Informix, Oracle, Sybase
Distributed databases	Part of base product	OmniConnect	Online server	No	DataJoiner	CA-OpenIngres/Star
2PC protocol	Yes	Yes	Yes, presumed abort	N/A	Yes	Yes, automatic
Hetergeneous	Through gateways	Through DirectConnect	No	No	Through DataJoiner	Through gateways
Optimization	Yes	Yes	Yes	No	Yes	Yes
RPC	Yes	Yes	No	Yes	No	No
Replication
Recording	Replication log/triggers	Log buffer	Log	Log	Log	Rules (triggers)
Hot standby	Yes	Yes	Yes	Yes	Yes	Yes
Peer-to-peer	Yes	Yes	No	No	Yes	Yes
To other DBMSs	Through gateways	Through DirectConnect	No	Through ODBC	Through DataJoiner	Through gateways
Cascading	Yes	Yes	No	No	No	Yes
Additional
Restrictions
Name lengths	30	30	18	30	18	32
Columns	254	250	2767	250	255	300
Column size	2GB	1962	32,767	255	4005, except LOB	2008, except BLOB 2GB
Tables	N/A	2 billion	477 million	2 billion	Storage dependent	N/A
Table size	N/A	storage dependent	64 terabytes	2 terabytes	64GB	N/A
Table width	By column	storage dependent	32,767	2048	Storage dependent	2008, except BLOB
Platforms (operating system)	Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh	Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh	Most Unix systems, Windows NT, Windows 95	Windo ws NT	Most Unix systems, Windows NT, VAX VMS, Windows 95, OS/2, Macintosh	Most Unix systems, Windows NT, VAX VMS, Windows 95 (CA-OpenIngres/Desktop)

^[1,2] Correction: Microsoft SQL Server 6.5 supports the ANSI outer join syntax. The chart printed in DBMS indicates "No" but this online chart is correct.

Choose for yourself by using the blank comparison criteria form available for download in Excel spreadsheet format from our Web page at http://www.dbmsmag.com.

Please send questions or comments to dbms@mfi.com
Updated Friday, November 1, 1996