doc

Document Sample
doc Powered By Docstoc
					   analysis of




   Group 18
Ezenne, Emmanuel
  Folkes, Denise
 Kennedy, Kemar
  McDonald, Eric
 Mclean Renbert
 Ramocan, Eliud
Analysis of IBM DB2® and Microsoft SQL Server 2005®



     Table of Contents
TITLE                                                              PAGE

1. Overview                                                        3
        1.1 Background of IBM DB2 UDB 8.1 ®
        1.2 Microsoft SQL Server 2005 ®
        1.3 Competition between IBM DB2® & Microsoft SQL Server®


2. Comparison                                                      7
        2.1 Operating System Support
        2.2 Hardware Requirements
        2.3 Software Requirements
        2.4 Performance
        2.5 TPC Tests
        2.6 Price
        2.7 Features
        2.8 Conclusion

3. Questions and Answers                                           15

4. Future Developments                                             23

5. Appendix                                                        25
        A. TPC Tests
        B. Security Features of Microsoft SQL Server

6. Glossary                                                        30

7. Bibliography                                                    31




                                                                        2
Analysis of IBM DB2® and Microsoft SQL Server 2005®



       Overview
This document will analyze the features of IBM DB2 UDB 8.1 and Microsoft SQL Server 2005.
But first, let us familiarize ourselves with the background of both relational database management
systems.


1.1      Background of IBM’s DB2®


DB2 has originated in the seventies where Dr. E.F. Codd, working for IBM, described the theory
of relational databases and in June of 1970 published the model for data manipulation. IBM,
however, did not believe in Codd's idea's potential, leaving its implementation to a group of
programmers who weren't under Codd's supervision, violating several fundamentals of the
relational model. For many years DB2 was exclusively available on IBM mainframes. Later IBM
brought DB2 to other platforms, including UNIX and Windows servers, then Linux (including Linux
on zSeries) and PDAs.


IBM has developed DB2® into a family of information management software products, which
currently comprises of thirteen products including DB2 UDB 8.1. Five of them are listed and
explained below:


      1. DB2 Connect- Make your company's host data
         directly available to your Personal Computer
         and LAN-based workstations.
      2. DB2 Everyplace- DB2 relational database and
         enterprise synchronization architecture for
         mobile and embedded devices.
      3. DB2 UDB Data Warehouse Editions- Deliver
         rich BI functionality inside the database
      4. DB2 Universal Database for z/OS- Use DB2 on
         the mainframe to run
         powerful enterprise applications,
         and make e-commerce a reality; and
         IBM’s flagship,
                                                               Figure 1- IBM DB2 Warehouse Edition
      5. DB2 Universal Database V8.1- IBM's relational
         database management system for AIX, Linux, HP-UX, Sun, and Windows.




                                                                                                     3
Analysis of IBM DB2® and Microsoft SQL Server 2005®



The latest version of DB2 UDB for Linux, Unix and Windows is version 8.2. This version contains
several features that improve the scalability, availability, and general robustness of the DB2
engine. In the scalability area, two significant features are materialized query tables and
multidimensional clustering. For availability, enhancements in the areas of on-line utilities and
replication implemented. In addition, this version provides autonomic features such as design
advisor and automatic memory tuning and monitoring. DB2 UDB 8.2 has seven (7) editions:


    1. DB2 Universal Database Enterprise Server Edition- is designed to meet the relational
        database server needs of mid- to large-size businesses.
    2. DB2 Universal Database Workgroup Server Unlimited Edition- offers a per-processor
        licensing model for relational database deployment in a departmental or workgroup or
        medium-sized business environment that has Internet users or number of users that
        makes per-processor licensing more attractive than the WSE per-user licensing model.
    3. DB2 Universal Database Workgroup Server Edition- is the relational database server
        of choice for deployment in a departmental or workgroup or medium-sized business
        environment that has a reasonable number of internal users. WSE is licensed on a per-
        user model, designed to provide an attractive price point for medium-size installations
        while providing a full-function relational database server.
    4. DB2 Universal Database Express Edition- IBM DB2 Universal Database Express
        Edition V8.1 is the lowest priced full function relational database, featuring self-tuning and
        self-configuring autonomic capabilities for ease of manageability on Linux or Windows
        platform choices. DB2 UDB Express Edition is designed to reduce the total costs of
        ownership for mid-market businesses.
    5. DB2 Universal Database Personal Edition- provides a single user full-function
        relational database, with built-in replication, ideal for desktop or laptop PC-based
        deployments. DB2 PE can be remotely managed, making it the perfect choice for
        deployment in occasionally connected or remote office implementations that don't require
        multi-user capability.
    6. DB2 Universal Database Universal Developer's Edition- offers a low cost package for
        a single application developer to design, build, and prototype applications for deployment
        on any of the DB2 client or server platforms.
    7. DB2 Universal Database Personal Developer's Edition- enables a developer to design
        and build single user desktop applications.




                                                                                                    4
Analysis of IBM DB2® and Microsoft SQL Server 2005®

1.2       Background of Microsoft SQL Server®


Originally, in about 1989, Microsoft teamed up with Sybase and Ashon-Tate to create and market
the first version of SQL Server 4.2, which was essentially the same as Sybase’s SQL Server 4.0.
Soon Sybase and Microsoft parted ways and pursued their own designs and marketing schemes,
making Microsoft SQL Server 6.0, Microsoft’s first version without any influence of Sybase.
Several versions have been done independently with version 7.0 took on a GUI interface and
SQL Server 2000 which became Microsoft’s the first commercial database rivaling Oracle and
IBM servers for the enterprise
market.



It took a long five years for the
next and latest version, Microsoft
SQL Server 2005, to be released.
Its price is now up to 25% higher
than SQL Server 2000, but
Microsoft has made some great
advances in functionality
integrated with other components
that make up the Microsoft
Windows Server 2003 that make
up the difference (see figure 2).                                                Figure 2- Components of SQL Server



Microsoft SQL Server 2005 has six (6) editions:


      1. SQL Server 2005 Enterprise Edition
          Enterprise Edition includes the complete set of enterprise data management and
          business intelligence features. SQL Server Enterprise offers the highest levels of
          scalability and availability of all the SQL Server 2005 editions. Additionally, it is optimized
          to run on x64- and Itanium-based servers, helping you achieve higher levels of database
          scalability and availability.
      2. SQL Server 2005 Standard Edition
          Standard Edition is an affordable option for small- and medium-sized organizations. It
          includes the core functionality needed for noncritical e-commerce, data warehousing, and
          line-of-business solutions. Standard Edition is optimized to run on win32, x64, and
          Itanium-based servers.



                                                                                                        5
