POEC6383 Technology Assessment Report Database Software Comparison

1. POEC6383Technology Assessment ReportDatabase Software Comparison Adrian Avila Hua Lu Sarah Craren Nov. 1, 2001

2. 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

3. 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

4. Factors for SelectingRDBMS 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

5. Current Status of RDBMS Software • Three Major RDBMS Software

6. Platform and System Requirement • Platform • 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

7. 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--- Oracle 9i

8. Personal Edition: Provides a single-user object-relational database management system for a PC-based 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 Edition (EE): Provides a multi-user object-relational database for complex configurations and large database needs for Intel to UNIX platforms and fromuniprocessors 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. 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--- DB2 UDB 7.2

9. 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 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. Windows CE Edition ForWindows CE-based devices. Desktop Engine For offline data storage, it’s easy to install and has smallest foot print of all the editions. Developer Edition For developers to build any type of application on top of SQL Server. Evaluation Edition For 120 days demonstration, testing, and evaluation Product Family--- SQL Server 2000

10. 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

11. 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 • Build-in datatypes

12. 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

13. 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

14. 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.

15. 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.

16. Program Language Support

17. 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.

18. 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.

19. 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.

20. Price and Performance • Oracle 9i Personal Edition named user $400 Standard Edition 1 processor $15,000 Enterprise Edition 1 processor $40,000 • Two simple license price plan include all feature • DB2 UDB 7 Personal Edition N/A Standard Edition 1 processor $14,350 Enterprise Edition 1 processor $28,000 • Dose not include same feature as oracle , the real price may be higher • SQL Server Personal Edition Comes with Enterprise edition 2000 Standard Edition 1 processor $4,999 Enterprise Edition 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.

21. 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

22. 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.

23. 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.

24. Reference • www.oracle.com • www.ibm.com • www.microsoft.com Thank you!