Technology / Comparison Of Rdbms

Comparison Of Rdbms

This essay Comparison Of Rdbms is available for you on Essays24.com! Search Term Papers, College Essay Examples and Free Essays on Essays24.com - full papers database.

Autor:  anton  01 September 2010
Tags:  Comparison
Words: 25775   |   Pages: 104
Views: 823

IMPLEMENTATION OF AN OPEN SYSTEMS APPLICATION ENVIRONMENT

RDBMS COMPARATIVE ANALYSIS

BACKGROUND AND OVERVIEW

The Defense Finance and Accounting Services Financial Systems Organization (DFAS) plans to establish a standard development and operational environment for DFAS applications running on workstations, network servers, and mid-tier platforms. To accomplish this, the Government has commissioned a study to 1) evaluate currently used development environments and industry standards and 2) perform an analysis of these alternative development environment; identifying the most effective solutions for DFAS.

A key component of this planned Open Systems Application Environment (OSAE) is a Relational Database Management System (RDBMS) that will allow portability and salability across multiple platforms. This report provides a comparative analysis of three leading RDBMSs including Oracle, DB2, and SYBASE System 10. In addition, it contains recommendations for RDBMS standardization within DFAS.

OPEN SYSTEMS APPLICATION ENVIRONMENT REQUIREMENTS

In it’s quest to achieve an open systems application environment, the Defense Finance and Accounting Services Financial Systems Organization has defined a set of requirements that the selected development environment must meet. In particular, the development environment must meet industry standards as well as DFAS specific requirements. Following is a listing of key industry standards and DFAS defined requirements.

KEY INDUSTRY STANDARDS

Data access language standards are key in the RDBMS industry. The American National Institute (ANSI) has adopted SQL as the standard language for relational database management systems as defined in document ANSI X3.135-189 “Database Language SQL with Integrity Enhancement” or ANSI SQL89. In addition, the International Standards Organization (ISO) in the document ISO 9075-1989 “Database Language SQL with Integrity Enhancement” and the United States Federal Government in the Federal Information Processing Standard Publication (FIPS PUB) 127 have also adopted SQL as the standard RDBMS access language. ANSI has also adopted a standard for Embedded SQL as defined in the document ANSI X3.168-189 “Database Language Embedded SQL”.

The current SQL standard, ANSI SQL-92 is defined in FIPS PUB 127-2, ANSI X3.135-1992, and ISO 9075:1992.

DFAS DEFINED RDBMS REQUIREMENTS

Following is an overview of DFAS requirements for the RDBMS component of the Open Systems Application Environment.

• The selected database must have full ANSI SQL Compliance

• The RDBMS must be easily transported to the Oracle RDBMS which is currently in use on the DFAS mid-tier systems.

• The RDBMS must provide salability from the workstation, Netware server, mid-tier, to the mainframe platforms.

OVERVIEW OF FINDINGS AND CONCLUSIONS

After careful review of the Government’s requirements, in conjunction with a thorough evaluation of three RDBMSs, it is the evaluator’s recommendation that DFAS standardize on the Oracle RDBMS. Key factors affecting the recommendation include Oracle’s industry proven open and scalable architecture and it’s superior ability to integrate into the current DFAS environment.

The other two RDBMSs that were evaluated are IBM’s DB2 and SYBASE’s System 10. Though these RDBMS provided several capabilities that can meet the Government’s needs, Oracle provided a far more comprehensive and superior solution.

Below is an brief overview of the three RDBMSs reviewed, followed by a comparative analysis.

RDBMS OVERVIEWS

ORACLE7 RDBMS

The Oracle7 Server is a relational database management system that enables true enterprise data management from the desktop to the data center. Oracle7’s scalable, reliable, integrated server architecture dynamically adapts to exploit uniprocessor and parallel hardware, to deliver unprecedented performance, salability, and availability providing unmatched price performance. Flexible, integrated, manageable distributed database facilities facilitate the deployment of practical distributed solutions that meet specific business needs. Oracle7 includes all the facilities necessary to construct enterprise-class applications, assure end-to-end user and data security, and comprehensively manage the Oracle environment.

For high-performance transaction processing, Oracle7 employs an integrated, multithreaded, multiserver architecture to handle thousands of simultaneous user requests. Through its adaptable parallel architecture, Oracle ensures optimal performance, salability, and availability facilitating organizational growth and data access predictability.

The sophisticated data concurrency control model of the Oracle7 Server ensures the powerful support for a multitude of processing requirements including, but not limited to On-line Transaction Processing and Decision Support, thus meeting specific and varied application requirements.

Distributed database query and transaction processing requirements are also supported with the Oracle7 Server. Developers and users can treat a physically distributed database as a single logical database though the transparent distributed capabilities of the Oracle7 Server, enabling the enterprise flexibility in the placement of its data. In addition, Oracle7 allows organizations to transparently replicate commonly used data to multiple nodes offering further flexibility in the placement and use of the enterprise’s critical information. Oracle7’s Open Gateway architecture ensures the smooth transition from legacy systems by enabling transparent data access during the migration process.