Analysis of IBM DB2® and Microsoft SQL Server 2005®

      3. SQL Server 2005 Workgroup Edition
         Workgroup Edition is the data management solution for small organizations or
         workgroups within larger entities. It includes all the core database features needed for
         data management in an affordable and simple-to-manage package.
      4. SQL Server 2005 Express Edition
         Express Edition helps developers build robust and reliable applications by providing a
         free, easy to use, and robust database when protecting and managing information inside
         and outside of applications is critical.
      5. SQL Server 2005 Developer Edition
         Developer Edition is designed to enable developers to build any type of application on
         top of SQL Server 2005. It includes all the functionality of Enterprise Edition (win32, x64,
         IA64) but with a special development and test license agreement that prohibits
         production deployment.
      6. SQL Server 2005 Mobile Edition
         Mobile Edition enables you to rapidly develop applications that extend enterprise data
         management and business intelligence capabilities to mobile devices.


1.3      Competition between IBM DB2® and Microsoft SQL Server®
Microsoft's primary competition includes Oracle and IBM DB2 and is currently ranked #3 in
revenue share among these top three database vendors. Oracle tends to be more competitive
with the mid-range database market, especially among the many customers concerned about
cross-platform compatibility. IBM tends to fare decently in the mid-range market and dominates
the high-end market, particularly with its z/OS database which is preeminent for enterprise OLTP.
In contrast to many of its competitors, SQL Server runs solely on Microsoft Windows based
operating systems. As a result, the relative strength and viability of SQL Server is directly affected
by the relative strengths and weaknesses of the Microsoft Windows platform. However, IBM and
Oracle beats Microsoft in any cross-platform contest by default. However, as the Comparison
section of this document will show, both have there special features and limitations in the areas of
interoperability, performance and price. Therefore to determine which is better or more suitable
must be placed in the context of the client’s application domain and its short-term and long-term
business targets and strategies. Questions of this nature are address in the Frequently Asked
Questions (FAQs) section. A glossary is available at the end of the document to amplify the
meaning of jargons and acronyms used in the study of database management systems.
.




                                                                                                    6
Analysis of IBM DB2® and Microsoft SQL Server 2005®

   Comparison
    Comparison
This document compares SQL Server 2005 with DB2 Universal Database version 8.1 based on
operating system support, hardware requirements, software requirements, performance, TPC
tests, price, features, and limitations.


2.1 Operating System Support
Microsoft SQL Server 2005 only works on Windows-based platforms, including Windows 9x,
Windows NT, Windows 2000\XP, Windows Server 2003 and Windows CE. This intrinsically limits
SQL Server to limitations of the Windows platform. In comparison with SQL Server 2005, DB2
Universal Database version 8.1 supports all known platforms, including Windows-based
platforms, AIX-based systems, HP-UX systems, Linux Intel, Sun Solaris and others. IBM and
other multi-platform information management systems are clear winners in this category.
However, Microsoft claims that clients deserve nothing less than superiority of the windows
platform explaining it facilitates the integration of SQL Server with other Microsoft applications
such as, Microsoft Visual Studio, Microsoft Office Suite and Microsoft SharePoint Portal Server,
which are now all comprised in Microsoft Windows Server 2003.




2.2 Hardware Requirements

To install SQL Server 2005, you should have the Intel or compatible platforms and the following
hardware:


   Hardware                                    Requirements
   Processor       Pentium 166 MHz or higher
                   32 MB RAM (minimum for Desktop Engine),
    Memory         64 MB RAM (minimum for all other editions),
                   128 MB RAM or more recommended
                270 MB (full installation),
                250 MB (typical),
                95 MB (minimum),
Hard disk space
                Desktop Engine: 44 MB
                Analysis Services: 50 MB minimum and 130 MB typical
                English Query: 80 MB


DB2 Universal Database v8.1 supports Windows-based platforms, AIX-based systems, HP-UX
systems, Linux Intel, Sun Solaris and so on.




                                                                                                     7
Analysis of IBM DB2® and Microsoft SQL Server 2005®

To install DB2 Universal Database v8.1 under the Windows-based platforms, you should have the
following hardware:


   Hardware                                Requirements
   Processor      Pentium or Pentium compatible CPU
                  RAM: 256 MB minimum,
    Memory
                  additional memory may be required.
                  Typical installation: 350 Mb minimum
                  Compact installation: 100 Mb minimum
                  Custom installation: 100 Mb minimum.
Hard disk space
                  Additional disk space may be required
                  on FAT drives with large cluster size.


To install DB2 Universal Database v8.1 under the UNIX Systems, such as AIX-based systems,
HP-UX systems, Linux and Sun Solaris, you should have the following hardware:


   Hardware                                Requirements
                  For AIX:
                      IBM RISC/6000 or eServer pSeries.

                  For HP-UX:
                      HP 9000 series 700 or 800 system.

                  For Linux:
   Processor          Intel 32-bit,
                      Intel 64-bit,
                      S/390 9672 generation or higher,
                      Multiprise 3000,
                      eServer z-Series.

                  For Solaris:
                      Solaris UltraSPARC-based computer.
                  RAM: 256 MB minimum,
    Memory
                  additional memory may be required.
                Typical installation: 450 to 550 Mb minimum
Hard disk space Compact installation: 350 to 400 Mb minimum
                Custom installation: 350 to 700 Mb minimum.




                                                                                            8
Analysis of IBM DB2® and Microsoft SQL Server 2005®


2.3 Software Requirements
SQL Server 2005


  Operating     Enterprise       Standard    Personal    Developer      Desktop     SQL
   System        Edition          Edition     Edition     Edition       Engine    Server CE
Windows CE           No                No      No            No             No       Yes
Windows 9x           No                No      Yes           No             Yes      No
Windows NT
4.0
Workstation          No                No      Yes           Yes            Yes      No
with Service
Pack 5
Windows NT
4.0 Server
                     Yes               Yes     Yes           Yes            Yes      No
with Service
Pack 5
Windows NT
4.0 Server
Enterprise
                     Yes               Yes     Yes           Yes            Yes      No
