POEC 6383 Technology Assessment Report Database Software Comparison

POEC 6383 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, DB 2, 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 DB 2 SQL Server Vendor Oracle IBM Microsoft Version Oracle 9 i DB 2 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, Tru 64) Oracle 9 i Y Y DB 2 UDB 7. 2 Y Y(no Tru 64) Y Y SQLServer 2000 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 9 i • Personal Edition – – Support single user development and deployment Full compatibility with Oracle 9 i 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--- DB 2 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: DB 2 Universal Database Personal Edition and DB 2 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: DB 2 Universal Database Workgroup Edition, DB 2 Extenders, and DB 2 XML Extender • 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: DB 2 Universal Database EE, DB 2 Extenders, DB 2 XML Extender, DB 2 OLAP Starter Kit, Application Development Client, Administration Client, Run-Time Client, Net. Data, Web sphere Application Server, Standard Edition and QMF. • Enterprise-Extended Edition (EEE): – – – 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 – – – 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 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 – • Desktop Engine – • 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. 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 9 i • Built-in Data types – – – Character_data types: CHAR, VARCHAR 2, NCHAR 2 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, TIMESTAMP • Supplied Data types – – any_types: SYS. Any. Type, SYS. Any. Data. Set XML_types: SYS. XMLType, SYS. Uri. Type spatial_types: MDSYS. SDO_Geometry media_types: ORDSYS. ORDAudio, ORDSYS. ORDImage, ORDSYS. ORDVIdeo

Support Data Types---DB 2 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, 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 – User. Defined. Datatype object • ANSI SQL Supported Data types – Most data types can be convert to System data types

Application Development Features • Oracle 9 i Oracle Programmer, Java, SQLJ, JDBC/ODBC, XML, Objects and extensibility, PL/SQL, User-defined aggregates, Globalization support, Autonomous transactions, i. SQL*Plus, Windows only feature---Microsoft Transaction Server integration, COM cartridge, App. Wizard for Visual Studio • DB 2 UDB 7 SQL types for stored procedures, functions, and methods. Changing Microsoft Visual Basic samples to use DB 2 as a data source, Linux support of Java stored procedures, save points in DB 2 SQL, List of JDCB methods and features that are not supported by DB 2, IBM OLE DB Native provider for DB 2, and Mapping JDBC transaction isolation levels to DB 2 isolation levels • SQL Server 2000 Transact-SQL, SQLXML, ODBC, ASP, Active Data object(ADO), OLE DB, English Query

Manageability Features • Oracle 9 i 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. • DB 2 UDB 7 DB 2’s self-management ability eliminates the need for DBA intervention. For Configuring a database DB 2 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 DB 2 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 9 i 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 • DB 2 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 C 2 level which is the highest security available in the industry.

Program Language Support Oracle 9 i DB 2 UDB 7. 2 SQL server 2000 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 9 i 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. • DB 2 UDB 7 Enterprise Performance Management designed to enable people to make better decisions. Activity Based Management People. Soft 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 9 i – – Oracle 9 i Application Server--- an integrated J 2 EE-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 • DB 2 UDB 7 – – – XML Extender Java Support Net Search Extender for a separately ordered feature of DB 2 EE XML Extender Net. Data enhancements—Web enabling technology for DB 2 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 9 i – – – Oracle Spatial extender can be used with Oracle 9 i 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 – – DB 2 UDB 7 provide a new Spatial Extender that can be hosted directly by DB 2 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 9 i – • • named user 1 processor $400 $15, 000 $40, 000 1 processor N/A $14, 350 $28, 000 Two simple license price plan include all feature DB 2 UDB 7 – 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. ---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 DB 2 is good for Data mining, Oracle is good for Internet application.

Ease of Use • Oracle 9 i: 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 • DB 2 UDB 7: Includes a complete suite of GUI administration tools that allow for easy installation, administration, and remote operations. DB 2 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 9 i 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. • DB 2 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 9 i 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 9 i is a fully object-oriented database, it can model any object in the real world, and finally it has most of the market share. • DB 2 UDB 7 has just move from main frame to Client/Server based database market. Similar to Oracle 9 i, 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!