IMPLEMENTATION OF AN OPEN SYSTEMS APPLICATION ENVIRONMENTRDBMS COMPARATIVE ANALYSISBACKGROUND AND OVERVIEWThe 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 REQUIREMENTSIn its 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 STANDARDSData 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 REQUIREMENTSFollowing is an overview of DFAS requirements for the RDBMS component of the Open Systems Application Environment.
The selected database must have full ANSI SQL ComplianceThe 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 CONCLUSIONSAfter careful review of the Governments requirements, in conjunction with a thorough evaluation of three RDBMSs, it is the evaluators recommendation that DFAS standardize on the Oracle RDBMS. Key factors affecting the recommendation include Oracles industry proven open and scalable architecture and its superior ability to integrate into the current DFAS environment.
The other two RDBMSs that were evaluated are IBMs DB2 and SYBASEs System 10. Though these RDBMS provided several capabilities that can meet the Governments 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 OVERVIEWSORACLE7 RDBMSThe Oracle7 Server is a relational database management system that enables true enterprise data management from the desktop to the data center. Oracle7s 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 enterprises critical information. Oracle7s 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, users multimedia requirements can be met through the use of Oracles Media Server technologies. And for environments that are tapping into the World Wide Web, Oracle provides the powerful and unparalleled Oracle WebServer. IBM DB2DB2 technology is now available in an entire family of products that run on all majorIBM platforms, including MVS/ESA, AIX/6000, OS/2, OS/400, VM and VSE. Many DB2products also run on non-IBM platforms: HP-UX clients and servers for Hewlett Packard’s UNIXenvironment; 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 1992SQL 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 interfacesand 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 10The SYBASE SQL Server supports mission-critical on-line transaction processing (OLTP)applications. SYBASE SQL Server Release 10 builds on the SYBASE SQL Server and addsadvanced new features to help organizations further reduce costs and improve competitiveness. Inaddition, 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 organizationsdevelop 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 usersand or transactions -Replication Server, for building , highly available distributed systems SYBASE Open Interoperability products, including the OmniSQL Gateway and OpenClient/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 dataand information in a distributed environment SYBASE Client/Server Tools, for an application development environment that helpsbusinesses 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 andguaranteed interoperability with standards-compliant components including X/A-compliant TPmonitors. 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 ANALYSISThis 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 STANDARDSOf 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 OSAEThe Selected Database Must Have Full ANSI SQL ComplianceAs 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 SystemsSince 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 PlatformsWhile 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 its database requirements grow. COMPARATIVE PRODUCT MATRIXAdministration * =YesOracle SYBASE Oracle7SQL Server + =No v7.2v10.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 * =YesOracleSYBASE Oracle7SQL Server + =Nov7.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 OracleSYBASE Oracle7 SQL Server + =No v7.2v10.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.2v10.02 Automatic Load Balancing * + Scalable SMP Performance * o VSA IBM DB2/6000 v2.0 * * Architecture, Loosely Coupled * =YesOracleSYBASE Oracle7SQL Server + =Nov7.2 v10.02 Name of Product or Option* Parallel * Navigation Loosely Coupled Architectures Serv.* Scalable Performance ** IBM DB2/6000 v2.0 + – + + Bulk Data Loading * =YesOracle SYBASE Oracle7SQL Server + =Nov7.2v10.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 OracleSYBASE Oracle7 SQL Server + =No v7.2v10.02 Parallel Direct Path Loads* New+ IBM DB2/6000 v2.0 * PE Concurrency Control (reference locking) * =Yes OracleSYBASE Oracle7 SQL Server + =No v7.2v10.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 OracleSYBASE Oracle7 SQL Server + =No v7.2v10.02 Distrib. (multi-site) Consistency* + Multi-Stmt. Consistent Read* + Multi-Version Read Consistency * + IBM DB2/6000 v2.0 + + + Data Dictionary * =Yes OracleSYBASE Oracle7 SQL Server + =No v7.2v10.02 Active Data Dictionary* * Can Query Data Dictionary* * FIPS, Basic Info. Schema + + References to Remote Objectso o Synonymso o New Synonyms, Publico o New IBM DB2/6000 v2.0 * * + + – + – + – Datatypes * =YesOracleSYBASE Oracle7SQL Server + =No v7.2v10.02 Array Datatype*+ Auto Datatype Conversion*o Bit/Binary (Logical)+o Char., Fixed Len. (CHAR)oo Char., Variable Len. (VARCHAR)oo Date and/or Time oo 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 * =YesOracleSYBASE Oracle7SQL Server + =No v7.2v10.02 BLOb Filters++ BLOb/LongText/Image Datatype oo Comparison Semanticso 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 OracleSYBASE Oracle7 SQL Server + =No v7.2v10.02 Array, Multi Dimension+ + Array, Single Dimension o + BLOb/Long Datatype (max.)o 32K+ – User Defined o + – Record o + Type Reference from Tableo + – IBM DB2/6000 v2.0 + + o “2GB,32700” o + – + – Distributed Database * =YesOracleSYBASE Oracle7SQL 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 Updateo o OmniSQL Simulate distrib. trans. failureo + Transparent Two-Phase Commit o + IBM DB2/6000 v2.0 * * + o * + + planned o limited o Distributed Database (Date’s Rules) * =YesOracle SYBASE Oracle7SQL Server + =Nov7.2v10.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 + =Nov7.2v10.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 Subscriptionoo New Forward replicate updates oo 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 + =Nov7.2v10.02 APPC Gateway *+ C, Ada…Stub Generation *o Open Server Under Transaction Management*+ IBM DB2/6000 v2.0 * + – + – Gateways, Transparent * =YesOracleSYBASE Oracle7SQL Server + =Nov7.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/400o + DEC RDB (Transparent) * + DEC RMS (Transparent) * + HP Allbase+ -+ IBM IMS+ -+ Gateway to SQL/DSo + Passthrough Capability+ o IBM DB2/6000 v2.0 + – + + o + + + + o o + + + o o + Indexing * =Yes OracleSYBASE Oracle7 SQL Server + =No v7.2v10.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 OracleSYBASE Oracle7SQL Server + =No v7.2v10.02 Check Constraints * * New Declarative Cascading Deletes * + Declarative Cascading Updates + + Declarative Entity Integrityo o New Deferrable Constraints+ + Distributed Declarative Integrity+ + Distributed Integrity via Triggerso o Enable/Disable Integrity o + IBM DB2/6000 v2.0 * * + o + – + – + o Constraints * =Yes OracleSYBASE Oracle7SQL Server + =No v7.2v10.02 Foreign Key Declarations * * New End of Stmt. Integrity Checks * + – Prevent Invalid Transitions* o Primary Key Declarations * o New Check Constraints Subqueries+ + – Unique Predicateo o Views with Checks Optionso o New IBM DB2/6000 v2.0 * + – + – o + – + – + – Limits * =YesOracleSYBASE Oracle7 SQL Server + =Nov7.2v10.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* 508Ko 1962 bytes Max. Row Size Max. Size of SQL Statement * 64K + – Max., Unstructured BLOb* 2GB o 2GB Max., Var. Length Char.* 2000o 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 OracleSYBASE Oracle7SQL 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 OracleSYBASE Oracle7SQL 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 Oracle7SQL Server + =No v7.2v10.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 * =YesOracleSYBASE Oracle7 SQL Server + =Nov7.2v10.02 Name of Product or* SQL NET * Net LibOptionAlternate Network*+ RoutingsAuto Detection of*+ -Server FailureDead Connection ++ Detection (Server) Client-Server Support ooName Servero+(Global DB Names) Network Protocol** Net-Gateway TranslationProgrammatic (3GL)InterfacesModule Language *+ODBC”Precompilers (C,Cobol,*oAda)” Proprietary Call-level*oInterface * OCI oDB-Lib/CT-LibIBM DB2/6000 v2.0 + – + + – + – o + – + + – + – o + – Query Optimization * =YesOracleSYBASE Oracle7SQL Server + =No v7.2v10.02 Cost-based Optimizer** Data Value Histograms++ Distributed Cost Optimization o+ Estimate Statisticso+ 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 Distributiono+ – Can prevent query start ++ Disk Quotas by Usero+ Can Limit Amount of I/O o+ Can Limit Idle Timeo+ Can Limit Amount of CPU o+ IBM DB2/6000 v2.0 * + + + – + + – o + – o PE + + – + – + o + – + – + – SQL Implementation * =Yes OracleSYBASE Oracle7 SQL Server + =No v7.2v10.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 Functiono New+ IBM DB2/6000 v2.0 + – + – + – o + + – + o + – + + – + – o + + + + – + + + + – + + + o Security * =Yes Oracle SYBASE Oracle7 SQL Server + =No v7.2v10.02 Col. Select and Update Privileges* * New Database Authorization* * Password Expiration+ * Encryption of PW Inside DBo * 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 OracleSYBASE Oracle7SQL Server + =Nov7.2v10.02 Name of MLS Product * Trusted * SYBASE OracleSecure Underlying Server release * 7/7.1* 10 B1 Rating*+ under eval. B2 Rating++Compartments oo DBMS MAC Mode o+OS MAC mode*+ – Labels *o Multilevel Import Utility *+ – Same as Standard RDBMS*+ – Trusted Subjects+o Trusted Comp. Base TCBoo micro (Subset Design)kernel IBM DB2/6000 v2.0 + – + – + – + – + – + – + – + – + – + – + – + – Sequence Generators * =YesOracleSYBASE Oracle7SQL Server + =Nov7.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 * =YesOracle SYBASE Oracle7SQL Server + =Nov7.2v10.02 BLOb space for stor. of “long”+* Control Growth of DB Objectso* Data compression of ASCII data++ Data compression in Indexesoo DB objects may span O/S files** Data Segmentation** Default Object Locations** Horizontal Striping +* Navigation on Disk by Key Value Objects can be placedo* 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 * =YesOracleSYBASE Oracle7SQL 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 * =YesOracle SYBASE Oracle7 SQL Server + =Nov7.2v10.02 Name of Language * PL/SQL* Transact-SQL Cursors Supported* * New DDL Supported+ + Dependency Trackingo + Dynamic SQL DDL + + Dynamic SQL DML o New+ Forward Referencingo + 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.2v10.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 * =YesOracleSYBASE Oracle7SQL Server + =Nov7.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/SQLo Transact-SQL Trigger can fire: AFTER ROW*+ AFTER STATEMENT*o AFTER TRANSACTION++ BEFORE ROWo+ BEFORE STATEMENToo 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 * =YesOracleSYBASE Oracle7SQL Server + =Nov7.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 * =YesOracleSYBASE Oracle7SQL 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 OracleSYBASE Oracle7 SQL Server + =No v7.2v10.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 QUOTESIn addition to the evaluators 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.2SummaryDatabase 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.
Oracles 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 scaleable 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 Oracles 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 supportWeaknesses?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 vendorsMarket PositionOracle 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, Oracles position as the leading relational dbms vendor will remain unchallenged for some years to come.
Oracle7 Release 7.2Technical FeaturesOracle 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 OLTPOracle7’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 ServersThe 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 SQLMany 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 OptimiserUntil 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 ClustersMany 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 FunctionsRelease 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 SupportData 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 as stored procedures and triggers. The situation has now been addressed in a manner which puts Oracle ahead of most of its competition.
With the enhancements in Oracle6 and particularly Oracle7, the performance profile associated with Oracle Version 5 is now a disWords/ Pages : 32,932 / 24