Edition with
Service Pack
5
Windows
2000                 No                No      Yes           Yes            Yes      No
Professional
Windows
                     Yes               Yes     Yes           Yes            Yes      No
2000 Server
Windows
2000
                     Yes               Yes     Yes           Yes            Yes      No
Advanced
Server
Windows
2000                 Yes               Yes     Yes           Yes            Yes      No
DataCenter
Windows XP
                     No                No      Yes           Yes            Yes      No
Professional


IBM DB2 requires the following software:


   Platform        Operating System Version                   Required Patches
  Windows-
                  Windows NT 4.0                Service Pack 6a or higher
   based
  Windows-                                      Service Pack 2 is required for
                  Windows 2000
   based                                        Windows Terminal Server
  Windows-
                  Windows XP                    Not Necessary
   based
                                                Maintenance Level 9 or later, and
  AIX-Based       AIX 4.3.3 (32-bit)
                                                APARs IY22308, Y32690, and IY33024



                                                                                           9
Analysis of IBM DB2® and Microsoft SQL Server 2005®


  AIX-Based       AIX 5L (32-bit)                  Maintenance Level 2 or later
                                                   Maintenance Level 2 or later, and
  AIX-Based       AIX 5.1.0 (32-bit)               APARs IY31254, IY32217, IY32905,
                                                   IY33023, and IY29345
                                                   Maintenance Level 2 or later, and
  AIX-Based       AIX 5.1.0 (64-bit)               APARs IY31254, IY32217, IY32905,
                                                   Y33023, and IY32466
                                                   December 2001 GOLDBASE11i,
     HP-UX        HP-UX 11i
                                                   December 2001 GOLDAPPS11i bundles
                  For Intel 32-bit:
                   kernel level 2.4.9 or later
                   glibc 2.2.4 or later
                   RPM 3 or later
     Linux                                         Not Necessary
                  For Intel 64-bit and z-Series:
                   Red Hat Linux 7.2
                   SuSE Linux SLES-7
  Sun Solaris     Solaris 7 (32-bit)               patch 106327-10
  Sun Solaris     Solaris 7 (64-bit)               patch 106300-11
  Sun Solaris     Solaris 8 (32-bit)               patch 108434-03 and 108528-12
  Sun Solaris     Solaris 8 (64-bit)               patch 108435-03 and 108528-12
  Sun Solaris     Solaris 9                        Not Necessary




2.4 Performance

It is very difficult to make the performance comparison between SQL Server 2005 and DB2
Universal Database v8.1. The performance of your databases depends rather from the
experience of the database developers and database administrator than from the database's
provider. You can use both of these RDBMS to build stable and efficient system. However, it is
possible to define the typical transactions, which used in inventory control systems, airline
reservation systems and banking systems. After defining these typical transactions, it is possible
to run them under the different database management systems working on the different hardware
and software platforms. The TPC Test (below) provides standardize performance benchmarks
that assume a specific volume of data, and design and architectural platform.


2.5 TPC Tests

Based on Top Ten TPC-C by Performance Version 5 Results As of 6-Apr-2006 4:09 PM [GMT],
IBM DB2 8.1 on IBM eServer p5 595 64p was ranked #1 and Microsoft SQL Server on Microsoft
Windows Server 2003 Datacenter Ed. ranked #3 (See Appendix A.1).




                                                                                                 10
Analysis of IBM DB2® and Microsoft SQL Server 2005®

Based on Top Ten TPC-C by Price/Performance Version 5 Results As of 6-Apr-2006 4:23 PM
[GMT], Microsoft SQL Server 2005 ranked #1 to #5 then IBM #6. (See Appendix A.2).

Because most organizations really do not run very large databases, the key points on which IBM
DB2 and Microsoft SQL Server won the TPC-C Performance benchmark and TPC-C
Performance/Price benchmark, respectively, do not really matter to the vast majority of
companies.


2.6 Price & Total Ownership Costs

        2.6.1 Price
                Price considers the cost to buy the software and standard installation costs, but
                excludes implicit economic costs and preferential discounts. Even though SQL
                Sever 2005 costs about 25% more than version 2000, it still maintains its
                competitive advantage as being the cheaper DBMS in comparison with DB2
                Universal Database v8.1. Other SQL Server advantage is that Microsoft includes
                the Online analytical processing (OLAP) and Data Mining as standard features in
                SQL Server 2005 Enterprise Edition. So, you can save up to four times with SQL
                Server 2005 Enterprise Edition if you use OLAP and Data Mining.
                Compare pricing for SQL Server 2005 Standard Edition and IBM DB2 v8.1
                Workgroup Edition:

                  Number of            IBM DB2 v8.1            SQL Server 2005
                    CPUs             Workgroup Edition         Standard Edition
                          1      $7,500                                       $6200
                          2      $15,000                                    $12,499
                          4      $30,000                                    $24,995
                          8      $60,000                                    $49,990
                       16        $120,000                                   $99,980
                       32        $240,000                                  $199,960


                Compare pricing for SQL Server 2005 Enterprise Edition (which include OLAP
                and Data Mining) and IBM DB2 v8.1 Enterprise Edition with OLAP and Data
                Mining:


                                                       IBM DB2 v8.1            SQL Server
                                  IBM DB2 v8.1
                  Number of                          Enterprise Edition          2005
                                    Enterprise
                    CPUs                            With OLAP and Data         Enterprise
                                     Edition
                                                          Mining                Edition
                          1      $25,000           $113,000                           $24,999
                          2      $50,000           $126,000                           $49,999



                                                                                                11
Analysis of IBM DB2® and Microsoft SQL Server 2005®


                          4          $100,000            $252,000                           $99,990
                          8          $200,000            $504,000                          $199,990
                         16          $400,000            $1,008,000                        $399,980
                         32          $800,000            $2,016,000                        $799,960


                  Note. This is not a full price comparison between SQL Server 2005 and DB2 Universal
                  Database v8.1. It is only a brief comparison. You can have any discounts and the prices
                  can be increased or decreased in the future. See Microsoft and IBM to get more
                  information about the price of their products.


        2.6.2 Total Ownership Costs (TOC)
                  Total Ownership Cost is the lifetime costs of a product. This includes the initial
                  hardware and software purchase price as well as installation, service, support,
                  upgrades, training, downtime and other factors. Both Microsoft and IBM on their
                  official websites claim that they have the lowest TOC, each quoting from different
                  reputable independent research firm. Neither of them is lying. Depending on the
                  assumptions of the business environment that the DBMS’s were operating, each
                  report yielded different results. The fact is, total ownership cost has a lot to do
                  with the owner’s (that is the client’s) business environment. That is the cost to
                  provide their service (including capital costs, technical support, administration
                  and end-user operations), customer satisfaction, quality levels and productivity—
                  some of which are difficult to quantify unless narrow assumptions are made.


