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, DB 2, 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 X 3.
The Research paper on Adoption of Electronic banking system in Ethiopian Banking industry: Barriers and Drivers
... Ethiopian banking industry faces to adopt E-banking system and capitalize on the opportunities by using the ICT applications. The ... the dimensions affecting the adoption of E-banking system in Ethiopian banking industries?” And the three questions are as follows ... assesses the thesis ‘Adoption of Electronic banking system in Ethiopian Banking industry: Barriers and Drivers’. The review will first ...
135-189 “Database Language SQL with Integrity Enhancement” or ANSI SQL 89. 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 X 3. 168-189 “Database Language Embedded SQL.” The current SQL standard, ANSI SQL-92 is defined in FIPS PUB 127-2, ANSI X 3. 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.
o 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. o 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 DB 2 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 ORACLE 7 RDBMS The Oracle 7 Server is a relational database management system that enables true enterprise data management from the desktop to the data center. Oracle 7’s scalable, reliable, integrated server architecture dynamically adapts to exploit uni processor 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.
... relational DBMS for large mainframes and midrange computers. The relational database is the primary method for organizing and maintaining data ... contains data on an entity and its attributes. Microsoft Access is a relational DBMS for desktop systems, whereas DB2, Oracle database, Microsoft SQL Server are ...
Oracle 7 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, Oracle 7 employs an integrated, multi threaded, multi server 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 Oracle 7 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 Oracle 7 Server. Developers and users can treat a physically distributed database as a single logical database though the transparent distributed capabilities of the Oracle 7 Server, enabling the enterprise flexibility in the placement of its data.
In addition, Oracle 7 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. Oracle 7’s Open Gateway architecture ensures the smooth transition from legacy systems by enabling transparent data access during the migration process. Oracle 7 also enables the enforcement of complex business rules through the storage of powerful PL/SQL procedures and triggers right in the Oracle 7 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 Oracle 7 Server include extensive National Language Support (NLS) of 8- and 16-bit languages, encompassing virtually all European and Asian languages. Further, Oracle 7 delivers an advanced security architecture based on roles and privileges, significantly reducing the burden and cost of security management.
... in response to the expected audience for the perticulare application. Some systems are intended for back rooms, some for the front office ... fields can be designed to interpret the data entered. This prevents users from entering data incorrectly, and thus eliminates the need for ... the problem. It’s the way the data is formatted for the system that is the issue. If you eliminate the ...
As an open, integrated, relational system Oracle 7 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 Text Server and Context. For the management of spatial temporal 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 Web Server.
IBM DB 2 DB 2 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 DB 2 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 DB 2 for AIX/6000 code are planned. Version enhancements to DB 2 provide a better platform than ever before for application development and data management in an open client / server environment.
DB 2’s functional enhancements conform to Open Blueprint and move closer to the ANSI/ISO 1992 SQL standard. DB 2 provides support for large networks supporting a maximum of 25, 000 users per DB 2 system. DB 2 allows information systems personnel to better control their distributed threads by allowing them to assign a different dispatching priority to each distributed application. DB 2 Performance is bolstered via new functions and major enhancements to some existing functions. For System/390 Parallel Sys plex users, new data-sharing capabilities take full advantage of power. Applications running on more than one DB 2 subsystem can read from and write to the same set of data concurrently.
... procedures for monitoring. Overall, the SQL Server 2000 system is more polished than the BORK version. With SQL Server 2000, you can monitor log shipping ... shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over. + Some data ...
A single catalog and directory serve all the DB 2 subsystems, and you can run the same application on more than one DB 2 subsystem. You can grow the system incrementally and achieve higher transaction rates than are possible on a single DB 2 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 DB 2 subsystem needs to come down, users can access their DB 2 data from another subsystem. DB 2 now includes stored procedures.
This new programmer-friendly function introduces a SQL interface that allows SQL requester’s to invoke user-written programs, or stored procedures, at a DB 2 server. Local DB 2 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 DB 2 improves concurrent access to logical partitions of a non partitioned 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.
DB 2 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 DB 2 provides new availability features. DB 2 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.
... offered by Access. Finally, the large DBMS systems like SQL Server, Oracle, and DB 2 are typically only utilized in large ... ), retrieved April 6, 2005, from web server vs my sql. htm Chigrik, Alexander, SQL Server 2000 vs Oracle 9 i (n. d. ), retrieved April ... by IBM. It offers some object-oriented functionality, as well as cross-platform compatibility, and is server-based. Finally, Oracle offers ...
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 SQL 89 compliant as well as ANSI SQL 92 Entry Level compliant. Looking at the facts, ANSI standards reflect a basic foundation upon which advanced functionality may be carefully added.
... when relational models aren’t the right fit. Microsoft SQL Server is a comprehensive information platform offering enterprise-ready technologies and ... . Product categories for handling streaming data divide into established proprietary products such as IBM’s InfoSphere Streams, and the ... of the feedback loop, taking data from input through to decision. A commercial from IBM makes the point that you ...
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 SQL 89 compliant, however, only Oracle is ANSI SQL 92 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 Oracle 7. The RDBMS Must Provide Salability From The Workstation, Netware Server, Mid-Tier, To The Mainframe Platforms While DB 2 is available on a variety of platforms, a key problem with the product is that it has inconsistent code lines. DB 2/6000 is written in C and ported to AIX from OS/2, whereas DB 2/MVS is written in 370 assembler and PLS. DB 2/6000 V 2 is not functionally equivalent to DB 2/MVS.
DB 2 applications, DB 2 programmer / administrative skill sets are only painfully portable between the DB 2 family. Scaling from the PC to the mainframe, thus is a complex and cumbersome task if DB 2 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. Oracle 7 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 (Maps).
As such, the Government can grow the Oracle database as it’s database requirements grow. COMPARATIVE PRODUCT MATRIX Administration = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
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 DB 2/6000 v 2. 0 Data Hub PE + limited PE Alerts = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Name of Product or Option included Open Server Alerts Non-Polling Alerts + Transaction-based + IBM DB 2/6000 v 2.
0 + – + planned + + Architecture = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. 0 + o Architecture, SMP = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Automatic Load Balancing + Scalable SMP Performance o VSA IBM DB 2/6000 v 2.
0 Architecture, Loosely Coupled = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Name of Product or Option Parallel Navigation Loosely Coupled Architectures Serv. Scalable Performance IBM DB 2/6000 v 2.
0 + – + + Bulk Data Loading = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. Bulk Load = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 Parallel Direct Path Loads New + IBM DB 2/6000 v 2. 0 PE Concurrency Control (reference locking) = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. 0 RR/CS/DR + o o o + + Consistency = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Distrib. (multi-site) Consistency + Multi-Stmt. Consistent Read + Multi-Version Read Consistency + IBM DB 2/6000 v 2.
0 + + + Data Dictionary = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. Datatypes = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
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 (2 D, 3 D, 4 D) Multi + Native Binary + o User-Defined + o IBM DB 2/6000 v 2. Datatypes, BLOb/Long = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 02 BLOb Filters + + BLOb/Long Text/Image Datatype o o Comparison Semantics o partial o Manipulation Under Transaction Management o + Piecewise Access, Insert/Update + Piecewise Access, Query o IBM DB 2/6000 v 2. Datatypes, Stored Procedures = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Array, Multi Dimension + + Array, Single Dimension o + BLOb/Long Datatype (max. ) o 32 K + – User Defined o + – Record o + Type Reference from Table o + – IBM DB 2/6000 v 2.
0 + + o ‘2 GB, 32700’ o + – + – Distributed Database = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Automatic Distributed Transaction Recovery + Definable 2 PC 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 dist rib. trans. failure o + Transparent Two-Phase Commit o + IBM DB 2/6000 v 2.
0 + o + + planned o limited o Distributed Database (Date’s Rules) = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. Distributed Database (Replication) = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
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 DB 2/6000 v 2. 0 Propagatolimited + – + o o + Gateways, Procedural = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 APPC Gateway + C, Ada… Stub Generation o Open Server Under Transaction Management + IBM DB 2/6000 v 2.
0 + – + – Gateways, Transparent = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Name of Product Transparent OmniSQL Developers Kit beta Open Server Distrib. Heterogeneous Trans. + Gateway to DB 2 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 All base + – + IBM IMS + – + Gateway to SQL/DS o + Passthrough Capability + o IBM DB 2/6000 v 2.
Indexing = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 B-Tree Clustered Index, Multi-Table + Clustered Index, Single Table + o Hashed Index + (table access by algorithm) Parallel Index Building New + ISAM + + IBM DB 2/6000 v 2. 0 PE + – Integrity, Server Enforced = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 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 DB 2/6000 v 2. Constraints = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Foreign Key Declarations New End of Stmt. Integrity Checks + – Prevent Invalid Transitions o Primary Key Declarations o New Check Constraints Sub queries + + – Unique Predicate o o Views with Checks Options o o New IBM DB 2/6000 v 2.
Limits = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Max. Columns Per Table 255 250 Max. Columns Per Index 16 16 Max. Indexes per table no-limit 251 Max.
Length of Schema 64 K + – Definition 508 K o 1962 bytes Max. Row Size Max. Size of SQL Statement 64 K + – Max. , Unstructured BLOb 2 GB o 2 GB 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 DB 2/6000 v 2. 0 255 16 32767 + – o 4005 + – o 2 GB o 32700 o no-limit + n / a o unlimited Locking = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 02 Implicit Locking for Dml 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 DB 2/6000 v 2. 0 + + + o o limited + + Logging/Journaling = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 Automatic Archiving programmatic Checkpoint Control Differed Database Writes Group Commits + – Multiplexed (mirrored) Logging o Transactions may span log files + IBM DB 2/6000 v 2. 0 + – + – o o o OS level + – National Language Support = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 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 DB 2/6000 v 2. 0 + o o o Networking = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 Name of Product or SQL NET Net LibOptionAlternate Network + RoutingsAuto 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 (3 GL) Interfaces Module Language +ODBC ” Pre compilers (C, Cobol, o Ada) ‘ Proprietary Call-level o Interface OCI oDB-Lib/CT-Lib IBM DB 2/6000 v 2. Query Optimization = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. PSQL Implementation = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 Alter table add col. cmd. + – Alter table drop col. cmd. + + – Alter table modify col. cmd.
o + – ANSI SQL 89 (FPS 127-1) Cert. o + ANSI SQL 92 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 Stmt. 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 DB 2/6000 v 2.
Security = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 + (SQL 3 style) Roles, Selective Activation + User Groups or Roles o IBM DB 2/6000 v 2. Security, Multilevel = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 Name of MLS Product Trusted SYBASE Oracle Secure Underlying Server release 7/7. 1 10 B 1 + under evil. B 2 + +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 DB 2/6000 v 2. Sequence Generators = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 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 DB 2/6000 v 2. 0 + + o use table + Storage and Space Management = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
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 DB 2/6000 v 2.
PE limited + – o + Stored Procedures = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. Stored Procedure Language = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7.
2 v 10. 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 DB 2/6000 v 2. 0 3 G Transactional Control = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 Asynchronous DML New + – Commit o Implicit Transaction Start o New Rollback DDL + + – Rollback Save points, sub-transactions + – TP Monitor Support X/A TP Monitor support o New IBM DB 2/6000 v 2. Triggers = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 SQL 3 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 DB 2/6000 v 2. Server Ports = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 02 DEC VMS DOS Windows 3.
1 + IBM MVS + IBM VM + Macintosh + Microsoft DOS v 6. 0 + Microsoft NT o Novell Net Ware o OS/2 o IBM DB 2/6000 v 2. 0 + + + + + + + planned + o Unix Ports = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10.
02 DEC Alpha + DEC Ultrix o HP/UX o IBM RS 6000 o NCR/AT&T o Pyramid SCO Sequent Sun OS Sun Solaris IBM DB 2/6000 v 2. 0 + + + o o+ + + + + Educational Services = Yes Oracle SYBASE Oracle 7 SQL Server + = No v 7. 2 v 10. 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 DB 2/6000 v 2. 0 39 80 (DB 2) 64 (DB 2) 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 Oracle 7 RDBMS. The “Technology Audit, Oracle 7 Release 7. 2” by the Butler Group, dated May 1995 and the “Profile, Oracle 7 Server Release 7. 2 Relational Database Management System” by the Aberdeen Group, 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 DB 2 and System 10 RDBMS solutions.
Following are the above mentioned studies as well as several industry quotes. BUTLER GROUP – TECHNOLOGY AUDIT ORACLE 7 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 administer ability, 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. Oracle 7 introduced a sophisticated server architecture, support for triggers, constraints and stored procedures, a new optimizer, new indexing mechanisms, enhanced distributed functionality and a repackaging of the product. Few in the industry would dispute that Oracle 7 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 V 6 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. Oracle 7 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 how query optimization, as well as more traditional OLTP applications, through stronger performance. The performance enhancements in Oracle 7 were significant. The new server architecture provided a highly scale able 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. Strength so Advanced functionality through triggers, constraints and stored procedure so Flexible, sophisticated parallel option so Several case studies demonstrating support for large databases (over 600 g bytes) and large user populations (several thousand concurrent users) o Sophisticated multi-threaded, multi-server architecture o Part of an extensive software tools product se to The most extensive platform supportWeaknesseso Limited support for complex data type so Support for competitive third party r dbms limited but improving o Strong competition on price and performance from new post relational vendors Market Position Oracle emerged in the early 1980 s 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 1980 s. 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 re engineering of the kernel, and this added row level locking, better free space management, and generally better performance. Oracle 7 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 utilization 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. Oracle 7 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 Oracle 7.
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 pre compilers. High-Performance OLTP Oracle 7’s position in the enterprise OLTP market is unmatched in terms of performance. Integrated functionality for Oracle 7 and Oracle 7 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 Oracle 7 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 Oracle 7 a clear contender for serious TP applications. The pre Oracle 7 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 Oracle 7, Oracle allowed a given user to reuse optimized queries, but they could not be shared.
The shared SQL facility makes it possible for a query to be formulated and optimized once and shared thereafter. This facility makes a considerable difference to CPU and memory utilization 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 Oracle 7 feature. Shared SQL is stored in the System Global Area (SGA) as parsed and optimized 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 Oracle 7 the optimizer 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 optimizer is particularly welcome, and provides a better optimization 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 optimization 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 data sets, 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 optimization high on the agenda. Release 7.
2 advances the query capabilities of Oracle 7 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 Oracle 7 were significant, it is in the area of functionality that Oracle 7 has been most enhanced. Certainly, prior to Oracle 7, 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 Oracle 6 and particularly Oracle 7, the performance profile associated with Oracle Version 5 is now a dis.