Oracle7 also enables the enforcement of complex business rules through the storage of powerful PL/SQL procedures and triggers right in the Oracle7 Server. Stored procedures and triggers increase application reliability and reduce development costs by allowing common procedures to be developed and maintained in a central location.

Additional features of the Oracle7 Server include extensive National Language Support (NLS) of 8- and 16-bit languages, encompassing virtually all European and Asian languages. Further, Oracle7 delivers an advanced security architecture based on roles and privileges, significantly reducing the burden and cost of security management.

As an open, integrated, relational system Oracle7 allows for efficient and dynamic operations of active and evolving applications.

In addition to the above features, Oracle also provides a variety of additional enabling technologies that support a variety of specialized information system requirements. For text management and thematically based text search and retrieval, Oracle offers the Oracle TextServer and Context. For the management of spatialtemporal data, typical of GIS systems, the Oracle Spatial Data Option is available. Further, user’s multimedia requirements can be met through the use of Oracle’s Media Server technologies. And for environments that are tapping into the World Wide Web, Oracle provides the powerful and unparalleled Oracle WebServer.

IBM DB2

DB2 technology is now available in an entire family of products that run on all major

IBM platforms, including MVS/ESA, AIX/6000, OS/2, OS/400, VM and VSE. Many DB2

products also run on non-IBM platforms: HP-UX clients and servers for Hewlett Packard's UNIX

environment; Solaris Operating Environment clients for Sun systems. Additional ports of DB2 for AIX/6000 code are planned.

Version enhancements to DB2 provide a better platform than ever before for application development and data management in an open client/server environment.

DB2's functional enhancements conform to Open Blueprint and move closer to the ANSI/ISO 1992

SQL standard.

DB2 provides support for large networks supporting a maximum of 25,000 users per DB2 system.

DB2 allows information systems personnel to better control their distributed threads by allowing them to assign a different dispatching priority to each distributed application.

DB2 Performance is bolstered via new functions and major enhancements to some existing functions. For System/390 Parallel Sysplex users, new data-sharing capabilities take full advantage of sysplex power. Applications running on more than one DB2 subsystem can read from and write to the same set of data concurrently. A single catalog and directory serve all the DB2 subsystems, and you can run the same application on more than one DB2 subsystem. You can grow the system incrementally and achieve higher transaction rates than are possible on a single DB2 subsystem.

Data sharing protects your investment in current applications, because existing SQL interfaces

and attachments remain intact. Developers don't need to modify applications to share data. And if one DB2 subsystem needs to come down, users can access their DB2 data from another subsystem.

DB2 now includes stored procedures. This new programmer-friendly function introduces a SQL interface that allows SQL requesters to invoke user-written programs, or stored procedures, at a DB2 server. Local DB2 applications or remote Distributed Relational Database Architecture (DRDA) applications can issue the new SQL CALL statement to invoke a stored procedure. A single send/receive operation invokes a series of SQL statements in the stored procedure, thus decreasing the costs of distributed SQL statement processing.

In enhancements to the existing partition independence function, of DB2 improves concurrent access to logical partitions of a nonpartitioned index by utilities and SQL applications. To further enhance performance, it extends parallel processing to CPU-intensive queries. A single query can generate several tasks that are performed on data in parallel.

DB2 provides a REORG TABLESPACE utility providing for improved performance and the ability to reclaim space by reorganizing the catalog and indexes.

To maximize the accessibility of DB2 provides new availability features. DB2 further enhances availability through improvements in partition independence.

SYBASE SQL SERVER 10

The SYBASE SQL Server supports mission-critical on-line transaction processing (OLTP)

applications. SYBASE SQL Server Release 10 builds on the SYBASE SQL Server and adds

advanced new features to help organizations further reduce costs and improve competitiveness. In

addition, the SQL Server's reliability has just been enhanced by the release of Backup Server, the newly designed backup system that helps companies manage backup and restore of databases and logs for SQL Server 10.

SYBASE SQL Server is more than an industry-leading RDBMS for on-line applications - it's also provides features that enable client/server computing. It's the foundation of the SYBASE Enterprise Client/Server Architecture, a modular, integrated approach to building enterprise-wide information systems. This foundation is enhanced in Version 10 with support for the X/Open X/A protocol, which provides standards-based distributed transaction management. SQL Server technology simplifies, controls, and supports open, standards-based connectivity, making it easy to build flexible, productive applications.

The SYBASE Enterprise Client/Server Architecture is a software framework to help organizations

develop and build an, enterprise-wide information system. SYBASE System 10 products support the SYBASE Enterprise Client/Server Architecture with:

SYBASE servers for a distributed system, including:

- SQL Server, RDBMS

- Navigation Server, for the management of terabyte databases with thousands of users and or transactions

-Replication Server, for building , highly available distributed systems

SYBASE Open Interoperability products, including the OmniSQL Gateway and Open

Client/Server Interfaces, which provide location-transparent interoperability among a range of RDBMSs and native file systems

SYBASE System Management family of products, to provide mainframe-class control of data