2.7 Features

Both SQL Server 2005 and IBM DB2 Universal Database v8.1 support the ANSI SQL-92 entry
level and do not support the ANSI SQL-92 intermediate level. This section seeks to compare their
SQL dialects, their GUI, and to highlight some limitations.


        2.7.1 SQL Dialect
        Microsoft SQL Server 2005 supports the SQL dialect called Transact-SQL, while the IBM
        DB2 v8.1 supports DB2 SQL. DB2 SQL dialect is a more verbose language than T-SQL.
        The table below serves to briefly compare their features:


        Feature                       DB2 SQL dialect                              T-SQL
                            Relational tables,
                                                                    Relational tables,
        Tables              Object tables,
                                                                    Temporary tables
                            Temporary tables
                            BEFORE triggers,                        AFTER triggers,
       Triggers
                            AFTER triggers,                         INSTEAD OF triggers


                                                                                                        12
Analysis of IBM DB2® and Microsoft SQL Server 2005®


                          INSTEAD OF triggers
                          DB2 SQL dialect statements,
                          Java methods,
     Procedures                                               T-SQL statements
                          third-generation language
                          (3GL) routines
                          External Scalar functions,
                          External table functions,           Scalar functions,
     User-defined         OLE DB External table functions,    Inline table-valued functions,
      functions           Sourced or Template functions,      Multistatement table-valued
                          SQL Scalar, Table or Row            functions
                          functions
        Arrays            Supported                           Not Supported


        2.7.2 Limitations
        Both information management systems have their limitations. Below serves to highlight
        some of them:

                         Feature                     SQL Server 2005             IBM DB2 v8.1
           column name length                   128                       128
           index name length                    128                       128
           table name length                    128                       128
           view name length                     128                       128
           cursor name length                   128                       18
           constraint name length               128                       18
           password length                      128                       32
           SQL variable length                  128                       64
           user name length                     128                       30
           index length                         900                       1024
           max char() size                      8000                      254
           max varchar() size                   8000                      32672
           max columns per table                1024                      1012
           max table row length                 8036                      32677
           max columns per index                16                        16
           max indexes per table                250                       32767
           longest SQL statement                16777216                  65535
           columns per SELECT statement 4096                              1012
           columns per INSERT statement 1024                              1012
           nested stored procedure levels 32                              16


2.8 Conclusion
It is not true that SQL Server 2005 is better than DB2 Universal Database v8.1 or vice versa. Both
products can be used to build stable and efficient systems and the stability and effectiveness of
an applications and databases depend rather from the experience of the database developers



                                                                                                    13
Analysis of IBM DB2® and Microsoft SQL Server 2005®

and database administrator than from the database's provider. But SQL Server 2005 has some
advantages in comparison with DB2 Universal Database v8.1 and vice versa.


The SQL Server 2005 advantages:


      SQL Server 2005 is cheaper to buy than DB2 v8.1 Database.
      SQL Server 2005 holds the top TPC-C performance and price/performance results.
      SQL Server 2005 is generally accepted as easier to install, use and manage.


The DB2 Universal Database v8.1 advantages:


      DB2 Universal Database v8.1 supports all known platforms, not only the Windows-based
       platforms.
      IBM DB@ v8.1 holds the top TPC-C performance and Performance results
      DB2 SQL dialect is more powerful language than T-SQL.
      More fine-tuning to the configuration can be done via start-up parameters.




                                                                                             14
Analysis of IBM DB2® and Microsoft SQL Server 2005®



    Questions & Answers
The questions are formulated to extract the depth of understanding of the facts and issues about
the information management systems: IBM DB2 UDB and Microsoft SQL Server. Questions are
categorized into Product knowledge, Integration, Security, Mobility, User Interface and Open
Source Databases.


Product Knowledge




Q    What is the difference between SQL Server Standard Edition and SQL Server Enterprise
     Edition?




 A        Answer written by Renbert McLean
          SQL Server 2005 Enterprise Edition includes the complete set of enterprise data
          management and business intelligence features. SQL Server Enterprise offers the
        highest levels of scalability and availability of all the SQL Server 2005 editions.
        Additionally, it is optimized to run on x64- and Itanium-based servers, helping you
        achieve higher levels of database scalability and availability.
        SQL Server 2005 Standard Edition is an affordable option for small- and medium-sized
        organizations. It includes the core functionality needed for non-critical e-commerce, data
        warehousing, and line-of-business solutions. Standard Edition is optimized to run on
        win32, x64, and Itanium-based servers.
        The table below serves to highlight some main differences between SQL Server
        Standard and SQL Server Enterprise editions:
         Feature                         MS SQL Server Standard               MS SQL Server
                                         Edition                              Enterprise Edition
         Suitability for Business        Small and Medium sized               Large sized
         Size                            organizations                        organizations
         Optimize to run on              Win32, x64, and Itanium-based        x64- and Itanium-based
                                         servers.                             servers (not optimized for
                                                                              win32)
         Number of CPUs                  4                                    No Limit
         Fast Recovery                   No
         Oracle Replication              No
         Advanced Business               No
         Analytics
         Proactive Caching               No
         Advanced Data                   No
         Management



                                                                                                   15
Analysis of IBM DB2® and Microsoft SQL Server 2005®



Integration




Q   It is a fact that DB2 and SQL Server are competitors who are always striving to dominate the
    market demanding information management.


    i.) Why then do they offer integration with software from their competitors?


    ii.) How does IBM DB2 allow you to access information from other databases Can you give
    an example to show how IBM DB2 makes integration with a competitors database system
    possible.




