Transcript Technology Assessment Report Database Software Comparison

POEC6383
Technology Assessment Report
Database Software Comparison
Adrian Avila
Hua Lu
Sarah Craren
Nov. 1, 2001
Contents
• Introduction
• Outline key factors influencing technology
selection for DBMS software
• Side by side comparison of major DBMS
software (Oracle, DB2, SQL Server)
• Spatial features of major DBMS
• Conclusions
Introduction
• What is a database and database management system
– A database is a collection of related data.
– A database management system (DBMS) is a collection of programs that enables
users to create and maintain a database.
• Why use a database management system?
–
–
–
–
–
–
–
–
To control redundancy
To restrict unauthorized access
To provide persistent storage for program objects and data structures
To permit inference and actions using rules
To provide multiple user interfaces
To representing complex relationships among data
To enforce integrity constraints
To provide backup and recovery
• The role of database in GIS
– GIS is a data driven information system
– Managing the data is a major job in a GIS application
Factors for Selecting
RDBMS Software
•
•
•
•
•
•
•
•
Platform and System Requirement
Support Data Types
Application function
Program language Supporting
Analysis Ability
Internet Ability
Price and Performance
Ease of Use and Documentation
Current Status of RDBMS Software
• Three Major RDBMS Software
Oracle
DB2
SQL Server
Vendor
Oracle
IBM
Microsoft
Version
Oracle 9i
DB2 UDB 7.2
SQL Server 2000
Address
Oracle Corporation
500 Oracle Parkway
Redwood Shores, CA 94065
1.650.506.7000
International Business
Machines Corporation
New Orchard Road
Armonk, NY 10504.
(914) 499-1900
Microsoft Corporation
One Microsoft Way
Redmond, WA 98052
1(800)-360-7561
Website
www.oracle.com
www.ibm.com
www.microsoft.com
Market
Share
46%
24%
7%
Based on IDC,May 2001 for 2000 RDBMS. Others 23%
Platform and System Requirement
• Platform
Windows
NT/2000
UNIX
Linux
OS/2
(AIX, HP-UX,
Solaris,Tru64)
Oracle 9i
Y
Y
Y
Y
DB2 UDB 7.2
Y
Y(no Tru64)
Y
Y
SQLServer2000
Y
N
N
N
• System Requirement
– They are distributed databases, run on the client/server environment
– System requirement differ by product edition on operating system, memory, hard
disk, and related software
– It depends on platform and software components which are installed
– It varies depending if it is installed on a server or a client
Product Family--- Oracle 9i
• Personal Edition
–
–
Support single user development and
deployment
Full compatibility with Oracle 9i SE and EE
• Standard Edition
–
–
–
For workgroup, department-level and
internet/intranet
On one to four processor servers
Rich functionality for most popular database
including Web sites, transaction processing,
multimedia, and content management
• Enterprise Edition
–
–
For enterprise applications including Web
sites, transaction processing, multimedia,
content management, and decision support.
From single processor servers to the largest
Symmetric Multiprocessing (SMP) servers
to massive clusters and mainframes.
• Extender
Oracle Enterprise Edition can be extended with
following options at extra cost:
– Oracle Real Application Clusters,
– Oracle Partitioning,
– Oracle Advanced Security,
– Oracle Label Security,
– Oracle OLAP,
– Oracle Data Mining,
– Oracle Spatial,
– Oracle Enterprise Manager Packs,
– Oracle Programmer
• Common feature(PE, SE, EE)
–
–
–
–
Stores and manages more data types than
any other database.
Has the most advanced SQL, Java, XML,
Web services and more.
Includes sophisticated performance,
reliability, and security features
Easy to configure and manage from any
Web browser.
Product Family--- DB2 UDB 7.2
• Personal Edition:
–
–
Provides a single-user object-relational
database management system for a PCbased desktop that is ideal for mobile
applications of the power-user.
The package includes: DB2 Universal
Database Personal Edition and DB2
Extenders
• Workgroup Edition(Standard):
–
–
Provides a multi-user object-relational
database for applications and data
shared in a workgroup or department
setting on PC based LAN’s. Ideal for
small businesses.
Includes: DB2 Universal Database
Workgroup Edition, DB2 Extenders,
and DB2 XML Extender
–
• Enterprise-Extended Edition
(EEE):
–
–
• Enterprise Edition (EE):
–
Provides a multi-user object-relational
database for complex configurations
and large database needs for Intel to
UNIX platforms and from
uniprocessors to the largest SMP’s.
Includes: DB2 Universal Database EE,
DB2 Extenders, DB2 XML Extender,
DB2 OLAP Starter Kit, Application
Development Client, Administration
Client, Run-Time Client, Net.Data, Web
sphere Application Server, Standard
Edition and QMF.
–
Provides a high performance
mechanism to support large databases
and offer greater scalability in
Massively Parallel Processors (MPP’s)
or clustered servers.
Ideal for applications requiring parallel
processing, mostly in data warehousing
and data mining.
Includes: Everything in the Enterprise
Edition and also Net.Data
Product Family--- SQL Server 2000
• Personal Edition
–
–
–
For mobile users disconnected from the
network but require SQL server data
storage.
Similar to SE, run on max 2 processors
in an SMP computer.
Only one using windows 98 or ME
edition.
–
–
–
• Standard Edition
–
–
–
For small and medium sized
organizations. It’s a fully web enabled
database.
Can be used on SMP systems up to 4
CPUs and 2 GB of RAM.
Performs replication, full text search,
English query, stored procedure
development and debugging tools and
SQL profiling and performance analysis
tools.
• Enterprise Edition
–
Includes all SQL databases, support the
–
•
Windows CE Edition
–
•
For offline data storage, it’s easy to install and has
smallest foot print of all the editions.
Developer Edition
–
•
For Windows CE-based devices.
Desktop Engine
–
•
largest web sites and enterprise OLTP
and data warehousing systems.
It supports up to 32 processors and up
to 64 GB of RAM
Allows OLAP cubes with large
dimensions.
Provides availability and uptime to
ensure that your applications stay up
and running when a disaster strikes.
Speed up an application by taking full
advantage of SMP computers.
For developers to build any type of application on top
of SQL Server.
Evaluation Edition
–
For 120 days demonstration, testing, and evaluation
Support Data Types---Oracle 9i
• Built-in Data types
–
–
–
–
–
–
Character_data types: CHAR, VARCHAR2, NCHAR, NCHAR2
Number_data types: NUMBER(p,s)
Long_and_row_data types: LONG, LONG RAW, RAW
Date time_data types: DATE, TIMESTAMP, INTERVAL
Large_object_data types: BLOB, CLOB, NCLOB, BFILE
Rowid_data types: ROWID, UROWID
• User-Defined Types
–
–
Using built-in or other user defined data types as building blocks to model the structure and
behavior of data in application.
Building block: Object Types, REFs, Varrays, Nested Tables
• ANSI SQL Supported Data types
–
Most data types can be convert to oracle data types, except GRAPHIC, VARGRAPHIC, LONG
VARGRAPHIC, TIME, TIMESTAMP
• Supplied Data types
–
–
–
–
any_types: SYS.AnyType, SYS.AnyData, SYS.AnyDataSet
XML_types: SYS.XMLType, SYS.UriType
spatial_types: MDSYS.SDO_Geometry
media_types: ORDSYS.ORDAudio, ORDSYS.ORDImage, ORDSYS.ORDVIdeo
Support Data Types---DB2 UDB 7
• Build-in datatypes
–
–
–
–
–
–
Character String: CHAR, VARCHAR, CLOB
Graphic String: GRAPHIC, VARGRAPHIC, DBCLOB
Binary String: BLOB
Numbers: SMALLINT, INTEGER, BIGINT, DECIMEL, REAL, DOUBLE
Datetime Values: TIME, TIMESTAMP, DATE
External Data: DATALINK
• User Defined Types
–
–
–
Distinct Types: is a user-defined data type that shares its internal representation with an
existing type (its "source" type), but is considered to be a separate and incompatible type for
most operations.
Structured Types: is a user-defined data type that has a structure that is defined in the
database. It contains a sequence of named attributes, each of which has a data type. A
structured type also includes a set of method specifications
Reference(REF) Types: is a companion type to a structured type. Similar to a distinct type, a
reference type is a scalar type that shares a common representation with one of the built-in data
types.
• ANSI SQL Supported Data types
–
Build-in datatypes is the ANSI SQL Data types
Support Data Types---SQL Server 2000
• System data types
– Exact Numerics: bigint, int, smallint, tiny,int, bit, decimal, numeric, money,
smallmoney
– Approximate Numerics: flot, real, datetime, smalldatetime
– Character Strings: char, varchar, text
– Unicode Character Strings: nchar, nvarchar, ntext
– Binary Strings: binary, varbinary, image
– Other Data Types: cursor, sql_variant, table, timestamp, uniqueidentifier
• User-defined Data types
– sp_addtype
– UserDefinedDatatype object
• ANSI SQL Supported Data types
– Most data types can be convert to System data types
Application Development Features
• Oracle 9i
Oracle Programmer, Java, SQLJ, JDBC/ODBC, XML, Objects and
extensibility, PL/SQL, User-defined aggregates, Globalization support,
Autonomous transactions, iSQL*Plus, Windows only feature---Microsoft
Transaction Server integration, COM cartridge, AppWizard for Visual Studio
• DB2 UDB 7
SQL types for stored procedures, functions, and methods. Changing Microsoft
Visual Basic samples to use DB2 as a data source, Linux support of Java
stored procedures, save points in DB2 SQL, List of JDCB methods and
features that are not supported by DB2, IBM OLE DB Native provider for
DB2, and Mapping JDBC transaction isolation levels to DB2 isolation levels
• SQL Server 2000
Transact-SQL, SQLXML, ODBC, ASP, Active Data object(ADO), OLE DB,
English Query
Manageability Features
• Oracle 9i
Oracle 9i provides very strong management features.
Oracle Enterprise Manager, Automatic undo management, Self-tuning memory management, Server
managed backup and recovery, Recovery manager, Legato Storage manager, Oracle managed files,
Resumable space allocation, Unused index identification, Duplexed backup sets(EE), Database
Resource Manager(EE). Extra cost option for EE: Oracle Change Management Pack, Diagnostics
Pack, Tuning Pack, Management Pack for Oracle Applications, Management Pack for Sap R/3.
• DB2 UDB 7
DB2’s self-management ability eliminates the need for DBA intervention. For Configuring a
database DB2 had SMART Guide that enables users to configure the knobs to get optimal
performance. The query patroller is a helpful tool in monitoring the activity on the database. For
maintaining the physical organization of the data DB2 has a reord utility that can reorganize the data
to allow efficient access.
• SQL Server 2000
SQL server 2000 and the active directory service in windows 2000 allow SQL server databases to be
managed centrally alongside other enterprise resources. Vastly simplifying system management in
large organizations. SQL Server 2000 enables centralized management but also endeavors to
automate management and tuning as much as possible to reduce the burden on the administrator.
When automation is not an option, SQL Server 2000 offers intuitive wizards to quickly step
administrators through complex tasks.
Security Features
• Oracle 9i
Provide role level, function level and row level security. Has Advanced Security
Option(EE), Oracle Label Security(EE), Encryption toolkit, Virtual Private
Database(EE), Fine grained auditing (EE), Password management, Proxy authentication
• DB2 UDB 7
Does not require users to be defined within the database, instead it relies on security
mechanisms. Once the user has been authenticated all authorization lies within the
database. During the user authentication process the database can be configured so that
passwords are encrypted during transmission rather than flowing in clear text.
• SQL Server 2000
Has role based security and integrated tools for security auditing. Also, it provides
support for sophisticated file and network encryption including secure socket layer
(SSL), and kerberos. Its certified under U.S. government with a C2 level which is the
highest security available in the industry.
Program Language Support
Oracle 9i
DB2 UDB 7.2
SQL server2000
Java
Y
Y
.NET
SQL
Y
Y
Y
C/C++
Y
Y
Y
PL/SQL
Y
--
--
Visual Basic
--
Y
Y
Perl
Y
Y
--
Cobol
--
Y
--
XML
Y
Y
Y
Analysis Ability
• Oracle 9i
Provide very strong index and query function to meet analysis need .
Such as Optimizer statistics management, Analytic functions, Function based index, Automated
parallel query degree(EE), Parallel statistics gathering(EE), Distributed queries, …At extra cost
option: Oracle OLAP, Oracle Data Mining.
• DB2 UDB 7
Enterprise Performance Managementdesigned to enable people to make better decisions.
Activity Based ManagementPeopleSoft Activity Based Management provides the ability for
organizations to implement strategic cost management and multi-dimensional profitability. E.g.
Accurate cost and Revenue Measurement, Powerful Decision Support, Rational Resource Allocation,
Improve Distribution, Service Profitability, and ABM Cost-to-serve.
• SQL Server 2000
Analysis Services (formerly OLAP Services) in SQL Server 2000 provide a complete, end-to-end
platform for analysis including relational storage, data extraction, OLAP optimization and querying,
data mining, and semantic modeling, among others.
English Query allows end users to pose questions in English instead of forming a query
with an SQL statement.
Internet Ability
• Oracle 9i
–
–
–
–
Oracle9i Application Server--- an integrated J2EE-certified platform to deploy all e-business
Web sites and internet applications using Java, Oracle XML Developer’s Kit.
Integrated with Oracle database and allows database developers to become productive Web
developers using PL/SQL, Java.
Provides productive tools to integrate your business process, applications and data.
Allows manage and secure your entire Web infrastructure within a single, comprehensive
management framework
• DB2 UDB 7
–
–
–
–
–
–
XML Extender
Java Support
Net Search Extender for a separately ordered feature of DB2
EE XML Extender
Net.Data enhancements—Web enabling technology for DB2
Websphere
• SQL Server 2000
–
–
–
–
Rich XML support
Web enabled analysis
Web access to data
Integration with .net enterprise servers – Microsoft biztalk and Microsoft commerce server.
Capability for Spatial Application
• Oracle 9i
–
–
–
–
–
–
Oracle Spatial extender can be used with Oracle 9i EE to manages location information
including road networks, wireless service boundaries and geocoded customer addresses.
Provide extendable spatial object data types.
Provides capability for load, index and query spatial data.
Provides spatial reference system support, and Oracle Spatial linear referencing system.
Provides geometric function and procedures, spatial aggregate functions, coordinate system
transformation functions. Fully use of SQL language for spatial data operation
Strong Partnerships with all major vendors in the GIS and location-based services markets.
• DB 2 UDB 7
–
–
DB2 UDB 7 provide a new Spatial Extender that can be hosted directly by DB2 UDB 7.
Supplies 13 spatial types to spatially model real world entities e.g. customer’s location, park's
boundary, course of rivers. Spatial indexes are provided for spatial columns to improve the
performance of spatial access to business tables. Management tools are provided to administer,
load/unload, analyze and browse spatial resource and data.
• SQL Server 2000
–
–
Dose not have a spatial extender.
Can be used as back end database for GIS application.
Price and Performance
•
Oracle 9i
–
•
•
named user
1 processor
1 processor
$400
$15,000
$40,000
1 processor
1 processor
N/A
$14,350
$28,000
Two simple license price plan include all feature
DB2 UDB 7
–
Personal Edition
Standard Edition
Enterprise Edition
Personal Edition
Standard Edition
Enterprise Edition
Dose not include same feature as oracle , the real price may be higher
SQL Server
2000
Personal Edition
Standard Edition
Enterprise Edition
Comes with Enterprise edition
1 processor
$4,999
1 processor
$19,999
Performance
–
–
–
There are three keys to an effective database system, they are performance, performance,
performance.---Dr.Bruce Lindsay.
We can not simply say who is the best, since there has many factors.
It depend on index, query optimize that DBMS provided, which field DBMS going to be used,
etc. Possible DB2 is good for Data mining, Oracle is good for Internet application.
Ease of Use
• Oracle 9i:
Provides a serials tools to make your work easier, e.g Oracle Developer, Oracle Reports
Developer, Oracle Forms Developer, Oracle E-business suit, Jdeveloper, XML
Developer’s Kit
• DB2 UDB 7:
Includes a complete suite of GUI administration tools that allow for easy installation,
administration, and remote operations. DB2 includes programmer-friendly tools to get
an application up-and-running quickly, and user-friendly tools to make end-users
immediately productive.
• SQL Server 2000:
Offers developer tools to assess and manipulation of data between the widest
array of sources and build new applications by taking advantage of existing
code.
Query Analyzer, Data Transformation Services, User-defined functions, etc.
• All three of them have trial version can be download for evaluation
Documentation
• Oracle 9i
doc.oracle.com has all the documentations available for download or view on the
internet.
otn.oracle.com has all the documentations and books for download. It is free to join.
• DB2 UDB 7
www-4.ibm.com/software/data/pubs/ provides a vast range of printed resources,
available in HTML format for online viewing, and Adobe Acrobat (.pdf) for printed
output.
• SQL Server 2000
msdn.microsoft.com/library/psdk/sql Books Online, the documentation for SQL Server
2000, which contains both concise and in-depth sections on new features.
Conclusion
• SQL Server 2000 is the least expensive of the three, however, it can
only run on the Windows environment.
• Oracle 9i is the most functional mainly because it can be run on any
operating system, it also provide a serials application to meet special
need (e.g Oracle Finance, Human Resource). Oracle 9i is a fully object-oriented
database, it can model any object in the real world, and finally it has
most of the market share.
• DB2 UDB 7 has just move from main frame to Client/Server based
database market. Similar to Oracle 9i, it can be run on any operating
system, and may potentially gain more market share later.
• Choosing a Database software depends on the consistency of the
organizational software system and the functions you need. However,
migrating the data to a new system may be relatively expensive.
Existing system may not meet your future development need.
Reference
1. www.oracle.com
2. www.ibm.com
3. www.microsoft.com
Thank you!