and information in a distributed environment

SYBASE Client/Server Tools, for an application development environment that helps

businesses create, use, and manage a wide variety of applications

The SYBASE SQL Server RDBMS is available on a variety of systems: from personal computers to minicomputers and workstations, to symmetric multiprocessor (SMP) systems.

SQL Server helps organizations with connectivity, with full and open standards compliance and

guaranteed interoperability with standards-compliant components including X/A-compliant TP

monitors. SYBASE lets developers write against the standard application programming interface (API) of their choice (such as Microsoft's ODBC) and then run the applications against SQL Server.

SYBASE provided server-enforced integrity in SQL Sever with stored procedures and triggers. Version 10 enhances this feature with ANSI SQL standard declarative referential integrity. The new declarative features can be mixed in any combination with procedural (trigger-based) integrity mechanisms.

COMPARATIVE ANALYSIS

This section will compare how each of the three RDBMSs comply with key industry standards and how they address DFAS’ OSAE RDBMS and requirements.

KEY INDUSTRY STANDARDS

Of the three RDBMSs reviewed, only Oracle is fully ANSI SQL89 compliant as well as ANSI SQL92 Entry Level compliant. Looking at the facts, ANSI standards reflect a basic foundation upon which advanced functionality may be carefully added. While standards frequently represent a less glamorous side of database technology, compliance provides assurance that vendors have invested in the building blocks to ensure a solid baseline from which to launch more innovative features.

RDBMS REQUIREMENTS FOR THE OSAE

The Selected Database Must Have Full ANSI SQL Compliance

As noted above, all three RDBMSs reviewed are fully ANSI SQL89 compliant, however, only Oracle is ANSI SQL92 Entry Level compliant and has also implemented many key features of the Intermediate Level.

The RDBMS Must Be Easily Transported To The Oracle RDBMS Which Is Currently In Use On The DFAS Mid-Tier Systems

Since Oracle is currently installed on the Mid-Tier Systems at DFAS it follows that any migration of systems would be most easily achieved using the same RDBMS, which is Oracle7.

The RDBMS Must Provide Salability From The Workstation, Netware Server, Mid-Tier, To The Mainframe Platforms

While DB2 is available on a variety of platforms, a key problem with the product is that it has inconsistent codelines. DB2/6000 is written in C and ported to AIX from OS/2, whereas DB2/MVS is written in 370 assembler and PLS. DB2/6000 V2 is not functionally equivalent to DB2/MVS. DB2 applications, DB2 programmer/administrative skill sets are only painfully portable between the DB2 family. Scaling from the PC to the mainframe, thus is a complex and cumbersome task if DB2 is the RDBMS being used.

The SYBASE architecture has been proven not to scale beyond four processors. Until SYBASE re-architects the core engine, customers are limited in their use of increasingly popular symmetric multiprocessor hardware.

Oracle7 is identical on all platforms. Oracle truly has the same look and feel, functionality, and usage between all products on all platforms. Oracle applications, programmer skills, and database administration tasks are easily portable across all 80 plus platforms on which Oracle products run. The Oracle RDBMS ports to PC, Mid-tier Server, SMPs, mainframes, and massively parallel processors (MPPs). As such, the Government can grow the Oracle database as it’s database requirements grow.

COMPARATIVE PRODUCT MATRIX

Administration

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Administration Tool * Serv. * Control

Backup, On-line "hot" Mgr. Srv.

Backup, Parallel * yes *

Bit-Mapped Monitoring Tool * *

* New * New

Distributed Database Admin. * New +

Recovery from System Failure * o

Recovery, On-line (Concurrent) * o

Recovery, Parallel * New +

Recovery, Point-in-Time * o

IBM

DB2/6000

v2.0

* DataHub

*

* PE

+

* limited

*

*

* PE

*

Alerts

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Product or Option * included * Open Server

Alerts * *

Non-Polling Alerts * +

Transaction-based * +

IBM

DB2/6000

v2.0

+ -

+ planned

+

+

Architecture

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Client Multi-Server * * VSA

Multithreaded Server * *

Multi-User Database * *

Raw Devices, Support for Unix * *

Sharable Cached Data * *

Shared Dynamic SQL * +

Shared Static SQL * o

IBM

DB2/6000

v2.0

*

*

*

*

*

+

o

Architecture, SMP

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Automatic Load Balancing * +

Scalable SMP Performance * o VSA

IBM

DB2/6000

v2.0

*

*

Architecture, Loosely Coupled

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Product or Option * Parallel * Navigation

Loosely Coupled Architectures Serv. *

Scalable Performance * *

IBM

DB2/6000

v2.0

+ -

+

+

Bulk Data Loading

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Utility or Option * SQL Loader * BCP

Data Loader * *

Direct Writes from Database * +

Flexible Format Controls * o

Incremental (table w/data) * +

Indexes Maintained During * o

IBM

DB2/6000

v2.0

+ -

o

+ -

+

+ -

+ -

Bulk Load

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Parallel Direct Path Loads * New +

IBM

DB2/6000

v2.0

* PE

Concurrency Control (reference locking)

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Deadlock Resolution * *

Default Trans. Isolation Level * ST * CS

Select for Update * * New

Committed Read (CR) + +

Cursor Stability (CS) + o

Dirty Read (DR) + +

Repeated Read (RR) + o

Statement. Consistency (ST) o +

Versioning + +

IBM

DB2/6000

v2.0

*

* RR/CS/DR

*

+

o

o

o

+

+

Consistency

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Distrib. (multi-site) Consistency * +

Multi-Stmt. Consistent Read * +

Multi-Version Read Consistency * +

IBM

DB2/6000

v2.0

+

+

+

Data Dictionary

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Active Data Dictionary * *

Can Query Data Dictionary * *

FIPS, Basic Info. Schema + +

References to Remote Objects o o

Synonyms o o New

Synonyms, Public o o New

IBM

DB2/6000

v2.0

*

*

+

+ -

+ -

+ -

Datatypes

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Array Datatype * +

Auto Datatype Conversion * o

Bit/Binary (Logical) + o

Char., Fixed Len. (CHAR) o o

Char., Variable Len. (VARCHAR) o o

Date and/or Time o o

Decimal * * New

Float * *

Integer * *

Multi-Dimension (2D,3D,4D) * MultiD +

Native Binary + o

User-Defined + o

IBM

DB2/6000

v2.0

+

+

o

o

o

o

*

*

*

+

o

o

Datatypes, BLOb/Long

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

BLOb Filters + +

BLOb/LongText/Image Datatype o o

Comparison Semantics o partial o

Manipulation Under

Transaction Management o +

Piecewise Access,

Insert/Update + *

Piecewise Access, Query o *

IBM

DB2/6000

v2.0

+

o

+

+ -

+ -

+ -

Datatypes, Stored Procedures

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Array, Multi Dimension + +

Array, Single Dimension o +

BLOb/Long Datatype (max.) o 32K + -

User Defined o + -

Record o +

Type Reference from Table o + -

IBM

DB2/6000

v2.0

+

+

o "2GB,32700"

o

+ -

+ -

Distributed Database

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Automatic Distributed

Transaction Recovery * +

Definable 2PC Coordinator * +

Distributed Query, Cursors * o OmniSQL

Force commit/Rollback of

In-Doubt Trans. * +

Global Database Names * +

Remote DDL + o OmniSQL

Remote Update o o OmniSQL

Simulate distrib. trans. failure o +

Transparent Two-Phase Commit o +

IBM

DB2/6000

v2.0

*

*

+

o

*

+

+ planned

o limited

o

Distributed Database (Date's Rules)

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Database Autonomy * *

No Reliance on Central Site * * partial

Continuous Operation * *

Location Transparency * +

Fragmentation Independence * partial o Navigation

Replication Independence * partial o Replication

Distributed Query * * OmniSQL

Distributed Transaction Mgt. * +

Hardware Independence * o

Operating System Independence * o

Network Independence * o partial

DBMS Independence * o

IBM

DB2/6000

v2.0

*

*

*

*

+ -

+

+

+

+

+

+

+

Distributed Database (Replication)

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Product or Option * Distributed * Replication

Corporate Rollup * * New

Declarative Master/Slave * * New

(single master)

Dynamic Ownership * +

(rotating masters)

Real-time Replication * +

(asynchronous)

Shared-Ownership * +

(update anywhere)

Conflict Resolution * +

(store & forward resolution)

Datatypes of Image/BLOb + +

Selective Subscription o o New

Forward replicate updates o o New

to primary

Time-based Replication * * New

Transaction-based * New * New

Replication * *

Replicate Specific Tables * +

Event Based Replication + use logs + -

Replication for

"hot-standby"

IBM

DB2/6000

v2.0

* Propagator

+ -

+ -

+ -

+

+ -

+ -

+ -

o limited

+ -

*

+

o

o

+

Gateways, Procedural

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

APPC Gateway * +

C, Ada...Stub Generation * o Open Server

Under Transaction Management * +

IBM

DB2/6000

v2.0

*

+ -

+ -

Gateways, Transparent

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Product * Transparent * OmniSQL

Developers Kit * beta * Open

Server

Distrib. Heterogeneous Trans. * +

Gateway to DB2 * o

Gateway to Informix + o beta

Gateway to Ingres + +

Gateway to Oracle + - o

Gateway to SYBASE + + -

Gateway to DRDA o +

Gateway to SQL/400 o +

DEC RDB (Transparent) * +

DEC RMS (Transparent) * +

HP Allbase + - +

IBM IMS + - +

Gateway to SQL/DS o +

Passthrough Capability + o

IBM

DB2/6000

v2.0

+ -

+

+

o

+

+

+

+

o

o

+

+

+

o

o

+

Indexing

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

B-Tree * *

Clustered Index, Multi-Table * +

Clustered Index, Single Table + o

Hashed Index * +

(table access by algorithm)

Parallel Index Building * New +

ISAM + +

IBM

DB2/6000

v2.0

*

*

*

*

* PE

+ -

Integrity, Server Enforced

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Check Constraints * * New

Declarative Cascading Deletes * +

Declarative Cascading Updates + +

Declarative Entity Integrity o o New

Deferrable Constraints + +

Distributed Declarative Integrity + +

Distributed Integrity via Triggers o o

Enable/Disable Integrity o +

IBM

DB2/6000

v2.0

*

*

+

o

+ -

+ -

+

o

Constraints

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Foreign Key Declarations * * New

End of Stmt. Integrity Checks * + -

Prevent Invalid Transitions * o

Primary Key Declarations * o New

Check Constraints Subqueries + + -

Unique Predicate o o

Views with Checks Options o o New

IBM

DB2/6000

v2.0

*

+ -

+ -

o

+ -

+ -

+ -

Limits

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Max. Columns Per Table * 255 * 250

Max. Columns Per Index * 16 * 16

Max. Indexes per table * no-limit * 251

Max. Length of Schema * 64K + -

Definition * 508K o 1962 bytes

Max. Row Size

Max. Size of SQL Statement * 64K + -

Max., Unstructured BLOb * 2GB o 2GB

Max., Var. Length Char. * 2000 o 255

Max. Tables in a Join Query * no-limit o 16

Max. Triggers per Table * unlimited o 3

Max. Users per Database * unlimited o unlimited

IBM

DB2/6000

v2.0

* 255

* 16

* 32767

+ -

o 4005

+ -

o 2GB

o 32700

o no-limit

+ n/a

o unlimited

Locking

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Implicit Locking for DMLs * *

Indexes locked at entry/key * +

Lock Escalation always Avoided * +

"Read Locks, no non-Requested" * +

Row Level Locking * +

User Definable Locks * +

Writers don't Block Readers * +

Readers don't Block Writers * +

IBM

DB2/6000

v2.0

*

+

+

+

o

o limited

+

+

Logging/Journaling

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Automatic Archiving * * programmatic

Checkpoint Control * *

Deffered Database Writes * *

Group Commits * + -

Multiplexed (mirrored) Logging * o

Transactions may span log files * +

IBM

DB2/6000

v2.0

+ -

+ -

o

o

o OS level

+ -

National Language Support

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Alternate Double Byte Char Set * *

Error Messages Translated * *

European Languages * *

Session Level Control * * New

Sorting/Collating Sequences * *

Translated Documentation * *

Upper/Lower case Conversion * *

IBM

DB2/6000

v2.0

*

*

*

+

o

o

o

Networking

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Product or * SQL NET * Net Lib

Option

Alternate Network * +

Routings

Auto Detection of * + -

Server Failure

Dead Connection + +

Detection

(Server)

Client-Server Support o o

Name Server o +

(Global DB Names)

Network Protocol * * Net-Gateway

Translation

Programmatic (3GL)

Interfaces

Module Language * +

ODBC

"Precompilers (C,Cobol, * o

Ada)"

Proprietary Call-level * o

Interface * OCI oDB-Lib/CT-Lib

IBM

DB2/6000

v2.0

+ -

+

+ -

+ -

o

+ -

+

+ -

+ -

o

+ -

Query Optimization

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Cost-based Optimizer * *

Data Value Histograms + +

Distributed Cost Optimization o +

Estimate Statistics o +

Optimizer Directives (hints) * * awkward

Index Only Access * *

Min./Max. Values stored * *

OR's in index * +

Parallel Data Query * o Navigation

Rule Based Optimizer * + -

Statistics Dynamically Updated + +

Statistics on Data Distribution o + -

Can prevent query start + +

Disk Quotas by User o +

Can Limit Amount of I/O o +

Can Limit Idle Time o +

Can Limit Amount of CPU o +

IBM

DB2/6000

v2.0

*

+

+

+ -

+

+ -

o

+ -

o PE

+

+ -

+ -

+

o

+ -

+ -

+ -

SQL Implementation

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Alter table add col. cmd. * + -

Alter table drop col. cmd. + + -

Alter table modify col. cmd. o + -

ANSI SQL89 (FPS 127-1) Cert. o +

ANSI SQL92 Entry(FIPS 127-2) o + New

Bind Variables Supported * * New

Conditional Expression Eval. * +

Cursors * o New

Supports Fetch Across Commits * + -

Multi-Row Fetch via Cursor * o New

Multi-Row Insert via Cursor * +

Nested Aggregates in Select * + -

Nulls * o

Outer Join (1-sided) * o

Outer Join (2-sided) +

Scrollable Cursors + +

Scrollable Updatable Cursors + +

Select Stmts. in Select List + o

Temporary Tables + o

Transitive Closure (connect by) o +

Unions in Views * +

Update T Set * +

Update Where Current of cursor * o New

Updatable Views of Joins + o

User Defined Function o New +

IBM

DB2/6000

v2.0

+ -

+ -

+ -

o

+

+ -

+

o

+ -

+

+ -

+ -

o

+

+

+

+ -

+

+

+

+ -

+

+

+

o

Security

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Col. Select and Update Privileges * * New

Database Authorization * *

Password Expiration + *

Encryption of PW Inside DB o * New

Encryption of PW Over Network o * New

non-Procedural Auditing o * New

O/S Authorization * +

and Authentication

Roles with Hierarchy * +

(SQL3 style)

Roles, Selective Activation * +

User Groups or Roles * o

IBM

DB2/6000

v2.0

*

*

+ -

+ -

+ -

+ -

*

+

o

o

Security, Multilevel

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of MLS Product * Trusted * SYBASE

Oracle Secure

Underlying Server release * 7/7.1 * 10

B1 Rating * + under eval.

B2 Rating + +

Compartments o o

DBMS MAC Mode o +

OS MAC mode * + -

Labels * o

Multilevel Import Utility * + -

Same as Standard RDBMS * + -

Trusted Subjects + o

Trusted Comp. Base TCB o o micro

(Subset Design) kernel

IBM

DB2/6000

v2.0

+ -

+ -

+ -

+ -

+ -

+ -

+ -

+ -

+ -

+ -

+ -

+ -

Sequence Generators

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Seq. Number or Serial Datatype * * New

No contention for Seq. Numbers * * New

Gap Free Generation... * use table * use table

Reference Current Val. of Seq. * + -

IBM

DB2/6000

v2.0

+

+

o use table

+

Storage and Space Management

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

BLOb space for stor. of "long" + *

Control Growth of DB Objects o *

Data compression of ASCII data + +

Data compression in Indexes o o

DB objects may span O/S files * *

Data Segmentation * *

Default Object Locations * *

Horizontal Striping + * Navigation

on Disk by Key Value

Objects can be placed o *

on specified disks

Read only access * + -

for optical storage

Var. Length Data * o

in Var. Length Rows

Vertical Table Partitioning + o Navigation

IBM

DB2/6000

v2.0

+ -

+ -

+ -

+ -

*

+ -

+ -

* PE

* limited

+ -

o

+

Stored Procedures

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Atomic Execution * +

(all works/all rolled back)

Cached in compiled format * + -

Call Other Procedures * o

Create or Replace Syntax * +

Dependency Mechanism * + -

Execute Privilege * o

for Stored Procedures

Remote Data my be Referenced * o OmniSQL

Remote Procedure Calls * o

Return Any Datatype * +

Returns Database Values * o

Tabular Data Streams * o

may return rows

Trans. Integrity for Distrib. Calls * +

IBM

DB2/6000

v2.0

+ -

o

o

+

+ -

+ -

o

+ -

*

*

+ -

+ -

Stored Procedure Language

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Name of Language * PL/SQL * Transact-SQL

Cursors Supported * * New

DDL Supported + +

Dependency Tracking o +

Dynamic SQL DDL + +

Dynamic SQL DML o New +

Forward Referencing o +

If Then, Else Logic * *

Looping * *

Overloading * +

Packages * +

Parameters, IN * o

Parameters, IN/OUT * o

Parameters, OUT * +

Parameters, Passed by Name * +

Variables, Global * +

Variables, Local * o

Variables, Session Persistent * +

IBM

DB2/6000

v2.0

* 3GL

*

+

o

+

+

o

*

*

+ -

+

o

o

+ -

+

+ -

+ -

+ -

Transactional Control

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Asynchronous DML * New + -

Commit * o

Implicit Transaction Start * o New

Rollback DDL + + -

Rollback * *

Savepoints, sub-transactions * + -

TP Monitor Support

X/A TP Monitor support * o New

IBM

DB2/6000

v2.0

+ -

o

+ -

+ -

*

+ -

o

Triggers

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Access new, old values * *

from within trigger

Access Remote Data * *

from Trigger

Enable / Disable Triggers * +

Multiple Same Type Triggers * New +

New Values May be * o

Set by Trigger

May set new values * o

for triggering columns

Recursive * o New

SQL3 Syntax (12 types) * +

Trigger Activates... * PL/SQL o Transact-SQL

Trigger can fire:

AFTER ROW * +

AFTER STATEMENT * o

AFTER TRANSACTION + +

BEFORE ROW o +

BEFORE STATEMENT o o

Triggers can Cascade * 32 levels * 16 levels

Triggers may call SP * *

Under Transaction Mgt. * * local only

When Verb * + -

IBM

DB2/6000

v2.0

+

+ -

+

+

+ -

+ -

+

+

+ -

o

+

+

o

+

+

+ -

o

+ -

Server Ports

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

DEC VMS * *

DOS Windows 3.1 * +

IBM MVS * +

IBM VM * +

Macintosh * +

Microsoft DOS * v6.0 +

Microsoft NT * o

Novell NetWare * o

OS/2 * o

IBM

DB2/6000

v2.0

+

+

+

+

+

+

+ planned

+

o

Unix Ports

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

DEC Alpha * +

DEC Ultrix * o

HP/UX * o

IBM RS6000 * o

NCR/AT&T * o

Pyramid * *

SCO * *

Sequent * *

Sun OS * *

Sun Solaris * *

IBM

DB2/6000

v2.0

+

+

+

o

o

+

+

+

+

+

Educational Services

* =Yes Oracle SYBASE

Oracle7 SQL Server

+ =No v7.2 v10.02

Educational Centers * 28 * 27

Reg. Scheduled Courses/year * 6250 * 2366

Unique Courses/year * 107 * 32

Size of Education Team * 250 * 80

Class Training * *

On-site Training * *

Media-based Training * *

Custom Training * *

Interactive Distance Learning * +

IBM

DB2/6000

v2.0

* 39

* 80 (DB2)

* 64 (DB2)

* 625

*

*

*

*

+

RECENT INDUSTRY STUDIES AND QUOTES

In addition to the evaluator’s findings, two key industry studies have pointed to the clear superiority of the Oracle7 RDBMS. The “Technology Audit, Oracle7 Release 7.2” by the Butler Group, dated May 1995 and the “Profile, Oracle7 Server Release 7.2 Relational Database Management System” by the AberdeenGroup, dated June 1995 both give Oracle high marks for providing a comprehensive and robust information management solution.

In addition, the evaluator has included several quotes from a variety of sources which indicate weaknesses in the DB2 and System 10 RDBMS solutions.

Following are the above mentioned studies as well as several industry quotes.

**************************************************************************************************************************************************************************

BUTLER GROUP - TECHNOLOGY AUDIT ORACLE7 RELEASE 7.2

Summary

Database technology has advanced to a stage similar to that of the motor industry: the sophistication of the underlying engine is such that it is no longer possible to make dramatic improvements to generate market leadership. The DBMS market leaders are now maintaining position by improvements in administerability, security and enhanced productivity and performance. These refinements broaden the applicability of relational database technology to high-end, business-critical OLTP and also decision support applications such as data warehousing.

Oracle7 introduced a sophisticated server architecture, support for triggers, constraints and stored procedures, a new optimiser, new indexing mechanisms, enhanced distributed functionality and a repackaging of the product. Few in the industry would dispute that Oracle7 was a major milestone in the product's history, and that it has given Oracle a firm technological foundation for the next five years. The re-engineering of Oracle in V6 addressed many of the architectural issues which we highlighted in previous analyses. It included the incorporation of row level locking, and more sophisticated use of database data structures. Oracle7 put Oracle in a very competitive position, and it should be remembered that the Oracle product set is very broad in its scope and capabilities. It includes tools for building business applications, graphics, text handling, CASE, reporting and network management.

Oracle’s latest release, 7.2, modestly labelled a maintenance release, has concentrated on extending the range of applicability of its dbms through enhanced support for systems management, application engineering, distributed security, and performance. The specifications were derived from customer requirements to support the broadest range of applications, including data warehousing, distributed database and OLTP. Alongside the maintenance improvements, Release 7.2 includes new features and algorithmic enhancements that dramatically improve scalability, productivity, and manageability.

The concept of data warehousing has caught the imagination of vendors and users alike and has prompted a range of new requirements for database technology. Oracle has responded to the warehousing market by supporting increased ad hoc query optimisation, as well as more traditional OLTP applications, through stronger performance.

The performance enhancements in Oracle7 were significant. The new server architecture provided a highly scalable

environment which can handle very large transaction processing applications as well as small departmental systems. These major enhancements, along with changes in functionality, will assure Oracle’s position as market leader in the RDBMS market. However, the Butler Group believe the extra support for Decision support and data warehousing will strengthen their position still further.

Strengths

•Advanced functionality through triggers, constraints and stored procedures

•Flexible, sophisticated parallel options

•Several case studies demonstrating support for large databases (over 600gbytes) and large user populations (several thousand concurrent users)

•Sophisticated multi-threaded, multi-server architecture

•Part of an extensive software tools product set

•The most extensive platform support

Weaknesses

•Limited support for complex data types

•Support for competitive third party rdbms limited but improving

•Strong competition on price and performance from new post relational vendors

Market Position

Oracle emerged in the early 1980s as one of the first commercially available relational database management systems which supported SQL. It rapidly gained market share, and Oracle doubled its revenues year on year during the 1980s. Provided the performance requirements were not too demanding Oracle offered excellent functionality, and application portability over an extensive range of hardware platforms. Version 6 delivered a much needed reengineering of the kernel, and this added row level locking, better free space management, and generally better performance. Oracle7 was a major release, and in our opinion took Oracle out of the "database lite" class into the heavyweight league.

These enhancements to Oracle give the product a good competitive position and open up several new markets. Very large database applications, and particularly very large data warehousing databases, can be implemented in Oracle through utilisation of the parallel options. Advanced replication functionality is included with the distributed option, and this will make the implementation of a wide variety of distributed systems much easier.

There are several quite well known implementations of Oracle databases which go well into the very large database category. Oracle databases measured approaching the hundreds of Giga-bytes and accommodating several thousands of users are now in existence. We know of over twenty sites in the 100-500 Gbytes range. One example also supports some 600+ plus concurrent users. Butler Group believes that with the widest range of platform support and continual technical advances, Oracle’s position as the leading relational dbms vendor will remain unchallenged for some years to come.

Oracle7 Release 7.2

Technical Features

Oracle now plan to issue maintenance releases every 12-18 months, Release 7.2 is the latest of these and is 100% compatible with all previous versions of Oracle7. Release 7.2 adds to the enhancements of 7.1, which included: Security Roles, Resource Limiter, XA support, National Language Support (16 bit character support) and multi-byte characters (e.g. Kanji). The scope of enhancements in Release 7.2 is broad and covers OLTP, decision support, distributed solutions, systems management, application development and enterprise security. There are also a series of accompanying product releases for the Server Manager, PL/SQL and precompilers.

High-Performance OLTP

Oracle7's position in the enterprise OLTP market is unmatched in terms of performance. Integrated functionality for Oracle7 and Oracle7 Parallel Server enable efficient use of all high performance computers (MPP, SMP and clusters), whilst maintaining compatibility with all server functions.

Multi-threaded Servers

The server architecture delivered in Oracle7 is one of the most sophisticated in the industry. Multi-threaded servers are now provided, which reduce memory usage, reduce the number of processes, and automatically balance cpu usage in multi-cpu configurations.

This new architecture has a number of key components at its heart. The listener process listens for any clients which are requesting services from Oracle, and assigns a dispatcher. The dispatcher assigns clients to the System Global Area (SGA) and manages the interaction between clients and the rest of the system. The clients will typically be Oracle applications written in SQL*Forms, although they may be anything from a bank teller machine to a bar code reader.

The number of dispatchers and servers may be tuned, as can the manner in which they are created and destroyed. This provides an excellent environment for the implementation of large performance critical applications, and makes Oracle7 a clear contender for serious TP applications. The pre Oracle7 single client/single server architecture can still be used if required, and can be integrated with the new multi-threaded server architecture where necessary.

Shared SQL

Many relational database management systems impose a query formulation overhead on the performance of applications. Until Oracle7, Oracle allowed a given user to reuse optimised queries, but they could not be shared. The shared SQL facility makes it possible for a query to be formulated and optimised once and shared thereafter. This facility makes a considerable difference to CPU and memory utilisation when many users need to execute similar queries. Transaction processing applications typically have many users executing the same tasks, and as such will benefit most from this Oracle7 feature. Shared SQL is stored in the System Global Area (SGA) as parsed and optimised SQL statements. The amount of memory dedicated to shared SQL can be tuned, and a 'last used' algorithm determines how SQL routines are included in, or moved out of, memory.

Support for caching the executable code of all PL/SQL objects is also included for stored procedures, functions, packages, and database triggers. These are also heavily shared in OLTP applications, thus benefiting any application that makes use of PL/SQL to encapsulate application logic within the server.

Cost Based Optimiser

Until Oracle7 the optimiser strategy was based upon the syntax of the query itself. This allows skilled Oracle programmers to formulate very efficient queries, but makes no allowance for unskilled users who do not appreciate the rules for optimum query execution. The addition of a cost based optimiser is particularly welcome, and provides a better optimisation method for the less skilled Oracle user. It does this by using statistics on table populations to generate the optimum query strategy and does not rely on the skill of the programmer.

Statistics on table populations are gathered through use of the ANALYZE TABLE command, and the execution of this command flushes old inappropriate execution plans. Hints can be included in SELECT statements which allow the user to use rule based optimisation as an override and also to tune queries manually.

Hash Clusters

Many relational database management systems rely exclusively on Btree indexing methods. These provide excellent levels of functionality, but tend to increase disk I/O and multi-user contention. In transaction processing applications, where full key data is used to retrieve data, hash indexes can improve performance quite considerably. The hashing mechanism translates a key value (a customer_code for example) into a database page number. No secondary index structures are used, so data can be stored and retrieved with a single I/O. There are exceptions to this, and they relate to how well space is pre-allocated, and how well the hashing algorithm distributes records in a table.

User Specified Hash Functions

Release 7.2 provides database administrators with greater control over the creation of hash clusters. User knowledge of the distribution of hashed data is likely to result in fewer hash collisions and improved access performance. Hash keys should only be used when full index information is supplied for record retrieval, and with hash keys it is necessary to pre-allocate table space. Btree indexes allow partial key searches (e.g. Find all employees starting with "SMITH*") to locate small numbers of records from large datasets, typically utilised by OLTP applications.

Decision Support

Data warehousing is placing new requirements on database technology, with issues such as replication, data management and query optimisation high on the agenda. Release 7.2 advances the query capabilities of Oracle7 by significantly improving the performance and scalability of common data warehouse operations and enhancing query specification flexibility. These performance gains have been achieved through several improvements.

While the performance enhancements in Oracle7 were significant, it is in the area of functionality that Oracle7 has been most enhanced. Certainly, prior to Oracle7, Oracle was quite deficient in its support for functionality enhancing features such



Get Better Grades Today

Join Essays24.com and get instant access to over 60,000+ Papers and Essays

closeLogin
Please enter your username and password
Username:
Password:
Forgot your password?