A        Answer written by Eliud Ramocan
         i.) It is a fact that many large multinational organizations use a combination of DB2,
         SQL Server and Oracle. Therefore, it is more advantageous for each information
       management system to provide an interface to interact with other databases, rather than
       to try imposing high implementation costs and training cost on their clients just to retrieve
       some data from a different brand database. Also, integration tools make each DBMS
       more versatile, which is an advantage for the database product.


       However, to ensure that they protect their market share, systems such as SQL Server
       provide a toolbar add-in with limited functionality for the user to interact with IBM DB2
       databases.

       For example, although you can view DB2 tables, views, and procedures with Visual
       Studio Server Explorer, much more functionality is available for the DB2 database by
       using the IBM Explorer add-in. The IBM Explorer includes functionality for DB2 databases
       this is similar to Server Explorer's functionality for SQL Server databases. This includes
       the ability to view and create tables, indexes, triggers, views, procedures, and functions
       using a series of wizards. Additionally you can view and update data in tables and views,
       and filter the data that is shown in the view/update pane as well. For Windows .NET
       clients, there is an option to create a DB2DataAdapter (an object that encapsulates
       SELECT/INSERT/UPDATE/DELETE) on IBM Explorer. There is an easy-to-use wizard
       for this when using SQL Server.

       Another good example is, while both DB2 UDB 8.2 and SQL Server 2005 have been
       integrated with Visual Studio and the .NET, SQL Server's integration is more
       comprehensive, more seamless, more functional, and better performing than DB2 UDB
       8.2.


                                                                                                   16
Analysis of IBM DB2® and Microsoft SQL Server 2005®

       DB2 UDB provides integration with Visual Studio as a plug-in to Visual Studio called "IBM
       DB2 Development Add-Ins for Visual Studio .NET." The DB2 add-ins offer quite a bit of
       Visual Studio functionality, but do not offer some important features that have an impact
       on developer productivity, including:

                Debugging of .NET stored procedures through Visual Studio.

                Automatic deployment of all .NET database objects.

                Integration of BI Technologies

       Therefore, if a the DB2 DBA wishes to manipulate a MS SQL Server databases using the
             above three functionalities they would have to either purchase MS SQL Server or
             learn some other third party software to meet their needs.




Q    I am the CEO of a fashion designing company, called Folkes Enterprise. I have been using
     Microsoft SQL Server for years now. However, I recently decided that Unix Operating
     System is the operating system for my company. Microsoft SQL Server cannot work on a
     Unix Operating System.
     Can I change from Microsoft SQL Server to IBM DB2 without doing it from scratch? If yes,
     what reasons would encourage me migrate from SQL Server to IBM DB2?




A          Answer written by Denise Folkes

           Yes, it is possible to change one’s database system from Microsoft SQL Server
           to IBM DB2. One can do that by using SQL Ways migration tool, IBM DB2 migration
           toolkit or any other migration tool. I would be encouraged to do so because of the great
           features IBM DB2 offers compared with Microsoft SQL Server. These include:
       o     IBM DB2 works on all known platforms. If I decide to use another operating
             system in the future it would be facilitated and I would not need to change my
             database system.
       o     IBM DB2 handles work loads from high-volume online transaction processing
             (OLTP) to complex multi-user queries while maintaining excellent performance.
       o     IBM DB2 provides integrated support for complex data. It allows storage and
             manipulation in the database of non-traditional data such as images, voice, video,
             complex documents, etc. These can be brought together in one SQL query and
             manipulated by in-built function. User can define a new type by using in-built support
             user defined data types and functions. It supports up to 2 GB of data and provides
             user triggers to ensure referential integrity.




                                                                                                  17
Analysis of IBM DB2® and Microsoft SQL Server 2005®

       o IBM DB2 uses one or more table space for each database. Having table spaces is
           much more advantageous because they require less administration. Microsoft SQL
           Server, on the other hand, has a temporary database called tempdb which stores
           data for temporary working storage needs (e.g. intermediate results for group by or
           order by) or temporary tables.




Security



Q       What security features does SQL Server provide?




A           Refer to Appendix B.




Q        What does DB2 use to enforce concurrency control and isolation?




 A           Answer written by Emmanuel Ezenne

              For isolation, DB2 supports the repeatable read (RR), read stability (RS), cursor
              stability (CS) and uncommitted read (UR) modes. Applications are able to choose
            their level of isolation at eh binding stage. The various isolation modes are
            implemented by using locks (see table below). DB2 supports record-level locks and
            table-level locks. A separate lock table data structure maintains lock information.
             Lock Mode                        Objects                  Interpretation
             IN (intent none)                 Tablespaces, tables      Read with no row locks
             IS (intent share)                Tablespaces, tables      Read with row locks
             NS (next key share)              Rows                     Read locks for RS or CS
                                                                       isolation levels
             S (share)                        Rows, tables             Read lock
             IX (intent exclusive)            Tablespaces, tables      Intend to update rows
             SIX (share with intent           Tables                   No read locks on rows but X
             exclusive)                                                locks on updated rows
             U (update)                       Rows, tables             Update lock but allows others
                                                                       to read
             NX (next-key exclusive)          Rows                     Next key lock for inserts/
                                                                       deletes to prevent phantom
                                                                       reads during RR index scans
             X (exclusive)                    Rows, tables             Only uncommitted readers
                                                                       allowed
             Z (super-exclusive)              Tablespaces, tables      Complete exclusive access

            A deadlock detection agent is activated for each database and periodically checks
            for deadlocks between transactions. The interval for deadlock detection is a
            configurable parameter. In case of a deadlock, the agent chooses a victim and
            aborts it with a deadlock SQL error code.



                                                                                                 18
Analysis of IBM DB2® and Microsoft SQL Server 2005®



Mobility




Q
        The transmission of information through wireless technology is exponentially increasing.
        What products do MS SQL Server and IBM DB2 provide to address this development?




A             Answer written by Kemar Kennedy


              MS SQL Server has a mobile edition called Microsoft SQL Server Mobile Edition.
       IBM DB2’s mobile edition is called DB2 Everyplace (see snap shots below). Both SQL
       Server and DB2’s mobile editions offer:
            Relational database functionality in the small footprint necessary for the memory
             limitations of today's mobile devices.
            Ability for data to be updated simultaneously on the device and the server,
             while accommodating for wireless network disruptions.
            Strong Data Security- enables the use of 128-bit encryption during data
             synchronization (over the wire) as well as RSA 128-bit data file encryption and
             database passwords to ensure data security on your mobile database.




           Microsoft SQL Server Mobile Edition
           (screenshot)

                                                                             IBM DB2 Everyplace (Screenshots)




                                                                                                  19
Analysis of IBM DB2® and Microsoft SQL Server 2005®


User Interface


Q           Does IBM DB2 provide a GUI? And does Microsoft SQL Server provide a command
            line interface?




A               Answer written by Eric McDonald
                Yes. Both IBM DB2 and Microsoft SQL Server provide GUI and command line
interfaces. If you prefer using a command line interface, for SQL Server you are used to the iSQL
utility. The equivalent interface in DB2 UDB is the command line processor (CLP).The figure
below shows theDB2 UDB CLP utility. We first execute from a command prompt window the
command db2cmd to open a command window. This will initialize the DB2 environment required
to run the CLP. We then invoke the CLP by executing the command db2 (see below).


SQL Server and DB2 UDB both provide command line interfaces that are also GUI tools. SQL
Server has the Query Analyzer. The equivalent in DB2 UDB is the Command Center, shown in




 MS SQL Server iSQL utility




                                                      DB2 UDB Command Line Processor
below.




Open Source Databases
      Open source databases like PostgreSQL and MySQL have been challenging the database
      giants like IBM, Oracle and Microsoft. What strategies have IBM DB2 and Microsoft SQL


                                                                                              20
Analysis of IBM DB2® and Microsoft SQL Server 2005®



Q       Server used to combat the growing popularity and competition from open source
        databases? In what ways are there strategies different?




 A           By Eliud Ramocan
             IBM DB2 does not consider open source a threat to the market, because of it
             believes commercial products will always have an upper hand for the high-end of the
market which IBM along wit Oracle dominate. Microsoft also claims immunity to open source
databases. But out of the three leading database vendors, Microsoft has more business in the
lower end of the market, thus they would most likely be affected the most out of the three.
However, it must be noted that SQL Server is not exactly what keeps Microsoft up and running as
a business. That software giant has plenty of other revenue streams to rest its head on. Even if a
MySQL were to eat away at its market share, it isn't likely that Microsoft will be that affected.

However, all three giant database vendors are taking strategic precautions to maintain their
claimed immunity to the rise of open source databases.

Strategy 1: Both Microsoft and IBM have Express Editions: Microsoft SQL Server Express
Edition 2005 and IBM’s DB2 Universal Database Express-C, respectively. Both express editions
are powerful and reliable data management systems that deliver rich features, data protection,
and performance for embedded application clients, light Web applications, and local data stores.
The strategy used is that users must register to use express editions, which both IBM and
Microsoft may use as a marketing tool for Express Edition users to upgrade to editions that
require purchase. However, there are some differences:
        SQL Server Express is free, DB2 Express-C is free, but other versions of Express attract
         a relatively small fee.
        DB2 Express is available on Linux and Windows, while (of course) SQL Server Express
         is limited to the Windows platform.
        SQL Server Express limits its database size to 4 GB and limits the number of concurrent
         users, while IBM’s Express has no limit on database size or number of concurrent users.
        SQL Server Express can only run on 1 CPU. DB2 Express can run on 2 CPUs (both
         which could be dual-core).

Strategy 2: Both Microsoft and IBM have been evolving themselves in to a software platform
provider that offers a complete managed environment, in order to be picked apart from the bottom
by this new disruptive force in the market. Microsoft has being marketing SQL Server as a
seamless integration Microsoft Windows Server 2003, Office 2003 and Visual Studios. IBM DB2
also promotes itself with Linux (which interestingly originated from open source) and members of
the IBM DB2 family. This strategy changes their image into a software platform provider that
provides integrated solutions for business clients in the mid-range to high end of the market—and
not merely a database vendor.


                                                                                                    21
Analysis of IBM DB2® and Microsoft SQL Server 2005®



      Future Developments
SQL Server’s Katmai


SQL Server’s next release, codenamed Katmai, is work-in-progress. Not much information is
known about its proposed features as Microsoft is waiting feedback about SQL Server 2005.
However, Microsoft is working on putting more intelligence in their business intelligence (BI)
component. In fact they have recently sent out surveys to a number of business customers to get
direct feedback on BI itself. SQL Server took five years to release SQL Server 2005. Hopefully it
won’t take that long for Katmai to be released.


IBM DB2’s Viper


As of December 2005, IBM has begun talking about "Viper," the codename for DB2 UDB Version
9 on distributed platforms, and "V.Next," referring to the next release of DB2 UDB for z/OS. IBM
claims that the new DB2 will be the first relational database to "natively" store XML. Other likely
enhancements include OLTP-related improvements for distributed platforms, business
intelligence/data warehousing-related improvements for z/OS, more self-tuning and self-
managing features, additional 64-bit exploitation (especially for z/OS), and continued
convergence of the SQL vocabularies between z/OS and distributed platforms.


Rise of Open Source Databases


Open-source databases took a giant step toward the mainstream in 2004 when Hewlett-Packard
began supporting MySQL and certifying it to run on HP servers--the first major system vendor to
do so. HP's move adds to the growing evidence that open-source databases--primarily MySQL--
are becoming a viable alternative to commercial databases from IBM, Microsoft, and Oracle. It's
no longer the lunatic fringe.


MySQL, PostgreSQL, Firebird and other open source databases have been making significant
strides that may soon affect the market shares of the leading database vendors such as IBM,
Oracle and Microsoft. But why go to an open-source database when Oracle 9i, Microsoft SQL
Server and IBM DB2 are held in such high regard? In short, for the same reason why businesses
are moving to Linux platforms: to save on total-cost-of-ownership, among other areas.


IDC analyst Carl Olofson summed up open-source databases technology: "Open source
relational database management system (RDBMS) software is a disruptive technology. As such,



                                                                                                      22
Analysis of IBM DB2® and Microsoft SQL Server 2005®

it is used, not to address the high end requirements of those who demand the latest and greatest,
but to address the needs of application and tools developers and vendors who are looking for just
enough database technology to provide an affordable solution."


The PostgreSQL core development team asserts that it has a much more ambitious goal than
competing. It wants to be the best RDBMS period, Open Source or proprietary. They have
already made some steps toward this goal by designing features which no proprietary database
has, like our support of more than a half-dozen procedural languages and support for certain
advanced parts of the SQL standard, among other things."


MySQL has been building and users have been coming: MySQL has seen an estimated 5 million
installations and over 30,000 downloads per day for its flagship product. The company counts
Yahoo!, Lucent Technologies, Sony Pictures Digital Entertainment among its customers. Open
source database is something that cannot be ignored for the future. Microsoft SQL Server and
IBM DB2 have already implemented some strategies that will maintain a level of immunity to the
potential disruption that open source databases may cause (see question in previous section
about Open Source Databases). So MySQL is already changing the database market, for users
and vendors, albeit in small ways. Judging by the momentum it has developed, its clear MySQL's
influence will become more pronounced. It's just a question of how quickly and how big.




                                                                                               23
Analysis of IBM DB2® and Microsoft SQL Server 2005®



       Appendix



Contents:

Appendix A: TPC Tests

Appendix B: Security Features of Microsoft SQL Server 2005




                                                             24
Analysis of IBM DB2® and Microsoft SQL Server 2005®


                                      Appendix A

                                      TPC Tests

Appendix A.1
Table Showing Top Ten TPC-C by Performance Version 5 Results As of 6-Apr-2006 4:09
PM [GMT]

                                                                               Operating
                Database              System          tpmC        Price/tpmC   System        Cluster
 1                      IBM DB2       IBM             3,210,540   5.07 US $    IBM AIX       N
                        UDB 8.2       eServer p5                               5L V5.3
                                      595 64p




 2                       Oracle       IBM             1,601,784   5.05 US $    IBM AIX       N
                         Database     eServer p5                               5L V5.3
                         10g          595 32p
                         Enterprise
                         Edition


 3                       Microsoft    hp Integrity    1,231,433   4.82 US $    Microsoft     N
                         SQL          Superdome                                Windows
                         Server                                                Server
                         2005                                                  2003
                         Enterprise                                            Datacenter
                         Edt SP1                                               Ed.(64-
                                                                               bit)SP1
 4                       Oracle       HP              1,184,893   5.52 US $    Red Hat       Y
                         Database     Integrity                                Enterprise
                         10g          rx5670                                   Linux AS 3
                         Enterprise   Cluster
                         Edition      64P


 5                       IBM DB2      IBM             1,025,486   5.43 US $    IBM AIX       N
                         UDB 8.1      eServer                                  5L V5.2
                                      pSeries
                                      690 Model
                                      7040-681




                                                                                            25
Analysis of IBM DB2® and Microsoft SQL Server 2005®




Appendix A.2

Top Ten TPC-C by Price/Performance Version 5 Results As of 6-Apr-2006 4:23 PM [GMT]
                                                                              Operating
 Rank          Database            System               tpmC      Price/tpmC System          Cluster
 1                      Microsoft  PowerEdge            38,622 .99 US $       Microsoft      N
                        SQL        2800/1/2.8GHz/2+2M                         Windows
                        Server                                                2003 x64
                        2005 x64                                              Server
                        Std. Ed.                                              Std. Ed.
                         Microsoft    PowerEdge            28,244   1.29 US $   Microsoft    N
                         SQL          2800/1/3.6GHz/2M                          Windows
                         Server                                                 Server
                         2005                                                   2003
                         Workgroup                                              Standard
                         Ed.                                                    Edition
 3                       Microsoft    PowerEdge            28,122   1.40 US $   Microsoft    N
                         SQL          2800/1/3.4GHz/2M                          Windows
                         Server                                                 Server
                         2000                                                   2003
                         Workgroup                                              Server
                         Ed.
 4                       Microsoft    PowerEdge            26,410   1.53 US $   Microsoft    N
                         SQL          2850/1/3.4GHz/1M                          Windows
                         Server                                                 Server
                         2000                                                   2003
                         Standard                                               Server
                         Ed.
 5                       Microsoft    HP ProLiant          17,810   1.57 US $   Microsoft    N
                         SQL          ML350T03                                  Windows
                         Server       X3.06/533/512K                            Server
                         2000         SA641                                     2003
                         Standard                                               Standard
                         Ed. SP3                                                Edition


 6                        IBM DB2     HP ProLiant ML350-   18,661   1.61 US $   SUSE         N
                         UDB          T03-X3.06/533                             LINUX
                         Express                                                Enterprise
                         Edition                                                Server 9
                         v8.1




                                                                                     26
Analysis of IBM DB2® and Microsoft SQL Server 2005®


                                        Appendix B


                 Security Features of Microsoft SQL Server 2005

The table below provides an overview of new and enhanced security features in SQL Server
2005.

Feature                   Description
Off by default            SQL Server 2005 enables only a limited number of core features and
                          services by default, thereby limiting the exposed surface area of the
                          server and allowing administrators to enable only those services and
                          features that are necessary in their environment.

                         Services and components that are disabled by default in SQL Server
                         2005 include: the Microsoft .NET Framework, SQL Service Broker
                         Network Connectivity, and HTTP connectivity in Analysis Services.
                         Other services such as SQL Server Agent, full-text search, and the
                         new Data Transformation Services (DTS) service are set to start up
                         manually and require explicit action to be set to start automatically.
Granular permission      A new security model in SQL Server 2005 allows administrators to
control                  manage permissions at a granular level and at a designated scope,
                         making management of permissions easier as well as ensuring that
                         the principle of least privileges is upheld.
Separation of users and SQL Server 2005 simplifies security administration by separating the
schema                   implicit link between users and the database objects that they own. For
                         example, in earlier versions of SQL Server, if you wanted to remove a
                         user, you had to first drop or reassign ownership of all database
                         objects that the user owned which significantly complicated the
                         process and potentially impacted a large number of applications. With
                         the new model, dropping users does not require an application
                         change.
Enforced password        Administrators are able to specify Microsoft Windows–style policies on
policy for standard      standard logins so that a consistent policy is applied across all
logins                   accounts in the domain.
Execution context on     SQL Server 2005 allows you to specify a context under which
modules                  statements in a module execute. This feature also acts as an excellent
                         mechanism for granular permission management.
Data Definition          With SQL Server 2005 you are able to specify triggers on DDL
Language (DDL)           operations, providing a supplemental mechanism for auditing DDL
triggers                 actions.
Data encryption within SQL Server 2005 supports encryption capabilities within the database
the database             itself, fully integrated with a key management infrastructure.
Clustering               SQL Server 2005 clustering supports Kerberos authentication against
authentication           a SQL Server 2005 virtual server.
Multiple proxy accounts SQL Server Agent supports multiple proxy accounts (one per job
                         subsystem).
No dependency on the SQL Server Agent no longer requires access to the LSA to use proxy
Local Security Authority accounts. Therefore, SQL Server Agent no longer requires the service
(LSA) database           to run as a local administrator for it to be enabled.
SQL Profiler no longer A new permission is available in SQL Server 2005 that allows users
requires system          who do not have system administrator rights to run SQL Profiler.
administrator rights



                                                                                              27
Analysis of IBM DB2® and Microsoft SQL Server 2005®

Analysis server             By default, client/server communications are encrypted. To centralize
communication               security assurance, server policy can be defined to reject unencrypted
encryption with server-     communications.
defined policies
Granular administrative  More administrative permissions are available in SQL Server 2005. In
roles for Analysis       addition to online analytical processing (OLAP) administrators,
server                   database administrators are able to possess administrative
                         permissions within the context of an individual database. New
                         permissions on objects enable users to see the object definition
                         (without being able to access the object itself) and to process an
                         object.
SQL Server Agent job SQL Server Agent has been enhanced to support assigning rights over
roles                    jobs in a granular fashion.
New tools and Help files A set of new deployment tools and documentation helps ensure that
                         SQL Server 2005 can be securely deployed into an existing SQL
                         Server topology or a new installation. These tools provide a step-by-
                         step approach by giving detailed information, analyzing the existing
                         topology, checking for prerequisites, recommending a configuration
                         setting, and validating each step.
Improved auditing        SQL Server 2005 Analysis Services includes new auditing capabilities
capability for Analysis integrated with SQL Profiler.
Services
Security bulletins       Microsoft will publish security bulletins and patches as appropriate for
                         SQL Server 2005. These bulletins help you understand and assess
                         potential threats to your existing environments, and how to neutralize
                         those threats.
Microsoft Internet       If you plan to deploy SQL Server 2005 on a Windows 2000 Server
Information Services     platform, the IIS Lockdown Wizard is a powerful tool for securing your
(IIS) Lockdown Wizard Web server environment. IIS Lockdown Wizard works by turning off
                         features that are unnecessary in your environment, thereby reducing
                         the exposed potential surface available to attack. To provide defense
                         in multiple layers of protection against attackers, a tool called
                         URLScan, with customized templates for each supported server role,
                         is integrated into the IIS Lockdown Wizard.

                            If you are deploying SQL Server 2005 on a Windows Server 2003
                            platform, the IIS Lockdown Wizard is integrated into IIS 6.0.

Source: SQL Server 2005 Security Features at a Glance
URL: http://www.microsoft.com/sql/technologies/security/security-at-a-glance.mspx
Published: November 7, 2005




                                                                                                28
Analysis of IBM DB2® and Microsoft SQL Server 2005®




        Glossary
• Clustering – software feature used with the Database to create larger data warehouse by
splitting the data between multiple instances of the database.


• Database – relational database software used to store the raw data that makes up the data
warehouse


• Data Mining – software used to find patterns and aberrations in the data that might lead to
useful business insight.


• Data Visualization – software used to display patterns and aberrations in the data using visual
display such human analyst can discover additional business insight.


• Partitioning – software feature that allows the data from a single table to be split into chunks
within the data warehouse such that it is easier to manage and to use (required for scaling of
data)


• ETL – Extract, Transformation, and Load software used to extract data from operational (e.g.,
OLTP) and other source systems. The software also needs to be able to transform the data into a
suitable format and encoding and then load it into the data warehouse.


• Query Manager – software used to monitor the performance of the queries and to control what
queries can run and when. This option is needed to keep one user from using all of the resources
of the data warehouse.


• OLAP – On-Line Analytical Processing, software used to create a multidimensional view of the
data for quick and efficient access to aggregated or summary data.


• SQL- Standard Query Language




                                                                                                     29
Analysis of IBM DB2® and Microsoft SQL Server 2005®




        Bibliography
Are Open Source Databases Following in Linux' Footsteps?; Clint Boulton, June 13, 2003
URL: http://www.internetnews.com/ent-news/article.php/10794_2221901_1
Date Accessed: 6-Apr-2006
Database System Concepts (Fifth Edtion), Silberschatz, , Korth, Sudarshan; 2006
Chapter 28: IBM DB2 UDB 8; Chapter 29: Microsoft SQL Server

DB2, Wikipedia
URL: http://en.wikipedia.org/wiki/DB2
Date Accessed: 6-Apr-2006

DB2 Universal Database for Linux, UNIX and Windows
URL: http://www-306.ibm.com/software/data/db2/udb/
Date Accessed: 6-Apr-2006
IBM DB2 Version 8.1 and SQL Server 2005 Price Comparison
URL: http://www.microsoft.com/sql/prodinfo/compare/ibm/db2v8.mspx
Date Accessed: 6-Apr-2006
Microsoft SQL Server Official Website
URL: http://www.microsoft.com/sql/default.mspx
Date Accessed: 6-Apr-2006
Next SQL Server Stop: Katmai, Barbara Darrow, CRN; 11:12 PM EDT Tue. Sep. 13, 2005
URL: http://www.crn.com/sections/microsoft/microsoft.jhtml?articleId=170702999
Date Accessed: 6-Apr-2006

Open Source, Open Questions, Rick Whiting; InformationWeek, July 19, 2004
URL: http://www.informationweek.com/story/showArticle.jhtml?articleID=23901139
Date Accessed: 6-Apr-2006

Practical Introduction to SQL Server Mobile
URL: http://msmobiles.com/catalog/i.php/564.html
Date Accessed: 6-Apr-2006

Playing a Role in SQL Server's BI Future; Brian Moran, InstantDoc #49640, March 9, 2006
URL: http://www.sqlmag.com/Articles/Print.cfm?ArticleID=49640
Date Accessed: 6-Apr-2006

Top Ten TPC-C by Performance Version 5 Results
URL: http://www.tpc.org/tpcc/results/tpcc_perf_results.asp
Date Accessed: 6-Apr-2006 4:54 AM [GMT]

Top Ten TPC-C by Price/Performance Version 5 Results
URL: http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp
Date Accessed: 6-Apr-2006 4:54 AM [GMT]

Total Cost of Ownership, Jacqueline Emigh; December 20, 1999
URL: http://www.computerworld.com/hardwaretopics/hardware/story/0,10801,42717,00.html
Date Accessed: 6-Apr-2006




                                                                                          30

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:9/20/2011
language:English
pages:30